How can I interpret the results of these DMVs to help me evaluate our partitioning strategy?

Version: SQL Server 2008 R2 Enterprise Edtn. (10.50.4000)

In attempt to evaluate our partitioning strategy, I wrote this query to get the access methods against indexes on partitions (in the broadest sense of the term, though I'm eliminating heaps). As I narrow my focus to partitioned tables, I believe I need to be looking at range_scan_count and singleton_lookup_count but am having a hard time conceptualizing.

SELECT 
    t.name AS table_name,
    i.name AS index_name,
    ios.partition_number, 
    leaf_insert_count,
    leaf_delete_count,
    leaf_update_count,
    leaf_ghost_count,
    range_scan_count,
    singleton_lookup_count,
    page_latch_wait_count ,
    page_latch_wait_in_ms,
    row_lock_count ,
    page_lock_count,
    row_lock_wait_in_ms ,
    page_lock_wait_in_ms,
    page_io_latch_wait_count ,
    page_io_latch_wait_in_ms
FROM sys.dm_db_partition_stats ps
    JOIN sys.tables t 
        ON ps.object_id = t.object_id
    JOIN sys.schemas s 
        ON t.schema_id = s.schema_id
    JOIN sys.indexes i 
        ON t.object_id = i.object_id
    AND ps.index_id = i.index_id
OUTER APPLY sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios                            
WHERE   
    ps.object_id = ios.object_id
    AND ps.index_id = ios.index_id
    AND ps.partition_number = ios.partition_number
    and ps.index_id = ios.index_id
    and ps.partition_number = ios.partition_number                                  
    and s.name <> 'sys'     
    and ps.index_id <> 0 ;

Relevant output (given the gap in SO's formatting of tables, this is a sample of the first 9 columns from the query above with last two columns being range_scan_count and singleton_lookup_count, respectively):

```
╔════════╦═════════════════╦════╦═══╦═══╦═══╦═══╦════════╦══════════╗
║ datetb ║ idx_datetb_col ║ 1 ║ 0 ║ 0 ║ 0 ║ 0 ║ 205740 ║ 3486408 ║
║ datetb ║ idx_datetb_col ║ 2 ║ 0 ║ 0 ║ 0 ║ 0 ║ 29617 ║ 1079649 ║
║ datetb ║ idx_datetb_col ║ 3 ║ 0 ║ 0 ║ 0 ║

Rather than looking at index utilization, I'd look at the plan cache to find your queries with the highest amounts of logical reads. Usually when I'm dealing with partitioning, I find just a handful of queries that are dominating reads - like 50-80% of the servers' reads overall. Check those queries to see if they're successfully doing partition elimination.

If they aren't doing partition elimination, but you think they should (based on your partition scheme), then work with the query writers to get partition elimination.

If they aren't eliminating partitions, and they can't (because of the way the query is written or the partition is designed), then it's time to start asking tough questions.

If the biggest logical read queries don't have anything to do with your partitioned table, then move on and focus on those other queries instead.

StackExchange Database Administrators Q#60113, answer score: 4