| Author |
Topic |
|
handro1104
Starting Member
3 Posts |
 Posted - 2012-02-11 : 08:54:30
|
| Recently I ran a query that took a long time to finish and it slowed down all the other users. Slowing down other user doesn't make sense; is this normal? Is there a way to make all users run independently? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-11 : 09:02:05
|
| its normal if you're using a transaction or placing an exclusive lock on one ore more shared objects------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
handro1104
Starting Member
3 Posts |
Posted - 2012-02-11 : 09:33:54
|
| Thank you very much for the answers. I don't need help with the query; my concern is why it affected, slowed down, all other used. It was a select query not using a transaction or placing an exclusive lock on one ore more shared objects. It was a very inefficient query on very large tables and probably used a huge space. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-11 : 09:43:51
|
| yep...even that can be a reason. if there were no proper indexes etc it can cause query to scan big table etc which can also cause hiccups. Analysing execution plan will give more info on that------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
handro1104
Starting Member
3 Posts |
Posted - 2012-02-11 : 10:05:47
|
| So, the thing is my queries will affect, slow down, other users. I was working under the impression that the execution was running in a CPU with multiprogramming and time slicing, giving a few milliseconds to each user in turn; that way if my query is inefficient, it will be very slow for me (it would take a lot of time slices), but nobody will be slowed down, they would get their time slices at the same time regardless of my query. This is how programs run in computers; is there a reason why a database would be different? If this is true, then in a production environment, a user running a complex query will slow down all other users. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-11 : 12:48:24
|
quote:
Originally posted by handro1104
So, the thing is my queries will affect, slow down, other users. I was working under the impression that the execution was running in a CPU with multiprogramming and time slicing, giving a few milliseconds to each user in turn;
That's not how the SQL OS handles multi-tasking. It lets a query run until it completes or exceeds its quantum (which is not a few ms). If the query was large and inefficient, it would probably have been run in parallel too, so using multiple cores concurrently. If it was really inefficient and large, it could have used 100% of the processing power on the server.In addition, it would have taken locks, even just as a read query. So any query that wanted to modify the locked objects (and an inefficient query could well lock far more than necessary for far longer than necessary) would have to wait.To add to that, the query could well have needed a larger amount of workspace memory than normal queries, and there's only a limited amount of that so it could have caused other queries to wait for memory grants, etc, etc, etc.quote:
If this is true, then in a production environment, a user running a complex query will slow down all other users.
Yup, which is why writing good, efficient code is so important.--Gail ShawSQL Server MVP |
|
|
|
|
Long query