September 4, 2012 at 6:21 pm
I have a Non-Unique Non-clustered index on col1,col2.Using sys.dm_db_index_usage_stats & sys.dm_db_index_operational_stats DMVs for this index shows this result:
user_seek=0
user_scan=67443
system_scan=5
system_seek=0
singleton_lookup_count=12628
range_scan_count=1828037
here's the question:
How is it possible to have singleton_lookups when there's no user_seek/system_seek?
Pooyan
September 6, 2012 at 3:36 pm
any suggestion?;-)
Pooyan
September 6, 2012 at 3:43 pm
Shot in the dark based on what you have posted, single record scans.
September 6, 2012 at 4:26 pm
this index also has some column included in it's leaf level.
Both col1,col2 are non-unique so even if the engine is looking for a single combination of them it should go with a range_scan not a singleton_lookup.The other thing here is that when the index is being scanned it should read all the leaf pages so it should again use the range_scan not a singleton_lookup
Pooyan
September 6, 2012 at 4:30 pm
pooyan_pdm (9/6/2012)
this index also has some column included in it's leaf level.Both col1,col2 are non-unique so even if the engine is looking for a single combination of them it should go with a range_scan not a singleton_lookup.The other thing here is that when the index is being scanned it should read all the leaf pages so it should again use the range_scan not a singleton_lookup
I have zero information from you to base any actual answer on what you are seeing. I don't have access to your system, I can't run queries or tests of any sort against your database. I gave you a simple shot in the dark. Sorry if it doesn't meet your criteria for an answer.
For a moment, think about what you posted, if that is all some one gave you, would you be able to give them an answer?
Every database is different in some way. What may make sense in one may not be the same in another.
September 6, 2012 at 5:11 pm
Thanx for your answer anyway. but it's a general question nothing to do with the database details.what I asked is how can we have singleton_lookups on an non-unique index while there's only user_scans and not user_seeks.
Pooyan
September 6, 2012 at 5:26 pm
And I gave you a valid answer, scans can be single record scans.
September 6, 2012 at 5:27 pm
The count of singleton_lookups is the number of individual row retrievals by key or by RID and, presumably they are happening as the result of scans rather than seeks.
September 6, 2012 at 5:51 pm
when the index key is non-unique even when you're looking for a single value(a user_seek) sql server performs a range_sacan because there might be more than one records with that specific value.So we should have range_scan instead of singleton_lookup.
Pooyan
September 6, 2012 at 6:56 pm
pooyan_pdm (9/6/2012)
when the index key is non-unique even when you're looking for a single value(a user_seek) sql server performs a range_sacan because there might be more than one records with that specific value.So we should have range_scan instead of singleton_lookup.
And there might not also. You got an answer, if you don't like it try asking it on another forum, like SQLTeam, and see what answers you get there.
What may seem logical may not be what actually happens behind the scenes.
September 6, 2012 at 6:59 pm
You are basing a generic question off the data from a specific table and index without giving anyone the benefit of know anything about the table, the data contained in the table, the index in question, or (and more importantly) the queries being run against the table that use that index.
You get back what you give.
September 6, 2012 at 8:34 pm
Lynn Pettis (9/6/2012)
pooyan_pdm (9/6/2012)
when the index key is non-unique even when you're looking for a single value(a user_seek) sql server performs a range_sacan because there might be more than one records with that specific value.So we should have range_scan instead of singleton_lookup.And there might not also. You got an answer, if you don't like it try asking it on another forum, like SQLTeam, and see what answers you get there.
What may seem logical may not be what actually happens behind the scenes.
. What I said is true. You can take a look at paul white article you can also create a non-unique index and test it yourself. You can also check this post out:
http://www.sqlservercentral.com/Forums/Topic1341796-1550-1.aspx?Highlight=Singleton
Pooyan
September 8, 2012 at 2:00 am
you've answered you're own question, if an index is non unique, accesses will be scans, not seeks, hence 0 seeks reported.
your question should be "why would I see a singleton lookup on a non uniq index?"
semi joins could generate singleton lookups:
... WHERE EXISTS (SELECT * FROM tWHERE COL1 =@p1 and COL2 =@p2)
As could fk's when you attempt to delete a row in the parent, it would do a singleton lookup to check if any child rows exist that reference the parent.
Also, you are mixing two dmvs. notice how you show 65 thousand user scans, and 1.8 million range scans? they aren't 1-to-1.
September 8, 2012 at 2:15 am
Oh, and if you update a col that is included in an index, it will probably show up as a singleton lookup for the index but not as a user scan.
User seeks and user scans only reflect when the index was used to satisfy a query, i.e. once per index seek or index scan operator in executed query plans.
Operational stats are at a lower level.
September 8, 2012 at 11:47 am
You can have user_seeks and range_scans on a non unique index at the same time when you're looking for a column of an index , I guess the reason there's no user_seeks here might be that there's lots of columns included in the leaf level of this index(I know it might not be the best design) and the optimizer decides to scan this index instead of scanning the clustered index. I agree with the rest of your post,one part of my question was that I was looking for scenarios that you have singleton_ lookups on a non unique index
Pooyan
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply