ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 常用的维护sql汇总

常用的维护sql汇总

原创 Linux操作系统 作者:hwtong 时间:2012-02-29 15:50:02 0 删除 编辑
1. 检查BLOCKING相关信息(SQLSERVER 2005 &2008):
SELECT TL.REQUEST_SESSION_ID AS WAITINGSESSIONID,
           WT.BLOCKING_SESSION_ID AS BLOCKINGSESSIONID,
           WT.RESOURCE_DESCRIPTION,
           WT.WAIT_TYPE,
           WT.WAIT_DURATION_MS,
           DB_NAME(TL.RESOURCE_DATABASE_ID) AS DATABASENAME,
           TL.RESOURCE_ASSOCIATED_ENTITY_ID AS WAITINGASSOCIATEDENTITY,
           TL.RESOURCE_TYPE AS WAITINGREQUESTTYPE,
           WRT.[TEXT] AS WAITINGSQL,
           BTL.REQUEST_TYPE BLOCKINGREQUESTTYPE,
           BRT.[TEXT] AS BLOCKINGTSQL
         FROM SYS.DM_TRAN_LOCKS TL
        JOIN SYS.DM_OS_WAITING_TASKS WT
        ON TL.LOCK_OWNER_ADDRESS=WT.WAITING_TASK_ADDRESS
        JOIN SYS.DM_EXEC_REQUESTS WR
        ON WR.SESSION_ID=TL.REQUEST_SESSION_ID
        CROSS APPLY SYS.DM_EXEC_SQL_TEXT(WR.SQL_HANDLE) AS WRT
        LEFT JOIN SYS.DM_EXEC_REQUESTS BR
        ON BR.SESSION_ID=WT.BLOCKING_SESSION_ID
        OUTER APPLY SYS.DM_EXEC_SQL_TEXT(BR.SQL_HANDLE)AS BRT
        LEFT JOIN SYS.DM_TRAN_LOCKS AS BTL
        ON BR.SESSION_ID=BTL.REQUEST_SESSION_ID

2.检查性能较差的SQL(SQLSERVER 2005 &2008):---按照逻辑读排序
SELECT SS.SUM_EXECUTION_COUNT,
                T.TEXT,
                SS.SUM_TOTAL_ELAPSED_TIME,
                SS.SUM_TOTAL_WORKER_TIME,
                SS.SUM_TOTAL_LOGICAL_READS,
                SS.SUM_TOTAL_LOGICAL_WRITES
FROM (SELECT S.PLAN_HANDLE,
                         SUM(S.EXECUTION_COUNT) SUM_EXECUTION_COUNT,
                         SUM(S.TOTAL_ELAPSED_TIME) SUM_TOTAL_ELAPSED_TIME,
                         SUM(S.TOTAL_WORKER_TIME) SUM_TOTAL_WORKER_TIME,
                         SUM(S.TOTAL_LOGICAL_READS) SUM_TOTAL_LOGICAL_READS,
                         SUM(S.TOTAL_LOGICAL_WRITES) SUM_TOTAL_LOGICAL_WRITES
          FROM SYS.DM_EXEC_QUERY_STATS S
          GROUP BY S.PLAN_HANDLE
          ) AS SS
          CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SS.PLAN_HANDLE) T
ORDER BY SUM_TOTAL_LOGICAL_READS DESC
3:检查各个数据库文件的IO状况L(SQLSERVER 2005 &2008)
SELECT * FROM SYS.DM_IO_VIRTUAL_FILE_STATS(NULL,NULL)
4:检查索引的碎片(SQLSERVER 2005 &2008)
SELECT
    OBJECT_NAME(OBJECT_ID),
    OBJECT_ID AS OBJECTID,
    INDEX_ID AS INDEXID,
    PARTITION_NUMBER AS PARTITIONNUM,
    AVG_FRAGMENTATION_IN_PERCENT AS FRAG_IN_PERCENT
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE AVG_FRAGMENTATION_IN_PERCENT > 10.0 AND INDEX_ID > 0
ORDER BY AVG_FRAGMENTATION_IN_PERCENT DESC;
5:检查从未被使用过的索引情况(SQLSERVER 2005 &2008)
SELECT OBJECT_NAME(A.OBJECT_ID) AS TABLE_NAME ,A.NAME INDEX_NAME FROM SYS.INDEXES A INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS B ON A.OBJECT_ID=B.OBJECT_ID AND A.INDEX_ID=B.INDEX_ID WHERE USER_SEEKS=0 AND USER_SCANS=0 AND USER_LOOKUPS=0 AND DATABASE_ID=5 AND A.INDEX_ID>0 ORDER BY TABLE_NAME
6:检查性能较差的SQL(SQLSERVER 2005 &2008):---按照CPU排序
SELECT
    HIGHEST_CPU_QUERIES.PLAN_HANDLE,
    HIGHEST_CPU_QUERIES.TOTAL_WORKER_TIME,
    Q.DBID,
    Q.OBJECTID,
    Q.NUMBER,
    Q.ENCRYPTED,
    Q.[TEXT]
FROM
    (SELECT TOP 50
        QS.PLAN_HANDLE,
        QS.TOTAL_WORKER_TIME
    FROM
        SYS.DM_EXEC_QUERY_STATS QS
    ORDER BY QS.TOTAL_WORKER_TIME DESC) AS HIGHEST_CPU_QUERIES
    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS Q
ORDER BY HIGHEST_CPU_QUERIES.TOTAL_WORKER_TIME DESC
7:检查数据库中表的数量
CREATE TABLE #_A_TABLESPACEUSED(
TABLENAME SYSNAME,
ROWS INT,
RESERVED VARCHAR(20),
DATASIZE VARCHAR(20),
INDEXSIZE VARCHAR(20),
UNUSED VARCHAR(20))

EXEC SP_MSFOREACHTABLE
@COMMAND1=N'INSERT INTO #_A_TABLESPACEUSED EXEC SP_SPACEUSED ''?'''

SELECT * FROM #_A_TABLESPACEUSED ORDER BY ROWS DESC
DROP TABLE #_A_TABLESPACEUSED


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16754036/viewspace-717374/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-07-06

  • 博文量
    116
  • 访问量
    284910