Check the Hardware...
How to measure performance of your SQL server
Hardware problems:
Check the event log for errors; check the event log of your storage system.
Hardware capacity:
The three most common components on your system that require tuning:
- Processor
- Disk subsystem
- Memory
1. 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.
2. 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 |
How to calculate the maximum I/O per second for next disk specifications:
| Specification |
Value |
Notes |
| 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
|
A random I/O on a typical system takes approximately 4.2 ms ((seek time read + seek time write)/2) for the disk to seek to where the data is held and an additional 2 ms in rotational latency, for a total of 6.2 ms. This gives a theoretical maximum of 161 I/Os per second (since 6.2 ins can occur 161 times per second). As noted earlier, if you run a disk drive at more than 85 percent of its capacity, queuing occurs. Therefore, the maximum recommended I/O rate is 137 I/Os per second. Taking into account overhead in the controller, a general rule is to drive these disk drives at no more than 125 1/ Os per second.
So the closer the number 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.
3. Memory: 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. Depending on the operating system and SQL server version support to 64 GB of memory is possible.
| Operating System |
SQL Server Enterprise |
SQL Server Standard |
| Windows 2000 Datacenter Server |
64 GB |
2 GB |
| Windows 2000 Advanced Server |
8 GB |
2 GB |
| Windows 2000 Server |
4 GB |
2 GB |
To allow applications on Windows 2000 Advanced Server and Datacenter Server to address more than 4 GB, these editions support Address Windowing Extensions (AWE). AWE allows physical memory pages above the standard 4 GB memory space to be acquired by applications. To allow AWE with Windows 2000, you must add the /PAE flag to the Boot.ini file. If you have between 4 GB and 16 GB in your system, you can also add the /3GB flag to the Boot file to allow only 1 GB of virtual memory space for the operating system, instead of 2 GB
| Amount of system memory |
Flag to add to the boot.ini file |
| 4 GB |
/3GB |
| 8 GB |
/3GB /PAE |
| 16 GB |
/3GB /PAE |
| Above 16 GB (16 GB to 64 GB) |
/PAE |
You must enable the AWE enabled option by setting it to I (its default is 0). Restart SQL Server for the 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
To enable AWE use the following statement: EXEC sp_configure 'awe enabled', '1'
For more details about enable and configure advanced options see the books online of SQL server.
Before you can analyze the performance monitor log files you need to know the hardware configuration of the SQL server.
Hardware configuration:
Number of CPU’s
CPU MHz
Physical RAM amount
If physical memory is more than 4 GB, what is the contents of the BOOT.INI
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 2000 server configured as stand alone server
Is the “application response” setting, set to “Optimize Performance for background services”
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 ? Write back cache will boost the performance of SQL server, however you should have a controller that offers battery backup. Otherwise you will get a corrupt database in case of a power failure. This is because SQL server already thinks that the data is stored, while it is still in cache. All data in cache is cleared after a power failure.
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. |
| Auto_close |
Off |
|
| Auto_create_statistics |
On |
|
| Auto_update_statistics |
On |
|
Auto_shrink
|
Off |
|
| Database auto grow |
On |
|
| Transaction log auto grow |
On |
|
| Recovery model |
Full |
|
Auto_close: 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.
Auto_create_ statistics: 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.
Auto_update_statitics: When set to On, existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed.
Auto_shrink: 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 logcan growth whenever necessary. There is only one restriction, you need to have diskspace available on the array where the transaction log is stored.
Recovery model: 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:
| Memory |
Available Bytes |
| Memory |
Page faults/sec |
| 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 |
| Server |
Server sessions |
| SQL Server: Cache manager |
Cache Hit ratio |
| SQL Server: Databases |
Transactions/sec |
| SQL Server: General statistics |
User connections |
System
|
Processor Queue length |
Explanation of performance monitor counters:
Memory
- Available bytes The amount of free space in memory
- Page faults/sec The number of page faults per second for code pages and data pages, averaged over the interval period.
Network interface
- Current Bandwidth The current size of the line
- Bytes Sent/sec The number of bytes sent by the system per second, averaged over the interval period.
- Bytes Received/sec The number of bytes received by the system per second, averaged over the interval period.
Physical disk
- Avg. Disk sec/Read The average time (in milliseconds) a read operation takes. This time is important because prolonged read and write operations indicate an over utilized disk.
- Avg. Disk sec/Write The average time (in milliseconds) a write operation takes. This time is important because prolonged read and write operations indicate an over utilized disk.
- Read queue length The actual queue length for read operations. A disk queue of 2 (per disk) is the maximum recommended value for this counter. If the MDF file stored on 5 disks the maximum allowed queue length is 10 (5*2).
- Write queue length The actual queue length for write operations. A disk queue of 2 (per disk) is the maximum recommended value for this counter. If the MDF file stored on 5 disks the maximum allowed queue length is 10 (5*2).
- Avg. Disk Reads/sec Avg. Disk sec/Read is the average time, in seconds, of a read of data from the disk.
- Avg. Disk Writes/sec Avg. Disk sec/Read is the average time, in seconds, of a read of data from the disk.
Processor
- % Processor Time The percentage of time the processor was busy. If more than 80 % you can have a CPU bottleneck.
Server
- Server sessions The number of user sessions currently going on within the server.
SQL Server: Cache Manager
- Cache Hit ratio The percentage of time a record was found in cache. The recommended cache hit ratio is 90 percent or more.
SQL Server: Databases
- Transactions/sec The number of transactions started for the database. These transactions come in the form of requests from client machines that are serviced by the database.
SQL Server: General statistics
- User connections The number of users connected to the database.
System
- Processor Queue length Processor Queue Length is the number of threads in the processor queue. There is a single queue for processor time even on computers with multiple processors. Unlike the disk counters, this counter counts ready threads only, not threads that are running. A sustained processor queue of greater than two threads generally indicates processor congestion. This counter displays the last observed value only; it is not an average.
Application problems:
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
TSQL: SQL: Batchcompleted
Selected Data Groups: Reads
Columns: Eventclass
Textdata
ApplicationName
NTusername
Loginname
CPU
Duration
Writes
Starttime
Filter: Reads Greater than or equal 100.000
Database ID: the database ID of the specific database you want to profile.
Analyze this profile to see if queries can be optimized.
