Home » Blog » Execution Plan Inaccuracies

Execution Plan Inaccuracies

2009-10-13 - General

Execution plans are a great tool when you are trying to performance tune your SQL code. They contain a lot of information about how SQL Server will achieve what we have asked it to do. You can see which way tables are accessed, how many rows are read, where data is sorted and so on. But you have to be careful when using them. You probably already know, that there are two types of Execution Plans. The Estimated Execution Plan you can get, without actually executing the query. This is an estimate of how SQL Server thinks it will execute the query and it is based manly on statistics of tables and indexes. The Actual Execution Plan on the other hand, SQL Server produces while actually executing the query. It contains information about ho much data was actually read and moved around.

That the Estimated Execution Plan can be inaccurate is not a surprise, because it is an estimate. But even the Actual Execution Plan can contain vastly inaccurate data.

Lets look at an example. First we create a table with the columns id, val1 and val2 and fill it with testing data:

CREATE TABLE dbo.TstData(

id INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,

val1 INT NOT NULL,

val2 VARCHAR(MAX)

);

INSERT INTO dbo.TstData(val1,val2)

SELECT A.no,REPLICATE(CHAR(65+(B.no % 26)),A.no)

FROM dbo.f_num(10) AS A

CROSS JOIN dbo.f_num(50) AS B;

CREATE CLUSTERED INDEX TstData_CI ON dbo.TstData(val1);

Then we create a view that concatenates the val2 columns together in id order, grouped by val1.

So if the data in dbo.TstData looked like this:

the view would return this:

This is the code for this first view:

CREATE VIEW dbo.TstVw1

SELECT DISTINCT A.val1,C.Cnct

FROM dbo.TstData AS A

CROSS APPLY(SELECT (SELECT B.val2 [text language="()"][/text]

FROM dbo.TstData AS B

WHERE B.val1 = A.val1

ORDER BY B.id

FOR XML PATH('')

) AS Cnct

) AS C;

It is using SQL Servers XML capabilities to do the concatenation.

Next we create a second view that does exactly the same thing, but instead of XML it is using a SQL function that cursors through all rows of the table to get there:

CREATE FUNCTION dbo.TstDataConcat(

@GrpVal INT

)

RETURNS VARCHAR(MAX)

BEGIN

DECLARE @Cnct VARCHAR(MAX);

DECLARE @Val1 INT;

DECLARE @Val2 VARCHAR(MAX);

SET @Cnct = '';

DECLARE cur CURSOR LOCAL FAST_FORWARD

FOR SELECT val1,val2

FROM dbo.TstData

ORDER BY id;

OPEN cur;

FETCH NEXT FROM cur INTO @Val1, @Val2;

WHILE(@@FETCH_STATUS = 0)

BEGIN

IF(@Val1 = @GrpVal)

BEGIN

SET @Cnct += @Val2;

END

FETCH NEXT FROM cur INTO @Val1, @Val2;

END

CLOSE cur;

DEALLOCATE cur;

RETURN @Cnct;

END

CREATE VIEW dbo.TstVw2

SELECT val1,dbo.TstDataConcat(val1) AS Cnct

FROM dbo.TstData

GROUP BY val1;

The function this view is using is intentionally not well optimized.

When we compare the performance of a SELECT*INTO#tst1 FROMdbo.TstVw1;with it's counterpart using dbo.TstVw2, by looking at the actual execution plan we find a clear indication, that the XML version is a lot more complicated:

SQL Server is telling us that the cost of the XML version is 0.640 or 94% of the batch. The cursor version is declared to cost a mere 0.042 or 6% of the batch. Below is an exerpt from the XML execution plan, containing some additional information about this.

<StmtSimpleStatementEstRows="107.495"StatementOptmLevel="FULL"StatementOptmEarlyAbortReason="GoodEnoughPlanFound"StatementSubTreeCost="0.640166"StatementText="SELECT * INTO #tst1 FROM dbo.TstVw1;"StatementType="SELECT INTO">

<QueryPlanDegreeOfParallelism="0"CachedPlanSize="40"CompileTime="11"CompileCPU="8"CompileMemory="112">

So according to the actual execution plan the cursor version is over 15 times faster than the XML version.

Now, we all know that this result is very unlikely, and when we look at the output of the comparison with STATISTICSTIMEON, we get these a lot more realistic results:

starting tst1 (CROSS APPLY)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(10 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 47 ms, elapsed time = 51 ms.

starting tst2 (FUNCTION)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(10 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 2797 ms, elapsed time = 3067 ms.

That is 47ms for the XML version compared to almost 3 seconds for the cursor. That means, eventhough the Execution Plan said the XML solution would be 15 times slower that the function, it ended up being close to 60 times faster.

So, while Execution Plans are an invaluable tool for performance monitoring and tuning, you always have to be careful about the values it returns. The counts and the operators shown in the Actual Execution Plan are reliable, but the time comparisons can not always be relied on.

If you want to try this for your self, you can download the full source here.