SQL-2016 Query Timeouts/Long execution

Question

Post reply

SQL-2016 Query Timeouts/Long execution

GonnaCatchIT

SSCrazy

Points: 2797
More actions
August 14, 2019 at 2:17 am

#3671204
Dear All,
I have just migrated from SQL-2008 to SQL-2016.
I see some of our queries get timed out ,take too long for execution.
The same queries are quick in SQL-2008.
I have tried executing them several times on SQL-2016 and execution plan doesn't help much.I don't want directly go to re-writing the queries as it would be too bigger task.
What aspects should I check on SQL-2016?
1. SQL Instance memory is about 135 gb ( same as SQL-2008)
2. SQL-2016 is TDE Enabled & SSL configured.
Sample query:
exec sp_executesql N'WITH Query AS ( SELECT MS.*, ROW_NUMBER() OVER( ORDER BY D_DT DESC ) AS RowNumber
FROM ( SELECT DISTINCT Col1,col2
FROM ( SELECT * FROM View WHERE DID = LTRIM(RTRIM(@DID)) and Type <> ''Fruit''
AND T_DT >= LTRIM(RTRIM(@FROM_DATE)) AND TRANSACTION_DT <= LTRIM(RTRIM(@TO_DATE))
AND Types IN (SELECT types FROM fruitypes ) ) TPYT INNER JOIN Veggies NPC ON TPYT.typeid = NPC.typeid
WHERE TPYT.T_DT >= LTRIM(RTRIM(@FROM_DATE)) AND TPYT.T_DT <= LTRIM(RTRIM(@TO_DATE)) AND TPYT.DID = LTRIM(RTRIM(@DID))
AND ( FTYPES IN (''0100'',''0200'') OR VTYPES IN (''200'',''400''))
AND TPYT.Org IN ('' Fresh '' , ''Veg '') ) MS ) SELECT *, (SELECT COUNT(1) FROM Query)AS TOTALCOUNT FROM Query
WHERE ROWNUMBER >= 1 AND ROWNUMBER <= 10 ORDER BY ROWNUMBER ASC; ',N'
@TO_DATE datetime,@FROM_DATE datetime,@DID nvarchar(5)',
@TO_DATE='2019-06-12 23:59:59',
@FROM_DATE='2019-06-11 00:00:00',@DID=N'0100'
- This topic was modified 4 years, 8 months ago by GonnaCatchIT.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 1

Perhaps it's the following problem. It was for us.

https://www.google.com/search?q=trace+flag+cardinality+estimator

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 2

Have you tried setting the Compatability level of the database to "SQL Server 2008"?

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 3

I'm with everyone else, get back to the old cardinality estimation engine for a short time. Turn on Query Store on your database. Run your load for a bit to see how things are behaving. Then, switch back to the new cardinality estimation engine. Any queries that regress in their behavior, use plan forcing from Query Store to make them use the older plans. Problem solved.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

GonnaCatchIT SSCrazy Points: 2797 More actions · Answer 4

Hi Jonanthan,

No we cannot set it to SQL-2008 as a company standard

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 5

Assuming you're on SP1, or greater, of SQL Server 2016, you can use DATABASE SCOPED CONFIGURATION to change the cardinality estimation engine. You should set LEGACY_CARDINALITY_ESTIMATION to ON.

Again, I would only do this in combination with Query Store so that you can turn it back off and use plan forcing to deal with regressions.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 6

GonnaCatchIT wrote:

Hi Jonanthan,
No we cannot set it to SQL-2008 as a company standard

You need to read some of the articles at the Google link I posted. You don't need to set the compatibility back to 2008. As Grant mentions, you don't even have to change the server wide setting if you're using 2016 or better.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GonnaCatchIT SSCrazy Points: 2797 More actions · Answer 7

Thanks Jeff for the article, I am going through it.

FYI, there was below changes made so far - though still no improvements.

Rebuild indexes and update statistics to reduce fragmentations

Max degree of parallelism was set to 1. Changed it to 10 now.

Remote Login timeout changed from 10 to 30 seconds

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 8

Check your cost threshold for parallelism. 5 is the default and it's too low.

I'd still strongly recommend doing what I suggested with query store.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

GonnaCatchIT SSCrazy Points: 2797 More actions · Answer 9

Dear All,

Thanks for all the Inputs. When I turned ON 'Legacy Cardinality Estimation', the same query got executed in 6-7 seconds.

With OFF, it takes over 2 minutes.

So I will review & try to re-write the query to improve the response time.

This reply was modified 4 years, 8 months ago by GonnaCatchIT.