Different Query Plan for same query

Question

Different Query Plan for same query

free_mascot

One Orange Chip

Points: 27169
More actions
May 17, 2017 at 4:01 am

#323550

Hello,
I am having very interesting issue. On one SQL instance therea re 2 different databases. Now I am execuitng SQL Query on database 1 it is very fast and taking around 45 sec to complete while on database 2 it is very very slow and taking long time. It also gives different query plan on each databases.
All the tables, views, indexes are similar in both the databases. Database settings are same on both the database. Instance is same so no question of different settings. Removed query from cache of database 2 still no hope. Done reindexing, update statistics too on database 2 but no hope.
Appreciate any help on this.
Thank you in advance.
Br,
FM
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

free_mascot - Wednesday, May 17, 2017 4:01 AM
Hello,
I am having very interesting issue. On one SQL instance therea re 2 different databases. Now I am execuitng SQL Query on database 1 it is very fast and taking around 45 sec to complete while on database 2 it is very very slow and taking long time. It also gives different query plan on each databases.
All the tables, views, indexes are similar in both the databases. Database settings are same on both the database. Instance is same so no question of different settings. Removed query from cache of database 2 still no hope. Done reindexing, update statistics too on database 2 but no hope.
Appreciate any help on this.
Thank you in advance.
Br,
FM

Can you post both plans as .sql attachments please? Actual rather than estimated, if possible. Cheers.

^{“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

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Hello Chris,

It will be difficult to post actual plan as it is production server and query can't complete on database 2 due to very slow execution.

Br,
FM

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

free_mascot - Wednesday, May 17, 2017 6:31 AM
Hello Chris,
It will be difficult to post actual plan as it is production server and query can't complete on database 2 due to very slow execution.
Br,
FM

Then post the estimated query plans.

Are the rowcounts and data EXACTLY the same?

Is indexing EXACTLY the same?

Are data types EXACTLY the same?

Are the databases in EXACTLY the same compatability mode?

Did you try running the query with OPTION (RECOMPILE) to see if that made a difference?

Are you certain that simple blocking isn't the reason it isn't completing on the second one? sp_whoisactive will show you that.

Speaking of which, that sproc will also show you if the bad query is killing tempdb, IO, CPU, etc.

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

Rechana Rajan SSCertifiable Points: 7717 More actions · Answer 4

free_mascot - Wednesday, May 17, 2017 4:01 AM
Hello,
I am having very interesting issue. On one SQL instance therea re 2 different databases. Now I am execuitng SQL Query on database 1 it is very fast and taking around 45 sec to complete while on database 2 it is very very slow and taking long time. It also gives different query plan on each databases.
All the tables, views, indexes are similar in both the databases. Database settings are same on both the database. Instance is same so no question of different settings. Removed query from cache of database 2 still no hope. Done reindexing, update statistics too on database 2 but no hope.
Appreciate any help on this.
Thank you in advance.
Br,
FM

You can try get the execution plan xml of both and compare it ..It might be using some different Index which is not available in other .

free_mascot One Orange Chip Points: 27169 More actions · Answer 5

Thank you Kevin and all.

Posting my experience so that it help to others too.
Finally I found the culprit. The issue was with the view.
I have matched all the Tables, columns, data types, indexes, database property, database settings, reindex, update statistics, recompile, match columns of views, blocking etc. and everything was exactly same but having different query plan.
Finally I have script out the views and found where clause in a view which was causing the issue.

Cheers,
HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

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

free_mascot - Tuesday, May 23, 2017 3:31 AM
Thank you Kevin and all.
Posting my experience so that it help to others too.
Finally I found the culprit. The issue was with the view.
I have matched all the Tables, columns, data types, indexes, database property, database settings, reindex, update statistics, recompile, match columns of views, blocking etc. and everything was exactly same but having different query plan.
Finally I have script out the views and found where clause in a view which was causing the issue.
Cheers,
HTH

What was the issue with the WHERE clause in the view?

^{“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 7

ChrisM@Work - Tuesday, May 23, 2017 5:24 AM
free_mascot - Tuesday, May 23, 2017 3:31 AM
Thank you Kevin and all.
Posting my experience so that it help to others too.
Finally I found the culprit. The issue was with the view.
I have matched all the Tables, columns, data types, indexes, database property, database settings, reindex, update statistics, recompile, match columns of views, blocking etc. and everything was exactly same but having different query plan.
Finally I have script out the views and found where clause in a view which was causing the issue.
Cheers,
HTH
What was the issue with the WHERE clause in the view?

I'm guessing function around column in WHERE. Anyone give me odds on that?? 😎

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