Life saver queries

These queries can save your life, at least they saved mine.

                                                                    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

 

Find out which Indexes are not used: Get rid of them, you are paying for  them.

select object_name(i.object_id),

i.name,

s.user_updates,

s.user_seeks,

s.user_scans,

s.user_lookups

from sys.indexes

            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

 

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s