Tag: TSQL

  • Get the List Of DBCC Commands & Syntax

    If you don’t remember all the DBCC commands “NO Issue” execute the DBCC HELP command to get the list of all the DBCC.

    DBCC HELP('?')

    checkalloc
    checkcatalog
    checkconstraints
    checkdb
    checkfilegroup
    checkident
    checktable
    cleantable
    dbreindex
    dropcleanbuffers
    free
    freeproccache
    freesessioncache
    freesystemcache
    help
    indexdefrag
    inputbuffer
    opentran
    outputbuffer
    pintable
    proccache
    show_statistics
    showcontig
    shrinkdatabase
    shrinkfile
    sqlperf
    traceoff
    traceon
    tracestatus
    unpintable
    updateusage
    useroptions

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Don’t remember syntax, execute below command to get the syntax.

    DBCC HELP('checktable')

    DBCC

  • Script to find out most CPU bound Queries

    Execute below query to get TOP 25 completed queries which have the highest cumulative CPU usage

    SELECT highest_cpu_queries.plan_handle, 
(highest_cpu_queries.total_worker_time/highest_cpu_queries.execution_count) AS AverageCPU, highest_cpu_queries.execution_count, highest_cpu_queries.total_worker_time, highest_cpu_queries.plan_generation_num, highest_cpu_queries.creation_time, highest_cpu_queries.last_execution_time, 
highest_cpu_queries.last_physical_reads, highest_cpu_queries.min_physical_reads,
q.dbid, q.objectid, q.number, q.encrypted, q.[text]
FROM (SELECT TOP 25 qs.plan_handle, qs.total_worker_time, qs.last_execution_time,
qs.plan_generation_num, qs.creation_time, qs.execution_count, qs.last_physical_reads,
qs.min_physical_reads 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 AverageCPU DESC

    Above script will return the queries which are completed. You can check the active sessions and CPU details using below query.

    SELECT SPID, CPU, s2.text, open_tran, status, program_name,
net_library, loginame FROM sys.sysprocesses 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
where cpu > 5000 and status = 'runnable'