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 |
|
bmturney
Starting Member
7 Posts |
 Posted - 2009-03-16 : 10:47:58
|
| I've worked with DB2 for a number of years but I'm relatively new to SQL Server... one frustrating problem that I've had in making the switch is the length of time it takes to cancel some queries... In DB2 I could cancel a query and in relatively short order it was done... and I could go on with what I was doing... with SQL Server in some cases it seems to take FOREVER... in one instance, about 18 seconds into the query I clicked Cancel to cancel the query and it took 22 minutes for it to finish canceling the query... what is going on? If it was on an Update or something I could be a little more forgiving because it has to back out the updates it has done to that point... but 22 minutes to back out of a SELECT that has been running for 18 seconds... someone please tell me there is a property some where that can be tweaked to improve this. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-16 : 10:53:06
|
You tell us a SELECT only needed 22 minutes to "rollback"?Show us the complete statement.I believe some transactions also were going on.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-16 : 10:56:17
|
| is a rather big query batch that you were running that it took 22 mins to cancle? |
|
|
|
bmturney
Starting Member
7 Posts |
Posted - 2009-03-16 : 11:05:58
|
| Unfortuntely I don't have the query that took 22 minutes to cancel any more... I do know that on the select, I was doing a full join on two tables, one had a little over 9 million records in it the other has several hundred thousand... I run into this problem quite often (the 22 minute one was an extreme case) but I often run into a problem where I'm running a query (just a simple select with a few joins and a where clause... maybe a group by and order by clause but nothing fancy like subselects or calling a UDF or anything like that... and if I try to cancel it a few seconds into the query it will take several minutes for it to finally cancel the query.(also understand that the database I'm working with is NOT normalized... the DBA that setup this database should be taken into a dark alley and pummeled with an entire library of DB Design for Dummies books) |
|
|
|
|
|
|
Canceling a Query in SQL Server...