学步园

expensive queries query

SELECT TOP 200 total_worker_time, execution_count ,        
(total_worker_time /execution_count)/1000000 AS [Avg CPU Time seconds] ,

CASE
 WHEN deqs.statement_start_offset = 0 AND deqs.statement_end_offset = -1
 THEN
 '-- see objectText column--'
 ELSE
 '-- query --' + CHAR(13) + CHAR(10)+ SUBSTRING(execText.text,deqs.statement_start_offset /2,(
(
CASE
 WHEN deqs.statement_end_offset = -1
 THEN DATALENGTH(execText.text)
ELSE
 deqs.statement_end_offset
END
 )
 - deqs.statement_start_offset )/2
 )
END AS queryText,
execText.text,(deqs.total_physical_reads/ deqs.execution_count) as PhysicalReadsAvg,
(deqs.total_logical_reads/deqs.execution_count) as LogicalReadsAvg, db_name(execText.dbid), deqs.plan_handle

FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text
(deqs.plan_handle) AS execText

ORDER BY (total_worker_time / execution_count ) DESC;

missing indexes query

SELECT DB_NAME(mid.database_id) as dbname,migs.avg_total_user_cost *(migs.avg_user_impact /100.0)*(migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_'+CONVERT(varchar,mig.index_group_handle)+'_'+CONVERT(varchar,mid.index_handle)+'_'+LEFT(PARSENAME(mid.statement,1),32)
 +']'+' ON '+mid.statement+' ('+ ISNULL(mid.equality_columns,'')+
CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN','
ELSE '' END
+ISNULL(mid.inequality_columns,'') +')'+ISNULL(' INCLUDE (' + mid.included_columns + ')','') AS create_index_statement, migs.*, mid.database_id, mid.[object_id]

FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) *(migs.user_seeks + migs.user_scans)>=1
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC