In situations where I need to analyze the performance of a SQL Server I used a checklist to get an overview of the configuration of the SQL Server. This will costs 10 minutes to fill in but it will is a good start to find the bottleneck in the SQL Server configuration. In this blog you will find a checklist for the hardware configuration and database settings. At the end you will find the Windows Performance counters and a SQL profile trace definition I uses as starting point.
Performance problems can consist of one or more of the following:
- Hardware problems: Hardware components could be malfunctioning. Faulty components can cause severe performance problems.
- Hardware capacity: You might be exceeding the capacity of the system components. You might need post capacity planning or to reconfigure existing hardware.
- Application problems: SQL statements might be inefficient, causing excessive usage of system resources
Check the event log for errors; check the event log of your storage system
The three most common components on your system that require tuning:
- Disk subsystem
- Processor: A general rule is that if your processor utilization stays continuously at 80 percent or above, or if it peaks often at this rate, you might have a CPU bottleneck. Performance counter: Processor object, % Processor Time counter.
- Disk subsystem: SQL server uses the following three categories of files for databases:
• Primary data files. A primary data file contains startup information for a database, points to other files used by the database, stores system tables and objects, and can also store database data and objects. Each database has exactly one primary file. (File extension .MDF)• Secondary data files. Secondary files are optional for each database. They can be used to hold data and objects, such as tables and indexes, which are not in the primary file. A database might need one or more secondary files placed on separate disks to spread data across the disks. (File extension .NDF)• Log files. A log file holds all the transaction log information for the database and cannot be used to hold any other data. (File extension .LDF). For performance reasons it is recommended to place the LDF file on a separate hard disk. The LDF should be the only file on this hard disk, otherwise the advantage of a separate hard disk for log operations is lost.• You can combine two or more disks into a RAID array. The main characteristic of a RAID array is that physical disk drives are combined to form a logical disk drive, which is a virtual disk drive. When configuring logical drives you need to select the RAID level:• RAID 0. Does not support redundancy. There is no fault tolerance, so if one disk fails, all data will be lost. RAID 0 is not recommended for storing SQL Server data files.• RAID 1. Also known as mirroring. Use RAID 1 when data fits on one disk drive. Use RAID 1 for your operating system disk. It can be time consuming to rebuild an OS in the event of a failure. Since the OS usually fits on one disk, RAID 1 is a good choice. Use RAID 1 for the transaction log. Typically the SQL Server transaction log can fit on one disk drive. In addition, the transaction log performs mostly sequential writes. Only rollback operations cause reads from the transaction log. Thus you can achieve a high rate of performance by isolating the transaction log to its own RAID I volume. Use write caching on RAID I volumes. Because RAID 1 writes will not finish until both writes have been done, you can improve performance of writes by using a write cache. When you use a write cache be sure that it is backed up by a battery.• RAID 5. Any disk volume that does more than 10 percent writes is not a good candidate for RAID. Use write caching on RAID 5 volumes. Since a RAID 5 write is not complete until two reads and two writes have been performed, you can improve the response time of writes through the use of a write cache. When you use a write cache, be sure that it is backed up with a battery. However, the write cache is not a cure for overdriving your disk drives. You must still stay within the capacity of those disks. As you can see, RAID 5 is economical, but at a performance price.• RAID 10. RAID 10 is a combination of RAD 0 and RAID 1. RAID 10 involves mirroring a disk stripe. Each disk has an exact duplicate, but each disk contains only a part of the data. This configuration gives you the fault—tolerant advantages of RAID 1 with the convenience and performance advantages of RAID 0. Use RAID 10 whenever the array experiences more than 10 percent writes. RAID 5 does not perform well with large numbers of writes. Use RAID 10 when performance is critical. Since RAID 10 supports split seeks, performance is very good. Use write caching on RAID 10 volumes. Since RAID 10 writes are not complete until both writes are done, you can improve the performance of writes by using a write cache. Write caching is only safe when used in conjunction with caches that are backed up with batteries.To determine the load placed on the individual disk drives in the system, you must perform some calculations based on next performance counters:
- Physical Disk Disk Reads/sec
- Physical Disk Disk Writes/sec
With next formulas you can determine how many I/Os are actually going to each disk in the array. RAID 0: I/Os per Disk = (Reads + Writes) Number of Disks
RAID 1: I/Os per Disk = (Reads + (2 * Writes))/2
RAID 5: I/Os per Disk = (Reads + (4 * Writes))/ Number of Disks
RAID 10: I/Os per Disk = (Reads + (2 * Writes))/ Number of Disks
|RAID level||Performance||Fault Tolerance||Cost|
|RAID 0||Best||No fault tolerance||Most economical|
|RAID 1||Good||Good||Most expensive|
|RAID 5||Good reads slow writes||Ok||Most economical Fault tolerant|
|RAID 10||Good||Excellent||Most expensive|
|Disk capacity||18 Gb||Unformatted disk capacity|
|Rotational speed||15.000 rpm||Speed at which the disk is spinning|
|Transfer rate||40 M/b sec||Speed of the SCSI bus|
|Average seek time||3,9 ms (read)|
4,5 ms (write)
|Times it takes to seek between tracks during random I/Os|
of actually I/O’s per second gets to maximum capacity the longer the latencies get. The disk queue lengths will growth which results in performance lose.
It is best to dedicate your database server to SQL Server applications only, if possible. That allows SQL Server to use as much memory as possible in the system without having to share it with other applications. SQL Server 2008 supports the maximum support memory of the operating system.
SQL Server 2008
|Windows Server 2003/2008 X86|
|Windows Server 2003/2008 X64 Web or Standard|
|Windows Server 2003/2008 X64 Enterprise or Datacenter|
for the operating system, instead of 2 GB
|Amount of system memory||Flag to add to the boot.ini file|
|8 GB||/3GB /PAE|
|16 GB||/3GB /PAE|
|Above 16 GB (16 GB to 64 GB)||/PAE|
setting to take effect. The AWE option is an advanced option. You must have show advanced options set to 1 to view the current values of advanced options or to change an advanced option using sp_configure. To configure show advanced op/ions, use the following statement: sp_configure “show advanced options”, 1
|Number of CPHU’s|
|Physical RAM amount|
|If physical memory is more than 4 GB, what is the contents of the |
|Total number of physical drives in each array|
|RAID level of array used for SQL server databases|
|Hardware versus Software RAID|
|Location of operating system|
|Location of SQL server executables|
|Location of SWAP file|
|Location of Tempdb|
|Location of System databases|
|Location of user databases (MDF)|
|Location of log files (LDF)|
|What is the size of all user databases|
|How many free disk space is available on each array|
|Is write back cache in Disk controller On or Off|
|Speed of disk drives (RPM)|
|What is the average seek time of the hard disk|
|What is the rotational latency of the hard disk|
|What is the speed of the network card|
|Are the network cards hard coded for Speed/Duplex|
|How many database are attached in SQL server|
|Is this Physical server dedicated to SQL server|
|Is “NTFS data file encryption and compression” turned off|
|Is the Windows 2003 server configured as stand alone server|
|Is the “application response” setting, set to “Optimize Performance for |
Location of operating system.
For best performance, operating system should be on a disk array that does not
include the SQL server data files.
Location of SQL server executables.
The location of the SQL server executables, is not critical, as long as they are not located on the same array as the SQL server data files.
Location of the swap file.
The location of the swap file is not critical, as long as they are not located on the same array as the SQL server data files.
Location of the Tempdb.
If the tempdb is heavily used, consider moving it to an array of its own, either RADI 1 or RAID 10, to boost disk I/O performance. Avoid RAID 5 arrays as they can be slow when writing data.
Location of User databases.
For best performance, user databases should be located on their own array (RAID 1,5 or 10), separate from all other data files, including log files.
How many free disk space is available ? While the performance effect isn’t huge, it is important that all of your disk arrays have at least 20% of free space. This is because NTFS needs extra space to work efficiently. If space is not available, then NTFS is not able to function at its full capacity and performance can degrade. It also leads to more disk fragmentation.
Is write back cache in Disk Controller on or off ?
Are the network cards hard coded for speed/Duplex ?
It is fairly common for a network card to auto-sense incorrectly, setting a less than optimum speed or duplex setting, which can significantly hurt network performance. So set manually the card’s speed and duplex setting.
Is this physical server dedicated to SQL server ?
SQL server should run on a dedicated physical server, not shared with other application software. When you share SQL server with other software, you force SQL server to fight over physical resources, which make it much more difficult to tune your server for optimum SQL server performance.
Database configuration settings:
|Database configuration setting||Default value||Current value, if other than default specify reason.|
|Database auto grow||On|
|Transaction log auto grow||On|
By default set to OFF, only useful to set On when you have a lot of SQL databases on your server which are used incidental. When set to ON, the database is closed and shut down cleanly when the last user of the database exits and all processes in the database complete, thereby freeing any resources. The database reopens automatically when a user tries to use the database again. If the database was shut down cleanly, the database is not reopened until a user tries to use the database the next time SQL Server is restarted. When set to OFF, the database remains open even if no users are currently using the database. The Auto_close option should not be used for databases accessed by an application that repeatedly makes and breaks connections to SQL Server. The overhead of closing and reopening the database between each connection will impair performance.
When set to On, statistics are automatically created on columns used in a predicate. Adding statistics improves query performance because the SQL Server query optimizer can better determine how to evaluate a query.
When set to On, existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed.
By default set to Off, because mostly every database will growth in size during usages.
Database auto grow:
By default set to On, the databases can growth whenever necessary. There is only one restriction, you need to have diskspace available on the array where the database is stored.
Transaction log auto grow: By default set to On,
The transaction log can growth whenever necessary. There is only one restriction, you need to have diskspace available on the array where the transaction log is stored.
Can be set to simple if only full back ups are made and no transaction log back ups are made. Use the performance monitor to collect data of the SQL server. Define a trace log with next counters and set the interval time on 1 minute.
Performance monitor counters:
|Network Interface||Current Bandwidth|
|Network Interface||Bytes Sent/sec|
|Network Interface||Bytes Received/sec|
|Physical Disk||Avg. Disk sec/Read|
|Physical Disk||Avg. Disk sec/Write|
|Physical Disk||Read queue length|
|Physical Disk||Write queue length|
|Physical Disk||Avg. Disk Reads/sec|
|Physical Disk||Avg. Disk Writes/sec|
|Processor||% Processor Time|
|Processor||Processor Queue length|
|SQL Server: Cache manager||Cache Hit ratio|
|SQL Server: Databases||Transactions/sec|
|SQL Server: General statistics||User connections|
|System||Processor Queue length|
over the interval period.
important because prolonged read and write operations indicate an over utilized
important because prolonged read and write operations indicate an over utilized
the maximum recommended value for this counter. If the MDF file stored on 5 disks the maximum allowed queue length is 10 (5*2).
SQL Server: Cache Manager
ratio is 90 percent or more.
SQL Server: Databases
SQL Server: General statistics
Use the SQL profiler to search for queries which results in a lot of reads or
writes. Define a trace file with
Events: Stored procedures: RPC Completed
Filter: Reads Greater than or equal 100.000