Practical Solution to SQL Server Performance Monitoring
A command to copy a file is shown below:
copy /Y SJperfomanceset_03010952.csv \yetoc$perflogs
Where option /Y overwrites the existing performance log file.
The following template of an import script with an added WHERE clause will insert only new records into the SJperformanceset table — those added after the last insert:
INSERT INTO [SJperformanceset] (
[(PDH-CSV 4#0) (Pacific Standard Time)(480)]
FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,
— csv file on a local drive
‘Data Source=C:PerfLogs;Extended properties=Text’)…SJperformanceset_02272122#csv
— add new records only
WHERE [(PDH-CSV 4#0) (Pacific Standard Time)(480)] > (SELECT max([CounterDateTime]) FROM SJperfomanceset)
The monitoring sets provided in this article should be enough to do most of your database performance analysis, but you can modify the monitoring set to your liking by adding or deleting some of the particular counters and modifying tables and import scripts accordingly. Using these examples, you can also create your own monitoring tool set for fine-grain monitoring of resource utilization in particular areas such as memory, I/O, processor, etc.
Predefined monitoring sets will increase your ability to setup a performance base line, to compare the performance of multiple servers, to find possible performance bottlenecks, and to monitor the overall health of your system. You can also use these sets for additional system analysis, generating reports, and predicting future problems.