SQL Server Performance Study by Performance Monitor (logman.exe)

 Using Logman

1. Open command prompt in an Administrator mode and run the below script.
2. Logman.exe create counter PerfLog-Short -o "c:\temp\%computername%_PerfLog-Short.blg" -f bincirc -v mmddhhmm -max 300 -c "\LogicalDisk(*)\*" "\Memory\*" "\.NET CLR Memory(*)\*" "\Cache\*" "\Network Interface(*)\*" "\Netlogon(*)\*" "\Paging File(*)\*" "\PhysicalDisk(*)\*" "\Processor(*)\*" "\Processor Information(*)\*" "\Process(*)\*" "\Redirector\*" "\Server\*" "\System\*" "\Server Work Queues(*)\*" "\Terminal Services\*" -si 00:00:01
   
   
1. -f bincirc -max 300 -si 1 : This piece of the command specifies that we are creating a Binary Circular file, sets the Maximum Log file size to 300MB, sets the Capture Interval at 1 seconds
3. To start the data collector, please execute blow command. Collect the data for 10 minutes during the Business hours.

Logman.exe start PerfLog-Short

4. To stop the data collector, please execute blow command.

Logman.exe stop PerfLog-Short

 

 

Using SQL Query

 

-- Shows queries where Max and average CPU time exceeds 200 ms and executed more than 1000 times. Please save the output in excel format.

DECLARE @cputime_threshold_microsec INT = 200*1000

DECLARE @execution_count INT = 1000

SELECT qs.total_worker_time/1000 total_cpu_time_ms,

       qs.max_worker_time/1000 max_cpu_time_ms,

       (qs.total_worker_time/1000)/execution_count average_cpu_time_ms,

       qs.execution_count,

       q.[text]

FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q

WHERE (qs.total_worker_time/execution_count > @cputime_threshold_microsec

        OR qs.max_worker_time > @cputime_threshold_microsec )

        AND execution_count > @execution_count

ORDER BY  qs.total_worker_time DESC