Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2003-09-09 : 19:09:54
|
| Hi, I have an SQL statement which took 5 seconds to execute in one database and its about 10 mins now and still running in another db. Both databases were copied from Prod at the same time so data is exact!The query plan in the first one is different from the second one. The second one is not using the same index the same way as the first one. First I compared the indexes, no change. Then I updated the stats for the one which took longer while the query was running. Should I cancel the query and rerun to see any change?I plan to rebuild index if update stats dont work. Is this pretty much how I approach to the problem?Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-09 : 19:10:58
|
| The query is probably being blocked by some other process. Check for blocking by running sp_who.Tara |
 |
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-09-09 : 19:24:25
|
| Well here is what happened. The tester ran the program (which has the query) first, it kept running for more than 1/2 hour. Then she complained about it so a programmer pasted that query and ran it in QA while the program was still running. So, I checked the locks and saw that the tester's process was blocked by the programmer's process so I made the programmer cancel his query. But before I checked the locks, I saw the query plan and compared with the one in first db. They were different so I ran update stats. Now the question is #1: Does blocking affect the person who is blocking as well as the person who is blocked?#2: I asked the tester to rerun the program since I tried to update the stats. Does it matter if you update the stats on the table when someone is querying against the same table?#3: If I rebuild the indexes, can I get the query plan changed or update stats is the one which does that?Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-09 : 19:27:45
|
| #1: Blocking only affects the spids that are being blocked. It does not affect the spid that is blocking other spids.#2: I do not think that it matters.#3: I'm not sure.Tara |
|
|
|
|
|
|
Query Plan changes