Some useful SQL Queries

Block queries

SELECT

    spid

    ,sp.status

    ,loginame   = SUBSTRING(loginame, 1, 12)

    ,hostname   = SUBSTRING(hostname, 1, 12)

    ,blk        = CONVERT(char(3), blocked)

    ,open_tran

    ,dbname     = SUBSTRING(DB_NAME(sp.dbid),1,10)

    ,cmd

    ,waittype

    ,waittime

    ,last_batch

    ,SQLStatement       =

        SUBSTRING

        (

            qt.text,

            er.statement_start_offset/2,

            (CASE WHEN er.statement_end_offset = -1

                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2

                ELSE er.statement_end_offset

                END - er.statement_start_offset)/2

        )

FROM master.dbo.sysprocesses sp

LEFT JOIN sys.dm_exec_requests er

    ON er.session_id = sp.spid

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) as qt

WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

AND blocked = 0

      

Kill process  - kill and processID

kill 201;

check Index

select the db and run

SELECT ps.database_id, object_name(ps.OBJECT_ID) as table_name, ps.index_id, b.name as index_name, ps.avg_fragmentation_in_percent,ps.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID

inner join sys.tables as c ON b.object_id = c.object_id

AND ps.index_id = b.index_id

WHERE ps.database_id = DB_ID()

and b.name is not null

ORDER BY ps.avg_fragmentation_in_percent desc

Check last executed stats

/* 1.Query to check when statistics was last executed */

SELECT

  st.object_id                          AS [Table ID]

, OBJECT_NAME(st.object_id)             AS [Table Name]

, st.name                               AS [Index Name]

, STATS_DATE(st.object_id, st.stats_id) AS [LastUpdated]

, modification_counter                  AS [Rows Modified]

FROM

sys.stats st

CROSS APPLY

sys.dm_db_stats_properties(st.object_id, st.stats_id) AS sp

WHERE

STATS_DATE(st.object_id, st.stats_id)<=DATEADD(DAY,-1,GETDATE()) 

AND modification_counter > 0

AND OBJECTPROPERTY(st.object_id,'IsUserTable')=1

order by OBJECT_NAME(st.object_id)

read sp

sp_helptext 'myDb.spName'

few

SELECT

       r.session_id

       ,st.TEXT AS batch_text

       ,SUBSTRING(st.TEXT, statement_start_offset / 2 + 1, (

                     (

                           CASE

                                  WHEN r.statement_end_offset = - 1

                                         THEN (LEN(CONVERT(NVARCHAR(max), st.TEXT)) * 2)

                                  ELSE r.statement_end_offset

                                  END

                           ) - r.statement_start_offset

                     ) / 2 + 1) AS statement_text

       ,qp.query_plan AS 'XML Plan'

       ,r.*

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st

CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp

ORDER BY cpu_time DESC

select

    P.spid,

       P.status

,   right(convert(varchar,

            dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'),

            121), 12) as 'batch_duration'

,   P.program_name

,   P.hostname

,   P.loginame

from master.dbo.sysprocesses P

where P.spid > 50

and      P.status not in ('background', 'sleeping','runnable')

and      P.cmd not in ('AWAITING COMMAND'

                    ,'MIRROR HANDLER'

                    ,'LAZY WRITER'

                    ,'CHECKPOINT SLEEP'

                    ,'RA MANAGER')

order by batch_duration desc