sqlserver的效能瓶頸

--sqlserver的效能瓶頸
--http://hi.baidu.com/cwh_blog/item/a85e86ddb46fbd57d73aae4f
--http://msdn.microsoft.com/zh-cn/magazine/cc135978.aspx
--許多應用程式效能的問題可能都是效能不佳的資料庫查詢文法造成的。我們該怎麼樣知道究竟瓶頸出現在哪裡呢？基本上，SQL Server 2005會收集所執行的查詢之相關資料，這些資料會保留在記憶體中，且在每次重新啟動後開始累積，換句話說，重開機之後這些資料就會消失了。

--這些資料，包含了資料表索引、查詢效能及伺服器I/O相關的問題資料。我們可以透過SQL Server Dynamic Management Views (DMV)和相關動態管理函數(Dynamic Management Functions, DMF)來查詢這些資料，用來呈現伺服器狀態，利用來診斷問題及調整資料庫效能。

--· 伺服器等待的原因

SELECT TOP 10

[Wait type] = wait_type,

[Wait time (s)] = wait_time_ms / 1000,

[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0

/ SUM(wait_time_ms) OVER())

FROM sys.dm_os_wait_stats

WHERE wait_type NOT LIKE '%SLEEP%'

ORDER BY wait_time_ms DESC;

--· 讀取及寫入

SELECT TOP 10

[Total Reads] = SUM(total_logical_reads)

,[Execution count] = SUM(qs.execution_count)

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

GROUP BY DB_NAME(qt.dbid)

ORDER BY [Total Reads] DESC;

SELECT TOP 10

[Total Writes] = SUM(total_logical_writes)

,[Execution count] = SUM(qs.execution_count)

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

GROUP BY DB_NAME(qt.dbid)

ORDER BY [Total Writes] DESC;

--· 遺漏資料庫索引

SELECT

DatabaseName = DB_NAME(database_id)

,[Number Indexes Missing] = count(*)

FROM sys.dm_db_missing_index_details

GROUP BY DB_NAME(database_id)

ORDER BY 2 DESC;

--· 高成本的遺漏索引

SELECT TOP 10

[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)

, avg_user_impact

, TableName = statement

, [EqualityUsage] = equality_columns

, [InequalityUsage] = inequality_columns

, [Include Cloumns] = included_columns

FROM sys.dm_db_missing_index_groups g

INNER JOIN sys.dm_db_missing_index_group_stats s

ON s.group_handle = g.index_group_handle

INNER JOIN sys.dm_db_missing_index_details d

ON d.index_handle = g.index_handle

ORDER BY [Total Cost] DESC;

--· 未使用的索引

-- Create required table structure only.

-- Note: this SQL must be the same as in the Database loop given in the following step.

SELECT TOP 1

DatabaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

,user_updates

,system_updates

-- Useful fields below:

--, *

INTO #TempUnusedIndexes

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

AND user_seeks = 0

AND user_scans = 0

AND user_lookups = 0

AND s.[object_id] = -999 -- Dummy value to get table structure.

;

-- Loop around all the databases on the server.

EXEC sp_MSForEachDB 'USE [?];

-- Table already exists.

INSERT INTO #TempUnusedIndexes

SELECT TOP 10

DatabaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

,user_updates

,system_updates

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0

AND user_seeks = 0

AND user_scans = 0

AND user_lookups = 0

AND i.name IS NOT NULL -- Ignore HEAP indexes.

ORDER BY user_updates DESC

;

'

-- Select records.

SELECT TOP 10 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC

-- Tidy up.

DROP TABLE #TempUnusedIndexes

--· 高成本的使用中索引

-- Create required table structure only.

-- Note: this SQL must be the same as in the Database loop given in the following step.

SELECT TOP 1

[Maintenance cost] = (user_updates + system_updates)

,[Retrieval usage] = (user_seeks + user_scans + user_lookups)

,DatabaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

INTO #TempMaintenanceCost

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

AND (user_updates + system_updates) > 0 -- Only report on active rows.

AND s.[object_id] = -999 -- Dummy value to get table structure.

