Sql-server – Same schema, same data, same execution plan

execution-plansql serversql server 2014

I have a T-SQL stored procedure in SQL Server 2014 SP2 that takes 3x as long on the server as it does from my local environment.

I restored my local database to what was on the server, so schema and data are the same.

I'm a programmer, not really a DBA, so bear with me on this.

The execution plans seem the same until you look at the XML. On my local environment, a Stream Aggregate is used for one of the queries to make a distinct select, while on the server, it looks like a Sort is used with a hash.

It appears that the fact that the server has less processors than my (beast) local machine is contributing to the problem. Every query takes a little longer on the server. Could this be the reason that the sort/hash is chosen on the server instead of the Stream Aggregate?

Is there a way to force the Stream Aggregate?

Here is the SQL for the stored proc: http://pastebin.com/kkXM3Bsf

Here is the execution plan for my local environment: http://pastebin.com/0u7SHxnM

Here is the execution plan for the server: http://pastebin.com/9sE9DfM0

Best Answer

The hardware configuration can affect plan selection as I discuss in my answer to the related Q & A:

difference in execution plans on UAT and PROD server

The question doesn't quantify the "3x" performance difference, but assuming it is important enough to be concerned about:

You can force the broad shape of a plan completely using a Plan Guide or USE PLAN hint. This will prevent the optimizer choosing a different plan shape over time as the data volume and distribution changes, so it is important to review forced plans regularly.
You can often get closer to the desired plan shape with targeted FORCESEEK hints on tables that should be accessed by a seek (e.g. driven by a nested loops join) rather than a scan (with a hash or merge join). In your case, the FORCESEEK hint(s) could be applied to Account and/or CustomerLocation. This may be preferable to forcing the whole plan shape as in the previous point, as it gives the optimizer freedom in other areas of the plan.
You could give the optimizer a better chance with the @country is null or ca.Country = @country predicate by using a RECOMPILE hint, or writing two separate queries (one for the NULL case; the other for non-NULL) surrounded by an IF @country IS NULL test. Try the two separate queries first.
You should be able to avoid the sort spill with a QUERYTRACEON (7470) hint. This is documented in:

FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct
There is no supported way to force a Stream Aggregate (over a Sort Distinct), without forcing the whole plan.
You should consider indexing the heap table variable (a PK at least), and/or using a temporary table instead.

I would start by writing the query as two alternatives (or using the RECOMPILE hint, if you can tolerate a recompilation on each execution), with the 7470 trace flag enabled, and go from there. Plan forcing would be my least favourite option.

If the server has a very large amount of memory, and plans routinely prefer memory-consuming operators when they should not, you could consider testing your whole workload with TF 2335 as in the linked Q & A. I would try the more targeted approaches first.

Related Solutions

Sql-server – SQL Server 2005 get execution plan of a overnight stored procedure

So is there a way I can see the execution plan of the proc or the statement in particular?

You have to refer to DMV's for extracting such information. sys.dm_exec_cached_plans, sys.dm_exec_sql_text and sys.dm_exec_query_plan are the ones to look into.

Especially sys.dm_exec_query_plan DMF will return the plan for a given batch or procedure (along with the "subplans" for each query comprising the batch).

Note: Restarting sql server will flush out all dmv data.

Example :

-- Do not run it in PROD !!!!
-- free up the procedure cache for TESTING ONLY !!
dbcc freeproccache

-- create an SP with different queries 
create procedure usp_Kin_Test
as
select name, create_date from sys.objects
where type = 'P'
order by create_date desc

select name, OBJECT_ID from sys.procedures
order by name

-- execute the query
exec usp_Kin_Test

--- Check the DMV's for cached query plan

SELECT deqp.dbid
    ,deqp.objectid
    ,CAST(detqp.query_plan AS XML) AS singleStatementPlan
    ,deqp.query_plan AS batch_query_plan
    ,ROW_NUMBER() OVER (
        ORDER BY Statement_Start_offset
        ) AS query_position
    ,CASE 
        WHEN deqs.statement_start_offset = 0
            AND deqs.statement_end_offset = - 1
            THEN '-- see objectText column--'
        ELSE '-- query --' + CHAR(13) + CHAR(10) + SUBSTRING(execText.TEXT, deqs.statement_start_offset / 2, (
                    (
                        CASE 
                            WHEN deqs.statement_end_offset = - 1
                                THEN DATALENGTH(execText.TEXT)
                            ELSE deqs.statement_end_offset
                            END
                        ) - deqs.statement_start_offset
                    ) / 2)
        END AS queryText
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle, deqs.statement_start_offset, deqs.statement_end_offset) AS detqp
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
WHERE execText.TEXT LIKE '%usp_Kin_Test%' -- CHANGE here for your stored procedure !!

enter image description here

Note : You can do many cool things by digging into Plan cache as described by Jonathan here. Also refer to DMVs for Query Plan Metadata

Sql-server – Getting a realistic query plan when partitioning with low volumes of data

It looks like SQL Server is generating a parameterized query plan that can work for any value of @CustomerPartitionKey. In order to do so, it seems to treat @CustomerPartitionKey as both a partition and a column you are seeking upon. If we take a look at the query plan where we have the bad estimate (3000 rows estimated, 300000 actual), we see that there are actually two separate seek predicates on part.Contact related to @CustomerPartitionKey:

Seek Keys[1]: Prefix: PtnId1004, [Test].[part].[Contact].CustomerPartitionKey = Scalar Operator([Expr1008]), Scalar Operator([@CustomerPartitionKey])

I think that the latter ([Test].[part].[Contact].CustomerPartitionKey = Scalar Operator([@CustomerPartitionKey]) is able to get a proper estimate based on parameter sniffing for the value of @CustomerPartitionKey. However, the former (Prefix: PtnId1004 = Scalar Operator([Expr1008])) is likely not able to do so, perhaps because Expr1008 is complicated expression that processes partition elimiation: [Expr1008]=RangePartitionNew([@CustomerPartitionKey],(0),(0),(1),(2),...,(97),(98)).

In this case, there are 100 partitions and the row estimate is exactly 100 times too low because SQL Server isn't able to process the partition elimination in the same smart way that it processes the actual seek on the column and uses an estimate for the runtime parameter value of 3. This theory is supported by the way that the estimated rows varies if you remove partitions; if you use 90 partitions instead, the estimate will be 3333.33 (300000 / 90).

In our own queries, we typically use a literal (e.g., 3 in this case) or use OPTION RECOMPILE whenever we are writing a query that is going to take advantage of partition elimination. This practice has worked fairly well for us given that the number of queries on the system is modest and query compilation overhead for queries against large partitioned tables is not a concern for us. Not necessarily a satisfying answer, but it might work for you.

Best Answer

Related Solutions

Sql-server – SQL Server 2005 get execution plan of a overnight stored procedure

Sql-server – Getting a realistic query plan when partitioning with low volumes of data

Related Question