Performance bottleneck of sqlserver

-- Performance bottleneck of sqlserver
Http://hi.baidu.com/cwh_blog/item/a85e86ddb46fbd57d73aae4f
Http://msdn.microsoft.com/zh-cn/magazine/cc135978.aspx
-- The performance of many applications may be caused by poor database query syntax. How do we know where the bottleneck is? Basically, SQL Server 2005 collects the queried data, which is stored in the memory and starts to accumulate after each restart. In other words, after the instance is restarted, the data disappears.

-- The data includes the data table index, query efficiency, and server I/O-related problem data. We can query the data through SQL Server dynamic management views (DMV) and related dynamic management functions (dynamic management functions, DMF) to display the server status, to diagnose problems and adjust database performance.

-- · Reasons for server waiting

Select top 10

[Wait type] = wait_type,

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

[% Waiting] = convert (decimal (100.0), wait_time_ms *

/Sum (wait_time_ms) over ())

From SYS. dm_ OS _wait_stats

Where wait_type not like '% Sleep %'

Order by wait_time_ms DESC;

-- Read and write

Select top 10

[Total reads] = sum (total_logical_reads)

, [Execution count] = sum(qs.exe cution_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.exe cution_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;

-- Missing database Indexes

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;

-- · High-cost missing Indexes

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

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;

-- Unused Indexes

-- 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

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

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], ''isshipped '') = 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

-- · High-cost in-use index

-- 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

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

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], ''isshipped '') = 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

-- · Common indexes

-- 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

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

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], ''isshipped '') = 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

-- · Logical fragment Index

-- 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) 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) 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], ''isshipped '') = 0

Order by [fragmentation %] DESC

;

'

-- Select records.

Select top 10 * from # tempfragmentation order by [fragmentation %] DESC

-- Tidy up.

Drop table # tempfragmentation

-- · High-cost query of I/O

Select top 10

[Average iO] = (total_logical_reads + total_logical_writes)/qs.exe cution_count

, [Total iO] = (total_logical_reads + total_logical_writes)

, [Execution count] = qs.exe cution_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;

-- · High-cost CPU Query

Select top 10

[Average CPU used] = total_worker_time/qs.exe cution_count

, [Total CPU used] = total_worker_time

, [Execution count] = qs.exe cution_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;

-- · High-cost CLR queries

Select top 10

[Average CLR time] = total_clr_time/execution_count

, [Total CLR time] = total_clr_time

, [Execution count] = qs.exe cution_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;

-- · The most frequently executed Query

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;

-- · Blocked Query

Select top 10

[Average time blocked] = (total_elapsed_time-total_worker_time)/qs.exe cution_count

, [Total time blocked] = total_elapsed_time-total_worker_time

, [Execution count] = qs.exe cution_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;

-- Minimum planned repetition rate

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;