RE: [SQLDownUnder] SQL Performance Testing

[Greg Low]

Hi Geoff,

 

If you get a chance, I’d be looking to talk to the users and find out their opinions too. Find out their pain points.

 

In terms of profiling, I’d be looking to set up traces on the production system (using server-side traces if necessary) rather than trying to work out how it’s used and trying to duplicate it.

 

I like to keep traces at each step of the way so I can compare what I’ve achieved over time. At first, I’m only interested in SQL Batch Completed and RPC Completed.

 

I normally start by saving the trace to a table and adding a column that I extract the underlying statement in to. If it’s a stored proc execution that’s pretty easy but otherwise I have some functions I use to try to find what’s going on in the statement. I then write queries to group the trace by the underlying command and sort by logical reads descending. That tells me pretty clearly where the system is spending its time. I normally find that well over half the total logical reads are related to a handful of queries.

 

I then look at those queries very carefully and do what I can to make them have far less impact.

 

I repeat the process until I’m happier with the outcome.

 

I then sort them by number of executions descending. This lets me find calls that are being made unnecessarily ie: things that are being requested constantly of the database and should have been cached in the web servers. This is often also quite telling.

 

I then filter the overall traces for anything with high logical reads. Physical reads are rarely of interest to me when doing this. If the system is reasonable, I normally set a bar where anything that takes more than about 1000 logical reads is “of interest”. (For some messy systems, that might be higher).  Anything that causes this is of interest to me.

 

Once I’ve gotten rid of anything that’s taking a lot of logical reads, I then look for blocking issues. I have two basic strategies for this. One involves looking for high duration queries in traces but my favourite way is to run a script that looks every ten seconds or so for any process that’s at the head of a blocking chain. Any one that is causing blocking, I look up what command they were executing and log it to a table. I let this process run for an hour or two. That will show me the main culprits that are blocking other code. I then look to reduce the blocking as far as possible. On most systems, you can usually remove most of it.

 

By the time you get to this point, things are usually pretty good.

Regards,

Greg

 

Dr Greg Low
Readify | Senior Consultant

Suite 206 Nolan Tower | 29 Rakaia Way | Docklands | VIC 3008 | Australia

M: +61 419 201 410 | E: mailto:greg.low@xxxxxxxxxxx | W: www.readify.net

 

From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Geoff Orr
Sent: Thursday, 6 September 2007 9:12 AM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: [SQLDownUnder] SQL Performance Testing

 

Guys and Gals

 

I have been asked to prepare a performance testing suggestions for a SQL based web site.

 

So how do I check my SQL and SQL server is up to the money.

 

Here is my starting point

 

1.       Build a test plan of the access paths for the system i.e. the search criteria used to access data

2.       Run a profiler whilst running the tests and see execution times over 1 sec (any query over a 1 sec is slow)

3.       Check the logical and physical reads.  If there is more than 5 times no of rows there may be an issue.

4.       Extract SQL into test script and measure overall times as a benchmark.

5.       Vary the data and run in a number of sessions

6.       Any statements that are “suspect” i.e. slow or read too much do a detailed examination of the query plan.

 

Any other suggestions.

 

Geoff Orr

FrontRow Data Solutions

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net