SQL Server Performance Tuning Basics
There are several factors that affect the performance of an application. This includes SQL Server (the program itself), the SQL Server configuration settings, the application’s Transact-SQL Code, the operating system (server and client), the hardware (server and client), the network hardware and bandwidth (LAN and WAN), the number of clients, client usage patterns, type and quantity of data stored in SQL Server and whether the application is OLTP- or OLAP-based
The success of a business can be reliant on the performance of SQL server. There are several ways to optimise server performance, a major method is testing at all the stages of application development. Emphasising the need to integrate the testing procedure with development processes starting from the earliest stages of application development and database design to be continued throughout the process.
Improvement by performance tuning the SQL Server occurs only after adopting the above-described process. This is because most scalability and performance related issues arise during the design stage. Should these issues have arise there is little choice but to rewrite the program or be bound to live with performance-related problems.
When starting to performance tune a SQL Server proceed carefully and use one dependent variable at a time. This will help to thoroughly analyse whether the change is working properly. This also avoids confusion regarding whether the change made gives the desired output.
The best way to test a performance tuned SQL Server is under realistic conditions. Using real data for testing against the expected data set; also use the actual hardware that will be used when the application goes actually into production.
When conducting performance tuning on a SQL Server the best approach is to address one problem at a time. Not all performance related problems are noticeable; some performance related problems are the result of more than one problem.
Before commencing performance tuning, it’s highly advised to have a thorough understanding of the technologies affecting application performance. These include the application language, database and application design, the SQL transactions such as how it indexes and stores in addition to networks and servers.
Performance tuning can be done using queries. This method initially involves improving indexes; this can be done by creating useful indexes. These indexes help to find the required data with very few disc input/output operations, resulting in less use of system resources.
To create useful indexes for the performance tuned SQL Server, knowledge of how the data is used is desirable. Consider the kinds of queries, frequency of running and how the query processor uses indexes to find data. While creating indexes check the queries, otherwise they will affect the user experience. Indexes created should aid the queries. Once the index is created, rerun the query to ensure that the desired output is given. If it doesn’t, then remove that index.
The method of indexing on columns used in the WHERE clause of critical queries is the best way to
performance tune SQL Server. It useful to create a selective index, as selectivity depends on the qualifying rows. Selectivity can be defined as the ratio of the qualifying rows to the total rows. When the ratio is low the index becomes very selective thus reducing the result set to a greater extent.
Create multiple column indexes, these type of indexes are extensions of single column indexes. They are very useful in evaluating filter expressions that match a prefix set of key columns. It is advised to not index small tables; scanning a table is easier.
Indexing on the primary keys and the foreign keys will also help in performance tuning SQL Server. The main reason for this is primary and foreign keys are always used to link the tables. Indexes when used with filter clauses can help in speeding the evaluation. All the filter clauses decrease the final set of a query. Some filters help reduce the quantity of data to be scanned. Using sorting operations is another method to help performance tune SQL Server. Different types of sorting operations are DISTICT, ODER BY and GROUP BY.
In some cases the query can be rewritten to use JOIN; which assists in performance tuning SQL Server. The primary advantage is evaluating tables in a different order. Thus a sub query returns to the desired output when it finds the data its trying to locate in the first qualifying row.
There are several tools available in the market which will help performance tuning. These tools help find problems that are related to the performance but also help in the development process. They can be used as troubleshooting tools for the existing applications. Server performance tools are mainly used to monitor and register the workload of a SQL Server. Indexing workload logs enables necessary changes to be made to improve server performance. There some tools that provide the graphical execution plan for the SQL query analysis.
Performance related problems can arise from off the shelf applications. For example, a company has purchased a application which uses the SQL server; in that case performance problems may arise as the application is using the SQL database as the backend.
The other methods to performance tune SQL Server involve managing the RAM cache as RAM is a very limited resource. Managing the RAM cache involves controlling the input/output, thus better utilising buffer cache. This is particularly relevant to recall when working with large data sets and indexes, doing so will reduce overall input/output arguments and improves parallel operations.
Disk input / output subsystem performance should be constantly monitored as disk queuing contributes towards bad performance. If the server is servicing requests from hundreds and thousands of connections, tuning the application and queries becomes extremely important.
Hardware plays an important role that is easily neglected. In some of the applications, issues start only once the application is rolled out. Following application implementation, the design is unable to be changed and thus the only way to improve performance is through hardware. Selecting the optimum hardware for the task depends on a variety of factors such as: the database size, number of end users and how the database is used; through OLTP or OLAP. An experienced DBA developer should be able to estimate optimum specifications for hardware. The best way to understand hardware requirements is to test the application while in development stage.
When purchasing a server, make sure it has the ability to expand the number of CPUs. The most important part of a server tuned for better performance is its memory. Another important factor to consider is the input/output subsystem. Purchase smaller drives over larger drives results in more discs in an array that make input/output faster.
Now the SQL Server is self tuning, meaning it observes what is running and adjusts itself accordingly for optimal performance. Hence why it is advised that application testing should be performed after allocating time for SQL Server to adjust to the workload.
To optimise application design, carry out as many data-centric tasks as possible on SQL Server, these tasks should be in the form of stored procedures. Avoid storing data from the database or creating complex object hierarchies. The design should consider the database connection and object pooling. Design should run queries asynchronously; to ensure that one query is not waiting for the other query to run.