Overview of SQL Server 2019 Performance Tuning
In todays world, most organizations are generating a huge amount of data and they are storing them in the databases. SQL Server is one of the most preferred relational database solutions on the market and plays a role as a data server for mission-critical applications. When considered from this point of view, any delay or performance decrease occurs on the database server might cause huge problems. To overcome SQL Servers performance issues, we need to have enough experience and knowledge, the following tips will help you understand the common performance problems in a simple manner and also explains where they need to look.
What Is SQL Server Performance Tuning?
SQL Server performance tuning encompasses a set of processes and procedures designed to optimize relational database queries, so they can run as efficiently as possible. SQL tuning involves several elements, including identifying which queries are experiencing slowdowns and optimizing them for maximum efficiency.
DBAs can attempt to rectify server performance problems at the system level, usually by incorporating additional memory and processors. But these measures are expensive, and they may not be effective at resolving the problem of slow queries. SQL performance tuning helps you locate poorly written SQL queries and instances of ineffective indexing. After doing so, you may find you dont need to invest in improving hardware or technical specifications.
SQL performance tuning can be tricky, especially when performed manually. Even small adjustments can have far-reaching effects on SQL Server and database performance.
Why Is SQL Server Performance Tuning Important?
Most businesses consider storage and information access to be primary functions. Internal and external users expect websites and applications to work quickly and effectively, which means servers and databases need to function as efficiently as possible.
A query delay of a few milliseconds may not seem like much, but it can quickly add up if each of your database queries is experiencing a similar delay. Combine this with huge amounts of continually generated data and retrieving information from a database can become timeconsuming. When business-critical operations become slow, the entire businesss functionality will likely be impacted.
Successful SQL tuning requires DBAs to stay on top of SQL Server performance and ensure database-related processes run smoothly.
Monitoring is recommended in dynamic environments. When it comes to SQL Server users, DBAs, and the system itself are performing events constantly changing data and database structure, user privileges, etc. SQL Server can manage automatically all these requests, but its self-tuning doesnt provide best results, and additional monitoring and manual tuning can provide better performance.
Analyze Wait Statistics
SQL Server performs tasks with the help of the threads and these threads need different resources to accomplish their tasks. Sometimes, the required resources are not available, so the thread begins to wait for this resource until it becomes available. The elapsed time until the source is available is called wait time.
At the same time, resource wait times are measured and stored by the SQL Server, and these measured values are called wait statistics or wait stats. Wait stats are the first step of the SQL Server performance tuning because it includes information that provides us to figure out where the bottleneck or issue can be. SQL Server offers a dynamic management view that returns information about the wait statistics. The following query returns the accumulated wait statistics since the SQL Server last started or wait stats cleared.
SELECT * FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
We can use the SQL Server wait types dictionary to find the description of the wait types. This allows us to have clearer information about which issue is indicated by the wait_type column. However, the wait statistics solely will not be enough to diagnose the root of the problem therefore we need to use other DMVs and tools to find out the crux of the problem. Such as the Performance Monitor ( Perfmon ) can help to identify the problem more clearly. Assume that, we see the PAGEIOLATCH_SH wait type has the biggest wait time. In this circumstance, at first, we can read the PAGEIOLATCH_SH description and suggested solutions. We understand that this wait type can be related to the followings problems:
Insufficient disk performance
Poorly designed queries
SQL Server memory setting or amount of the memory Page compression option
Now we will take a glance at which tools can help to investigate the insufficient disk performance item. To identify the details of I/O problems, we can use Perfmon and the following counters will give detailed information about the disk subsystem performances:
Measured term Counter
Total IOPS Disk Transfers/sec
Throughput Disk Bytes/sec
Read Latency Avg. Disk sec/Read
Write Latency Avg. Disk sec/Write
Also, we can use dm_io_virtual_file_stats dynamic view because it gives the I/O statistics of the database files. The following query will return the database file latency.
SELECT DB_NAME(vfs.database_id) AS database_name ,physical_name AS [Physical Name], size_on_disk_bytes / 1024 / 1024. AS [Size of Disk] , CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [Average Read latency] , CAST(io_stall_write_ms/(1.0 + num_of_writes) AS NUMERIC(10,1)) AS [Average Write latency] , CAST((io_stall_read_ms + io_stall_write_ms)
/(1.0 + num_of_reads + num_of_writes)
AS NUMERIC(10,1)) AS [Average Total Latency], num_of_bytes_read / NULLIF(num_of_reads, 0) AS [Average Bytes Per Read], num_of_bytes_written / NULLIF(num_of_writes, 0) AS [Average Bytes Per Write]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id ORDER BY [Average Total Latency] DESC
Optimize TempDB
TempDB is a system database and used for various temporary operations by SQL Server. TempDB is a shared resource and it is used by all databases on the instance. Insufficient TempDB database performance can degrade the performance of the following operations:
Creating local or global temporary tables Table variables
Online indexing
Snapshot Isolation Levels
Cursors
Multiple Active Record Sets (MARS)
TempDB performance is very important to the SQL Server performance tuning. In order to improve the performance of the TempDB, we can apply the following best practices.
Create multiple tempdb files with the same size
Locate the TempDB files on the separated place from the user database files Locate the TempDB to fastest disk subsystems
Consider using Memory-Optimized TempDB Metadata (SQL Server 2019) Track TempDB growing
Check memory options of the SQL Server
SQL Server allocates memory to cache data and index pages. Also, it caches the compiled execution plans in the memory. SQL Server is designed to allocate maximum memory as possible and it does not release this memory. In this case, the operating system may start using the paging file due to insufficient memory and it causes slowing and long response times on the operating systems. Setting the maximum and minimum memory option for effective SQL Server performance tuning can help to avoid this type of problem. We can use the following straightforward formula if the SQL Server instance is running on a non-shared server.
