December 17, 2012 at 12:31 am
Hi
i have a table having 2000000 rows. and i want a result of :
select max(pksrnoN) from Table1
it is numeric field
after waiting for 48 minutes i have to cancel it, but result did not came.
table is already indexed. normally it is working but today it did not.
why ?
help pls.
December 18, 2012 at 2:22 am
Im not sure but I have encountered similar problems when a table becomes locked. I would use SP_Lock or SP_who2 to try and diagnose what process is locking the table and then Kill 'SPID' to end the process. Hope this helps
December 18, 2012 at 7:39 am
I'd investigate adding the "with(nolock)" hint to your query - it doesn't involve killing processes, and it might solve your problem if it's being caused by an errant lock - BUT, you will need to be aware of the issues that this particular hint can/might cause ("dirty reads").
December 18, 2012 at 7:56 am
SoberCounsel (12/18/2012)
I'd investigate adding the "with(nolock)" hint to your query - it doesn't involve killing processes, and it might solve your problem if it's being caused by an errant lock - BUT, you will need to be aware of the issues that this particular hint can/might cause ("dirty reads").
The NOLOCK query hint has for worse implications than what most people consider "dirty reads". People hear that and think it only means uncommitted transactions. You can get duplicate or even missing data. Now I am not saying it should never be used but the implications are far greater than just a flippant "throw a NOLOCK on that query so it will run faster".
Here are just a couple of articles explaining this hint in further detail.
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 18, 2012 at 9:20 am
I knew by suggesting it that a few eyebrows would probably be raised!
Thanks Sean, for adding the links to further info about the nolock hint... as it happens, I was already aware of the issues it can cause (not just dirty reads!), and so I feel I have to highlight that I wasn't suggesting to just chuck it into queries regardless!!
I do still find that it's a useful hint - for me, it comes in most useful when dealing with large OLTP tables - though familiarity with the actual data being queried is a must, as is awareness of the potential problems with the hint.
😉
December 18, 2012 at 9:42 am
SoberCounsel (12/18/2012)
I knew by suggesting it that a few eyebrows would probably be raised!Thanks Sean, for adding the links to further info about the nolock hint... as it happens, I was already aware of the issues it can cause (not just dirty reads!), and so I feel I have to highlight that I wasn't suggesting to just chuck it into queries regardless!!
I do still find that it's a useful hint - for me, it comes in most useful when dealing with large OLTP tables - though familiarity with the actual data being queried is a must, as is awareness of the potential problems with the hint.
😉
You might want to take a look at isolation for large OLTP situations. Frequently using snapshot isolation is a better approach than using NOLOCK. It does have a little bit of associated overhead too. Nothing comes free in the database universe. 😛
http://msdn.microsoft.com/en-us/library/ms173763%28v=sql.105%29.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 19, 2012 at 4:04 am
I like it, thanks! 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply