Back

SECTION 1

Query performance tuning remains an important part of today’s database applications. Yes, hardware performance is constantly improving. Upgrades to SQL Server—especially to the optimizer, which helps determine how a query is executed, and the query engine, which executes the query—lead to better performance all on their own.

There are, however, many pitfalls for the unwary. As a result, a proven process is required to ensure that you correctly identify and resolve performance bottlenecks.

• Identifying problematic SQL queries
• Analyzing a query execution plan
• Evaluating the effectiveness of the current indexes
• Avoiding bookmark lookups
• Evaluating the effectiveness of the current statistics
• Understanding parameter sniffing and fixing it when it breaks
• Analyzing and resolving fragmentation
• Optimizing execution plan caching
• Analyzing and avoiding statement recompilation
• Minimizing blocking and deadlocks
• Analyzing the effectiveness of cursor use
• Applying in-memory table storage and procedure execution
• Applying performance-tuning processes, tools, and optimization techniques to optimize SQL workloads

In this lesson we are not going to cover the following since we are certainly covering on T-SQL

so there will be no coverage of the following but we will see that in another upcoming reading lessen or video lessen.
• Hardware choices
• Application coding methodologies
• Server configuration (except where it impacts query tuning)
• SQL Server Integration Services
• SQL Server Analysis Services
• SQL Server Reporting Services
• PowerShell

The Performance Tuning Process

What is the Performance Tuning Process consists of?

The performance tuning process consists of identifying performance bottlenecks, prioritizing the identified issues,
troubleshooting their causes, applying different resolutions, and quantifying performance improvements—and then
repeating the whole process again and again. In doing this tuning, it is necessary to be a little creative, since most of the time there is no one silver bullet to improve performance.

What is its Core Process?

During the tuning process, you must examine various hardware and software factors that can affect the performance
of a SQL Server–based application. You should be asking yourself the following general questions during the
performance analysis:

• Is any other resource-intensive application running on the same server?

Having another resource-intensive application running on the same server can limit the resources available to SQL Server. Even an application running as a service can consume a good part of the system resources and limit the resources available to SQL Server. For example, applications may be configured to work with the processor at a higher priority than SQL Server. Priority is the weight given to a resource that pushes the processor to give it greater preference when executing. To determine the priority of a process, follow these steps:

  1. Launch Windows Task Manager.
  2. Select View ➤ Select Columns.
  3. Select the Base Priority check box.
  4. Click the OK button

These steps will add the Base Priority column to the list of processes. Subsequently, you will be able to determine that the SQL Server process (sqlservr.exe) by default runs at Normal priority, whereas the Windows Task Manager process (taskmgr.exe) runs at High priority. Therefore, to allow SQL Server to maximize the use of available resources, you should look for all the nonessential applications/services running on the SQL Server machine and ensure they are not acting as resource hogs.

Is the capacity of the hardware subsystem capable of withstanding the maximum workload?

Improperly configuring the hardware can prevent SQL Server from gaining the maximum benefit from the available resources. The main hardware resources to be considered are processor, memory, disk, and network. If the capacity of a particular hardware resource is small, then it can soon become a performance bottleneck for SQL Server. While I’m not covering hardware choices, as a part of tuning queries, you do need to understand how and where you may see performance bottlenecks because of the hardware you have. Chapters 2, 3, and 4 cover some of these hardware bottlenecks in detail

• Is SQL Server configured properly?

You should also look at the configuration of SQL Server, since proper configuration is essential for an optimized application. There is a long list of SQL Server configurations that defines the generic behavior of a SQL Server installation. These configurations can be viewed and modified using a system stored procedure, sys.configurations. Many of these configurations can also be managed interactively through SQL Server Management Studio.

Since the SQL Server configurations are applicable for the complete SQL Server installation, a standard configuration is usually preferred. The good news is that, generally, you need not modify the majority of these configurations; the default settings work best for most situations. In fact, the general recommendation is to keep most SQL Server configurations at the default values.

The default settings on the model database are adequate for most systems. You should probably adjust autogrowth settings from the defaults, but many of the other properties, such as autoclose or autoshrink, should be left off, while others, such as
the automatic creation of statistics, should be left on in most circumstances

Does the shared environment, whether VM or platform, have adequate resources, or am I dealing with a configuration issue there or even resource contention from outside forces?

If you’re running inside of some hosted environment, you might be sharing a server with a number of other virtual machines or databases. In some cases, you can work with the vendor or your local administrators to adjust the settings of these virtual environments to help your SQL Server instance perform better. But, in many circumstance you’ll have little to no control over the behavior of the systems at all. You’ll need to work with the individual platform to determine when you’re hitting limits on that platform that could also be causing performance issues.

• Is the database connection between SQL Server and the database application efficient?

