Sql server cpu utilisation

Sql server cpu utilisation

 Days ago I wanted to know which query is taking lots of time and cpu utilisation for execution.
and I came to one blog from where I copied below query. It tells details of all query. You need to fire it on your SQL server to know more.

SELECT
    substring(text,qs.statement_start_offset/2
        ,(CASE   
            WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2)
    ,qs.plan_generation_num as recompiles
    ,qs.execution_count as execution_count
    ,qs.total_elapsed_time - qs.total_worker_time as total_wait_time
    ,qs.total_worker_time as cpu_time
    ,qs.total_logical_reads as reads
    ,qs.total_logical_writes as writes
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    LEFT JOIN sys.dm_exec_requests r
        ON qs.sql_handle = r.sql_handle
ORDER BY 3 DESC
Sql server cpu utilisationMS SQL server shutdowngodaddy hosting MS SQL server configurationsql server default portsbackup sql databasesql server agent backupThe shrink operation is not duplicated on the mirror database when you use database mirroring in SQL Server 2005sql server free spaceMS SQL server service packMS SQL server mirroring remove end points master keyclient server communication

Author

My name is Satalaj, but people call me Sat. Here is my homepage: . I live in Pune, PN and work as a Software Engineer. I'm former MVP in ASP.net year 2010.
Disclaimer: Views or opinion expressed here are my personal research and it has nothing to do with my employer. You are free to use the code, ideas/hints in your projects. However, you should not copy and paste my original content to other web sites. Feel free to copy or extend the code.
If you want to fight with me, this website is not for you.
 

I'm Satalaj.