The Only 10 Tips You Need For SQL Server Performance Tuning

Article views

3331

VIEWS

Is SQL Server that complex? Different dbas hold different views on this relation database management system but that doesn’t change one apparent fact; SQL Server is taking the database world by storm. This RDBMS delivers the best platform for storage and retrieval of data at freewill. It is even better when you optimise your server to achieve maximum performance. That is possible through performance tuning.

Truth be told, many database administrators find performance tuning confusing. How exactly do you do it to ensure optimum results? Well, some things in life just require experience and performance tuning turns out to be one of them. However, even the most experienced dba consultant picked it up from somewhere. You need a concrete foundation. This guide delivers exactly that:

1. No more waiting around

Every time SQL server slows down while operating, the incident is tracked in the wait statistics. In order to comprehend the resources the SQL server is waiting on in such cases, you need to have a fine understanding of this area. For instance, if the greater bulk of your waits is concentrated on page_IO_latch issue, chances are high that there is something wrong with I/O. On the other hand, LCK_XX wait type indicates an issue with blocking. By knowing what each wait implies, you will spend more time focusing on likely problem instead of running a general performance tuning procedure. As such, the first trick is identifying the various waits and narrowing down to specific bottlenecks.

2. Find I/O bottlenecks

If there is one notable aspect that affects the performance of SQL Server, it is I/O bottleneck. To locate I/O issue, you can;

Look for the presence of page_IO_latch and log_write waits in the wait statistics

Find areas where there are excess stalls on I/O using DMF sys.dm_io_virtual_file_stats()

Take advantage of trusty PerfMon counters

Where there are multiple occurrences of I/O bottlenecks, you should locate all the possible queries and tune them before thinking of putting additional hardware.

3. Identify problem queries

For any SQL server you are dealing with, there are chances of about 9 queries or stored procedures responsible for over 80% the tuned bottleneck. By rooting out these problem queries and prioritizing their tuning, you will have an easy time optimizing the general performance of the server.

4. Adjust MAX memory limit

Most people prefer not to interfere with the MAX memory settings. It might not look like a big deal until you remember that there exist some parts of the Operating System in need of memory some time, or most of the times. To avoid any inconvenience, go ahead and adjust this memory setting to 1 or 2GB less than the memory level on the server. The amount of memory you leave should depend on the other running programs and the amount of memory they are likely to demand for efficient operation.

5. Adopt Different Staging Databases

Using separate staging databases in SQL Server instance comes with several benefits. For one, there is the possibility of getting minimally logged load as opposed to fully logged load. With such an arrangement, you can expect faster import process. By creating a different staging database, you will be able to carry out manipulation and importing on that database resulting to less strain on the server.

6. Go slow on tempdp contention

There are certain applications that make extensive use of tempdb. If yours is one of them, there are chances for running on contention with regards to certain components linked with the tempdb files. The good news is, it is possible to minimize the contention. You have various options with the easiest one being observing the statistic waits page for page_latch_UP waits then acting on it.

7. Don’t ignore log files

Many people downplay the essence of transaction log files with respect to performance. As such, they fail to leave sufficient space in the transaction log files that can sustain normal operations. To avoid possible slow-down, you need to ensure the availability of free space in the log file, at any given time. Yet again, you need to minimize the amount of virtual log files within the transaction log.

8. Split log files and data

For enhanced SQL Server performance, it is necessary to separate data and log file. They should be in different physical drives. In return, you will be splitting sequential access of the data files from random access.

9. Maximize use of index

Most people fail to use the DMF, which is a critical source of information. Actively maximizing the use of DMF can give you information on various indexes, how they are used and the level of usage. If you are always wondering about the indexes being used for given tables, this DMF is the solution.

10. Think of reuse

With each growing day, there is an increase in transaction rates for database applications. This is because many people are tempted to load more data on single servers. The result is increased activity within the servers. When query plans compile in SQL Server, it becomes extraordinarily expensive with regards to CPU usage and memory.

Lately, there has been an increase in applications capable of speeding up development without being able to re-use the query plan. It is necessary for you to determine the statements that are not capitalizing on the reuse of query plans. With such information, you can set out to work on the part of applications that are not reusing query plans. Prioritize the ones that need quick action if you want to get real value for your money. Never ignore how the absence of plan reuse can affect SQL server performance with increase in transaction rates.

Conclusion

Identifying the common performance issues in SQL Server is the first step towards capitalizing on optimum performance. By going through this guide and following the information therein, you will be a step closer toward achieving the ultimate goals; that of a highly effective and fast performing database.

Sources

Lucy Jones is database administration expert based in New York. She is widely experienced in the field of database management systems. If you have any question you need to ask a dba consultant, feel free to contact her.