March 5, 2009 at 2:06 pm
Bruce W Cassidy (3/5/2009)
Marios Philippopoulos (3/5/2009)
Actually, ID columns can be used a lot in queries. Not in the WHERE clause of course, but, certainly when they are in join clauses.[font="Verdana"]Er... really? Can you show me an example of SQL Server generating a range query as part of a join? I would have expected a merge or nested loop or the like.[/font]
Doesn't that involve an index scan? That's what I mean by range query, sloppy terminology on my part. But it is essentially the same thing behind the scenes, is it not?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 5, 2009 at 2:08 pm
Bruce W Cassidy (3/5/2009)
But I don't see any advantage for using the date over an ID field, so I still tend to use an ID field.
There is if you don't have an identity field. Last major system I worked on had uniqueidentifiers for primary keys. Ain't sticking a cluster on one of those thank you.
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
March 5, 2009 at 2:12 pm
Marios Philippopoulos (3/5/2009)
Doesn't that involve an index scan?
Not with good indexes. Single row index seeks or a full index scan or seeks on some other criteria followed by a join
That's what I mean by range query, sloppy terminology on my part. But it is essentially the same thing behind the scenes, is it not?
No. Range scan is an index seek to find the start of the range and then read along the index to find the end. You will never see one of those on a foreign key column as part of a join, because joins just don't work that way.
Range scans are things like SomeDate BETWEEN @Val1 and @val2, SomeStr LIKE 'AB%', SomeInt > 42
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
March 5, 2009 at 2:12 pm
[font="Verdana"]Heh. Pushing the limits of my own understanding here!
I believe a join would be merging a list of distinct values, not a range of values. So I'd expect that to be a bit different.
[/font]
March 5, 2009 at 2:19 pm
Thanks both, it seems I need to brush up on my knowledge of query-plan operators... 🙂
I'm still puzzled on this though:
where do the scans reported on the OID-based indexes originate from, if not from join operations...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 5, 2009 at 2:24 pm
GilaMonster (3/5/2009)
Bruce W Cassidy (3/5/2009)
But I don't see any advantage for using the date over an ID field, so I still tend to use an ID field.There is if you don't have an identity field. Last major system I worked on had uniqueidentifiers for primary keys. Ain't sticking a cluster on one of those thank you.
[font="Verdana"]Ick. Fair point.[/font]
March 5, 2009 at 2:49 pm
Going back to the original question, the DMV information (for seeks/scans/lookups at least) may be misleading as they are the most efficient methods based on that specific set of indexes. Change the indexes and you may change the results.
March 5, 2009 at 3:02 pm
Marios Philippopoulos (3/5/2009)
where do the scans reported on the OID-based indexes originate from, if not from join operations...
OID?
If you're talking about a scan on a table involved in a join then there are several reasons for a scan:
The join is a merge or hash. With those joins, selection of matching rows is done in the join, not in an index seek
The join is a nested loop and the table is the outer table in the join.
The join is a nested loop, the table is the inner but either there isn't an appropriate index or the optimiser has noted that the index isn't covering and there are too many rows to seek and lookup. (This is a bad position to be in. Poor performance very likely)
There's a difference between an index scan (read entire index, no seek predicates) and a range scan (seek predicate to find start or end of range and then read along the leaf pages). A range scan appears in the execution plan as an index seek.
An index scan on a cluster is a table scan, which is not something you want to see on a big table.
A range scan on a cluster can be fast, especially if the range is small).
Range scans are the result of inequality predicates.
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
March 5, 2009 at 3:05 pm
matt stockham (3/5/2009)
Going back to the original question, the DMV information (for seeks/scans/lookups at least) may be misleading as they are the most efficient methods based on that specific set of indexes.
Yup, and the other thing to note about the DMV is that it is information since the last time the service was started. It is not persisted across a restart of SQL.
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
Viewing 9 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply