SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Not getting result of MAX()


Not getting result of MAX()

Author
Message
KcV
KcV
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 222
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.
chris.williams 58795
chris.williams 58795
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 15
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
SoberCounsel
SoberCounsel
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 140
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").
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60062 Visits: 17951
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
SoberCounsel
SoberCounsel
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 140
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.

;-)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60062 Visits: 17951
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. :-P

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
SoberCounsel
SoberCounsel
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 140
I like it, thanks! :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search