Developers often believe that database performance and scalability issues they encounter are issues with the database itself and, therefore, must be fixed by their DBA. Based on what our users tell us, the real root cause, in most cases, is inefficient data access patterns coming directly from their own application code or by database access frameworks they use. Developers can therefore solve database performance issues by simply  fixing their queries or correctly configuring the database access frameworks. As they often lack visibility into what is happening at the database level, they are either not aware or do not understand the problem. To get a quick overview of common patterns check out ASP.NET Database Access Patterns Gone Wild or watch Database Diagnostics with Dynatrace on YouTube.

Most common database access problem: inefficient and excessive database queries caused by misconfigured OR Frameworks or bad custom coding.
Most common database access problem: inefficient and excessive database queries caused by misconfigured OR Frameworks or bad custom coding.

To better support analyzing database related application performance problems we at Dynatrace recently extended our existing support for database monitoring by providing a Database Agent for SQL Server and Oracle (more databases on the short list). In my last blog, I focus on leveraging Execution Plan Caching via Prepared Statements and Bind Values for Oracle Database. In this blog I will focus on SQL Server and Parameterized Queries (the .NET counterpart of Java’s Prepared Statement) and specifically on the Plan Cache Bloat problem caused by using Ad Hoc Queries.

To follow my steps to determine how your current database access performance impacts your application, simply register for your Dynatrace Free Trial License which includes all features shown in this blog. Particularly useful for developers and testers the free trial license will automatically convert to a lifetime Personal License allowing you to eliminate database related performance problems as you develop or test the app. We make it so easy – so there is no excuse any more!

SQL Query Lifecycle

Before a query is executed, the database engine will first parse the statement (check syntax and permissions), then generates an execution plan (this step is called Compilation in SQL Server language) and finally the query will be executed and the result returned to the client.

SQL Server has a memory pool used to store both execution plans and data cache. The execution plan cache stores the execution plans for future reuse. The data cache is used to store data in memory, enabling the database engine to read directly from the memory instead of the disk, which would be much slower.

If the query is already in the cache, the database need not go through the compilation process. It can reuse the existing execution plan in the plan cache.

Creating execution plans can be costly. Caching them will speed up SQL Executions of the whole database!
Creating execution plans can be costly. Caching them will speed up SQL Executions of the whole database!

Query compilation is an expensive process that increases CPU load on busy systems.  SQL Server can only reuse plans for queries when there is a character-for-character match of the query texts. Otherwise, a new execution plan will be generated for every SQL statement.

Parameterized Queries are an alternative way to pass data to the database. Instead of writing the actual values, parameters are inserted into the SQL statement. This way the statements do not change when executing them with different values. Parameterized Queries are also the best way to prevent SQL injection.

Code Examples showing an Ad Hoc query for which an execution plan will be generated every time vs a code example using a Parameterized query for which the same execution plan will be reused.
Code Examples showing an Ad Hoc query for which an execution plan will be generated every time vs a code example using a Parameterized query for which the same execution plan will be reused.

How to quickly identify whether your application uses Ad Hoc vs. Parameterized Queries?

To check whether your application uses Ad Hoc or Parameterized Queries, simply open the Database Dashlet, and sort the queries by number of executions ascending, to spot queries executed only a few times. Check whether you can find similar statements and literal values in the SQL query text.

Use the Database Dashlet to spot Ad Hoc Queries. Sort by Executions or by SQL to spot similar queries!
Use the Database Dashlet to spot Ad Hoc Queries. Sort by Executions or by SQL to spot similar queries!

Impact of High Number of Compilations per Second

If you have too many Ad Hoc queries it means SQL Server is constantly compiling new SQL Statements, consuming excessive CPU. The two SQL Server performance metrics providing insight into what behavior your applications currently have are Batch Request/s and SQL Compilation/s. Batch Requests indicates the number of incoming queries, and Compilations is the number of new execution plans the database must generate. The expected ration between Batch Request and Compilation typically depends on the type of application you are developing. In a transaction-oriented application, you should be able to parameterize most of the queries. In a reporting type application, where many custom queries might be needed, the number of Ad Hoc queries is expected to be higher.

Obviously the lower the Batch Request and Compilation ratio the better. If your ratio exceeds 10% (meaning a new execution plan is generated for more than 10% of the queries), you need to check your queries.

The Dynatrace Database Agent automatically queries the key performance metrics from SQL Server (using SQL Server Performance Counters and Dynamic Management Views).

Dynatrace Database Agent automatically provides all important metrics to identify CPU and plan cache related issues.
Dynatrace Database Agent automatically provides all important metrics to identify CPU and plan cache related issues.

Additionally, you will see a high CPU usage (remember the generation of execution plan is a CPU intensive operation) and might also spot a higher Disk I/O read if your query plan cache is bloating your memory.

Query Plan Cache bloated by Ad-Hoc Queries

As mentioned before, SQL Server has a pool of memory that is used to store both execution plans and data buffers. The total size of memory used by the plan cache to save all the execution plan is dynamic. This means that the percentage of the pool allocation between execution plans and data buffers fluctuates dynamically, depending on the state of the system.

SQL Server has a pool of memory that is used to store both execution plans and data buffers
SQL Server has a pool of memory that is used to store both execution plans and data buffers
With Ad Hoc Queries, the size of the Plan Cache will dynamically expend and “steal” memory from the Data Cache.
With Ad Hoc Queries, the size of the Plan Cache will dynamically expend and “steal” memory from the Data Cache.

The more unique statements (Ad Hoc Queries) are sent to the database, the greater the number of execution plans stored in the cache. These execution plans will be useless, and won’t be reused by other queries, since the text of the SQL statements will differ with each request.

The size of the plan cache will expend automatically and therefore reduce the memory available for the data cache. Memory is wasted on single-use execution plans. It is not uncommon to find SQL Server installation that have hundreds of MBs and even GBs of “wasted” memory!

Since the memory available for the Data Cache will be reduced, SQL Server will have to read from the Disk more often, thus impacting the overall database performance for the applications using it.

The gain insight into the actual SQL Statements executed by your application simply instrument your application with one of our Dynatrace agents (.NET, Java, PHP, etc)  to see which SQL queries need to be adapted as they are currently not leveraging query parameterization:

Dynatrace Database Dashlet shows full SQL statements. Drill down to the PurePath Dashlet to find out which line of code executes those statements and directly jump to the source code in Visual Studio to fix it!
Dynatrace Database Dashlet shows full SQL statements. Drill down to the PurePath Dashlet to find out which line of code executes those statements and directly jump to the source code in Visual Studio to fix it!

With Dynatrace you see the list of SQL Statements band which line of code executes these statements by drilling to the PurePath Dashlet. Using our Visual Studio or Eclipse, you can also directly drill down to the relevant line of codes in your favorite code editor. No more excuse for not fixing your queries!

For more information on how this is done feel free to watch our Diagnostics Tutorials on our Performance Clinic YouTube Channel or APM University.

Using Parameterized Queries to Improve Performance

Once the SQL queries have been adapted to use parameters, the Database Dashlet shows the updated statements – including the parameters. An additional optional feature is to turn on “Bind Value Capturing” which also shows you each execution of Parameterized Statements with the actual values being passed:

Verify that SQLs are now using parameters!
Verify that SQLs are now using parameters!
Verify that SQLs are now using parameters! The value of the parameters used by each call can also be displayed in Dynatrace
Verify that SQLs are now using parameters! The value of the parameters used by each call can also be displayed in Dynatrace

With these changes you will see a decrease in the CPU usage, the number of compilation/s and – provided you clean the query plan cache to free the memory – a decrease in Disk I/O read. This will result in increased throughput and reduced query execution time.

Monitor and compare these SQL Server Performance metrics to understand the impact it has on overall resource consumption.
Monitor and compare these SQL Server Performance metrics to understand the impact it has on overall resource consumption.

What if my application is not responsible for High Compilation?

If your application is already optimized and not the cause of the high compilations and high CPU usage, you can find out who else is using the Database with the Dynatrace Database Agent Live View. Simply instrument those applications with one of our agents (.NET, Java, PHP, …) to review in the details the queries.

Dynatrace Database Agent shows you who else is connected to the Database – and potentially sending inefficient queries to the database
Dynatrace Database Agent shows you who else is connected to the Database – and potentially sending inefficient queries to the database
With the Database Agent you can also see in the real-time which SQL queries are being executed
With the Database Agent you can also see in the real-time which SQL queries are being executed

Verify your own application database access patterns

To do this type of analysis on your own application feel free to Download the Dynatrace Free Trial. Set up the SQL Server Agent to check the ratio between Batch Request and Compilation, as well as CPU utilization on your database instance. Then instrument your application with our .NET agent to check your SQL statements.

More information/Source:

Execution Plan Caching and Reuse (article for SQL Server 2008, but the concept are still the same in SQL Server 2016!) (https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx)

Plan cache and optimizing for adhoc workloads http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/