Open Performance Monitor in Windows and open Data Collector Sets > User Defined.
Right click and click on New > Data Collector Set.
Give your set a name like SQL Performance Data Collector or something similar
Choose Create Manually and click Next.
Under Create data logs, select all 3 checkboxes and click Finish.
Under the User Defined section on the left, click on your new SQL data collector.
In the right pane, you should see 3 data collector types. Right click the Performance Counter one and click Properties
Add the following data collectors:
- Memory\Pages/Sec
- Memory\Available MBytes
- Network Interface(*)\Bytes Total/sec
- PhysicalDisk(_Total)\Avg. Disk Queue Length
- PhysicalDisk(_Total)\Avg. Disk sec/Read
- PhysicalDisk(_Total)\Avg. Disk sec/Write
- PhysicalDisk(_Total)\Disk Reads/sec
- PhysicalDisk(_Total)\Disk Writes/sec
- Processor(_Total)\% Processor Time
- SQLServer:Buffer Manager\Buffer cache hit ratio
- SQLServer:General Statistics\User Connections
- SQLServer:Memory Manager\Free Memory (KB)
- SQLServer:Memory Manager\Total Server Memory
- SQLServer:Memory Manager\Memory Grants Per Sec
- SQLServer:SQL Statistics\Batch Requests/sec
- SQLServer:SQL Statistics\SQL Compilations/sec
- SQLServer:SQL Statistics\SQL Re-Compilations/sec
- System\Processor Queue Length
- TCPv4\Connecitons Active
- TCPv4\Connections Established
Membership in the local Performance Log Users or Administrators group, or equivalent, is the minimum required to complete this procedure.
Note - The Performance Log Users group must be assigned the Log on as a batch user user right, as described in Enable Logging for Performance Log Users Group Members.
To schedule the Start condition for a Data Collector Set
- In Windows Performance Monitor, expand Data Collector Sets and click User Defined .
- In the console pane, right-click the name of the Data Collector Set that you want to schedule and click Properties .
- Click the Schedule tab.
- Click Add to create a start date, time, or day for data collection. When configuring a new data collector set, ensure that this date is after the current date and time.
- If you do not want to collect new data after a certain date, select Expiration date and choose a date from the calendar.
Note - Selecting an expiration date will not stop data collection in progress on that date. It will prevent new instances of data collection from starting after the expiration date. You must use the Stop Condition tab to configure how data collection is stopped. - When finished, click OK .
To schedule the Stop condition for a Data Collector Set
- In Windows Performance Monitor, expand Data Collector Sets and click User Defined .
- In the console pane, right-click the name of the Data Collector Set that you want to schedule and click Properties.
- Click the Stop Condition tab.
- To stop collecting data after a period of time, select overall duration and choose the quantity and units. Note that your overall duration must be longer than the interval at which data is sampled in order to see any data in the report. Do not select an overall duration if you want to collect data indefinitely.
- Use limits to segment data collection into separate logs by selecting When a limit is reached, restart the data collector set . If both limit types are selected, data collection will stop or restart when the first limit is reached.
- Select Duration to configure a time period for data collection to write to a single log file.
- Select Maximum Size to restart the Data Collector Set or to stop collecting data when the log file reaches the limit.
Note - If an overall duration is configured, it will override limits. - If you have configured an overall duration, you can select Stop when all data collectors have finished to let all data collectors finish recording the most recent values before the Data Collector Set is stopped.
- When finished, click OK .
Important - Larger log files result in longer report generation times. If you review your logs frequently to see recent data, we recommend that you use limits to automatically segment your logs. You can use the relog command to segment long log files or combine multiple short log files.
Additional Info:
Installing SQL Server adds SQL Server—specific performance counters to the Windows 2000 System Monitor (Performance Monitor in Windows NT 4.0). You can use these counters with the standard Win2K or NT performance counters to keep tabs on your system's response to gradual load changes. To establish a baseline, begin system monitoring when the system is running as usual. Seven important performance counters can give you a quick idea of your SQL Server system's overall status.
7. Bytes Total/sec
The Bytes Total/sec counter, which is in the Network Interface object, can help you find a network-adapter bottleneck. Compare this number with your total available bandwidth. Generally, the counter should show less than 50 percent of the available bandwidth.
6. Total Server Memory
This counter, located in the Memory Manager object, shows SQL Server's overall memory usage in kilobytes. Add memory if this number is consistently higher than the amount of physical memory in the system.
5. Average Disk Queue Length
This disk-performance counter in the PhysicalDisk object shows how many requests are usually waiting for disk access. Microsoft recommends that the number of waiting I/O requests be no more than 1.5 to 2 times the number of spindles that make up the physical disk. If this counter number is consistently higher than the recommended number, you might benefit from faster disks or additional disk drives.
4. Cache Hit Ratio
This counter in the Cache Manager object tells you whether SQL Server can pull your queries' execution plans out of the Procedure Cache. Ideally, this number should consistently be higher than 85 percent. If you see the number drop over time, consider adding RAM or optimizing your queries.
3. Buffer Cache Hit Ratio
Buffer cache use is application-specific. The Buffer Cache Hit Ratio counter in the Buffer Manager object shows how often SQL Server is able to pull data from the buffer cache. The higher the number, the better; when SQL Server can find the data pages it needs in the buffer cache, it doesn't have to perform a physical I/O. If you notice that this ratio is dropping over time, consider adding RAM.
2. Pages/Sec
The Pages/Sec counter, located in the Memory object, shows the number of pages that SQL Server retrieved from disk because of page faults or that SQL Server wrote to disk to free up space in the working set. Paging spikes are typical, but this counter number should remain close to zero. An increase in paging can signal the need to add memory; you might also attempt to reduce the number of applications that are running.
1. % Processor Time
One of the most vital counters to monitor is the % Processor Time counter in the Processor object. This counter tells you what percentage of its capacity the processor is using. You'll occasionally see spikes that show periods when the system is running at 100 percent. However, if you see extended periods when usage is greater than 80 percent, your system is CPU-bound and might benefit from more processing power.