Couldn't agree more. Chase the 10 offenders and your database will get a lot better. I already do this with DMVs and I should say that it is the second most important part of my job as DBA. The first one is to secure the data.

Marcos5/25/2009 1:44:16 PM

Yes it is amazing how much performance you can gain by addressing just a relatively few number of queries in most systems. The query_hash column in 2008 will be of great help to people running SQL 2008 and I was thinking of writing about that in a future article as well.

Andrew11/13/2008 5:46:05 PM

I like to use the DMVs to get a lot of this information. Particularly the sys.dm_exec_query_stats. And in SQL Server 2008, the view has a new column called query_hash. This column is similar to the SQL_Signature defined above, but in a hashed format so it's useful for grouping when aggregating.

I'm also a big fan of getting the biggest bang for your effort and so I totally agree with the tip on tackling the top 10 offenders in order to reduce 90 percent of the problems. I've also heard this as the 20-80 rule and talk about it quite a bit at
http://dbwhisperer.blogspot.com/2008/08/80-20-rule-and-sql-server-performance.html
and
http://dbwhisperer.blogspot.com/2008/10/reporting-services-is-fun-again.html

Michael11/4/2008 9:52:48 AM