SQL Profiler and Tuning Advisor

Question

We are having problems with database performance, and I have a lot of experience with .NET Profilers and always perform the analysis on the app, but much like a lot of developers I am now waiting until really late (when its a problem) to start analyzing and trying to gather the data on how to fix the issue.

This is probably not going to be a one answer post just more of a "HELP I AM A DB IDIOT" post and looking for any direction personal advise, recommendations, and experience on how to track down issues.

As far as the setup we use SQL 2005, I have very limited access in production to only run SQL Database Engine Tuning Advisor, and SQL Profiler through a portal interface, I can copy and paste but that's about it. One key thing I would like to do is get a true snap shot of production queries and calls so I can load those into the tuning engine in a lower environment that I can try to nail the DB so I can get the recommendations from the Engine Tuning Advisor.

Answer 1

This script can be used to determine if you have choosen the right indexes. You need to look at how often the index is used for seek and compare it to how often the index is updated. Seek performance comes at the cost of update performance. And what is worse, when index is frequently updated you causes the index to be fragmented and the statistics to be out of date.

You should also compare the range_scan_count to singleton_lookup_count. Range scan is preferred before singleton lookup. A singleton lookup may be the cause of and index seek and a key lookup operation. That is, for every row found in the index seek, sql will lookup the datapage in the clustered index, and that is okay for lets say a couple of thousands, but not for millions of rows.

CREATE PROCEDURE [ADMIN].[spIndexCostBenefit]
    @dbname [nvarchar](75)
WITH EXECUTE AS CALLER
AS
--set @dbname='Chess'
declare @dbid nvarchar(5)
declare @sql nvarchar(2000)
select @dbid = convert(nvarchar(5),db_id(@dbname))

set @sql=N'select ''object'' = object_name(iu.object_id, iu.database_id)
        , i.name
        ,''user reads'' = iu.user_seeks + iu.user_scans + iu.user_lookups
        ,''system reads'' = iu.system_seeks + iu.system_scans + iu.system_lookups
        ,''user writes'' = iu.user_updates
        ,''system writes'' = iu.system_updates
from '+ @dbname + '.sys.dm_db_index_usage_stats iu
,' + @dbname + '.sys.indexes i
where 
    iu.database_id = ' + @dbid + '
    and iu.index_id=i.index_id
    and iu.object_id=i.object_id
    and (iu.user_seeks + iu.user_scans + iu.user_lookups)<iu.user_updates
order by ''user reads'' desc'

exec sp_executesql @sql

set @sql=N'SELECT
   ''object'' = object_name(o.object_id, o.database_id),
   o.index_id,
   ''usage_reads'' = user_seeks + user_scans + user_lookups,
   ''operational_reads'' = range_scan_count + singleton_lookup_count,
   range_scan_count,
   singleton_lookup_count,
   ''usage writes'' = user_updates,
   ''operational_leaf_writes'' = leaf_insert_count + leaf_update_count + leaf_delete_count,
   leaf_insert_count,
   leaf_update_count,
   leaf_delete_count,
   ''operational_leaf_page_splits'' = leaf_allocation_count,
   ''operational_nonleaf_writes'' = nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count,
   ''operational_nonleaf_page_splits'' = nonleaf_allocation_count
FROM
   ' + @dbname + '.sys.dm_db_index_operational_stats(' + @dbid + ', NULL, NULL, NULL) o,
   ' + @dbname + '.sys.dm_db_index_usage_stats u
WHERE
   u.object_id = o.object_id
   AND u.index_id = o.index_id
ORDER BY
   operational_reads DESC,
   operational_leaf_writes,
   operational_nonleaf_writes'

exec sp_executesql @sql

GO

Answer 2

Here are some links that should get you started in your quest for performance.

What resources exist for Database performance-tuning?

Favourite performance tuning tricks

What generic techniques can be applied to optimize SQL queries?

Best way to improve performance (and include somehow failover)