;

-- Loop around all the databases on the server.

EXEC sp_MSForEachDB 'USE [?];

-- Table already exists.

INSERT INTO #TempMaintenanceCost

SELECT TOP 10

[Maintenance cost] = (user_updates + system_updates)

,[Retrieval usage] = (user_seeks + user_scans + user_lookups)

,DatabaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND i.name IS NOT NULL -- Ignore HEAP indexes.

AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0

AND (user_updates + system_updates) > 0 -- Only report on active rows.

ORDER BY [Maintenance cost] DESC

;

'

-- Select records.

SELECT TOP 10 * FROM #TempMaintenanceCost

ORDER BY [Maintenance cost] DESC

-- Tidy up.

DROP TABLE #TempMaintenanceCost

--· 常用的索引

-- Create required table structure only.

-- Note: this SQL must be the same as in the Database loop given in the -- following step.

SELECT TOP 1

[Usage] = (user_seeks + user_scans + user_lookups)

,DatabaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

INTO #TempUsage

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

AND (user_seeks + user_scans + user_lookups) > 0

-- Only report on active rows.

AND s.[object_id] = -999 -- Dummy value to get table structure.

;

-- Loop around all the databases on the server.

EXEC sp_MSForEachDB 'USE [?];

-- Table already exists.

INSERT INTO #TempUsage

SELECT TOP 10

[Usage] = (user_seeks + user_scans + user_lookups)

,DatabaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND i.name IS NOT NULL -- Ignore HEAP indexes.

AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0

AND (user_seeks + user_scans + user_lookups) > 0 -- Only report on active rows.

ORDER BY [Usage] DESC

;

'

-- Select records.

SELECT TOP 10 * FROM #TempUsage ORDER BY [Usage] DESC

-- Tidy up.

DROP TABLE #TempUsage

--· 邏輯片段的索引

-- Create required table structure only.

-- Note: this SQL must be the same as in the Database loop given in the -- following step.

SELECT TOP 1

DatbaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)

INTO #TempFragmentation

FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.[object_id] = -999 -- Dummy value just to get table structure.

;

-- Loop around all the databases on the server.

EXEC sp_MSForEachDB 'USE [?];

-- Table already exists.

INSERT INTO #TempFragmentation

SELECT TOP 10

DatbaseName = DB_NAME()

,TableName = OBJECT_NAME(s.[object_id])

,IndexName = i.name

,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)

FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND i.name IS NOT NULL -- Ignore HEAP indexes.

AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0

ORDER BY [Fragmentation %] DESC

;

'

-- Select records.

SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC

-- Tidy up.

DROP TABLE #TempFragmentation

--· I/O的高成本查詢

SELECT TOP 10

[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count

,[Total IO] = (total_logical_reads + total_logical_writes)

,[Execution count] = qs.execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Average IO] DESC;

--· CPU的高成本查詢

SELECT TOP 10

[Average CPU used] = total_worker_time / qs.execution_count

,[Total CPU used] = total_worker_time

,[Execution count] = qs.execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset END -

qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Average CPU used] DESC;

--· 高成本的CLR查詢

SELECT TOP 10

[Average CLR Time] = total_clr_time / execution_count

,[Total CLR Time] = total_clr_time

,[Execution count] = qs.execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

WHERE total_clr_time <> 0

ORDER BY [Average CLR Time] DESC;

--· 最常執行的查詢

SELECT TOP 10

[Execution count] = execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Execution count] DESC;

--· 遭到封鎖的查詢

SELECT TOP 10

[Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count

,[Total Time Blocked] = total_elapsed_time - total_worker_time

,[Execution count] = qs.execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Average Time Blocked] DESC;

--最低計劃重複使用率

SELECT TOP 10

[Plan usage] = cp.usecounts

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX),

qt.text)) * 2 ELSE qs.statement_end_offset END -

qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

,cp.cacheobjtype

FROM sys.dm_exec_query_stats qs

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

INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle

WHERE cp.plan_handle=qs.plan_handle

ORDER BY [Plan usage] ASC;