| Author |
Topic |
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
 Posted - 2009-01-29 : 04:08:46
|
| I have a database that shares a server with other production databases. There is a particular query (a simple one) that takes a minute to run on this server. If this database is restored to any other server, the query only takes a few seconds to run. Profiler is saying that though the databases and the query are identical on both servers, the amount of disk reads for this query on the offending server is much much more than it is on the latter. Both servers are connected to a SAN (though different LUNS). I put the database on the offending server into single user mode, but the query was still slow. I killed some client connections into other databases and suddenly the query time sped up to a few seconds.Anu ideas what could be going on?ThankDrew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-29 : 04:51:54
|
Some thing to be rembered of 1. Query Optimizer uses saved statististics histogram to determine query plan2. Things such as RAM, CPU and other things have impact on which plan is used3. Storage Engine can add parallellism to planUse the Actual Execution Plan (not SQL Profiler) in this case to compare plans between the two servers.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-29 : 10:21:56
|
| Might be the query was blocked/locked in first server. Check with sp_who2 active. |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2009-01-29 : 17:46:04
|
| Hi, there is no blocking.OK, here's an update. There is no one logged onto the database other than me. All other databases are in single user mode. I put the offending database in single user mode and then back into multi. I then opened another tab in ssms and ran the same query. It returns a result set within seconds. However the other tab which is still open when run takes 5 minutes to run. If I run both at the same time, one will take seconds, the other minutes. Any ideas?Drew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-29 : 17:56:59
|
| So are Execution plans same ? |
|
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2009-01-29 : 18:57:56
|
quote:
Originally posted by drewsalem
Hi, there is no blocking.OK, here's an update. There is no one logged onto the database other than me. All other databases are in single user mode. I put the offending database in single user mode and then back into multi. I then opened another tab in ssms and ran the same query. It returns a result set within seconds. However the other tab which is still open when run takes 5 minutes to run. If I run both at the same time, one will take seconds, the other minutes.
Would this have anything to do with the execution plan caching the query? I think when you first execute a query it caches it for future runs. When you opened up another tab and ran it again it was using a cached execution plan perhaps?r&r |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2009-01-30 : 04:04:01
|
| Hi again,The plans are the same.I don't think it's a cache thing as when I run the query in the original tab (connection) it is still slow on subsequent runs.Additionally, later during the day even the fast runs become slow. Not gradually, but suddenly.Drew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2009-01-30 : 06:33:23
|
| This appears to be what is happening:1. When SQL Server first starts up, query runs in less than 15 seconds2. After a short period of time, query jumps (not a gradual climb) from 15 seconds to several minutes.3. If the database is then put into single_user mode and then back into multi_user mode, the query runs fast for all new connections into database. If I use the earlier connection (the one also used to change the database to single/multi user), it is slow.4. Go back to step 2Drew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-30 : 06:46:05
|
Memory problems?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2009-01-30 : 06:57:53
|
| The server has memory to spare. Can you explain the connection you think it may have with memory. Do you think it may be a network issue? ThanksDrew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-01-30 : 09:04:21
|
| It might be totally irrelevant, but my thoughts point to updating statistics and rebuilding indexes when I hear these sort of things...- Lumbago |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-30 : 09:59:19
|
| Or if all suggestions didn't help, Correlate SQL Profiler results with Performance monitors counters and pin down the issue. |
|
|
|
|
Slow query, but speeds up on any other server