Archive for category Query optimisation

Closer look at auto-parameterization

Most of us in the SQL Server world take auto-parameterization of ad hoc SQL statements for granted. Decisions made regarding safe, unsafe and failed parameterizations and configuration settings such as ‘optimize for ad hoc work loads’ are key players in determining the query plan for ad hoc SQL. If you are using the dynamic management views for your performance monitoring you are likely to see a variety of output depending on the decisions that the query optimizer has made.

Below I present results from my “deep dive” into auto-parameterization. The commands listed should never be run on your production server. Tests involving clearing the procedure cache and changing configuration settings should only ever be run on dedicated test servers. The commands and output are from my developer edition of SQL Server 2008.

The test data:


-- Create a sample table
IF OBJECT_ID('dbo.MyTable') IS NOT NULL DROP TABLE dbo.MyTable;

CREATE TABLE dbo.MyTable
(
id int NOT NULL IDENTITY,
somedata char(3) NOT NULL
);

-- Populate table
INSERT INTO dbo.MyTable (somedata)
SELECT TOP (2000) 'abc' FROM master.dbo.spt_values;

CREATE INDEX idxMyTable ON dbo.MyTable (id);

Note the number of safe and unsafe auto-parameterizations before we run any SQL:


SELECT counter_name,cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Safe Auto-Params/sec','Unsafe Auto-Params/sec')
ORDER BY counter_name;

Now free the procedure cache:


DBCC FREEPROCCACHE;

Select some data:


SELECT id FROM dbo.MyTable WHERE id = 15
GO
SELECT id FROM dbo.MyTable WHERE id = 1500
GO

Note the changes in safe and unsafe auto-parameterizations by running the sys.dm_os_performance_counters again. The safe counter has increased by 2:

Here is what we see in the query plan and query stats DMVs:


SELECT st.text,qp.query_plan,cp.usecounts,cp.size_in_bytes,cp.cacheobjtype,cp.objtype,cp.plan_handle
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.text like '%MyTable%'
OPTION (RECOMPILE);
GO

SELECT st.text,qp.query_plan,qs.creation_time,qs.last_execution_time,qs.execution_count,qs.plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE st.text like '%MyTable%'
OPTION (RECOMPILE);
GO

If we run the same two select statements again we see the same results for the safe and unsafe auto-parameterization counts (since the exact same ad hoc statements have been run) but the usecounts and execution counts from the plan cache and query stats DMVs have changed:

Looking at the results from sys.dm_exec_cached_plans you could come to the conclusion that the ad hoc SQL had been run as the usecounts have increased. In fact, these plans are “shell” plans. The actual statements that were run were the parameterized forms of the queries. You can see this from the results of sys.dm_exec_query_stats.

Now let’s see the output for ad hoc SQL that results in unsafe parameterization. Free the procedure cached and then run the following:


SELECT somedata FROM dbo.MyTable WHERE id = 15
GO
SELECT somedata FROM dbo.MyTable WHERE id = 1500
GO

The number of unsafe auto-parameterizations will have increased by 2:

The output from the query plan and query stats DMVs will be as follows:

Run the same ad hoc SQL again and we see the safe and unsafe auto-parameterization counts have stayed the same but the usecounts and execution counts for the query plan and query stats DMVs have changed:

For the second half of this post let’s run the entire scenario again with ‘optimize for ad hoc workloads’ switched on.


EXEC sp_configure 'optimize for ad hoc workloads',1;
RECONFIGURE WITH OVERRIDE;

Free the procedure cache and make a note of the safe and unsafe auto-parameterizations. Run

SELECT id FROM dbo.MyTable WHERE id = 15
GO
SELECT id FROM dbo.MyTable WHERE id = 1500
GO

Just like the first run the number of safe auto-parameterizations will have increase by 2. The result for the query plan and query stats DMVs will be as follows:

You’ll see the query plan stubs. Note the small size_in_bytes value. The actual commands that were run were the auto-parameterized forms of the ad hoc SQL.

Run the same ad hoc SQL again and, unlike the first scenario, the safe auto-parameterizations count will have increased by 2. The DMV queries will show this:

The query plan stubs have been replace with query plan shells. These shells point to the parameterized plans. You can see the execution counts in the query stats DMV have increased to 2.

Finally, we free the procedure cache and show the results for unsafe auto-parameterizations by running the same two selects as previously:

SELECT somedata FROM dbo.MyTable WHERE id = 15
GO
SELECT somedata FROM dbo.MyTable WHERE id = 1500
GO

Just as before the number of unsafe auto-parameterizations will have increased by 2. The output for the DMV queries will be as follows:

You see the query plan stubs. Now run the same two ad hoc statements again. The unsafe auto-parameterization counts increase by 2 and the following is the output for the DMV queries:

Interestingly, the execution count appears as 1 when in fact the statements have been run twice. In effect, you lose the query statistics for the first execution (the stub execution).

From the result sets above you can see that compiled plan stubs don’t have a plan_handle. In cases where a plan_handle does exist you can use the following query to correlate a compiled plan “shell” to its corresponding auto-parameterized plan:


;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
CAST (cp.plan_handle AS varbinary(64)) AS plan_handle,
c.value('xs:hexBinary(substring(@ParameterizedPlanHandle,3))','varbinary(64)') AS ParameterizedPlanHandle
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple[@ParameterizedPlanHandle]') AS t(c)

If you view the XML plan for one of the “shell” plans you’ll see the reference to the ParameterizedPlanHandle attribute:

The SQL code produces a simple result set that you can use as the basis of a view or CTE to fetch more details.

I hope this post has given you an insight into the transitions that occur for ad hoc SQL processing. A typical production SQL Server will process thousands of such statements and making sense of the DMV statistics can be difficult. Focusing on a subset of the statistics can yield interesting results from a performance monitoring and tuning perspective.


		
Advertisements

Leave a comment