12

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 ║ 0 ║  29617 ║  1174547 ║
║ datetb ║ idx_datetb_col  ║  4 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  2952991 ║
║ datetb ║ idx_datetb_col  ║  5 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  3974886 ║
║ datetb ║ idx_datetb_col  ║  6 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  2931450 ║
║ datetb ║ idx_datetb_col  ║  7 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  3316960 ║
║ datetb ║ idx_datetb_col  ║  8 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  3393439 ║
║ datetb ║ idx_datetb_col  ║  9 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  3735495 ║
║ datetb ║ idx_datetb_col  ║ 10 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  4803804 ║
║ datetb ║ idx_datetb_col  ║ 11 ║ 0 ║ 0 ║ 0 ║ 0 ║  29617 ║  7655091 ║
║ datetb ║ idx_datetb_col  ║ 12 ║ 1 ║ 0 ║ 0 ║ 0 ║ 174326 ║ 47377226 ║
╚════════╩═════════════════╩════╩═══╩═══╩═══╩═══╩════════╩══════════╝

I see a few different possibility but I need some direction on how to think about this (of course I'm couching this in "may" because I know that "it depends", but I'm also looking for conceptual comprehension):

  1. Similar values for all partitions of range_scan_count may indicate that we're not getting good partition elimination because we're scanning all partitions roughly the same number of times.
  2. Varying values for all partitions of singleton_lookup_count accompanied with significantly lower values for range_scan_count may indicate good frequent partition elimination because we're scanning less than we're seeking.
  3. ?

Those are my thoughts so far. I was hoping to have someone weigh in on how I could use this, or another set of information, to determine which tables would most likely benefit from dropping partitioning in favor of indexes altogether.

EDIT

Here's a clipped DDL:

CREATE TABLE [dbo].[date_table](
    [date_id] [int] NOT NULL,
    [calendar_date] [datetime] NULL,
    [valdate] [datetime] NULL,
        CONSTRAINT [PK_datedb] PRIMARY KEY CLUSTERED 
        (
            [date_id] ASC
        ) ON [partschm]([date_id]);

CREATE UNIQUE NONCLUSTERED INDEX [idx_datetb_col] ON [dbo].[date_table]
(
    [calendar_date] DESC,
    [date_id] ASC
) ON [partschm]([date_id])
GO
  • Could you edit the question to include the table schema? Any interpretation has to depend on the business meaning of the partitioning. – Jon Seigel Mar 4 '14 at 18:29
  • @JonSeigel I'd be happy to do that, but it'd result in a wall of code so I'm updating with a clipped equivalent – swasheck Mar 4 '14 at 20:17
4

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.

  • @swasheck You bet! Glad I could help. – Brent Ozar Jul 16 '14 at 6:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.