Data Page Count Influence on the Query Execution Plan

In my post Row and Page Count Influence on Estimated CPU and I/O Cost I demonstrated how I/O cost of a clustered index scan had sensitivity to page counts but not row counts.  For this post I’ll lay out a direct connection between data page counts and the query execution plan that gets generated.

To illustrate the scenario, I created a table in the Credit database based on the charge table and I added two indexes, one clustered and one nonclustered:

 
USE [Credit];
GO

SELECT TOP 575000
[charge_no],
[member_no],
[provider_no],
[category_no],
[charge_dt],
[charge_amt],
[statement_no],
[charge_code]
INTO [dbo].[charge_demo]
FROM [dbo].[charge];
GO

CREATE CLUSTERED INDEX [charge_demo_charge_no]
ON [dbo].[charge_demo] ([charge_no]);
GO

CREATE NONCLUSTERED INDEX [charge_demo_charge_amt]
ON [dbo].[charge_demo] ([charge_amt])
INCLUDE ([member_no])
WITH (FILLFACTOR = 100);
GO
 

Next, I checked the data page counts by index for this new 575,000 row table:

SELECT  [index_id],
[in_row_data_page_count]
FROM    [sys].[dm_db_partition_stats]
WHERE   [object_id] = OBJECT_ID('dbo.charge_demo');
GO

The clustered index has 3,426 data pages and the nonclustered index has 1,567 data pages.

Next I looked at the execution plan for the following query:

SELECT [member_no],
SUM([charge_amt]) AS [charge_amt]
FROM [dbo].[charge_demo]
WHERE [charge_amt] > 0
GROUP BY [member_no]
OPTION (RECOMPILE);
GO
 

The query execution plan (via SQL Sentry Plan Explorer) was as follows:

image thumb Data Page Count Influence on the Query Execution Plan

The overall estimated subtree cost for the plan ended up being 4.6168.

Next, I rebuilt the nonclustered index using a very low fill factor (far lower than I would ever recommend, but I was doing this to demonstrate the placement of the same number of rows over many more pages than the original default fill factor):

CREATE NONCLUSTERED INDEX [charge_demo_charge_amt]
ON [dbo].[charge_demo] ([charge_amt])
INCLUDE ([member_no])
WITH (FILLFACTOR = 1, DROP_EXISTING = ON);
GO
 

The clustered index still has 3,426 data pages (since we didn’t change it), but now the nonclustered index has 143,753 data pages instead of the original 1,567 data pages.  And again, this represents the same 575,000 row count. Re-executing the original test query, I saw the following changed plan:

image thumb1 Data Page Count Influence on the Query Execution Plan

The overall estimated subtree cost for the plan increased to 54.3065 with a few other significant changes as well.  The second plan switched to using a clustered index scan instead of a nonclustered index seek.  Also, the second plan uses a stream aggregate with an “injected” sort operation, instead of the original plan’s hash match aggregate operation.

New Course: “SQL Server: Query Plan Analysis”

Yesterday Pluralsight published my new course, SQL Server: Query Plan Analysis. The short description is as follows: “Learn how to interpret and analyze SQL Server query plans, making performance problems easier to recognize and understand, applicable to developers, DBAs, and anyone responsible for SQL Server, from SQL Server 2005 onwards.”

As of today, that makes 15 SQLskills-authored courses available in the Pluralsight library (Pluralsight author course links below):

Glenn Berry

Paul Randal

Jonathan Kehayias

Joe Sack

Erin Stellato

We have much more planned across the team, so stay tuned…

Which LOB pages are associated with a specific columnstore segment?

Chalk this post up as an “exploratory” mission based on the following question…

How do I track which LOB pages are associated with a specific columnstore index segment?

Jonathan Kehayias and I discussed this the other day and hashed out a few options for tracking this.

To illustrate this topic and keep it at a granular level – I used a simple table named FactIDDemo with a bigint FactID column that had a unique clustered index on it.  Again – exploratory and not intended to be a realistic implementation pattern.

I loaded the table with 1,048,576 rows. And the segment statistics were as follows:

SELECT  [partition_id],
[segment_id],
[row_count],
[min_data_id],
[max_data_id]
FROM    sys.[column_store_segments]
WHERE   [partition_id] = 72057594043236352 ;

image thumb Which LOB pages are associated with a specific columnstore segment?

How many LOB pages were allocated for this single segment?

SELECT  [partition_id],
[object_id],
[index_id],
[partition_number],
[in_row_used_page_count],
[lob_used_page_count],
[used_page_count],
[row_count]
FROM sys.[dm_db_partition_stats]
WHERE [partition_id] = 72057594043236352;

image thumb1 Which LOB pages are associated with a specific columnstore segment?

We see 351 used LOB pages and executing DBCC IND confirms this as well, outputting the page ids accordingly:

DBCC IND('BigFactTable', 'FactIDDemo', 2);

That command returned 351 rows – one of which was the IAM page and the remainder text pages.

I also created the following XE session to validate page access (testing on a cold cache using my session ID as well):

CREATE EVENT SESSION [Columnstore Page Access] ON SERVER
ADD EVENT sqlserver.physical_page_read(
ACTION(sqlserver.session_id)
WHERE ([sqlserver].[session_id]=(57)))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

And I used the following test query to initiate the physical page read events:

SELECT  COUNT([FactID]) AS [FactIDCount]
FROM [dbo].[FactIDDemo]
WHERE [FactID] BETWEEN 1 AND 1048576;
GO

This query resulted in 349 events related directly to the columnstore index access.  That is different from the 351 page count from sys.[dm_db_partition_stats] and DBCC IND output.  The XE event didn’t capture the IAM page reference (in my case, page 1870780) – and it also didn’t retrieve page 1870777 which was a pagetype 3 (LOB page) and when I looked at it via DBCC PAGE, didn’t show BLOB_FRAGMENT sections.

Segments are the unit of transfer for columnstore index access.  While this is the logical unit of transfer, a segment is stored as one or more LOB pages – and to track that physical access, it seems that the sqlserver.physical_page_read is a viable way of doing so.

Validating Instance-Level Index View and MERGE Optimization Activity

You may have read the blog posts a few weeks ago regarding indexed views and MERGE statement usage and incorrect results. If not, please see the following blog posts:

I won’t rehash what they have collectively already covered thoroughly – but just a quick tip about identifying index view and MERGE optimization activity via the sys.dm_exec_query_optimizer_info DMV…  The following query shows counter name and occurrences of optimizations for MERGE statements and indexed views having been matched since the SQL Server instance last restarted:

SELECT  [counter],
[occurrence]
FROM sys.[dm_exec_query_optimizer_info]
WHERE counter IN
('merge stmt',
'indexed views matched');

I see this as a “first cut” check – but there are some key limitations to why this would only be a starting data point and not the “end all, be all” approach:

  • As mentioned earlier, occurrences are as of the last restart of SQL Server – and you could still be impacted by the issue - but a specific workload may not have been executed yet
  • The counters are instance level – so you will not know which databases were specifically associated with the activity and whether this is an actual MERGE + indexed view combo as described in the aforementioned blog posts

Even with the limitations, if you see non-zero values for the counters, this might accelerate your investigation and application of the appropriate cumulative update.  I prefer keeping up with serious issues in this case, but if you need to prioritize what gets patched in larger environments with thousands of SQL Server instances, this may help drive that prioritization.

Are Filtered Statistics Useful with Parameterization?

This post is based on a question I received last week…

If we have a filtered index that was not a match for a query due to parameterization (or a variation of which the parameter is not known at compile time), we can see the unmatched filtered index name in the UnmatchedIndexes element of SHOWPLAN_XML output.

The question I received on this subject was, “what about for filtered statistics?”  Can those still be used for parameterized scenarios? The following is a quick test to see if the stats also don’t qualify for matching… I’ll start by showing the filtered statistics matching behavior using the following statistics object:

USE [Credit];
GO

CREATE STATISTICS fstat_charge_provider_no
ON [dbo].[charge] ([member_no], [category_no])
WHERE [provider_no] = 484;
GO

The following query will use the statistics, and I’ve included the trace flags to show the usage from a query optimizer perspective:

SELECT DISTINCT
        1.[member_no],
        1.[category_no],
        1.[provider_no]
FROM    [dbo].[charge] AS c
WHERE   1.[provider_no] = 484 AND
        1.[member_no] = 9527 AND
        1.[category_no] = 2
OPTION  (QUERYTRACEON 3604, QUERYTRACEON 9204);
GO

This returns the following message output based on an initial compilation:

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 2, ColumnName: category_no, EmptyTable: FALSE

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 3, ColumnName: provider_no, EmptyTable: FALSE

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 6, ColumnName: member_no, EmptyTable: FALSE

Filtered stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 7, ColumnName: member_no, 
Expr: ([provider_no]=(484)), EmptyTable: FALSE

Filtered stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 7, ColumnName: member_no, 
Expr: ([provider_no]=(484)), EmptyTable: FALSE

Filtered stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 7, ColumnName: member_no, 
Expr: ([provider_no]=(484)), EmptyTable: FALSE

Now let’s use a local variable instead for the provider_no predicate (used for the filtered statistics):

DECLARE @provider_no INT = 484;

SELECT DISTINCT
        1.[member_no],
        1.[category_no],
        1.[provider_no]
FROM    [dbo].[charge] AS c
WHERE   1.[provider_no] = @provider_no AND
        1.[member_no] = 9527 AND
        1.[category_no] = 2
OPTION  (QUERYTRACEON 3604, QUERYTRACEON 9204);
GO

This time we see the following trace flag 9204 output:

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 2, ColumnName: category_no, EmptyTable: FALSE

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 3, ColumnName: provider_no, EmptyTable: FALSE

Stats loaded: DbName: Credit, ObjName: dbo.charge, IndexId: 6, ColumnName: member_no, EmptyTable: FALSE

Even though the provider_no value matched “484”, the filtered stats were not loaded as the value was not known at compile time. This is one specific scenario – so if you have a different one on this subject, please feel free to share on the comments of this post.

Thanks!

Redundant Query Plan Branches

Consider the following “basic_member” view definition from the Credit database:

CREATE VIEW [dbo].[basic_member]
AS
SELECT  [member].[member_no],
[member].[lastname],
[member].[firstname],
[member].[middleinitial],
[member].[street],
[member].[city],
[member].[state_prov],
[member].[mail_code],
[member].[phone_no],
[member].[region_no],
[member].[expr_dt],
[member].[member_code]
FROM    [dbo].[member]
WHERE   [member].[member_no] NOT IN (SELECT    [corp_member].[member_no]
FROM      [dbo].[corp_member]);

GO

A simple SELECT from this view returns 8,498 rows and has the following plan shape (and I’m boxing in an “areas of interest” via SQL Sentry Plan Explorer’s rendering of the plan):

image thumb4 Redundant Query Plan Branches

We see that the view has a predicate on member_no NOT IN the corp_member table.  But what happens if the original report writer doesn’t look at the view definition and decides they need this same predicate applied at the the view reference scope (not realizing this was already taken care of)?  For example:

SELECT  [basic_member].[member_no],
[basic_member].[lastname],
[basic_member].[firstname],
[basic_member].[middleinitial],
[basic_member].[street],
[basic_member].[city],
[basic_member].[state_prov],
[basic_member].[mail_code],
[basic_member].[phone_no],
[basic_member].[region_no],
[basic_member].[expr_dt],
[basic_member].[member_code]
FROM    [dbo].[basic_member]
WHERE   [basic_member].[member_no] NOT IN
(SELECT   [corp_member].[member_no]
FROM     [dbo].[corp_member]);

Like the previous query against the view, we see 8,498 rows.  But unlike the previous query, we see the following plan:

image thumb5 Redundant Query Plan Branches

Notice the redundancy – even though the result set is identical between the two versions.  And the tables I’m using are small, but you can still see the difference in scan count and logical reads.

Query Against View

Table ‘member’. Scan count 2, logical reads 305, physical reads 2, read-ahead reads 294, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘corporation’. Scan count 1, logical reads 8, physical reads 1, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query with Redundant Predicate

Table ‘member’. Scan count 3, logical reads 325, physical reads 2, read-ahead reads 294, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘corporation’. Scan count 2, logical reads 16, physical reads 1, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And there is additional I/O overhead associated with the second plan of course.  For the Credit database, the scale is small, but imagine the difference for a very large table.

You cannot always count on easily identifying redundant areas.  The Query Optimizer may find an optimal plan in spite of the way it was written – but if you do see repeating branches in a query execution tree associated with a performance issue, you may want to explore the possibility of overlapping/redundant logic further.

SQLIntersection: Unashamed Database Engine Coverage

