Index Primer - Just what statistics are kept?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Josef Richberg » Index Primer - Just what statistics are kept?...

Add to briefcase

13 posts, Page 2 of 2

««1 2

Index Primer - Just what statistics are kept?

Expand / Collapse

Author

Message

Posted Wednesday, October 28, 2009 2:43 PM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, November 09, 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

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:22 PM
Points: 192, Visits: 111

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

AndrewJacksonZA

Posted Tuesday, November 03, 2009 7:37 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 5:52 AM
Points: 165, Visits: 1,022

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

13 posts, Page 2 of 2

««1 2

Permissions

Expand / Collapse