Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Cost 27%


Query Cost 27%

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44922 Visits: 39857
GilaMonster (5/17/2012)
Jeff Moden (5/16/2012)
Are you suggesting that if I use something like a tenth of a table that a scan is virtually guaranteed?


If you are using a non-covering index, then absolutely yes. The tipping point (where using a noncovering index and doing key lookups is less efficient than a table scan) is somewhere around 0.5% of the total rows in the table (it's ~ number of rows = 30% of the number of pages in the table)

There's a post on my blog "Seek or Scan" which shows this. Sorry, don't have time to find it, got to get to class.


I guess I need a lesson on how to do a search once in your site, Gail.

{edit} Found it using Google but there doesn't appear to be a way to search in your fine site.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47147 Visits: 44346
The built in search was useless, so I turned it off. It's on my to-fix list. Should have mentioned the category - indexes - would have made things easier to find.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44922 Visits: 39857
No problem. Thanks.

I ran the test you included in the Blog entry and actually did get seeks at the .5% and .3% levels instead of scans, but that still absolutely supports what you said. I was having a hard time accepting it all until I realized that you and Craig where talking about things that didn't cover in the index.

Shifting back to the likes of things like calendar tables (keeps coming up because I'm considering writing an article about such things)... your examples seem to exemplify why to not fill it with a bunch of garbage that SQL has functions for. If you don't need to search for it, you might not want to include it in the actual table just because its convenient. Then again, if you use it a lot, maybe. Heh... "It Depends".

Thanks for the feedback and "SQL in the Wild".

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5685 Visits: 7660
Jeff Moden (5/17/2012)
No problem. Thanks.

I ran the test you included in the Blog entry and actually did get seeks at the .5% and .3% levels instead of scans, but that still absolutely supports what you said. I was having a hard time accepting it all until I realized that you and Craig where talking about things that didn't cover in the index.

Apologies for the confusion. Yeah, my numbers were off too, sorry, it's 0.003 or .3%... and I dropped it in the middle of the two, heh. Whoops. I find most non-clusters are non-covering and used for search mechanics, though you do have the occassional exception for tight data pulls.

Shifting back to the likes of things like calendar tables (keeps coming up because I'm considering writing an article about such things)... your examples seem to exemplify why to not fill it with a bunch of garbage that SQL has functions for. If you don't need to search for it, you might not want to include it in the actual table just because its convenient. Then again, if you use it a lot, maybe. Heh... "It Depends".

Thanks for the feedback and "SQL in the Wild".

An alternative to proc or table is to meet in the middle with a non-persisted calculated column, Jeff. Keeps it out of the data-pages but available on row-usage. I'd recommend being gentle with them though. Somewhere around here I have some tests where I shattered table speed goofing around with them but I'll have to find it and my notes. Another article I started research on and then let fall off the earth.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47147 Visits: 44346
Evil Kraig F (5/18/2012)

Apologies for the confusion. Yeah, my numbers were off too, sorry, it's 0.003 or .3%... and I dropped it in the middle of the two, heh.


No, it's not. It's usually around 0.5% of the table, but it is not a hard and fast % of the table. The most accurate calculation would be that the query tips around row count = 20% of total pages in the table.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5685 Visits: 7660
GilaMonster (5/18/2012)
Evil Kraig F (5/18/2012)

Apologies for the confusion. Yeah, my numbers were off too, sorry, it's 0.003 or .3%... and I dropped it in the middle of the two, heh.


No, it's not. It's usually around 0.5% of the table, but it is not a hard and fast % of the table. The most accurate calculation would be that the query tips around row count = 20% of total pages in the table.

Which may be where I confused myself. Thank you for re-clarifying. I think I'll go re-read your blog a few times until it gets better stuck in my head. :-)

Where the heck did I pull 0.3% from? Goes digging....


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47147 Visits: 44346
Evil Kraig F (5/18/2012)
I think I'll go re-read your blog a few times until it gets better stuck in my head. :-)


That's not on my blog. It's stuff I've learned since writing that post.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5685 Visits: 7660
GilaMonster (5/18/2012)
Evil Kraig F (5/18/2012)
I think I'll go re-read your blog a few times until it gets better stuck in my head. :-)


That's not on my blog. It's stuff I've learned since writing that post.


Hm, I pulled that number from ... somewhere... and yours is 4% actually for the one I was thinking of. It's out there somewhere, dangit.

I need to find it, I need to go...

TO THE CLOUD!


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5685 Visits: 7660
Alright, I found where I pulled .3% out of ... the Aether... and realized why I'd done it.

For those interested, a more in depth discussion can be found here from Kimberly Tripp:
http://www.sqlskills.com/BLOGS/KIMBERLY/category/The-Tipping-Point.aspx

It's because the majority of tables I was working with at the time were in the range of 100 rows/page (give or take) and that number simply stuck to my head. Yawhoops. Sorry.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
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