SQL Server Performance Tuning: Pt. 1

Tuesday Jul 31st 2001 by Aaron Goldman
Share:

Author Aaron Goldman details basic SQL Server optimization techniques. Part I leads you through a system analysis, teaching you how to discover which aspects of your system are causing bottlenecks.

Oftentimes, even with ever increasing speed and the reduced price of hardware, a process will be too slow. Your users will typically be the ones complaining of slow performance, or you might have batch processes that don't finish within their time window. Faster processing will also help with deadlocks and concurrency issues.

This paper details basic SQL Server optimization techniques which have been successful for me.

Coding

The first rule of optimization, as one of my professors used to say, is: "Don't". That's also the second rule, too.

What he meant, assuming I understood correctly, was that in the general course of writing systems, very few things will actually need to be optimized. Either because they run rarely or because the datasets are not large or the speed of the hardware and compiler will make up for our shortcomings. Furthermore, we are not likely to avoid the weakest parts by vigilance at the coding stage--it's like looking for a needle in a haystack. It's usually 1% or less in a system that is too slow. If we waste our time paying attention to performance all the time, it will ending up costing far more than it will likely benefit.

When coding, you'll do better by disregarding optimization and instead being mindful of the best practices and the functional requirements.

Identification

So, after much of the coding is complete, you will test a system and find that some aspects of it are too slow. Or more likely, you will monitor your production system CPU with Performance Monitor and see that it maxes out more than you would like for longer than you would like. Or you may be hit with a process, likely one that runs in batch mode, that just doesnt seem to finish.

In SQL programming it's usually pretty easy to narrow down your search for slow processes because the starting point for processes is easily identifiable as a single stored procedure call. And, having that starting point, SQL trace will pinpoint exactly which statements take the most amount of time.

On the other hand, if your entire system is just slow, and CPU usage seems too high and you don't know why, you can easily setup a Profiler trace to find the offending code.

What to Look For

The usual case is a statement or process that takes up too much CPU time. This is the first thing I primarily look for. More rare nowadays is the process that experiences poor performance from too many disk accesses.

SQL Profiler nee Trace

SQL Profiler is the main tool I use in optimization. You can read about it in BOL and in almost any SQL Server book, so I won't go into much detail here. An article about SQL Profiler can also be found in technet.

I like to set up the trace to log to a table so it can be quickly queried and searched. You can put the table on a different server from your trace. You set this up by checking "Capture to Table" at the bottom of the General tab in the Trace setup dialog as you can see below:

SQL 7.0 Trace Setup
SQL 7.0 Trace Setup
(Click image for full size)

SQL 2000 Trace Setup
SQL 2000 Trace Setup
(Click image for full size)

Also, you will probably want to limit your trace to just capture your own activities so other users don't clog up your data. If I'm testing in Query Analyzer, I limit the trace to my own spid (SELECT @@spid) on the Filters tab of the trace setup. Alternatively, selecting database, username, program, or hostname (your client PC) can be useful. Combining trace filters can be quirky and often doesn't yield expected results--go figure...

If you have a very long process with many different stored procedures, you may need to trace it twice. The first time, just trace stored procedure completion. This will tell you which procedure(s) are slow. The second time, trace statement completion of the particular slow stored procedures as you run them manually in testing or by filtering for the procedure name pattern (procName%) in the TextData filter. You can also request CPU > 100 ms (or your threshold of choice). This will reduce your logs to a manageable size. This is also the method for investigating a server where you have no idea what the slow process is.

If doing a long trace on a server of a day or so, you may want to filter on CPU > 100 and occassionally remove rows with NULL in the CPU column to keep the size of the log table from growing too large.

Trace Stored Procedures
Trace Stored Procedures
First trace just stored procedures to find which ones are slow.

Trace Statements Within Procedures
Trace Statements Within Procedures
Then trace statements inside
the stored procedures to find which statements need to be optimized.

The trace table will have more than enough information to find the slow parts in SQL Code. You can just select your answer from the trace table:

SELECT CPU, TextData 
   from TraceTable 
   ORDER BY CPU desc

If statements are traced, this will be your final answer. If it's a procedure-only trace, you will have to run the procedure(s) and trace statements in the suspect procedures.

If you have procedures, you can dig deeper with an aggregate query:

select sum(cpu) 'sum cpu', 
                  avg(cpu) 'avg cpu',
                  max(cpu) 'max cpu',
                  count(cpu) 'times run' 
   left(cast(textdata as varchar(128)),
        isnull(nullif (CHARINDEX (' ', cast(textdata as varchar(5000))),  0) , 255))
   from trace_table
   where cpu is not null
         and textData is not null
   group by left(cast(textdata as varchar(128)),
                 isnull(nullif (CHARINDEX (' ', cast(textdata as varchar(5000))), 0) , 255))
   order by avg(cpu)

This query does a good enough job of extracting the stored procedure names from the full text (stripping the parameters) and showing which ones are slow, how often they run, etc. Once I find the slow ones, I extract the full text from the trace table so I have examples to test with:

select distinct 
   left('exec ' + rtrim(cast(textdata as varchar(5000))) , 145),
       char(13)
   from trace_table
   where cpu is not null
         and textData like 'procName%'
   order by cpu desc

A simple way to minimize the costs vs. the benefits is, of course, to fix the longest running queries first.

Show Execution Plan

A decent adjunct to the Profiler is the graphical Execution Plan available in the query analyzer. Just select the Query Menu and near the bottom is "Show Execution Plan". Select it and run a query or stored procedure and the execution plan will be shown graphically. You can also show the execution plan without running the query by selecting "Display Estimated Execution Plan" under Query Menu (or Ctl-L).

The information shown is all you need to know to attack an optimization problem. It tells what the execution strategy is and the relative cost of each piece. Optimization will consist of either speeding up individual parts, most likely with indexes, or making the database engine choose a different execution strategy altogether by reformatting the query or rearchitecting the data.

If more than one statement is executed, as in a batch or procedure, or inside a trigger, they will all be graphed and the relative costs will be displayed:

Execution Plan Screen Shot
(Click image for full size)

Reading the output can be something of an art, especially for complex queries involving many tables where the picture is so large it's difficult to see many of the pieces at once. It's not truly necessary to know what all the icons and terminology mean, but only to understand how they relate to the query and point to the slow operations. A gut feel will suffice.

To optimize a query, you should check each piece for its relative cost. Find the one or two spots with a high cost. In the above picture there is one piece that takes 21% and everything else is negligible. When optimizing a query you can usually just concentrate on minimizing the most expensive parts, while ignoring the rest.

If you hover the mouse over an icon, you can see its details:

Execution Plan Details (from Mouseover)
(Click image for full size)

Common things to look for are using an unexpected index and getting a large rowcount when you believe the query should be easily restricted on this table.

There's also a text based execution plan you can use, but I prefer the graphical output.

Statistics IO

In Query Analyzer you can turn on the display of statistics by setting the session option:

Set Statistics IO ON

Or by changing the current connection properties under the Query menu:

SQL 7.0 Connection Options
SQL 7.0 Connection Options
(Click image for full size)

SQL 2000 Connection Options
SQL 2000 Connection Options
(Click image for full size)

Here is sample output for Statistics IO: [Editor's note: lines in the following output were reformatted for better display on this Web page.]

Table 'dbi'. 
  Scan count 1, logical reads 3, 
  physical reads 0, read-ahead reads 0.
Table 'matter_resolution'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'dbi_bm'. 
  Scan count 1, logical reads 5, 
  physical reads 2, read-ahead reads 0.
Table 'dbi'. 
  Scan count 1, logical reads 3, 
  physical reads 1, read-ahead reads 0.
Table 'initial_matter'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'processing_summary'. 
  Scan count 5, logical reads 2892, 
  physical reads 0, read-ahead reads 209.
Table 'amount_detail'. 
  Scan count 2, logical reads 2091, 
  physical reads 0, read-ahead reads 2096.
Table 'system_user'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'case_type_lu'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'processing_action_type_lu'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'dbi_bm_charge_event'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.
Table 'event_by_dbi_bm'. 
  Scan count 0, logical reads 0, 
  physical reads 0, read-ahead reads 0.

Pages accessed is the important thing here. The output shows that the two tables processing_summary and amount_detail are the worst performing parts of the query with logical read counts in the thousands whereas the other tables only call for a few pages each.

Statistics IO is good for a quick read, but is very difficult to use for long stored procedures because the output is not delimited. There is no quick way to tell which SQL statement these statistics refer to.

Some Definitions:

Page: A unit of storage in SQL Server. In Version 7 a page is 8K and stores data, indexes, or code. Whenever SQL Server is looking through a table or an index, it pulls data from memory or disk a page at a time.

Data Page: As used in this article, a page containing table data as opposed to index data. Index entries point to table data pages.

Query Plan: The exact steps SQL Server uses to access or update data.

Optimizer: The component in SQL Server which generates a query plan.

Table Scan: Searching for data by checking each row in the table one by one--very slow for medium and larger data sets, quick for small datasets.

Identifying the Problem

The details from the trace will tell you exactly where your problem is, but you still have to figure out what it is.

The actual process of optimization will be trial and error. You will likely have to run the offending statements several times until you get them right. If your code is essentially a select which doesn't modify data, you can just run it over and over. But if it modifies data you should run it in a transaction/rollback block so you can repeat it:

A simple select:

Select * from authors
  where phone like '408%'

or

exec sp_selectStatement

An updating statement:

begin tran
  go

  update authors
     set phone = 'N/A'
     where phone like '408%'
  go

  rollback
  go

or

begin tran
  go

  exec sp_updatingStatement
  go

  rollback
  go

Use SQL Profiler, Query Analyzer's Show Execution Plan, and/or Statistics IO to see how long each statement is taking paying attention to cpu time.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved