This article assumes that you already have an optimal data model along with the most efficient data tier in your application. You've spent plenty of time optimizing indexes, poring over the SQL explain plans, and tuning the SQL queries that your application uses to retrieve data from your SQL Server database. The application has been running fairly smoothly, performance isn't great but has been acceptable to the end users.
At some point, due to application load from the users, data volume, or other external variables, you notice that the queries you optimized utilizing the Management Studio's estimated explain plan do not perform as expected.
Let's take a look at the table we'll be querying. We're using the
VARCHAR data type to store all string data in our database.
Let's take a look at the explain plan of a simple query against the database.
The estimated explain plan shows that the query should perform very well in your production environment, utilizing an index seek on a last name, first name compound index.
There have been numerous complaints from users regarding poor performance of the application. So utilizing the SQL Server Profiler you use the Standard Template (the security events have been removed to eliminate noise for this example) and run the profiler.
The profiler shows only "exec sp_execute ##,####,XXXX" type of events. This is the signal that the application is using prepared statements to access the data in the database.
So you execute the following query to determine what is currently running on the database.
SELECT a.session_id, a.STATUS, a.start_time, a.command, SUBSTRING(b.text, (a.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(b.text) ELSE a.statement_end_offset END - a.statement_start_offset)/2) + 1) AS StatementText FROM sys.dm_exec_requests a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b WHERE a.STATUS IN ('running', 'suspended')
This query returns a handful of SQL statements but since there is no visibility on the parameters that the statement is using it's difficult to determine if the indexes are being properly utilized. The SQL Profiler can be utilized to retrieve the SQL Statement executed as well as the parameters that are passed into the statement.
Open the properties of the currently running profiler and add SP:CacheHit listed under Stored Procedures. Resume the profiler. The new
SP:CacheHit event will show the following TextData:
DECLARE @p1 INT SET @p1=345 EXEC sp_prepexec @p1 output,N'@P0 nvarchar(4000)',N'SELECT Cl.FirstName, Cl.LastName, Cl.ClaimantId FROM Claimant Cl WITH (NOLOCK) WHERE Cl.LastName LIKE @P0' ,'Smith%' SELECT @p1
The first thing that stands out is the Index Scan versus the Index Seek of the same query executed in Management Studio. The number of rows, CPU and I/O cost is substantially higher than when the query is executed in Management Studio. Upon closer inspection of the query that was retrieved from the SP:CacheHit TextData, the parameter is passed in as an
NVARCHAR which is causing the Index Scan.
There two options to resolve this performance issue. The table itself can be modified to store the string data as an
NVARCHAR or the application's ODBC/JDBC driver can be configured to not use
NVARCHAR. The following shows an example of the ODBC/JDBC driver being configured to not use NVARCHAR. This causes the prepared statement execution to pass in a
VARCHAR which will result in an Index Seek (this of course assumes you that VARCHAR and NVARCHAR are interchangable given your application's requirements).
The Estimated number of rows as well as the CPU and I/O cost is now in line with the estimated execution plan of the query that was executed in Management Studio.