Life saver Queries.

These queries can save your life.

 Query to find the query using the most 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 10

        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

 Indexes not used:

select object_name(i.object_id),

i.name,

s.user_updates,

s.user_seeks,

s.user_scans,

s.user_lookups

from sys.indexes i

            left join sys.dm_db_index_usage_stats s

on s.object_id = i.object_id and

                  i.index_id = s.index_id and s.database_id = DB_ID('ILT_Stage')

where objectproperty(i.object_id, 'IsIndexable') = 1 and

-- index_usage_stats has no reference to this index (not being used)

s.index_id is null or

-- index is being updated, but not used by seeks/scans/lookups

(s.user_updates > 0 and s.user_seeks = 0

and s.user_scans = 0 and s.user_lookups = 0)

order by object_name(i.object_id) asc

 Missing indexes:

SELECT

  mig.index_group_handle, mid.index_handle,

  CONVERT (decimal (28,1),

    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)

  ) AS improvement_measure,

  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)

  + ' 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 CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

 TempDB:

 Queries to find which SQL statements are using Tempdb the most:

SELECT 

    t1.session_id,

    (t1.internal_objects_alloc_page_count + task_alloc) as allocated,

    (t1.internal_objects_dealloc_page_count + task_dealloc) as

    deallocated

from sys.dm_db_session_space_usage as t1,

    (select session_id,

        sum(internal_objects_alloc_page_count)

            as task_alloc,

    sum (internal_objects_dealloc_page_count) as

        task_dealloc

      from sys.dm_db_task_space_usage group by session_id) as t2

where t1.session_id = t2.session_id and t1.session_id >50

order by allocated DESC

 Highest CPU queries:

select 

    t1.session_id,

    t1.request_id,

    t1.task_alloc,

    t1.task_dealloc,

    t2.sql_handle,

    t2.statement_start_offset,

    t2.statement_end_offset,

    t2.plan_handle

from (Select session_id,

             request_id,

             sum(internal_objects_alloc_page_count) as task_alloc,

             sum (internal_objects_dealloc_page_count) as task_dealloc

      from sys.dm_db_task_space_usage

      group by session_id, request_id) as t1,

      sys.dm_exec_requests as t2

where t1.session_id = t2.session_id and

     (t1.request_id = t2.request_id)

order by t1.task_alloc DESC

Thanks to Santosh Tawde for providing these queries. I have some more useful DMV queries in my previous post.