Poor connectivity between SQL Server and the database application can hurt application performance. One
of the questions you should ask yourself is, how good is the database connection? For example, the query executed
by the application may be highly optimized, but the database connection used to submit this query may add
considerable overhead to the query performance. Ensuring that you have an optimal network configuration with
appropriate bandwidth will be a fundamental part of your system setup. This is especially true if you’re hosting your
environments on the cloud.

• Does the database design support the fastest data retrieval (and modification for an updatable database)?

The design of the database should also be analyzed while troubleshooting performance. This helps you understand not only the entity-relationship model of the database but also why a query may be written in a certain way. Although it may not always be possible to modify an in-use database design because of wider implications on the database application, a good understanding of the database design helps you focus in the right direction and understand the impact of a resolution. This is especially true of the primary and foreign keys and the clustered indexes used in the tables

• Is the user workload, consisting of SQL queries, optimized to reduce the load on SQL Server?

The application may be slow because of poorly built queries, the queries might not be able to use the indexes, or perhaps even the indexes themselves are inefficient or missing. If any of the queries are not optimized sufficiently, they can seriously impact other queries’ performance. I cover index optimization in depth in SECTION 8, 9, 11, 12 and 13. The next question at this stage should be, is a query slow because of its resource intensiveness or because of concurrency issues with other queries? You can find in-depth information on blocking analysis in SECTION 20.

• What processes are causing the system to slow down as reflected in the measurement of various wait states, performance counters, and dynamic management objects?

When processes run on a server, even one with multiple processors, at times one process will be waiting on another to complete. You can get a fundamental understanding of the root cause of slowdowns by identifying what is waiting and what is causing it to wait. You can realize this through operating system counters that you access through dynamic management views within SQL Server and through Performance Monitor. We cover this information in SECTION 2–4 and in SECTION 20. The challenge is to find out which factor is causing the performance bottleneck. For example, with slow-running SQL queries and high pressure on the hardware resources, you may find that both poor database design and a non optimized query workload are to blame. In such a case, you must diagnose the symptoms further and correlate the findings with possible causes. Because performance tuning can be time-consuming and costly, you should ideally take a preventive approach by designing the system for optimum performance from the outset.

To strengthen the preventive approach, every lesson that you learn during the optimization of poor performance should be considered an optimization guideline when implementing new database applications. There are also proven best practices that you should consider while implementing database applications. We present these best practices in detail throughout the book, and Chapter 26 is dedicated to outlining many of the optimization best practices.

If any of the above factors is not configured properly, then the overall system performance may suffer.

Please ensure that you take the performance optimization techniques into consideration at the early stages of your database application development.

Where to Focus Efforts

When you tune a particular system, pay special attention to the data access layer (the database queries and stored procedures executed by your code or through your object relational mapping engine or otherwise that are used to
access the database). You will usually find that you can positively affect performance in the data access layer far more than if you spend an equal amount of time figuring out how to tune the hardware, operating system, or SQL
Server configuration. Although a proper configuration of the hardware, operating system, and SQL Server instance is essential for the best performance of a database application, these fields have standardized so much that you usually
need to spend only a limited amount of time configuring them properly for performance.
Application design issues such as query design and indexing strategies, on the other hand, are unique to your code and data set. Consequently, there is usually more to optimize in the data access layer than in the hardware, operating system, or SQL Server configuration

SQL Server Performance Killers

Let’s now consider the major problem areas that can degrade SQL Server performance. By being aware of the main performance killers in SQL Server in advance, you will be able to focus your tuning efforts on the likely causes. Once you have optimized the hardware, operating system, and SQL Server settings, the main performance killers in SQL Server are as follows, in a rough order (with the worst appearing first):

• Insufficient indexing

Insufficient indexing is usually one of the biggest performance killers in SQL Server. In the absence of proper indexing for a query, SQL Server has to retrieve and process much more data while executing the query. This causes high amounts of stress on the disk, memory, and CPU, increasing the query execution time significantly. Increased query execution time then can lead to excessive blocking and deadlocks in SQL Server. You will learn how to determine indexing strategies and resolve indexing problems in SECTION 8-12.

Generally, indexes are considered to be the responsibility of the database administrator (DBA). However, the DBA can’t proactively define how to use the indexes, since the use of indexes is determined by the database queries and stored procedures written by the developers. Therefore, defining the indexes must be a shared responsibility since the developers usually have more knowledge of the data to be retrieved and the DBAs have a better understanding of how indexes work. Indexes created without the knowledge of the queries serve little purpose.

<<<PREVIOUS SESSION ||||||||||||||||||| NEXT SESSION>>>

Unlock your potential and shape the future of database administration with SQL DBA School – where learning meets innovation!

sql classes free online - sql training course online

SQL DBA School provides an in-depth, comprehensive curriculum designed to empower students with essential skills and knowledge in database administration.

Work Hours

“SQL DBA School: Navigating You to Database Excellence!”

Digital Marketing Courses Template Kit by Jegtheme
Copyright © 2022. All rights reserved.