June 3, 2009 at 11:12 pm
Dear All,
All of sudden, one of my query is running very slow in live database. But same query is running very fast in TEST database (both the DBs are in same server and identical.).
What could be the problem..?
Pls help..Urgent...
Thanks.
June 4, 2009 at 12:26 am
Hello,
Can you see any blocking occurring when the Query is run on the Live DB?
Also, when you say the DBs are the same, I assume you mean the schemas, but not the volume of data contained?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 4, 2009 at 12:40 am
John Marsh (6/4/2009)
Hello,Can you see any blocking occurring when the Query is run on the Live DB?
Also, when you say the DBs are the same, I assume you mean the schemas, but not the volume of data contained?
I wil check the activity monitor for blocking.
Well DBs are absolutely same...even the data, I had restored test DB from Live DB backup...
June 4, 2009 at 7:57 am
How old is the backup? Has more data been added to either database? Has either one had statistics updated or indexes rebuilt? Are you using identical parameters inside the queries against each database? Have you checked to see if they're generating different execution plans?
"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
June 4, 2009 at 8:29 am
Backup is most latest.
Have not updated statistics or indexes at all.
Parameters are also same. Execution plan I need to check..
Thanks.
June 5, 2009 at 1:01 am
First Execute this
Use [YourDBName]
GO
EXEC sp_updatestats
It will update the Stats for all the tables
Then try to rebuild or at least defrag the tables which are used mostly
then you need to check that where are files located on server any physical disk difference can greatly affect this any RAID configuration any other physical structure change.
First try these steps then tell us what is the progress !
Best of Luck !
Musab
http://www.sqlhelpline.com
July 21, 2009 at 8:19 am
Hello,
I encountered exactly similar problem, on LiveDB few queries are slower than on test DB. I run profiler and it is clearly visible that the same select query has more reads and cpu count on LiveDB, about 20% slower.
Everything which you mentioned in previous posts was checked, statistics, indexes, volume of data is the same, the SQL Server versions are the same. It also cannot be lock activity, all those queries are executed with nolock hint.
Of course hardware configuration is different, Live DB is bigger machine which can bear larger load but I have always been experiencing that Live DB was at least twice faster than test system.
Do you have other clues what can be responsible for such behaviour?
Cheers,
Bartek
July 21, 2009 at 8:25 am
Don't just compare query times or CPU load, look at the wait states. What are processes waiting on inside the system. That will tell you where the bottleneck is.
"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
July 21, 2009 at 8:27 am
Sorry guys, my question is related with SS2000. :blush:
But I think it is irrelevant in this case.
Any way, do you know how to move this post to SS2000 forum group?
Cheers,
Bartek
July 21, 2009 at 8:38 am
I'm not sure if Steve can move just the question or not. Just repost it. However, what I said still applies. Wait states are what will tell you the cause of the slow-down.
"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
July 21, 2009 at 8:53 am
Thanks Grant for help, certainly I will use your advice.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply