SQL query running slow after upgrading from 2005 to SQL 2012

Question

SQL query running slow after upgrading from 2005 to SQL 2012

SqlSavy

SSCarpal Tunnel

Points: 4065
More actions
October 19, 2016 at 7:54 am

#322241

I’m seeing some strange behaviour with the ''update" query
If you increase the ‘available’ memory available to the SQL Instance to >90gb then the query seems to build an efficient plan and runs in around 30seconds.
If you set the memory to anything below that then it builds a different plan which (conservative estimates) runs for days on end.
On the previous SQL2005 Instance it only had 6gb of memory available and ran fine.
So far, I have run:
•DBCC UPDATEUSAGE
•Update stats on all tables with fullscan
•Cleared Proc Cache
•Rebuilt Indexes
•Integrity Checks.
But I am still seeing the query fail.
Do you have any ideas how to help resolve?

Viewing 15 posts - 1 through 15 (of 20 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Sqlsavy (10/19/2016)
I’m seeing some strange behaviour with the ''update" query
If you increase the ‘available’ memory available to the SQL Instance to >90gb then the query seems to build an efficient plan and runs in around 30seconds.
If you set the memory to anything below that then it builds a different plan which (conservative estimates) runs for days on end.
On the previous SQL2005 Instance it only had 6gb of memory available and ran fine.
So far, I have run:
•DBCC UPDATEUSAGE
•Update stats on all tables with fullscan
•Cleared Proc Cache
•Rebuilt Indexes
•Integrity Checks.
But I am still seeing the query fail.
Do you have any ideas how to help resolve?

Sure - post the actual plan for the 30s query and the estimated plan for the slow query.

Edit:

You can also switch between the cardinality estimators using trace flags at the query level:

https://support.microsoft.com/en-gb/kb/2801413

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

My question is this: if you have 90GB of memory and more on your server, why not let SQL Server use it? But I agree, let's see the execution plans so that we can understand what's going on.

ChrisM@Work (10/19/2016)
You can also switch between the cardinality estimators using trace flags at the query level

SQL Servers 2005 and 2012 use the same cardinality estimator, don't they? The new one was introduced in 2014.

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

John Mitchell-245523 (10/19/2016)
My question is this: if you have 90GB of memory and more on your server, why not let SQL Server use it? But I agree, let's see the execution plans so that we can understand what's going on.
ChrisM@Work (10/19/2016)
You can also switch between the cardinality estimators using trace flags at the query level
SQL Servers 2005 and 2012 use the same cardinality estimator, don't they? The new one was introduced in 2014.
John

Oops yes, thanks John.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

I would like to see the query too.

My guess without further information is a huge rowcount somewhere is needing a huge memory grant for a sort, hash, etc and with lower memory the optimizer figures it will spool to disk and jacks up the cost, leading to a loopy-type plan. Note I don't actually know for sure the optimizer has such knowledge built in.

A loopy plan hitting an unindexed object could exacerbate the issue.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SqlSavy SSCarpal Tunnel Points: 4065 More actions · Answer 5

Thank you all for the responses.

I've attached query plan with resrticated and unlimitted memory please have a look.

Thanks

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

are you able to post the .sqlplan rather than the word doc please?

what does your "dbo.function" perform?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Alan Burstein SSC Guru Points: 61136 More actions · Answer 7

Sqlsavy (10/19/2016)
Thank you all for the responses.
I've attached query plan with resrticated and unlimitted memory please have a look.
Thanks
query:
UPDATE dbo.table1
SET table1.field = somevalue
FROM dbo.table1 AS ABC
INNER JOIN dbo.table2 AS xyz
ON ABC.fieldname = dbo.function( xyz.fieldname , xyz.fieldname )
WHERE ABC.fieldname <> xyz.fieldname ;

Can you post the actual execution plans? E.g. run both queries in SSMS with include actual execution plan turned on; right-click the plan, select save execution plan as... Then post those files. We can use that to help understand the problem.

That said, from the pictures of the plan I see some things:

First, I see a lot of sort operations. These require a lot of memory - if the sort operator can't get enough memory to perform the sort it has to do what's called a memory spill and use the tempdb. This shows up with a yellow warning sign in the actual execution plan. I would bet that you're getting a big spill on the system with less memory. The impact of a spill can be huge depending on how much data is being sorted and the condition of your tempdb and underlying disk. Building an covering index to avoid those sorts would speed things up and the query would require a lot less memory to run efficiently.

The other thing that stands out is how there's a table spool in the execution plan on the limited memory query. That's SQL Server creating a work table in the tempdb - this can have a dramatic impact on performance, especially if your tempdb is busy are starved for IO. There are ways to re-write your query to eliminate table spools from the plan.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

MadAdmin SSChampion Points: 11261 More actions · Answer 8

Sqlsavy (10/19/2016)
Thank you all for the responses.
I've attached query plan with resrticated and unlimitted memory please have a look.
Thanks
query:
UPDATE dbo.table1
SET table1.field = somevalue
FROM dbo.table1 AS ABC
INNER JOIN dbo.table2 AS xyz
ON ABC.fieldname = dbo.function( xyz.fieldname , xyz.fieldname )
WHERE ABC.fieldname <> xyz.fieldname ;

Scalar function anyone?

Lack of parallelism, together with the fact that sql cannot optimise between the outer query and the tables in the function.

Doing a join on a scalar function is probably the most optimum way of ensuring the slowest performance.

At least if you remove the function, you could get parallelism going and better usage of stats, and it will perform better in both sql2005 and 2012.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

SqlSavy SSCarpal Tunnel Points: 4065 More actions · Answer 9

Thank you everyone.

I can't post the actual query plan because of company policy.

You've been very helpful we've logged a case with MS now

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Sqlsavy (10/20/2016)
Thank you everyone.
I can't post the actual query plan because of company policy.
You've been very helpful we've logged a case with MS now

SQL Sentry Plan Explorer has an option to obfuscate the objects in an execution plan - and it's free:

https://www.sentryone.com/plan-explorer?ad=g-sitelink&gclid=CLTqr-mD6c8CFUPgGwodg2EAkQ

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MadAdmin SSChampion Points: 11261 More actions · Answer 11

Scalar functions are a black-box from a cardinality estimation perspective.

If you're encountering plan quality issues due to them, consider inline table functions as an alternative – or even pulling out the function reference entirely and just referencing objects directly.

https://sqlperformance.com/2012/11/t-sql-queries/ten-common-threats-to-execution-plan-quality

If a function is used in the SELECT or WHERE, the function can be called many, many times.

If the function is very resource-intensive, it could be causing your query to be very slow – and you would never see the execution of the function within the execution plan of the calling query.

https://www.brentozar.com/archive/2014/10/sql-server-functions-dragging-query/

So I would not bother looking at the plan (or raising an MS ticket) until I have removed the scalar function.

This is a known antipattern.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

Sqlsavy (10/20/2016)
Thank you everyone.
I can't post the actual query plan because of company policy.
You've been very helpful we've logged a case with MS now

I can't see this being Microsoft's fault - at all.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

wtren SSC Eights! Points: 806 More actions · Answer 13

wtren

SSC Eights!

Points: 806

October 21, 2016 at 4:10 am

#1907890

did you try the maxdop option?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

wtren (10/21/2016)
did you try the maxdop option?

Pointless - it's a serial plan.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden