sqlserver 性能調(diào)優(yōu)腳本

-- 查看日志空間占用率
DBCC SQLPERF ( LOGSPACE)

SELECT TOP 10

        st.text AS SQL_Full                                        --父級(jí)完整語句

        ,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 SQL_Part    --統(tǒng)計(jì)對(duì)應(yīng)的部分語句

        , CAST( ((qs.total_elapsed_time / 1000000.0)/qs.execution_count) AS DECIMAL(28,2) ) AS [平均消耗秒數(shù)]

        , CAST(qs.last_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最后完成消耗秒數(shù)]

        , qs.last_execution_time AS [最后執(zhí)行時(shí)間]

        , CAST(qs.min_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最小消耗秒數(shù)]

        , CAST(qs.max_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最大消耗秒數(shù)]

        , CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [總消耗秒數(shù)]

        , (qs.execution_count) AS [總執(zhí)行次數(shù)]

        , creation_time AS [編譯計(jì)劃的時(shí)間]

        , CAST(qs.last_worker_time / 1000000.0 AS DECIMAL(28, 2)) AS [最后完成占用CPU秒數(shù)]

        , qp.query_plan

    from sys.dm_exec_query_stats qs

        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

WHERE qs.last_execution_time>DATEADD(n,-30,GETDATE())

ORDER BY qs.last_worker_time DESC

--執(zhí)行最慢的20條SQL語句
SELECT TOP 20 (total_elapsed_time / execution_count) / 1000 N'平均時(shí)間ms',
       total_elapsed_time / 1000 N'總花費(fèi)時(shí)間ms',
       total_worker_time / 1000 N'所用的CPU總時(shí)間ms',
       total_physical_reads N'物理讀取總次數(shù)',
       total_logical_reads / execution_count N'每次邏輯讀次數(shù)',
       total_logical_reads N'邏輯讀取總次數(shù)',
       total_logical_writes N'邏輯寫入總次數(shù)',
       execution_count N'執(zhí)行次數(shù)',
       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
       ) N'執(zhí)行語句',
       creation_time N'語句編譯時(shí)間',
       last_execution_time         N'上次執(zhí)行時(shí)間'
FROM   sys.dm_exec_query_stats  AS qs
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE  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
       ) NOT LIKE '?tch%'
ORDER BY
       total_elapsed_time / execution_count DESC;

--總耗CPU最多的前個(gè)SQL:
SELECT TOP 20
       total_worker_time / 1000           AS [總消耗CPU 時(shí)間(ms)],
       execution_count [運(yùn)行次數(shù)],
       qs.total_worker_time / qs.execution_count / 1000 AS 
       [平均消耗CPU 時(shí)間(ms)],
       last_execution_time                AS [最后一次執(zhí)行時(shí)間],
       max_worker_time / 1000             AS [最大執(zhí)行時(shí)間(ms)],
       SUBSTRING(
           qt.text,
           qs.statement_start_offset / 2 + 1,
           (
               CASE 
                    WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text)
                    ELSE qs.statement_end_offset
               END -qs.statement_start_offset
           ) / 2 + 1
       )                                  AS [使用CPU的語法],
       qt.text [完整語法],
       qt.dbid,
       dbname = DB_NAME(qt.dbid),
       qt.objectid,
       OBJECT_NAME(qt.objectid, qt.dbid)     ObjectName
FROM   sys.dm_exec_query_stats qs WITH (NOLOCK)
                                       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS 
       qt
WHERE  execution_count > 1
ORDER BY
       total_worker_time DESC

--平均耗CPU最多的前個(gè)SQL:
SELECT TOP 20
       total_worker_time / 1000           AS [總消耗CPU 時(shí)間(ms)],
       execution_count [運(yùn)行次數(shù)],
       qs.total_worker_time / qs.execution_count / 1000 AS 
       [平均消耗CPU 時(shí)間(ms)],
       last_execution_time                AS [最后一次執(zhí)行時(shí)間],
       min_worker_time / 1000             AS [最小執(zhí)行時(shí)間(ms)],
       max_worker_time / 1000             AS [最大執(zhí)行時(shí)間(ms)],
       SUBSTRING(
           qt.text,
           qs.statement_start_offset / 2 + 1,
           (
               CASE 
                    WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text)
                    ELSE qs.statement_end_offset
               END -qs.statement_start_offset
           ) / 2 + 1
       )                                  AS [使用CPU的語法],
       qt.text [完整語法],
       qt.dbid,
       dbname = DB_NAME(qt.dbid),
       qt.objectid,
       OBJECT_NAME(qt.objectid, qt.dbid)     ObjectName
FROM   sys.dm_exec_query_stats qs WITH (NOLOCK)
                                       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS 
       qt
WHERE  execution_count > 1
ORDER BY
       (qs.total_worker_time / qs.execution_count / 1000) DESC