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
编辑器加载中...