SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 indexes...
 Forum Locked
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 04/04/2006 :  16:34:44  Show Profile  Visit spirit1's Homepage
thanx. just wanted to know that... can you give the link to his blog about this?

but my real question is why doesn't the SQL server's optimizer use a seek instead of a scan
in the statement without where?


Go with the flow & have fun! Else fight the flow
Blog thingie: http://weblogs.sqlteam.com/mladenp

Edited by - spirit1 on 04/04/2006 16:35:13
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 04/04/2006 :  16:43:27  Show Profile  Visit tkizer's Homepage
http://www.sql-server-performance.com/dbcc_commands.asp
http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp

All I can find in Adam's blog is him using it during his performance analysis. One example:
http://www.sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

You really only need DBCC DROPCLEANBUFFERS in your example as we only care about clearing out the data pages from the cache.

Perhaps you can forward your question about the seek/scan along to Paul and he can have an engineer from MS help answer it.

Tara Kizer
aka tduggan
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 04/04/2006 :  16:47:51  Show Profile  Visit spirit1's Homepage
well i would... but he's on vacation

i'll send him a mail... thanx tara.

Go with the flow & have fun! Else fight the flow
Blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 04/04/2006 :  17:34:38  Show Profile
>> why doesn't the SQL server's optimizer use a seek instead of a scan
>> in the statement without where?

Because it has no predicate to attempt optimisation, and hence can't use the histogram and thus no IO strategy.

I blogged a little about this.. http://weblogs.sqlteam.com/davidm/archive/2005/04/07/4291.aspx
I use these "fake" data hints in base views all the time..



DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 04/04/2006 :  17:40:47  Show Profile  Visit spirit1's Homepage
yeah now that re-read it i remember i've read it a while ago... nice!

at the risk of sounding silly
what histogram??

Go with the flow & have fun! Else fight the flow
Blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 04/04/2006 :  18:17:22  Show Profile  Visit spirit1's Homepage
well after reading this :
http://www.sql-server-performance.com/indexes_not_equal.asp
i know which histogram you meant

hmm... some more testing awaits tommorow



Go with the flow & have fun! Else fight the flow
Blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000