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

Query Cost 27% Expand / Collapse
Author
Message
Posted Thursday, May 17, 2012 4:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 36,612, Visits: 31,050
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1301635
Posted Thursday, May 17, 2012 4:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 42,332, Visits: 35,386
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 2008, MVP
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

Post #1301642
Posted Thursday, May 17, 2012 5:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 36,612, Visits: 31,050
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1301646
Posted Friday, May 18, 2012 3:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:05 AM
Points: 6,091, Visits: 7,114
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
Post #1302855
Posted Friday, May 18, 2012 3:41 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 42,332, Visits: 35,386
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 2008, MVP
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

Post #1302870
Posted Friday, May 18, 2012 4:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:05 AM
Points: 6,091, Visits: 7,114
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
Post #1302882
Posted Friday, May 18, 2012 4:12 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 42,332, Visits: 35,386
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 2008, MVP
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

Post #1302885
Posted Friday, May 18, 2012 4:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:05 AM
Points: 6,091, Visits: 7,114
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
Post #1302886
Posted Friday, May 18, 2012 4:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:05 AM
Points: 6,091, Visits: 7,114
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
Post #1302888
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse