SQLSkills blogspot

Microsoft SQL Server Tuning Tips (Part 2)

Microsoft SQL Server Tuning Tips( Part1)

THE LIST OF COMMON DBA TASKS INCLUDES SETTING UP A SERVER PERFORMANCE BASE LINE AND COMPARING THE PERFORMANCE OF TWO OR MORE SERVERS. WE OFTEN HAVE TO DO THIS URGENTLY WHEN OUR CUSTOMER CALLS AND EXPECTS US TO IDENTIFY AND REMOVE A PERFORMANCE BOTTLENECK RIGHT AWAY. THE FOLLOWING ARTICLE INCLUDES CODE AND HANDS-ON INSTRUCTION ON HOW TO AUTOMATE SUCH TEDIOUS TASKS AS REUSING THE PRESET MONITOR PARAMETERS AND ANALYZING THE RESULTS. GIVEN THE EVER-INCREASING SIZE OF DATABASES AND THE IMPORTANCE OF DATABASE PERFORMANCE, THIS ARTICLE CAN ALSO HELP YOU MONITOR AND TUNE THE PERFORMANCE OF YOUR SYSTEM, AS WELL AS IMPROVE YOUR PERFORMANCE AS A DBA.

The Performance Monitor console for Microsoft Windows 2000 Server can monitor both the operating system and the utilization of SQL Server resources. Unfortunately, it stores the results of the monitoring as a binary file or comma delimited text file, which is inconvenient for further analysis. Although Windows XP and Windows 2003 can write directly to the SQL Server database, the format of the stored data is also hard to use. However, we can work around this problem.

A typical monitoring setup includes the following scripts:

A Performance Monitor Counter Log file containing a set of monitoring counters stored as an HTML file.
The script creating the SQL Server database table containing proper fields for the Counter Log’s list of counters.
A script for copying data from the performance log file to the table mentioned above.
Scripts to analyze the resulting performance data.

The typical SQLperfmon Counter Logs I usually use contain the following counters:

Memory counters
     - Pages/sec
     - Page Reads/sec
Disk counters for every physical disk
     - Avg. Disk Queue Length,
     - Avg. Disk sec/Transfer
Processor counters
     - % Processor Time – _Total
System counters
     - Processor Queue Length
SQL Server counters
     - Access Methods – Page Splits/sec
     - General Statistics – User Connections
     - Buffer Manager – Buffer Cash Hit Ratio

Observing the Counter Log HTML file, you’ll notice that a computer name is not mentioned anywhere in the script, thus this SQLperfmon.htm file can be installed on any machine running Windows 2000 Server with SQL Server 2000. Here is the script for SQLperfmon.htm:

In order to prepare and execute the monitoring processes you should perform the following steps on the server you will monitor:

Create a C:PerfLogs folder and copy SQLperfmon.htm to this folder.
Start the Windows Performance Monitor console by clicking the Start button on your desktop, selecting theRun option, and typing in a “perfmon” command.
On the Tree tab, expand Performance Logs and Alerts. Right-click on Counter Logs and choose New Log Settings From… (Figure 1).

Figure 1
Select the SQLperfmon.htm file from the C:PerfLogs folder (Figure 2).

Figure 2
The Counter Log properties window appears (Figure 3).

Figure 3

On the General tab, you can see the performance log file name, a list of all the predefined counters, and the monitoring interval. You can change the log file name and the monitoring interval without any impact on the described monitoring process. The counters list assumes that the monitored server has two physical disks: “0 C:” and “1 D:”. If the disk configuration is different from the counter list, you can modify the counter list along with the corresponding PerfmonDataCustomer01 table and import scripts.

On the Schedule tab, you can define when to start and stop monitoring. For example, you may want to monitor the server only during business hours. In order to change the location of the file or the file name pattern, use the Log Files tab. When editing, make sure that the file type remains “Text File – CSV.”
Click OK, and the new SQLperfmon counter log will be added to the log list. You can start the monitoring process immediately, or wait for it to be started by the scheduler, if set (Figure 4).

Figure 4
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.

SJperformanceset.htm:

SJperformanceset Table Script:

CREATE TABLE [SJperformanceset] (
     [CounterDateTime] [datetime] NOT NULL,
     [Available MBytes] [numeric](18, 2) NULL,
     [Page Reads/sec] [numeric](18, 2) NULL,
     [Percent Idle Time] [numeric](18, 2) NULL,
     [Avg Disk Queue Length] [numeric](18, 2) NULL,
     [Processor Time] [numeric](18, 2) NULL,
     [Full Scans/sec] [numeric](18, 2) NULL,
     [Cache Hit Ratio] [numeric](18, 2) NULL,
     [Transactions/Sec] [numeric](18, 2) NULL,
     [User Connections] [numeric](18, 2) NULL,
     [Average Wait Time] [numeric](18, 2) NULL,
     [Processor Queue Length] [numeric](18, 2) NULL
)
GO
ALTER TABLE [SJperformanceset] WITH NOCHECK ADD
     CONSTRAINT [PK_SJperformanceset] PRIMARY KEY CLUSTERED
     (
      [CounterDateTime]
     )
GO

Insert Monitoring Data Script:

INSERT INTO [SJperformanceset] (
     [CounterDateTime]
     ,[Available MBytes]
     ,[Page Reads/sec]
     ,[Percent Idle Time]
     ,[Avg Disk Queue Length]
     ,[Processor Time]
     ,[Full Scans/sec]
     ,[Cache Hit Ratio]
     ,[Transactions/Sec]
     ,[User Connections]
     ,[Average Wait Time]
     ,[Processor Queue Length]
)
SELECT
     [(PDH-CSV 4#0) (Pacific Standard Time)(480)]
     ,cast([MemoryAvailable MBytes] as float)
     ,cast([MemoryPages/sec] as float)
     ,cast([PhysicalDisk(0 C:)% Idle Time] as float)
     ,cast([PhysicalDisk(0 C:)Avg# Disk Queue Length] as float)
     ,cast([Processor(_Total)% Processor Time] as float)
     ,cast([SQLServer:Access MethodsFull Scans/sec] as float)
     ,cast([SQLServer:Buffer ManagerBuffer cache hit ratio] as float)
     ,cast([SQLServer:Databases(pubs)Transactions/Sec] as float)
     ,cast([SQLServer:General StatisticsUser Connections] as float)
     ,cast([SQLServer:Locks(_Total)Average Wait Time (ms)] 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’)…SJperformanceset_02272122#csv

If you need to consistently monitor the database server’s performance and analyze the log data, you can run both the file copying process and the data importing process using the scheduler.

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] (
     [CounterDateTime]
…
)
SELECT
     [(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.

SQLSkills blogspot

Search This Blog

GUNNU

Microsoft SQL Server Tuning Tips (Part 2)

Microsoft SQL Server Tuning Tips( Part1)

Translate

My Blog List