Exploring Columnstore Index Batch Sizes

In this blog post we’ll explore columnstore index batch execution mode limits.  I’m using SQL Server 2012 Developer Edition, version 11.0.3349 (SP1, CU3).

To start off with, I’ll create a table that uses six different supported nonclustered columnstore index data types:

USE [CS];
GO

CREATE TABLE dbo.[FactContrived]
(
col00 TINYINT NOT NULL DEFAULT 255,
col01 SMALLINT NOT NULL DEFAULT 32767,
col02 INT NOT NULL DEFAULT 2147483647,
col03 BIGINT NOT NULL DEFAULT 9223372036854775807,
col04 SMALLMONEY NOT NULL DEFAULT 214748.3647,
col05 MONEY NOT NULL DEFAULT 922337203685477.5807);
GO

I’ll populate this table with 1,048,576 rows, all using the same value for each row (contrived by-design):

SET NOCOUNT ON;
GO

INSERT dbo.[FactContrived]
DEFAULT VALUES;
GO

INSERT dbo.[FactContrived]
SELECT  [col00],
[col01],
[col02],
[col03],
[col04],
[col05]
FROM dbo.[FactContrived];
GO 20
 

Next I’ll create a nonclustered columnstore index on each column in the table:

CREATE NONCLUSTERED COLUMNSTORE INDEX [NCI_FactContrived]
ON dbo.[FactContrived]
([col00], [col01], [col02], [col03], [col04], [col05] )
WITH (MAXDOP = 1);
GO

Once created, I’ll check the page count statistics with the following query:

SELECT  [partition_id],
SUM([in_row_used_page_count]) AS [in_row_used_page_count],
SUM([lob_used_page_count]) AS [lob_used_page_count],
SUM([row_count]) AS [row_count]
FROM    sys.[dm_db_partition_stats]
WHERE   [object_id] = OBJECT_ID('FactContrived') AND
[index_id] = INDEXPROPERTY
(OBJECT_ID('FactContrived'),
'NCI_FactContrived',
'IndexId')
GROUP BY [partition_id];
GO

This query returns the following results:

image thumb Exploring Columnstore Index Batch Sizes

So as expected, the nonclustered columnstore index is stored within LOB pages (1,050 total).

Next I’ll check the segment metadata for the specific partition_id:

 
SELECT
[column_id],
[segment_id],
[encoding_type],
[row_count],
[primary_dictionary_id],
[secondary_dictionary_id],
[min_data_id],
[max_data_id],
[on_disk_size]
FROM    sys.[column_store_segments]
WHERE [partition_id] = 72057594039762944;

This returns the following 7 rows:

image thumb1 Exploring Columnstore Index Batch Sizes

Observations on this output:

  • We have 7 columns instead of 6 that are defined for the index.  Recall that there is no primary key or unique column for this table – and also look at column_id 7 and its associated on_disk_size and primary_dictionary_id of “-1”.
  • Our data was duplicated across each column when we populated it and notice the min_data_id and max_data_id is identical for each column as expected (except column_id 7).

Next I’ll check the dictionary metadata:

SELECT  [column_id],
[dictionary_id],
[type],
[entry_count],
[on_disk_size]
FROM    sys.column_store_dictionaries
WHERE [partition_id] = 72057594039762944;

This returns the following 6 rows (no dictionary for our column_id 7):

image thumb2 Exploring Columnstore Index Batch Sizes

Notice each dictionary just has one entry (since each column has just one unique value in the table across the 1,048,576 rows).  Also notice the very small on_disk_size is bytes – and the dictionary type of “1” which according to BOL is a “Hash dictionary containing int values.”

Now I’ll execute a query that leverages the columnstore index using batch execution mode:

SELECT    [col00],
MAX([col00]) AS [MaxCol00]
FROM [dbo].[FactContrived]
GROUP BY [col00]
OPTION (RECOMPILE);
GO

The plan (via SQL Sentry Plan Explorer) is as follows:

image thumb3 Exploring Columnstore Index Batch Sizes

The Plan Tree tab shows that batch execution mode was used for the Columnstore Index Scan and the Hash Match (Partial Aggregate) operators:

image thumb4 Exploring Columnstore Index Batch Sizes

As for thread distribution, while the plan had a degree of parallelism of 8 and and one branch with 8 reserved threads, only one thread has rows associated with it:

image thumb5 Exploring Columnstore Index Batch Sizes

Now regarding the actual number of batches, we see the following:

image thumb6 Exploring Columnstore Index Batch Sizes

So with 1,166 batches of rows across 1,048,576 rows, we’re looking at an average batch size of 899.29 rows.

Experimenting with referencing the other columns of this table, we’ll see the same number of batches each time (I’ll spare you the repeated results).

Now when columnstore indexes were first discussed a year or so ago, you would see references to batch sizes of approximately 1,000 rows.  For example, the SQL Server Columnstore Index FAQ talks about how a batch “typically represents about 1000 rows of data.”  Then a few months after SQL Server 2012 I recall seeing references to it being approximately 900 rows.  The results of this particular example show we’re closer to that 900 row value.

Now for contrast – I went ahead and dropped the nonclustered columnstore index, truncated the table, and repopulated it with random values instead of using one unique value per column.  Below is a revisiting of the metadata for the new data distributions after adding back a nonclustered columnstore index…

Partition Stats

image thumb7 Exploring Columnstore Index Batch Sizes

Notice we have more LOB pages for the random data vs. the uniform data – as expected.

Segment Metadata

image thumb8 Exploring Columnstore Index Batch Sizes

Notice the higher on_disk_size and the varying min/max data ranges per segment.  Also notice that with the random, more unique data, there only one of the columns (our tinyint column) has a primary_dictionary.

Dictionary Metadata

image thumb9 Exploring Columnstore Index Batch Sizes

And we see one dictionary entry for our tinyint column.

Revisiting our original test query, the plan shape changes somewhat (additional Parallel Gather Streams), given that we now actually have 256 rows returned based on the tinyint column:

image thumb10 Exploring Columnstore Index Batch Sizes

As for row distribution across threads, we still see one thread handling the batch-mode operations and then a spread of the row-mode rows across the other operators:

image thumb11 Exploring Columnstore Index Batch Sizes

Now as for the number of batches, we see the following:

image thumb12 Exploring Columnstore Index Batch Sizes

So 1,280 batches over 1,048,576 rows.  Averaging 819.2 rows per batch, vs. our previous test’s 899.29 rows.

There is more to explore on this subject – but that’s all for today as I’m about to jump on a plane to Chicago to help deliver IE2: Immersion Event on Performance Tuning.

Documenting Availability Group PBM Conditions

There doesn’t seem to be much documentation out on the web regarding the AlwaysOn Availability Group policies and associated conditions that come with SQL Server 2012, so I thought I would aggregate / organize some of the condition logic here for reference-sake…  Also – looking at conditions in SSMS, you’ll see that for some conditions like AlwaysOnArDataSynchronizationHealthCondition, you cannot expand the full field information in the GUI or even select the various fields in order to see the descriptions. Given this, in some cases I had to confirm the expression logic via msdb.dbo.syspolicy_conditions (looking through the Expression XML). Of course, you could just use the raw XML for documentation, but it isn’t very friendly to look at. 

So here is the reference information for AG-related conditions as of SQL Server version 11.0.3349…

AlwaysOnAgAutomaticFailoverHealthCondition   

  • References the Availability Group State facet
  • Referenced by AlwaysOnAgAutomaticFailoverHealthPolicy
  • Expression:
    • (@IsAutoFailover = True AND @NumberOfSynchronizedSecondaryReplicas > 0) OR @IsAutoFailover = False

AlwaysOnAgOnlineStateHealthCondition   

  • References the Availability Group State facet
  • Referenced by AlwaysOnAgOnlineStateHealthPolicy
  • Expression:
    • @IsOnline = True

AlwaysOnAgReplicasConnectionHealthCondition   

  • References the Availability Group State facet
  • Referenced by AlwaysOnAgReplicasConnectionHealthPolicy
  • Expression:
    • @NumberOfDisconnectedReplicas = 0

AlwaysOnAgReplicasDataSynchronizationHealthCondition   

  • References the Availability Group State facet
  • Referenced by AlwaysOnAgReplicasDataSynchronizationHealthPolicy
  • Expression:
    • @NumberOfNotSynchronizingReplicas = 0

AlwaysOnAgReplicasRoleHealthCondition   

  • References the Availability Group State facet
  • Referenced by AlwaysOnAgReplicasRoleHealthPolicy
  • Expression:
    • @NumberOfReplicasWithUnhealthyRole = 0

AlwaysOnAgSynchronousReplicasDataSynchronizationHealthCondition   

  • References the Availability Group State facet
  • Referenced by AlwaysOnAgSynchronousReplicasDataSynchronizationHealthPolicy
  • Expression:
    • @NumberOfNotSynchronizedReplicas = 0

AlwaysOnAgWSFClusterHealthCondition   

  • References the Server facet
  • Referenced by AlwaysOnAgWSFClusterHealthPolicy
  • Expression:
    • @ClusterQuorumState = NormalQuorum

AlwaysOnArConnectionHealthCondition   

  • References the Availability Replica facet
  • Referenced by AlwaysOnArConnectionHealthPolicy
  • Expression:
    • @ConnectionState = Connected

AlwaysOnArDataSynchronizationHealthCondition   

  • References the Availability Replica facet
  • Referenced by AlwaysOnArDataSynchronizationHealthPolicy
  • Expression:
    • (@AvailabilityMode = AsynchronousCommit AND (@RollupSynchronizationState = Synchronizing OR @RollupSynchronizationState = Synchronized) OR @RollupSynchronizationState = Synchronized)

AlwaysOnArJoinStateHealthCondition   

  • References the Availability Replica facet
  • Referenced by AlwaysOnArJoinStateHealthPolicy
  • Expression:
    • @JoinState != NotJoined

AlwaysOnArRoleHealthCondition  

  • References the Availability Replica facet
  • Referenced by AlwaysOnArRoleHealthPolicy
  • Expression:
    • @Role = Primary OR @Role = Secondary

AlwaysOnDbrDataSynchronizationCondition   

  • References the Database Replica State facet
  • Referenced by AlwaysOnDbrDataSynchronizationState (notice the change in naming convention)
  • Expression:
    • (@ReplicaAvailabilityMode = AsynchronousCommit AND @SynchronizationState != NotSynchronizing) OR @SynchronizationState = Synchronized

AlwaysOnDbrJoinStateCondition  

  • References the Database Replica State facet
  • Referenced by AlwaysOnDbrJoinStatePolicy
  • Expression:
    • @IsJoined = True

AlwaysOnDbrSuspendStateCondition   

  • References the Database Replica State facet
  • Referenced by AlwaysOnDbrSuspendStatePolicy
  • Expression:
    • @IsSuspended = False

IsHadrEnabled   

  • References the Server facet
  • Referenced by all of the AlwaysOnAg / AlwaysOnAr and AlwaysOnDbr policies
  • Expression:
    • @IsHadrEnabled = True

I bothered with documenting this in order to better understand some of the availability group health model logic at the availability group, availability replica and database replica scope.  Microsoft did talk a little about the “AlwaysOn Health Model” in the following post as well, so I recommend reviewing this if you plan on deploying availability groups in your environment:

The AlwaysOn Health Model Part 1 — Health Model Architecture

If you want to extend the model further (or override policies), Microsoft’s Will Snavely also blogged about it here:

The AlwaysOn Health Model Part 2 — Extending the Health Model

SQLPerformance.com “The Case of the Cardinality Estimate Red Herring”

My fourth guest blog post was published today on SQLPerformance.com:

The Case of the Cardinality Estimate Red Herring

This was inspired by a case that Erin Stellato and I worked together on a few months ago. While I’ve talked quite a bit this year about the importance of investigating bad cardinality estimates, this blog post points to a scenario where the estimate issue isn’t always the full query performance troubleshooting story.

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