4 sätt att lösa prestandaproblem i sql-server – del 2

Detta är del 2 av 3 i en serie om att lösa prestandaproblem i sql-server.
Del 1 går igenom hur man letar upp prestandaproblem med profiler, del 2 går igenom DMV och del 3 går igenom performance monitor.

DMV

I och med sql-server 2005 och introduktionen av DMV, dynamic management views så har det blivit enklare att lösa prestandaproblem. Med sql-server 2008 utökades antal DMV:er vilket ger ytterligare möjligheter att direkt få reda på var prestandaproblemen ligger.
Jag listar här några bra och användbara DMV:er som fungerar i både sql-server 2005 och sql-server 2008. De flesta kommer from glenn berry eller microsoft.

Hitta alla index som inte använts sedan senaste omstarten av sql-server

Oanvända index vill vi inte ha eftersom det försämrar skrivprestanda

SELECT   OBJECT_NAME(object_id), 
         i.name,
         i.index_id as index_id
FROM     sys.indexes i 
WHERE    i.index_id NOT IN (SELECT s.index_id 
                            FROM   sys.dm_db_index_usage_stats s 
                            WHERE  s.object_id = i.object_id 
                                   AND i.index_id = s.index_id 
                                   AND database_id = DB_ID()) 
ORDER BY OBJECT_NAME(object_id) ASC 

Top 20 frågor som genererar mest cpu

SELECT   TOP 20 qt.TEXT                                     AS 'SP Name', 
                qs.total_worker_time                        AS 'TotalWorkerTime', 
                qs.total_worker_time / qs.execution_count   AS 'AvgWorkerTime', 
                qs.execution_count                          AS 'Execution Count', 
                ISNULL(qs.execution_count / DATEDIFF(SECOND,qs.creation_time,GETDATE()), 
                       0) AS 'Calls/Second', 
                ISNULL(qs.total_elapsed_time / qs.execution_count, 
                       0) AS 'AvgElapsedTime', 
                qs.max_logical_reads, 
                qs.max_logical_writes, 
                DATEDIFF(MINUTE,qs.creation_time,GETDATE()) AS 'Age in Cache' 
FROM     sys.dm_exec_query_stats AS qs 
         CROSS APPLY sys.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qt 
WHERE    qt.dbid = DB_ID() -- Filter by current database  
ORDER BY qs.total_worker_time DESC 

Top 20 frågor som genererar mest logical reads (minne)

SELECT   TOP 20 qt.TEXT                                                          AS 'SP Name', 
                total_logical_reads, 
                qs.execution_count                                               AS 'Execution Count', 
                total_logical_reads / qs.execution_count                         AS 'AvgLogicalReads', 
                qs.execution_count / DATEDIFF(SECOND,qs.creation_time,GETDATE()) AS 'Calls/Second', 
                qs.total_worker_time / qs.execution_count                        AS 'AvgWorkerTime', 
                qs.total_worker_time                                             AS 'TotalWorkerTime', 
                qs.total_elapsed_time / qs.execution_count                       AS 'AvgElapsedTime', 
                qs.total_logical_writes, 
                qs.max_logical_reads, 
                qs.max_logical_writes, 
                qs.total_physical_reads, 
                DATEDIFF(MINUTE,qs.creation_time,GETDATE())                      AS 'Age in Cache', 
                qt.dbid 
FROM     sys.dm_exec_query_stats AS qs 
         CROSS APPLY sys.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qt 
WHERE    qt.dbid = DB_ID() -- Filter by current database  
ORDER BY total_logical_reads DESC 

Top 20 frågor som genererar mest physical reads (I/O)

SELECT   TOP 20 qt.TEXT                                                          AS 'SP Name', 
                qs.total_physical_reads, 
                qs.total_physical_reads / qs.execution_count                     AS 'Avg Physical Reads', 
                qs.execution_count                                               AS 'Execution Count', 
                qs.execution_count / DATEDIFF(SECOND,qs.creation_time,GETDATE()) AS 'Calls/Second', 
                qs.total_worker_time / qs.execution_count                        AS 'AvgWorkerTime', 
                qs.total_worker_time                                             AS 'TotalWorkerTime', 
                qs.total_elapsed_time / qs.execution_count                       AS 'AvgElapsedTime', 
                qs.max_logical_reads, 
                qs.max_logical_writes, 
                DATEDIFF(MINUTE,qs.creation_time,GETDATE())                      AS 'Age in Cache', 
                qt.dbid 
FROM     sys.dm_exec_query_stats AS qs 
         CROSS APPLY sys.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qt 
WHERE    qt.dbid = DB_ID() -- Filter by current database  
ORDER BY qs.total_physical_reads DESC 

Top 20 frågor som genererar mest writes (I/O)

SELECT TOP 20 qt.text AS 'SP Name', qs.total_logical_writes, qs.total_logical_writes/qs.execution_count AS 'AvgLogicalWrites',
    qs.total_logical_writes/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Logical Writes/Min',  
    qs.execution_count AS 'Execution Count', 
    qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', 
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
    qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
    qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads', qt.dbid
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    WHERE qt.dbid = db_id() -- Filter by current database
    ORDER BY qs.total_logical_writes DESC

En lista på index som har fler writes än reads.

När ett index har fler skrivningar än läsningar så kan det tyda på ett dåligt val av index.

SELECT   OBJECT_NAME(s.object_id)                                AS [Table Name], 
         i.name                                                  AS [Index Name], 
         i.index_id, 
         user_updates                                            AS [Total Writes], 
         user_seeks + user_scans + user_lookups                  AS [Total Reads], 
         user_updates - (user_seeks + user_scans + user_lookups) AS [Difference] 
FROM     sys.dm_db_index_usage_stats AS s WITH (NOLOCK) 
         INNER JOIN sys.indexes AS i WITH (NOLOCK) 
           ON s.object_id = i.object_id 
              AND i.index_id = s.index_id 
WHERE    OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 
         AND s.database_id = DB_ID() 
         AND user_updates > (user_seeks + user_scans + user_lookups) 
         AND i.index_id > 1 
ORDER BY [Difference] DESC, 
         [Total Writes] DESC, 
         [Total Reads] ASC; 

Läs del tre om performance monitor.

1 kommentar

  1. […] dagarna. Del 1 går igenom hur man letar upp prestandaproblem med profiler, del 2 går igenom DMV och del 3 går igenom performance […]

RSS feed for comments on this post

Kommentarer inaktiverade.

%d bloggare gillar detta: