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

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!