Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Not getting result of MAX() Expand / Collapse
Author
Message
Posted Monday, December 17, 2012 12:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 18, 2014 5:07 AM
Points: 65, Visits: 203
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.

Post #1397110
Posted Tuesday, December 18, 2012 2:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 4:13 AM
Points: 23, 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
Post #1397616
Posted Tuesday, December 18, 2012 7:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 8:28 AM
Points: 51, Visits: 139
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").
Post #1397799
Posted Tuesday, December 18, 2012 7:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 13,139, Visits: 11,979
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 Moden's 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)
Post #1397814
Posted Tuesday, December 18, 2012 9:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 8:28 AM
Points: 51, Visits: 139
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.


Post #1397877
Posted Tuesday, December 18, 2012 9:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 13,139, Visits: 11,979
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 Moden's 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)
Post #1397886
Posted Wednesday, December 19, 2012 4:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 8:28 AM
Points: 51, Visits: 139
I like it, thanks!
Post #1398284
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse