Practical Solution to SQL Server Performance Monitoring – Page 3

When the monitoring process is completed, you can transfer the performance log file data to your SQL Server. For this purpose, create a table in which to save the monitoring data using the following script:

CREATE TABLE [PerfmonDataCustomer01] (
     [CounterDateTime] [datetime] NOT NULL,
     [Page Reads/sec] [numeric](18, 2) NULL,
     [Pages/sec] [numeric](18, 2) NULL,
     [Avg Disk0 Queue Length] [numeric](18, 2) NULL,
     [Avg Disk0 sec/Transfer] [numeric](18, 2) NULL,
     [Avg Disk1 Queue Length] [numeric](18, 2) NULL,
     [Avg Disk1 sec/Transfer] [numeric](18, 2) NULL,
     [Processor Time] [numeric](18, 2) NULL,
     [Page Splits/sec] [numeric](18, 2) NULL,
     [Cache Hit Ratio] [numeric](18, 2) NULL,
     [User Connections] [numeric](18, 2) NULL,
     [Processor Queue Length] [numeric](18, 2) NULL
)
GO
ALTER TABLE [dbo].[PerfmonDataCustomer01] WITH NOCHECK ADD
     CONSTRAINT [PK_PerfmonDataCustomer01] PRIMARY KEY CLUSTERED
     (
      [CounterDateTime]
     )
GO

I use the OpenDataSource function in the import script because in my opinion it is a more flexible solution than the BCP utility, a DTS package, or a BULK INSERT statement.

The following script imports data from the C:PerfLogsSQLperfmon_02271405.csv file into the PerfmonDataCustomer01 table:

INSERT INTO [PerfmonDataCustomer01] (
     [CounterDateTime]
     ,[Page Reads/sec]
     ,[Pages/sec]
     ,[Avg Disk0 Queue Length]
     ,[Avg Disk0 sec/Transfer]
     ,[Avg Disk1 Queue Length]
     ,[Avg Disk1 sec/Transfer]
     ,[Processor Time]
     ,[Page Splits/sec]
     ,[Cache Hit Ratio]
     ,[User Connections]
     ,[Processor Queue Length]
)
SELECT
     [(PDH-CSV 4#0) (Pacific Standard Time)(480)]
     ,cast([MemoryPage Reads/sec] as float)
     ,cast([MemoryPages/sec] as float)
     ,cast([PhysicalDisk(0 C:)Avg# Disk Queue Length] as float)
     ,cast([PhysicalDisk(0 C:)Avg# Disk sec/Transfer] as float)
     ,cast([PhysicalDisk(1 D:)Avg# Disk Queue Length] as float)
     ,cast([PhysicalDisk(1 D:)Avg# Disk sec/Transfer] as float)
     ,cast([Processor(_Total)% Processor Time] as float)
     ,cast([SQLServer:Access MethodsPage Splits/sec] as float)
     ,cast([SQLServer:Buffer ManagerBuffer cache hit ratio] as float)
     ,cast([SQLServer:General StatisticsUser Connections] as float)
     ,cast([SystemProcessor Queue Length] as float)
FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,
— csv file on a local drive
‘Data Source=C:PerfLogs;Extended properties=Text’)…SQLperfmon_02271405#csv

If your performance log file is stored in a location that is different from the one specified in the script, then the last line of the script that specifies the file name and the Data Source parameter should be modified. Please note that you should use the “#” character instead of “.” in the file name. If the performance log file is stored in the shared folder on the remote server the Data Source part could look like this:

— csv file on a shared folder
‘Data Source=\server01PerfLogs;Extended properties=Text’)…SQLperfmon_02271405#csv

Please note that the monitoring server’s time zone defines the first field name of the Performance Log file. The field names shown in the first line of the .csv file must match the first field name in the SELECT statement of the import script. If not, you need to edit the first selected item in the script above. Again, please make sure that you use the “#” character instead of “.” in the field name. For example, if the field name in the .csv file is “(PDH-CSV 4.0) (Pacific Standard Time)(480)” you should use “[(PDH-CSV 4#0) (Pacific Standard Time)(480)]” in this script.

When the import is completed, you can compare the performance data of different servers, analyze recorded data using Microsoft recommendations, and decide how to resolve the issues that were found. Several sample queries for the data analysis are provided below:

SELECT AVG([Processor Time]) FROM PerfmonDataCustomer01
SELECT AVG([Processor Queue Length]) FROM PerfmonDataCustomer01
SELECT TOP 10 [Processor Time], [Processor Queue Length] FROM PerfmonDataCustomer01
ORDER BY [Processor Queue Length] DESC

Another monitoring setup that could be useful for SQL Server performance monitoring was recommended in “Performance Monitoring — Basic Counters” by Steve Jones. It also uses the Counter Log file with the corresponding table and import scripts. I included all the counters recommended in that article except the “Network Interface Object” because it uses a particular network card name, which may not be the same on different servers. The following script assumes that the monitored server has a physical disk, “0 C:”, and that the Transactions/Sec counter will check the “pubs” database.

Continues…

Leave a comment Cancel reply