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