Performance issues with new Cardinality Estimator

Hi Friends,

Today I am going to share a performance issue that happened has a result of database Migration. 

As you know the mainstream support for SQL Server 2012 ended on 9 January 2018. Its extended support will end on 10 October 2023. Hence we Migrated it from SQL Server 2012 to 2017.

On the source server we have around 10 databases with 4 different applications. After a while one of the Application is not functioning as expected. As part of temporary remediation we changed the compatibility level to 110 and the performance was back to normal.

One might ask a question like hasn't been this tested on Test Environment? Well for few Applications we don't have  them.

Coming back to this application it has few queries which runs very frequently and when it executes on 2012 these queries are going to parallelism and executes in 5 seconds. When the same hits on 2017 the execution plan doesn't have any parallel operators and it executes for 1 minute and 5 Seconds.

We have exact configuration in terms of CPU, Memory and when it comes to SQL the statistics are up to date, Cost threshold for parallelism is 50 and MAXDOP is 4 so there is nothing to compare except compatibility level.

As you know there are many changes been made with respect to Cardinality Estimator from 2014 hence there is probability to have Regressed queries.

I would like to share a technique which you can make use of if you are troubleshooting performance issues . Don't blindly believe in Cost even though you turn on Actual Execution plan

Okay to make you better understand as I said above we have problems with one of the stored procedures and we have multiple lines of select statements inside them. When we execute the stored Procedure with Actual Execution plan we will have get the cost associated with each of them.

If you interpret the below images one of them says cost as 2% and the other as 75%.

In general we will consider the one with higher cost however it is not always true. We need to check which statements are executing for higher duration and then try to fine tune them.  But ensure when you are testing any stored procedure not to have DML statements inside them.

How will you come to know which statements are executing for longer duration inside an stored procedure?

There are many ways but my approach is to make use of Extended events(credits to Guy Glanster)

SELECT

DatabaseId = DB_ID (N'DBNAME') ,

ProcedureObjectId = OBJECT_ID (N'ProcedureName');

GO

CREATE EVENT SESSION

StatementExecution

ON

SERVER

ADD EVENT

sqlserver.sp_statement_completed

(

WHERE 

database_id = 16 (pass the input of dbid) 

AND

object_id = 1445580188 (pass the input of sp)

)

ADD TARGET

package0.ring_buffer;

GO

-- Start the event session

ALTER EVENT SESSION

StatementExecution

ON

SERVER

STATE = START;

GO

-- Query the ring buffer

WITH

TargetData

(

TargetDataXML

)

AS

(

SELECT

TargetDataXML = CAST (target_data AS XML)

FROM

sys.dm_xe_session_targets AS SessionTargets

INNER JOIN

sys.dm_xe_sessions EventSessions

ON

SessionTargets.event_session_address = EventSessions.address

WHERE

SessionTargets.target_name = N'ring_buffer'

AND

EventSessions.name = N'StatementExecution'

)

SELECT

StatemenmtText = SessionEventData.value (N'(data[@name="statement"]/value/text())[1]' , 'NVARCHAR(MAX)') ,

StatementDateTime = SessionEventData.value ('(@timestamp)[1]' , 'DATETIME2') ,

Duration_Microseconds = SessionEventData.value (N'(data[@name="duration"]/value/text())[1]' , 'BIGINT')

FROM

TargetData

CROSS APPLY

TargetDataXML.nodes (N'/RingBufferTarget/event') AS SessionEvents (SessionEventData)

ORDER BY

StatementDateTime ASC;

GO

-- Stop the event session

ALTER EVENT SESSION

StatementExecution

ON

SERVER

STATE = STOP;

GO

By making use of the above script I realized the problem is not with the one which has higher cost but with the first image where the cost is 2%. This way one can easily find where the problem is with in the stored Procedure

 

Now coming back to the problem there are 3 ways to overcome the problem 

1)one making use of trace flag 9481 which disables the new Cardinality estimator at instance level. 

2) However you really don't need to do these if your having problem with only one database from SQL 2016. As we have the below option available with this we can have our DB compatibility level at 130 and still we can run on OLD CE.

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

3)If we are on SQL Server 2014 then instead of enabling trace flag at instance level you can try making use of  OPTION (QUERYTRACEON 9481)

By the way this stored procedure execution has come down to 1 second after suggestion of an Index my end. As said Performance tuning is an area where there is lot of scope to improve ourselves.

Happy Reading my friends.