Talking about SQL Server optimization, query tuning is often the fastest way to accelerate SQL Server performance. Most often system-level server performance (memory, processors, and so on) improvement measures are ineffective and expensive.
Expert developers, but also experienced DBAs, believe most performance issues can be traced to poorly written queries & inefficient indexing, not hardware constraints. In fact, some performance issues can only be resolved through query tuning.
Unfortunately, query tuning is not a perfect science, and this process will have to be applied considering each scenario.
However, let’s begin considering the hardware side, in which we will have, usually, to have proper performance (not only for SQL Server), the following guidelines could be useful:
- Use RAID 1+0 with 4 or more spindles. The more disks, the better the performance with this configuration.
- Use a battery backed-up write back caching controller. This improves write performance regardless of the RAID level.
- Use high RPM hard-drives, better if datastores or general disks are SSDs.
- Separate the data, tempdb (especially), and Transaction Log files on separate LUNs.
- Maximize the available RAM (and impose a limit for SQL Server).
- Utilize a 64-bit based architecture.
Symptoms of Performance Issues
The following symptoms are the most typical when talking of an application/database software solution:
- Web (interface or activities) or desktop application performance will be slow (high response time or general slowness).
- Long Running Queries in Orion log files.
- SQL Client timeout errors.
SQL Server – Activity Monitor
Activity Monitor is a tool in SQL Management Studio that shows you activity in real time. Real-time data might not be enough to troubleshoot performance issues.
Open Activity Monitor and right-click the SQL Server in Object Explorer to launch Activity Monitor.
Review Processes in the SQL Activity Monitor
Expand Processes to display all active SQL Processes currently running on the server. There are several useful columns here for troubleshooting:
- Session ID – This is the SQL Process ID or SPID
- Login – SQL Login used for this SPID
- Database – Database the process is running on
- Task State – Current state of the process
- Command – Type of SQL statement
- Application – Application on the host running the process
- Wait Time – Time the Process has spent waiting if suspended
- Wait Type – Why the process is suspended
- Blocked By – SPID Blocking the process if suspended
- Hostname – Hostname executing the process
To filter processes, click on the column header drop-down.
Review Suspended Processes in the Activity Monitor
Processes go into the Suspended state when there are not enough resources available to complete the task. The wait time counter shows how many milliseconds the process has been waiting, the Wait Type explains why the process is waiting. These waits will often result in blocking of other processes which will show in the BlockedBy column.
Common Wait Types are the following:
- ASYNC_NETWORK_IO: This is usually a sign of network latency between the client and the server.
- CXPACKET: Indicates that SQL is waiting on a Parallel process to complete. This can be a sign of resource issues on the SQL server (CPU, MEM, DiskIO) or the query itself is poorly written..
- PAGEIOLATCH_EX: Buffer latches including the PAGEIOLATCH_EX wait type are used to synchronize access to BUF structures and associated pages in the SQL Server database. This can be a sign of resource issues on the SQL server or a poorly written query. When seen in conjunction with the CXPACKET wait Index Fragmentation is often the cause.
- WRITELOG: When a SQL Server session waits on the WRITELOG wait type, it is waiting to write the contents of the log cache to disk where the transaction log is stored. This is almost always a sing of poor disk performance.
- LCK_(X): This Occurs when a resource is in use by another query, usually the result of a UPDATE, INSERT, or DELETE statement. LCK waits are usually caused by resource contention but may also be the results of process blocking from any of the above wait types.
Review Resource Waits in the Activity Monitor
This view will show you the total active and cumulative wait time for each Wait Type. This can be useful for troubleshooting resource issues. Keep in mind that anything less then 1000 ms (Recent Wait Time) is considered normal.
Review Data File I/O in the Activity Monitor
This view displays the response time for all of the SQL database files. This is a useful tool for troubleshooting disk I/O latency. Response times greater than 10 ms cause slowness, greater than 100 ms cause problems.
Review SQL Server Configuration
- Having more files in the filegroup helps the SQL server distribute the load generated by multiple threads while working with files.
- The recommended ratio between the number of cores and the files in the filegroup is typically 4:1 or 2:1 (for example, 16 cores and four files, or 16 cores and eight files).
- The Transaction log should have enough space to grow to 50% of the total database size.
- The size and growth setting for all files in a filegroup must be set to identical values in order to distribute the load evenly.
- For the transaction log, it is not effective to create more files, because the SQL server can only use the first file.
- For the tempdb database, use an SSD disk.
- RAID 1+0, striping and mirroring, is preferred for database files.
- Attached Storage (SAN/NAS) Arrays are supported if throughput is high enough.
OS Power Saving setting to High Performance
In Windows Server 2008 and later OS, the default power saving setting is set to Balanced, which means that components such as CPU and storage will be scaled back if the system is not busy. In some cases, this may result in performance degradation for SQL Server.
If the instance of SQL Server is under heavy load and is hosted by Windows Server 2008 or later OS, set the operating system power saving plan to High Performance.
Why not RAID 5?
RAID 5 is the most common Disk subsystem topology deployed on servers. RAID 5 or 6 may be fine for small environments but will cause performance issues when scaled. This topology is meant for availability and not performance and may cause IO issues in SQL.
SQL and Memory Consumption
SQL does not often release memory once granted. Because of this it may appear that SQL is using all of the available memory on the system. This may not cause performance issues as long as the Operating System has sufficient memory to run effectively. You may need to limit the amount of memory SQL can use to prevent this from happening.
What affects the SQL Server performance on Virtual Machines?
Host Operating System: the SQL Server manages its own space in the database files itself, it reads from and writes directly to the disk. Most other applications let the operating system (OS) control their reads to the disk, through a cache. When you place the SQL Server in a virtual environment, the virtual machine itself is on an external OS. This external OS controls the disks, and so the SQL Server writes could be cached by the host OS without the SQL Server’s knowledge. This might delay writes to the disk on your virtual environment, but there are new software technologies that can avoid this by taking virtual disks more “near” to the physical layer.
Disk space shared by VMs: in virtual environments, the disk space provided to one VM is carved from a larger array that is used for other VMs. If your SQL Server is installed on a VM, its performance could be affected by other VMs attempting to read/write to the same disks. Be sure placing SQL Server node or nodes on physical hosts with low disk I/O activities by other VMs to avoid this kind of scenario.
SQL Database Settings
The following are some of the most common configurations that should be done during a SQL Server implementation (based on the actual scenario that we are facing). Review the following settings for your SQL server and the database:
Maximum Degree of Parallelism (MAXDOP)
This setting specifies the number of processors used to execute a query in a parallel plan. Use the number of physical cores in a single CPU socket.
Where: Server > Advanced
Cost Threshold of Parallelism
This setting specifies when the SQL server creates and runs parallel plans for queries. The default value 5 is rather low, consider using 50 and adjust as necessary.
Where: Server > Advanced
Instant File Initialization
When your SQL server needs to allocate space for operations, it fills the necessary space with zeros. This is often not necessary. To skip this step and use the allocated space for data files immediately, enable the instant file initialization.
To enable instant file initialization, make sure the service account has the Perform Volume Maintenance Tasks privilege enabled.
To enable it during SQL Server Setup we will have to:
- In SQL Server Setup wizard, go to Server Configuration > Service Accounts.
- Select the Grant Perform Volume Maintenance Task box.
Starting with SQL Server 2016, instant file initialization permission can be granted to the SQL service account at install time, during SQL setup.
Using the Windows Local Security Policy tool:
- Open the Local Security Policy tool, and navigate to Security Settings > Local Policies > User Rights Assignment.
- In the pane on the left, select Perform volume maintenance tasks, and add the account under which the SQL Service is running.
- New empty database files are created faster. The growth of transaction log files is not affected because log files need all the zeros.
Database file settings
- Pre-allocate as much disk space as possible to save time.
- Define an absolute auto-growth setting with a reasonable size, such as 1GB, and so on), instead of an auto-growth percentage.
Where: Database > Properties > Files
- Data files: autogrowth 1024 MB.
- Transaction log file: initial size 8192 MB, autogrowth 8192 MB for large environments, while initial size 2048 MB, autogrowth 1024 MB for medium environments.
Do not reserve all memory to the SQL Server, because this can lead to a lack of memory for the host operating system.
The amount of memory to reserve for the operating system depends on the maximum system memory. Use a memory calculator to find out how much to reserve for your system, such as sqlmem (©2016-2018 Microsoft, available at https://archive.codeplex.com/?p=sqlmem) or SQL Max Memory Calculator (©2018 mirontolli, available at http://sqlmax.chuvash.eu/).
If additional resource-intensive services are running on the host operating system, reserve sufficient memory for the host operating system.
CPU setting recommendations
- Ensure that power-saving technologies are disabled on the CPU.
- Recommended Database recovery mode: SIMPLE.
- When you do not need High Availability, cluster, or replication, use the SIMPLE recovery mode.
Where to set Recovery Model: Database > Properties > Options > Recovery model:
If the database was in FULL recovery for some time, the transaction log grew large and you already switched to SIMPLE:
- Call CHECKPOINT from the Management Studio session window once or twice to flush the log.
- Change the size of the transaction log file to one of the above-recommended values (both initial size and autogrowth).
When FULL recovery is needed:
- Create a SQL Agent job that executes database log backups every 5-10 minutes to ensure that your log file does not get too big.
- Create a SQL Agent job that executes regular FULL database backups.
- Ensure that the SQL Agent is enabled at all replicas and the log backup job runs there.
- Monitor the size of the transaction log file and adjust the backup policy so that the backup file does not grow too large.
- Use the sys.fn_hadr_backup_is_preferred_replica to get the replica preferred for backups (Review the following sample backup procedure).
CREATE PROCEDURE p_BackupDatabaseAG
DECLARE @FileName varchar(512) = @BackupPath +
CAST(@@SERVERNAME AS VARCHAR) + ‘_’ + @DatabaseName;
DECLARE @SQLcmd VARCHAR(MAX);
IF sys.fn_hadr_backup_is_preferred_replica(@DatabaseName) = 1
IF @BackupType = ‘FULL’
SET @FileName = @FileName + ‘_FULL_’+
REPLACE(CONVERT(VARCHAR(10), GETDATE(), 112), ‘/’, ”) +
REPLACE(CONVERT(VARCHAR(10), GETDATE(), 108) , ‘:’, ”) + ‘.bak’;
SET @SQLcmd = ‘BACKUP DATABASE ‘ + QUOTENAME(@DatabaseName) +
‘ TO DISK = ”’ + @FileName + ”’ WITH COPY_ONLY ;’;
ELSE IF @BackupType = ‘LOG’
SET @FileName = @FileName + ‘_LOG_’/*+
REPLACE(CONVERT(VARCHAR(10), GETDATE(), 112), ‘/’, ”) +
REPLACE(CONVERT(VARCHAR(10), GETDATE(), 108) , ‘:’, ”) */ + ‘.trn’;
SET @SQLcmd = ‘BACKUP LOG ‘ + QUOTENAME(@DatabaseName) +
‘ TO DISK = ”’ + @FileName + ”’ WITH NOFORMAT, INIT;’;
Enable the Query Optimizer Fixes
In SQL Server 2016 and 2017, make sure the Query Optimizer Fixes setting is on (Database > Properties > Options > Query Optimizer Fixes = ON).
In earlier SQL versions, enable trace flag 4199 at service level. Run the following SQL query:
|DBCC TRACEON (4199);|
Flag 4199 allows you to install bug fixes or performance fixes created after a new major version release. If trace flag 4199 is not enabled, these fixes are hidden and blocked to prevent them from harming other programs.
- Check the compatibility level of the database according to SQL Server version.
- Where to find: Database > Properties > Options > Compatibility level.
- Make sure the highest available option is selected. For example, do not use SQL Server 2008(100) for SQL Server 2017.
Antivirus exclusion for SQL Server
When you configure antivirus software settings, make sure that you exclude the following files or directories on SQL Server machine from virus scanning. Doing this improves the performance of the files and helps make sure that the files are not locked when the SQL Server service must use them. However, if these files become infected, your antivirus software cannot detect the infection.
- SQL Server data files (.mdf, .ndf, .ldf files).
- SQL Server backup files (.bak, .trn files).
- Full-Text catalog files.
- Trace files (.trc files).
- SQL audit files for SQL Server 2008 or later versions (.sqlaudit files).
- SQL query files (.sql files).
- The directory that holds Analysis Services data.
- The directory that holds Analysis Services temporary files that are used during Analysis Services processing.
- Analysis Services backup files.
- The directory that holds Analysis Services log files.
- Directories for any Analysis Services 2005 and later-version partitions that are not stored in the default data directory.
- Filestream data files (SQL 2008 and later versions).
- Remote Blob Storage files (SQL 2008 and later versions).
- The directory that holds Reporting Services temporary files and Logs (RSTempFiles and LogFiles).
Processes to exclude from virus scanning
SQL Server 2016
%ProgramFiles%\Microsoft SQL Server\MSSQL13.<instance Name>\MSSQL\Binn\SQLServr.exe
%ProgramFiles%\Microsoft SQL Server\MSRS13.<instance Name>\Reporting Services\ReportServer\Bin\ReportingServicesService.exe
%ProgramFiles%\Microsoft SQL Server\MSAS13.<instance Name>\OLAP\Bin\MSMDSrv.exe
SQL Server 2014
%ProgramFiles%\Microsoft SQL Server\MSSQL12.<instance Name>\MSSQL\Binn\SQLServr.exe
%ProgramFiles%\Microsoft SQL Server\MSRS12.<instance Name>\Reporting Services\ReportServer\Bin\ReportingServicesService.exe
%ProgramFiles%\Microsoft SQL Server\MSAS12.<instance Name>\OLAP\Bin\MSMDSrv.exe
SQL Server 2012
%ProgramFiles%\Microsoft SQL Server\MSSQL11.<instance Name>\MSSQL\Binn\SQLServr.exe
%ProgramFiles%\Microsoft SQL Server\MSRS11.<instance Name>\Reporting Services\ReportServer\Bin\ReportingServicesService.exe
%ProgramFiles%\Microsoft SQL Server\MSAS11.<instance Name>\OLAP\Bin\MSMDSrv.exe
SQL Server 2008 R2
%ProgramFiles%\Microsoft SQL Server\MSSQL10_50.<instance Name>\MSSQL\Binn\SQLServr.exe
%ProgramFiles%\Microsoft SQL Server\MSSQL10_50.<instance Name>\Reporting Services\ReportServer\Bin\ReportingServicesService.exe
%ProgramFiles%\Microsoft SQL Server\MSSQL10_50.<instance Name>\OLAP\Bin\MSMDSrv.exe
SQL Server 2008
%ProgramFiles%\Microsoft SQL Server\MSSQL10.<instance Name>\MSSQL\Binn\SQLServr.exe
%ProgramFiles%\Microsoft SQL Server\MSSQL10.<instance Name>\Reporting Services\ReportServer\Bin\ReportingServicesService.exe
%ProgramFiles%\Microsoft SQL Server\MSSQL10.<instance Name>\OLAP\Bin\MSMDSrv.exe
If you back up the database to a disk or if you back up the transaction log to a disk, you can exclude the backup files from the virus scanning.
You can run antivirus software on a SQL Server cluster. However, you must make sure that the antivirus software is a cluster-aware version. If you are running antivirus software on a cluster, make sure that you also exclude these locations from virus scanning, for example:
Q:\ (Quorum drive)
Setting appropriate Page file size
Windows uses the paging file (pagefile.sys) as secondary random-access memory (RAM). The paging file and physical memory make up virtual memory. By default, Windows stores the paging file on the boot partition. When Windows 2008 and later page file is configured to “Automatically manage paging file for all drives”, page file is managed by the system according to how much virtual memory and disk space you have.
By default, “Automatically manage paging file size for all drives” setting is selected so that Windows 2008 or later system can manage the paging file without users interruption and configures roughly to two times to the size of physical RAM. If you want to change the paging file size, move the pagefile.sys to another drive, or disable virtual memory paging, uncheck the check box of Automatically manage paging file size for all drives.
To set Paging file to Custom Size:
- Click Start, right-click Computer and select Properties.
- Select the Advanced System Settings.
- Under Performance, click Settings button.
- Click the Advanced tab.
- Under Virtual Memory, click Change button.
- If “Automatically manage paging file size for all drives” is selected, uncheck the option.
- Select the appropriate drives
- Choose Custom size.
- Set Initial Size (MB) and Maximum Size (MB) as appropriate.