Parellism on one database but not an other | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Parellism on one database but not an other

I am experiencing this issue on a clients server. I run the procedure on their production database and it can take from 4 to 7 minutes to complete. But when I run the same procedure on a copy of their production, which is on the same server, same disk, it runs in about 40 seconds. I checked the execution plans and found the culprit, which is a delete statement from a table, but on the production server it is using parallelism and the copy of production database is not. Why is this happening? I’ve also updated statistics and defragged indexes on the production database but its still using parallelism. Any ideas would be appreciated. Thanks. Travis
I presume you have set only 1 processor for parallelism, did you?
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.
It is the optimizer who decides when to use the parallelism based on the cost …if your server is configured to > 1 processor …
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

quote:Originally posted by Luis Martin I presume you have set only 1 processor for parallelism, did you?
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

Its set to use all available processors. Travis
Are you using MAXDOP hint in the queries? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
quote:Originally posted by satya Are you using MAXDOP hint in the queries? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

The execution plan that is using parallelism I added MAXDOP 1 but the execution plan is different compared to the database that is a copy of production. I can add the MAXDOP 1 but it still doesn’t explain the different execution plans and completion times. I’ve tried recompiling the procedure too. Travis
Are the both servers matched to each other in terms of memory & configuration? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
quote:Originally posted by satya Are you using MAXDOP hint in the queries? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

The execution plan that is using parallelism I added MAXDOP 1 but the execution plan is different compared to the database that is a copy of production. I can add the MAXDOP 1 but it still doesn’t explain the different execution plans and completion times. I’ve tried recompiling the procedure too. Travis
Satya- Both databases are on the same server. Travis
What was the sample did you use when you update the stats….
If you use default sample try with FULL SCAN option… As I mentioned before, it is optimizer which dicides what to use and also depends on the load…
Did both dbs tried when there is no load? after recompile procedure… Also create procedure with different name using the same code and see …
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

quote:Originally posted by MohammedU What was the sample did you use when you update the stats….
If you use default sample try with FULL SCAN option… As I mentioned before, it is optimizer which dicides what to use and also depends on the load…
Did both dbs tried when there is no load? after recompile procedure… Also create procedure with different name using the same code and see …
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

Sorry, I was on vacation this past weekend. I will try using FULL SCAN and create the procedure with a different name. I also did try running it when there was no load on the server. I understand that the optimizer decides what to use but when both databases are relatively copies and on the same server how can they have such different execution plans? Travis
]]>