query is running fine few days before but now its taking more time in sql server

Question

query is running fine few days before but now its taking more time in sql server

kesavan.rv

SSC Eights!

Points: 844
More actions
December 2, 2014 at 4:26 am

#299013

Dear All,
we have an complex select query which is running fine few days before but now its taking more time.
Also the same query is running fine in the DEV environment(taking 52 seconds) where the server has less configuration then the PROD environment(taking 10mins).
I did the following thing and it was not working..
1) Updating the statistics.
2) Re-indexing
when I tried using MAXDOP query hint and set the DOP to 1, the query took 4mins in PROD.
some other details:
Dev server have 2 processor and duel core 32 GB of RAM
PROD has 132GB RAM and 2 processor (totally 40CUP's)
SQL Server 2008R2
OS: windows Server 2008
DB size:~ 400GB
MAXDOP set to 8
TIA.
regards,
Kesavan

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

First thing that comes to mind is to compare the execution plans from both servers.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

And to check indexes on both and data volumes. If the data volumes are different between the servers, a different plan is expected.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kesavan.rv SSC Eights! Points: 844 More actions · Answer 3

Thanks Koen Verbeeck for ur reply.

I will check the execution plan.

Thanks GilaMonster for ur reply:

the data volume is same also the index(recently we restored the PROD DB into DEV).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

What do you mean by 'recently'? More than a few days? From before the prod server started running slow?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kesavan.rv SSC Eights! Points: 844 More actions · Answer 5

GilaMonster,

the restoration happen one month back.

this both the server are new and the DB is related to People soft.

Recently we upgraded the DB from 2005 to 2008R2(in new server).

after the completion of the upgrade, we restored the PROD into DEV

Initially i.e. last month the query was running fine (taking ~52 seconds) and now its running slow.

regards,

Kesavan.

kesavan.rv SSC Eights! Points: 844 More actions · Answer 6

Hi Koen Verbeeck,

both the execution plans are not the same..

one of the clustered index scan having cost as 56% in PROD but in DEV its 14%

clustered index key lookup have 28% in PROD and DEV having cost at 9%.

this clustered index are created on a computer column.

can you please help me in finding

1.how come the execution plan are not same as the index, data are same

Regards,

Kesavan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Post the plans please, descriptions of them don't help.

Plus table definitions, index definitions and the query itself.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass