There are many reasons why people choose a Client Server database like Microsoft SQL Server 7.0 or 2000. These industrial strength database servers reduce the dataflow over a WAN, allow hundreds or even thousands of simultaneous users, allow the backing up of the database while it is in use, are scalable (using all the memory and processors you can throw at it) and are virtually trouble free.

However, there are still two things that are absolutely necessary to be done, Tuning and Maintenance. Tuning consists of setting up the server and software in the best way possible to optimize performance and stability. Maintenance is those things necessary to be performed on a regular basis to keep the system running well.

Note: before you even consider doing work to your SQL Server make sure that you have a backup of your database(s) just in case. Do two separate backups on different media just as a safety measure. Also FYI backing up the mssql7 directory on the server doesn't back up the database. You have to use an agent to allow your backup software (ie. Veritas Backup Exec) to backup the database(s) or you can use enterprise manager to backup the database(s)

Tuning
SQL Server (SQL) loves memory. The more you throw at it the more that it uses and the faster the program runs. SQL has a dynamic memory allocation feature that divides the memory up between the operating system and SQL.

Monitor the memory use in SQL by using the Performance Monitor. Watch the Page Faults / sec counter. If page faults are being continuously being generated (after system and SQL server startup) you don’t have enough memory.

a. Run Everything
Make sure you are running everything that normally runs on the SQL server computer. Have a server dedicated to SQL.

b. High cache-hit ration
You want to keep a high cache-hit ratio. If it is below 85%-90%, you might benefit from increased memory. If you are continuously experiencing page faults, you need more memory.

c. Monitoring SQL
You wouldn’t drive a car without a dashboard. Yet most people run their computers without ever looking at their “dashboard”. The dashboard in an NT or 2000 server is very sophisticated. It allows you to decide what ‘gauges’ you want on your dash. However, you can go crazy from too much choice. There are probably 256 or so choices. Instead of trying to work with everything you should concentrate on:

1. Disk I/O (input / output)
2. Processor
3. Memory
4. User connections
5. Network

d. To get into the System Monitor

1. Click the Start button
2. Point to Settings, Control Panel
3. From the Control. Panel, double –click Administrative Tools
4. From the Administrative Tools, double-click Performance. The Performance dialog now appears.

e. Adding Counters

1. From the Performance dialog box, click the Add button on the toolbar. The Add Counters dialog box appears.
2. Click the Add object type and then counter type. Click the Add button to add the counter to the chart.
3. Additionally you might need to choose the appropriate instance of a counter, e.g., Database.
4. Click close

f. Changing the Refresh Rate
Sometimes you can lose what is happening if the refresh rate occurs too often. You might want to change the update frequency so it occurs every 10-15 seconds, instead of every second.Go to the properties icon in the performance toolbar. The System Monitor Properties box pops up. Choose General and change the Update Automatically to the value you want.

g. Changing the scale of the System Monitor
The default scale may not always be appropriate, being too large to show small, but very significant changes. To change this, go to Properties of the counter and choose a more appropriate vertical scale.

h. Logging the System Monitor to a file
You can save the information created by the System Monitor by clicking on the Performance Logs and Alserts section of the treeview. Right-cllick the Counter Logs folder. From the right-mouse menu select New Log Settings. Enter a log name and click OK. Add the counters to track. Click the OK button this will start the logging. When you are ready to stop logging right-click the name of the log file and select the Stop menu option.

To look at the log file, first click on the System Monitor Section of the treeview. Click on the View Log File Data toolbar button. A Select Log File dialog box appears. Pick the file name you entered above and click the Open button. This loads the corresponding counters into the System Monitor. Click the Add toolbar button to select an object for the log file. This allows you to view the data inside the log file in a graphic form.

i. Disk I/O
You want to minimize disk I/O when working with SQL, since the physical movement of the hard disk is one of the slowest things in a computer (besides your typing). If you do have to access the hard disk, you want it to occur a quickly as possible.
Two counters monitor this.

PhysicalDisk: % Disk Time
Measures the percentage of elapsed time tat the disk is busy with read/write activity. A high value that is consistant, such as above 2, might indicate that your disk system is the bottleneck.

PhysicalDisk: Current Disk Queue Length (choose total instance if you are using more than one disk)
This tracks the number of outstanding requests on the disk. If this number stays greater than 3 might show a disk-related bottleneck. If one disk value is large and the other low, redistributing your data might help.

j. Processor
SQL uses the processor a lot. Values that remain high for long periods might indicate that your CPU is the bottleneck. If the CPU is the bottleneck, the Processor Time Counter will show it.

Processor: % Processor Time Counter
A value that stays consistently above 80% to 90% for a period of time, indicates that a faster processor or multiple processors might help.

k. Memory
We already said that SQL needs lots of memory. It uses it to not only run a program, but it also holds data and frequently used objects. If it’s in memory, it’s accessed electronically, rather than having to physically move the heads and read the hard drive.

SQLServer: BufferManager: Buffer Cache Hit Ratio Counter
This tracks the rate at which data was found if the data cache. If the data is not in the data cache, it has to go to the hard disk. If consistently less than 85%, this indicates you don’t have enough memory.

Memory: Page Faults/sec Counter
This tracks the number of times the operating system doesn’t find a virtual page in memory and has to go to disk to find it. This is much slower than pulling it out of memory. A number running over 85% for a period of time indicates not enough memory.

Paging File: % Usage Counter
This shows the percent of NT’s paging file currently in use. If a large percentage of the page file is in use, you might want to increase the size of the paging file to prevent an out-of-virtual-memory error. You can also add more memory to your sever. A growing page file occurs when you specify an intial page fle size smaller than the specified maximum size. Growing this file is a slow proposition. It is better to set the intial page file size to be the same as your maximum page file size. The operating system is then released from the task of growing the file.

SQLServer: Buffer Manager: Lazy Writes/sec Counter
This measures the number of pages flushed per second from the memory to disk, since the data cache is too small. This probably indicates memory is too small if the number remains greater than zero for any length of time.

l. User Connections
SQLServer: General Statistics: User Connections Counter
How many people do you actively using the SQL database at one time. If you have spikes where the CPU utilitzation goes up at the same time that user connections increase, you may want to find out why?

m. Network
NBT Connection: Bytes Total/sec Counter
This reads the number of bytes read to and written to the network. This can help if you are not using stored procedures. If you find that this counter is high and your transaction rate is low, stored procedures will help. If the counter is extremely high for an extended period, you can probably improve performance by using faster network cards.

n. Other Tools
The SQL Server Profiler and the Current Activity Monitor in the SQL Server Enterprise Manager can provide additional tools for looking at individual user actions and performance of queries.

Maintenance
There are several things that should be done to a SQL database on a regular basis to maintain its speed and keep it in good shape. Like a car, a database needs periodic servicing and good preventive maintenance.
This can be divided up into five major areas:

SQL Server maintenance
Database maintenance
Table/object maintenance
Job Maintenance
Windows NT/2000/XP Maintenance

a. SQL Server Maintenance
1. Monitor error logs
2. Record configuration information
3. Manage logins

b. Database Maintenance
1. Backup your database and transaction log
2 . Test your backup and recovery strategy
3. Audit database access

c. Table/Object Maintenance
1. Monitor the record count
2. Audit object permissions

d. Job Maintenance
1. Job status
2. Schedule
3. Duration
4. Output

e. Windows NT/2000/XP Maintenance
1. Monitor the Windows NT event log
2. Back up the Registry
3. Keep the Emergency Repair disk current
4. Run disk defragmentation Utilities
5. Monitor available disk space
6. Monitor CPU and memory Usage

Making the SQL Database Smaller

a. Shrink the Database
The Shrinkdatabase command reduces the physical size of the database. If no parameter is set, it is shrunk to the smallest size possible for the database.

b. Truncate and Shrink the Transaction Log
Backing up the transaction log and clearing inactive entries

1. Open Enterprise manager
2. Open databases
3. Single click on your Database (CLIENTST2000SQL) to highlight it
4. Click on tools
5. From the pull down click backup database
6. Under the backup section click the radio button next to transaction log
7. Click the options tab at the top
8. Make sure that there is a check mark next to the Remove inactive entries from transaction log.
9. At the bottom click ok Note: At this point it will start the process and you will see a progress bar showing you the progress after it completes it will come back and give you a window (The backup operation has been completed successfully.)
10. Click OK

Note: This should drastically reduce the size of your transaction log. another good procedure to keep your data clean is to shrink your database make sure when you shrink your database that you have the (reorganize database- move data pages to the beginning of the files) checked you can schedule this if you would like. Additional procedure that you can do is to truncate the transaction log T his is supposed to remove inactive entries from the log but when we did it we didn't see any difference in the LDF file (the transaction log file located in the mssql7\data directory on the server). We noticed a difference in the transaction log after we did the backup and had the backup option selected to remove the inactive entries as explained in the above procedure.

Copyright © 2002 All rights reserved Kemp's Case Works, Inc.