Easy performance metrics for SQL Server 2000

Question

The reports that I use (and update) are taking a long time (some take hours). I felt this is far too long and asked previously about this. After taking a long look at various web sites that discuss SQL performance, they all take the stance of being DBA's. However I'm not, and neither are my colleagues (I guess if we had a DBA then we wouldn't have this problem).

What I want is a simple way of returning the top 10 or so most run and worst performing scripts. I would of hoped there is a nice SET METRICS ON switch, but I guess if that was the case then the sites wouldn't go on about recording profiles.

The last thing I want to do is to cause performance to drop even further and recording a profile sounds like a performance killer.

Answer 1

You have at least following options.

• look at the plan of a bad performing query in SQL Analyzer and try to optimize it, query by query from there.
• or use a script (see below) to give you advice by analyzing SQLServer's statistics on what indexes you could create.
• or use the Database Engine Tuning Advisor to suggest and/or create indexes for you to speed up your queries
• or use a tool like redgate's SQL Response to give you more information than you can digest

In the end, automated tools will get you a long way. It may even be enough in your case but keep in mind that there is no automated tool that will be able to outperform a skilled DBA for the mear fact that automated tools can not rewrite your queries.

---

SET CONCAT_NULL_YIELDS_NULL OFF
--Joining the views gives a nice picture of what indexes 
--would help and how much they would help
SELECT 
  'CREATE INDEX IX_' + UPPER(REPLACE(REPLACE(COALESCE(equality_columns, inequality_columns), '[', ''), ']', ''))
    + ' ON ' + d.statement + '(' + COALESCE(equality_columns, inequality_columns) 
  + CASE WHEN equality_columns IS NOT NULL THEN  
     CASE WHEN inequality_columns IS NOT NULL THEN ', ' + inequality_columns 
     END END 
    + ')' + CASE WHEN included_columns IS NOT NULL THEN ' INCLUDE (' + included_columns + ')' END
  , object_name(object_id)
  , d.*
  , s.*
FROM    sys.dm_db_missing_index_details d 
      LEFT OUTER JOIN sys.dm_db_missing_index_groups g ON   d.index_handle = g.index_handle
      LEFT OUTER JOIN sys.dm_db_missing_index_group_stats s ON  g.index_group_handle = s.group_handle
WHERE   database_id = db_id()
ORDER BY avg_total_user_cost DESC

Answer 2

You should be able to go thru the sys.dm_exec_query_stats table, which keeps information on all queries against a database.

SELECT  creation_time  
    ,last_execution_time 
    ,total_physical_reads
    ,total_logical_reads  
    ,total_logical_writes
    , execution_count 
    , total_worker_time
    , total_elapsed_time 
    , total_elapsed_time / execution_count avg_elapsed_time
    ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
     ((CASE statement_end_offset 
      WHEN -1 THEN DATALENGTH(st.text)
      ELSE qs.statement_end_offset END 
        - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY last_execution_time,total_elapsed_time / execution_count DESC;

Gives you basic timing information of how long, historically, queries took.