March 30, 2011 at 9:21 am
Does the singleton_lookup_count number get re-set when an index gets rebuilt? I suspect it would, but I wanted to double check.
Thanks
Henry
March 30, 2011 at 10:20 am
I ran a quick test and it does.
Here's the test:
CREATE TABLE #test
(
id INT CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
)
INSERT INTO #test
(
id
)
SELECT TOP 100
ROW_NUMBER() OVER (ORDER BY AC.column_id)
FROM
sys.all_columns AS AC
SELECT
*
FROM
#test AS T
WHERE
T.id = 1 ;
SELECT
DDIOS.database_id,
DDIOS.OBJECT_ID,
OBJECT_NAME(DDIOS.object_id),
DDIOS.index_id,
DDIOS.singleton_lookup_count
FROM
sys.dm_db_index_operational_stats(DB_ID('tempdb'), OBJECT_ID('#test'), 1, 1) AS DDIOS
ALTER INDEX PK_test ON #test REBUILD
SELECT
DDIOS.database_id,
DDIOS.OBJECT_ID,
OBJECT_NAME(DDIOS.object_id),
DDIOS.index_id,
DDIOS.singleton_lookup_count
FROM
sys.dm_db_index_operational_stats(DB_ID('tempdb'), OBJECT_ID('#test'), 1, 1) AS DDIOS
DROP TABLE #test
From BOL:
The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available. Dropping an index will cause the corresponding statistics to be removed from memory and no longer be reported by the function. Other DDL operations against the index may cause the value of the statistics to be reset to zero.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2011 at 11:49 am
The BOL entry reads a bit like a car rental agreement and to be blunt is some ways is useless
The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache.
(So the value is re-set if it wasn't in the metadata and it is brought into the cache)
Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started.
(May reflect? What are the odds on it is reflecting reality? Are the numbers going to be off on the low side or the high side)
The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available.
(Finally a declarative sentence)
Dropping an index will cause the corresponding statistics to be removed from memory and no longer be reported by the function.
(two in a row!)
Other DDL operations against the index may cause the value of the statistics to be reset to zero.
(May cause, are there some specific DDL actions that will or will not or can a specific DDL action sometimes cause the statistics to be reset? If so what are those)
March 30, 2011 at 11:55 am
I agree that the BOL entry isn't all that helpful, but with the test provided it shows that ALTER INDEX REBUILD is one of the DDL statements that DOES cause a reset. I would bet the ALTER INDEX REORGANIZE does not reset.
It would be nice if the documentation was more specific about what will cause a reset.
I don't think that they can be more specific because the cache can be so variable based on load and amount of memory, so there is no guarantee that values will stay in cache.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 20, 2011 at 9:00 am
Jack Corbett (3/30/2011)
It would be nice if the documentation was more specific about what will cause a reset.
Personally, I'd rather have a column which tells me when the data in the row began being collected, so I can calculate rates over time.
Knowledge of what triggers a reset is less important to me than knowledge of what time period the row covered at the time it was collected.
April 20, 2011 at 10:39 pm
How does this value of the singleton_lookup_count helpful to us?
Thanks in advance
April 21, 2011 at 9:10 am
The value in singleton_lookup_count can help you determine the usefulness of the index. This basically says how many times a query is returning a single row from the index. This can help show contention/hotspots.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply