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 ««12

Index Primer - Just what statistics are kept? Expand / Collapse
Author
Message
Posted Wednesday, October 28, 2009 2:43 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
Another possibility is to use a while statement and delete/insert a smaller amount of records on each iteration so that the optimizer will decide to use a lower level of locking like page locks. This way you can avoid dirty reads because select statements will wait, but only until the current batch is complete.

While 0=0
Begin
Delete/Insert top (50000) TableAlias
From TableName TableAlias
Join etc
Where etc

If @@RowCount < 50000
Break
End

One implication of this method is that each batch is it's own transaction, so the whole thing cannot be committed/rolled back. Additionally this will ultimately take longer than executing full delete/insert in one fell swoop, but the duration on any particular row or page being locked will be decreased.

P.S. It probably goes without saying, but - you would need to change the 50000 to whatever makes sense.
Post #810328
Posted Thursday, October 29, 2009 4:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 26, 2014 3:29 PM
Points: 192, Visits: 130
Well Done.
I get better understanding with your examples plus your calculations.

One question:
Does it mean that index for C105_CompanyCode, and index for C105_Statement_Acct might be 'dropped', when a composite index of "C105_CompanyCode,C105_StatementAcct " is available?

C105_CompanyCode
C105_Statement_Acct
C105_CompanyCode,C105_StatementAcct

One suggestion:
If you could combine with dbcc showcontig, that might be very interesting for a cross over understanding.

Thanks for your contribution.
-David




Post #811266
Posted Tuesday, November 3, 2009 7:37 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:38 AM
Points: 166, Visits: 1,051
Thank you very much for an extremely informative and eye opening article. Much appreciated and, for me, it's quite a well timed read.

Thank you.
Post #812928
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse