SQL Server Performance Tuning can be a difficult assignment, especially when working with a massive database where even the minor change can raise a significant impact on the existing query performance. Performance Tuning always plays a vital role in database performance as well as product performance. A query can be optimized with the proper evaluation of a piece of code or SQL Statements. The important thing in Query Optimization is to understand, which process or expression needs evaluation in the piece of code. Everyone expects to get a quick response from the query on the production, but How?
Performance Tuning deals with the specifics about the Server, Client, Design & Architecture, Database Engine, Hardware & Network design. As the initial step of SQL Server configuration, we need to calculate the required Buffer Memory (RAM), Storage, CPU’s, etc. Different types of hardware are available with various quality in the market to perform the best. The below image represents what hardware configurations are required to be monitored to observe the performance.
Managing the hardware for SQL Server is not the only goal to get high performance with the SQL Server. But systematic designing and monitoring are required to make it available for better long-term performance as well. If database architecture requirement is to configure multiple SQL Server instances in the single machine, then the Database engineer has to calculate and design the plan for hardware allocation according to the usage.
Below you will find the critical performance parameters for performance tuning with different categories.
RAM or Buffer Memory
SQL Server loads the data into the RAM, which is processed before processing or returning to the client. What should be the RAM value for the SQL Server? Buffer memory should be of a size that is enough to maintain active data of your database. Query logic and data manipulation of each SQL request will be performed in the RAM for better performance unless fetching all data from the data page and manipulating it. If your SQL Server is not configured with enough RAM, then the older cache will be removed, and new active data will be kept in the buffer memory. Each edition and version of SQL server has different criteria for the Memory Allocation (RAM).
SQL Server allows configuring the memory by a server property. SQL Server buffer memory can be configured with two parameters, Minimum server memory, and Maximum server memory. Minimum memory allocation will consume the (n) MB memory for buffer when the SQL Server service starts. Maximum memory will be the highest memory allocation for a buffer to the SQL Server as memory available from the hardware.
Processor (CPU)
Multithreading by processors improves the performance of parallel SQL Server thread executions. SQL Server allows specifying the processor sockets (Numa Nodes) with the cores and their Logical CPUs for the processing affinity and I/O affinity. A socket is the numa node at the hardware side; the dual-core processor will have two logical CPU’s and the quad-core will have four logical CPUs. The user system and laptop have a single socket, and servers use multiple sockets to boost performance.
Users can configure the CPU allocation by the server properties. By default, an automatic option will be selected for allocating the processor to SQL Server dynamically as available to process the task. While unchecking the automatic option, the user will be allowed to specify the logical CPUs for the SQL Server thread process and I/O operations.
IO / IOPS (Input/output operations per second)
Most of us know I/O stands for Input/output and IOPS stands for the Input/output per second. IOPS clearly defines a ratio of the number of operations performed by storage within a period to the duration of this period in seconds, and it is the unit to measure read and write operations on the storage device. There are many parameters of IO, which can affect the SQL Server performance and that needs to be taken care of for the performance tuning.
IOPS Calculation
Several Read/Write operations on the Disk. Different types of disks can have different IOPS length
Average Disk Queue Length
Each hardware can perform a limited number of Read/Write operation per second, but if storage has more IO requests than the maximum limit, then that threads or request will be in Disk Queue. It happens when massive read/write operations are being performed parallel to the routine task. So, this average disk queue needs to be monitored for performance tuning
Read Vs. Write Operation Ratio
Performance tuning for a Read and Write operation is different. Based on the nature of product operation, hardware quality can be decided. If write operations are very high, then the user can choose the premium hardware to perform the best IOPS with better SAN box cache
Average IO Latency
The standard reading says that read operation should be completed in less than 15ms, and Write operation should be completed in less than 25ms. If average IO latency is higher than this reading, then disk queue and hardware performance need to be verified
- Read < 15ms
- Write < 25ms
Split IO
A physical disk can be partitioned in the logic disk with the drive letters. We follow Infra standard with the distribution of the data into the multiple logical drives, which in turn organize the IO. Still, each drive may not have the same average IOPS calculation. Therefore, the operation team has to monitor the disk wise IO to track the usage of Physical and Logical disk
RAID Level
RAID configuration is most likely to be used for installed applications on storage, which means you are using multiple disks for reliability and redundancy. Write operations can be powerfully penalized by some RAID operations. At least six disk operations are required for every writes request in RAID level 6 while, only two disk operations are needed for RAID levels 1 and 10. To sum up all this, we can say that the lower the number of disk operations, the higher the IOPS capacity
Basically, RAID Levels are the concept of data striping and mirroring with storage. Below is the list of RAID levels that are implementing by the organizations
- RAID 0 (Data Striping)
- RAID 1 (Data Mirroring)
- RAID 4 (Data Striping with the Parity Information)
- RAID 5 (Data Striping with the Parity Information distribution on all disks)
- RAID 6 (Data Striping with the Double Parity Information for better data redundancy)
- RAID 10 ([RAID 1 + RAID 0] = [Mirroring + Striping] – Hybrid RAID)
DISK Performance
Physical Disk and Logical Disk both can be used in Infra standard. Logical Disk is the logical partitions of the physical disk with the drive letter. The logical disk is used to distribute the data into multiple logical units. Each disk has the IOPS life with the (n) number of total IOPS. When it crosses this digit, disk refreshment may be required to maintain the performance. Therefore, the operational team performs tech-refresh with updating the hardware on the server. Bytes per Second Disk IO is also one of the performance parameters for the disk. Different quality disks have various capacities to perform in a range of IO per second on disk.
With the increasing use of application and engine, a lake of hardware assets degrades the SQL Server performance. The basic knowledge of hardware performance tuning can help Database Administrators to monitor and improve the SQL Server performance.
These monitors and metrics are also costly while fetching details in a predefined polling period. So, configure the detailed monitor for the server, but it should be available when its basic monitors alert the critical situation. Nowadays, many third-party monitors are available in the industry with the configurable parameter to set up as required.
Conclusion
Performance Tuning at the hardware level isn’t a simple assignment for the users. Users can gain that expertise when an issue happens with the hardware resources and solve it with the challenging investigation. And that is the reason, it is a good practice to have a strong Technical Operation team in an organization.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020