On the week of April 8th, in the MGM Grand in Las Vegas, I’ll be delivering a few sessions at the SQLIntersection conference along with some pretty incredible company, including:

  • Aaron Bertrand, Sr. Consultant, SQL Sentry, Inc. [blog | twitter]
  • Andrew J. Kelly, Mentor, SolidQ [blog | twitter]
  • Bob Ward, Principal Architect Escalation Engineer, Microsoft [blog | twitter]
  • Brent Ozar, Brent Ozar Unlimited [blog | twitter]
  • Conor Cunningham, Principal Architect, SQL Server, Microsoft [blog]
  • Grant Fritchey, Product Evangelist, Red Gate Software [blog | twitter]
  • Jeremiah Peschka, Brent Ozar Unlimited [blog | twitter]
  • Kendra Little, Managing Director, Brent Ozar Unlimited [blog | twitter]
  • Kevin Farlee, SQL Program Manager, Microsoft [blog]
  • Kevin Kline, Director of Engineering Services, SQL Sentry, Inc. [blog | twitter]
  • Kimberly L. Tripp, President/Founder, SQLskills.com [blog | twitter]
  • Paul S. Randal, CEO / Owner, SQLskills.com [blog | twitter]
  • Paul White, SQL Kiwi Limited [blog | twitter
  • Steve Jones, Editor, SQLServerCentral.com [blog | twitter]

One thing that I love about the lineup is its unashamed coverage of Database Engine-centric  topics (box product and Windows Azure SQL Database).  There will be coverage of AlwaysOn Availability Groups, SQL Server I/O patterns, columnstore indexes, database design, performance monitoring & tuning, SQL Server databases on SMB file shares, database encryption, Filestream and Filetable, table partitioning, query execution plans, indexing, virtualization, parallelism, PBM and CMS, statistics, Hekaton, parameter sniffing, and T-SQL practices.  And with that block of database engine topics, there is still a little Hadoop thrown in for good measure (compliments Jeremiah Peschka).

What is also cool about this conference is the inclusion of track “hosts”.  Aaron Bertrand, Brent Ozar and Kimberly L. Tripp will help guide the attendees through the topics and sessions and help build on a cohesive and interactive experience.

This will be a great experience and I hope to see you there!

Ill Be There thumb SQLIntersection: Unashamed Database Engine Coverage

SSC Article – Windows Server 2012 Deduplication – Should you use it with SQL Server Backups?

SQLServerCentral.com just published the following article:

Windows Server 2012 Deduplication – Should you use it with SQL Server Backups?

This new Windows Server functionality is promising, but from a SQL Server DBA perspective there are some areas you should be careful of and I explore some of the considerations in this article.

Cheers!

Columnstore Segment Population Skew

My last post about nonclustered Columnstore indexes was written back on February 25th, 2012 (Exploring Columnstore Index Metadata, Segment Distribution and Elimination Behaviors).  Amazed by how quickly time passes.

Anyhow, this is a quick post on segment population skew based on parallel nonclustered Columnstore index creations.

I’ll use the same 123,695,104 row FactInternetSales table I used almost a year ago to demonstrate.  I’ll create the following nonclustered Columnstore index just on one column, to keep things simple:

CREATE NONCLUSTERED COLUMNSTORE INDEX [NCSI_FactInternetSales]
ON [dbo].[FactInternetSales]
(
[ProductKey]
);
 

The index takes 31 seconds to create on my laptop and it was created using 8 threads (which I can confirm via the SQL Server execution plan in, this case, SQL Sentry Plan Explorer):

image thumb3 Columnstore Segment Population Skew

Adding up the actual rows by thread, we get the 123,695,104 row count.

Now if we look at sys.column_store_segments, we can see that the last few segments were populated with less than the maximum 1,048,576 rows:

SELECT  [partition_id],
[column_id],
[segment_id],
[row_count]
FROM sys.column_store_segments
WHERE [row_count] = 1048576 AND
[column_id] = 2;

image thumb1 Columnstore Segment Population Skew

Now the purpose of this short post is to show what happens if we remove parallelism from the overall Columnstore index build (aside from increasing build time and reducing the memory grant):

 
DROP INDEX [NCSI_FactInternetSales]  ON [dbo].[FactInternetSales];
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX [NCSI_FactInternetSales]
ON [dbo].[FactInternetSales]
(
[ProductKey]
)WITH (DROP_EXISTING = OFF, MAXDOP = 1);
GO

Now instead of running in 31 seconds with 8 schedulers, this serial index build took (not surprisingly) 2 minutes and 10 seconds to build.

How many segments fell beneath the 1,048,576 row count?

image thumb2 Columnstore Segment Population Skew

This time, just one segment, the last one to be populated.  With 117 segments (segment_id 0 through segment_id 117) populated at 1,048,576 per segment, and 123,695,104 rows – our 118th segment has the remaining 1,011,712 rows.

Should the more tightly packed segments provide meaningful performance gains versus the parallel-built, partially filled version?  I haven’t tested this yet, but I will at some point.  Let me know if you get a chance to do so before I do.  My wild guess would be that the benefit would be minor, at best – but as with most things I would like to see for myself.

SQL Server Event Schedule for H1 2013

I’ve had a couple of months at home, focusing on consulting engagements, and now I’m about to enter back in to conference / Immersion Event season.  I’ll still be consulting, but half of the time I’ll be doing it from a hotel room.

While it is nice to interact with people via email, phone and social media – I much prefer getting a chance to see people in person. Here are the public events I’ll be speaking & teaching at the first half of this year:

Hope to see you at one (or more) of these events!