credit Performance Monitor, or , measures performance statistics on a regular interval, and saves those stats in a file. Perfmon To set it up for Server, do the below steps: SQL Open perfmon from the run Expand Data Collector Sets Right click User Defined Click -> New Data Collector Set Give it a name Click Create manually Click Next -> Create data logs -> Performance counter -> Next Click Add in the next screen Add Available Bytes, Available MBytes, Page faults per second, Pages per second under Memory Add % disk time, Average disk second per read, Average disk second per write, Current disk queue length, disk bytes per second, disk transfer per second under Physical Disk % privileged Time, % processor time under Processor Add Freespace scans per second, Fullscan/sec under SQL Server access methods Add buffer cache hit ratio, checkpoint pages/sec, free pages, lazy writes/sec, page life expectancy under SQL Server buffer manager Add user conections under SQL Server general statistics Add Total latch wait time under SQL Server latches Add Lock timeouts/sec, Lock wait time, number of deadlocks/sec under SQL Server locks Under SQL Server memory manager, add memory grants pending, targe server memory and total server memory Under SQL Server SQL statistics, add batch requests/sec, SQL compilations/sec, SQL re-compilations/sec Under System, add context switches/sec, processor queue length Click OK -> Next -> Next -> Finish Right click this new collector set under User defined and choose start When one needs to see the report, stop this collector and open the collector file and view as report. Source Photos Originally published at xameeramir.github.io .

Too Long; Didn't Read

Making security fun one episode at a time

tSetting Up Perfmon for SQL Server Tuning

Too Long; Didn't Read

Zameer Ansari

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

tSetting Up Perfmon for SQL Server Tuning

Too Long; Didn't Read

Zameer Ansari

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES