Archive for January, 2007

Simple Method for Tuning SQL Server

January 26th, 2007

I have written the following script and have found it to be a rather effective, minimally invasive, and low-tech way to track down troublesome SQL statements when tuning my apps for SQL Server. I’ll usually set it up as a SQL Server Agent job to run once per minute. It logs all currently running SQL statements to the SQLLog table, for analysis at a later date. (Note that the job must be run as the sa user, or another user with sysadmin privileges in order for the fn_get_sql function to work.) The thing I like about this technique is that it seems to get a good cross-section of both long-running statements, and statements that may be short in duration, but are run often. I’ll then do a count on statements to see where to focus my tuning efforts. Enjoy!

Read the rest of this entry »