SQL Server 2000 Performance Guidelines - Tuning

From Guidance Share

Jump to: navigation, search

- J.D. Meier, Srinath Vasireddy, Ashish Babbar, Sharon Bjeletich and Alex Mackman


Use SQL Profiler to Identify Long-Running Queries

Use the SQL Profiler SQLProfiler TSQL_Duration template to identify queries with the longest durations. Long-running queries have the greatest potential for locking resources longer, blocking other users, and limiting scalability. They are also the best candidates for optimization. Reviewing long-running queries is a continuous process and requires representative loads to ensure effective tuning.

In some cases, using the SQL Profiler templates is somewhat limiting when you use them to measure the change in performance while you test new indexes or application design changes. SQL Profiler can become a very powerful baseline tool when you save the results of your performance test as a trace file. Trace files use the .trc extension. Beginning with SQL Server 2000, you can use these trace files to write automated reports that quantitatively measure gains in the performance of your application for certain query types that otherwise would not be grouped properly when using the template. There is the clever fn_trace_gettable trace reporting function. This trace function is shown in the following sample code:

  SELECT Count(*) as CountOfEvents,
  AVG(Duration) AS AvgDuration,
  SUM(Duration) AS [SumDuration],
  SUBSTRING(TextData, 1, 30) AS [Text Data]
  FROM ::fn_trace_gettable('F:\MyTrace.trc',default)
  WHERE EventClass in (10,12) -- BatchCompleted, RPC Completed
  GROUP BY SUBSTRING(TextData, 1, 30)

Take Note of Small Queries Called Often

Often, small queries that run fairly quickly but that are called often are overlooked. Use SQL Profiler to identify queries that are called often, and then try to optimize them. Optimizing one query that runs hundreds of times may be more effective than optimizing one long-running query that runs only once.

You can modify the fn_trace_gettable sample code to order by SUM(CPU) so that it becomes an effective tool for identifying small queries or stored procedures that may be called thousands of times in an hour. When you tally their CPU costs, these queries can represent a huge expense to the overall performance of your SQL Server. By correctly designing indexes and avoiding bookmark lookups, you can shorten each call by milliseconds. Over time, this can amount to a big saving.

This kind of reporting is also useful for arranging small queries by reads and writes. Before you upgrade your subsystem, consider using these reporting methods to identify small queries. You can then plan application design changes that may help consolidate these small queries into larger batches.

Use Sp_lock and Sp_who2 to Evaluate Locking and Blocking

Use the sp_ lock and sp_who2 system stored procedures to find out which locks are acquired by your query. You should use the least restrictive locks possible. Investigate queries that result in table locks. These table locks block other users from accessing the entire table and can severely limit scalability.

Evaluate Waittype and Waittime in master..sysprocesses

OLTP servers may report degradation of simple insert operations, update operations, and delete operations over time. The increase in average duration might occur due to a sysprocesses.waittype value of 0x0081. This is the log writer, and this waittype value means there is a delay in execution while your system process ID (SPID) waits on the two-phase commit process to the transaction log. You can measure this delay by capturing the sysprocesses.waittime value. This value may indicate that your transaction log is on the same spindle set (volume) as your data. It may also indicate that you do not have an adequate I/O subsystem where the log file exists, or that you have an inappropriately configured IO subsystem where the log file exists.

Your database administrator should also pay close attention to common locked resources. These can indicate a specific problem in a particular table. Specific Waittype values can be an early indication that your server is underpowered or overusing the disk, the CPU, or the transaction log. You can find out if your server is underpowered or overusing resources by taking snapshots of the sysprocesses and syslockinfo tables approximately every five seconds and by measuring how long a SPID waited.

More Information

For more information about sp_lock , see "sp_lock" in the "Transact-SQL Reference" on MSDN at http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_la-lz_6cdn.asp.

For more information about how to analyze blocking, see the following Knowledge Base articles:

Use DBCC OPENTRAN to Locate Long-Running Transactions

Run the DBCC OPENTRAN command to discover transactions that are open for long periods of time. This command has to be run repeatedly over the discovery period because transactions come and go. However, transactions that are reported continuously by this command may be running too long or are not committing at all. You should investigate these transactions.

Personal tools