Tag Archives: Part
Server-Side Synchronization Series Part 2: How Server-Side Sync reaches out to Exchange
This second part of the Server-Side Sync blog series will be going over how we reach out to Exchange and what properties are used to determine how far we go back in our search for Exchange items. This part will just be going over Incoming Email, and we will cover ACT in more detail later in the series.
How does Dynamics schedule the interval timings to reach out to Exchange? Do all users reach out at the same time?
Let’s focus on a single user that has been properly configured and test/enabled:
When test/enable is run on the mailbox for all three Sync methods (Incoming, Outgoing, and ACT), Dynamics 365 reaches out to Exchange through EWS requests sent from servers running the Asynchronous Processing Service. For online, this means that certain server clusters take turns sending requests to the Exchange environment specified in the Email Server Profile (in the above setup, this is Exchange Online). We send several different SOAP requests to Exchange, such as finditem, getitem, senditem, and syncfolderhierarchy. We send a test email to the user’s own mailbox as part of the steps when performing the test. A Full list of EWS operations can be found here:
https://docs.microsoft.com/en-us/exchange/client-developer/web-service-reference/getitem-operation
After test/enable completes these operations, the “Configuration Test Results” screen above will change to “Success” and a bunch of values are modified on the associated MailboxBase table for the user. Please note the button labeled “Download Mailbox Details” in the header of the mailbox below:
This button makes a call to the MailboxBase and returns a ton of useful column data that will tell you the next time the user Mailbox record will reach out to Exchange:
A full list of each value and how to read them to determine when Server-Side Sync will fire next can be found on my blog post here:
How to read Download Mailbox Details
It’s important to note that the “postponemailboxprocessinguntil” value will have 5 minutes added to the exact time the mailbox completes processing, so will always have at least 5 minutes between each Exchange check (time that it takes sync to complete + 5 minutes).
So were reaching out to Exchange on a regular schedule now, but what does this actually do?
The value we want to focus on for this part is the Dynamics MailboxBase field “ProcessEmailReceivedAfter”. This value is not on the default mailbox form, but can be added or queried through the mailbox entity API. This timedate value is where we store the received time of the last email we successfully promoted from Exchange.
It’s important to understand this field to understand what we ask Exchange for when we reach out every 5 minutes. Here is the back and forth conversation we have with Exchange each time we reach out for incoming email:
Since we found a few emails to promote to Dynamics automatically, we will update the “ProcessEmailReceivedAfter” time to the exact received time of the most recent email we promoted. The next time we reach out to Exchange, we will only query for emails since that time, since we know nothing new has entered the Inbox before that value.
But wait, if we only check for emails back to a certain time, what if we move items from a subfolder to the inbox? Wouldn’t they be missed since their received times are before what we have set?
That is correct. If you move emails from a subfolder and we have promoted an item that arrived after the received time of the email you’re moving, we will never evaluate this moved item on subsequent cycles. This is where this field comes in; you can manually modify this time for the specific user context, causing our next sync cycle to go back farther in time to evaluate emails. This would allow the sync session to find the email moved from the subfolder in this scenario.
Wont we also reprocess all the other emails still in the Inbox that we already evaluated? Wont this make duplicates?
We would also evaluate all of the other emails with received times after this timedate value, but duplicate emails cannot occur due to the “MessageID” field on the emails in Exchange. Each email has a unique Message ID, which we pass as part of the email creation within Dynamics.
On the Dynamics SQL side, “MessageID” has a duplicate key constraint, meaning that the column can only have unique values (aside from one specific exception, which we will discuss in a later blog series). This prevents duplicate emails from being created in Dynamics even if they are reprocessed.
The next part of our blog series will go over correlation methods, which is how the emails in the above scenario were automatically promoted based on how the user is configured.
Server-Side Synchronization Series Part 3: Correlation and Automatic Promotion
This third part of the Server-Side Sync blog series will focus on what correlation is and how to view Exchange item properties.
What is correlation?
For Dynamics 365, correlation is a set of checks to determine if an email matches any condition that is configured by an end user to automatically promote an Exchange item into Dynamics.
Remember that “promotion” in this context doesn’t mean a change in rank, it means the attempted creation of an activity record within Dynamics (such as an Emails or Appointments) using data within an Exchange item.
How is Correlation configured?
When setting up Server-Side Sync for the first time, InReplyTo correlation is configured by default for user mailboxes. There are two areas within Dynamics 365 where this is controlled, the organization-level System Settings and the user-level personalization options.
System Settings-> Email:
The “Use correlation to track email conversations” checkbox allows Server-Side Sync to evaluate emails to determine if they match any correlation method setup within the organization. You’ll notice some others available for selection here, such as Tracking Token and Smart Matching, which we will discuss later in this series.
User-Level Personalization Settings -> Email
The value for each user is by default set to “Email messages in response to Dynamics 365 email” upon user record creation. This value means that it will use InReplyTo correlation when determining whether to not to automatically create an Email record in Dynamics using values from the email sitting within the Exchange Inbox.
How are emails stored in Dynamics? What fields does Server-Side Sync evaluate?
Recall in the previous entry in our series that Emails within Dynamics have a “MessageID” field that contains a unique, duplicate constrained value. Activity records such as emails are stored on the ActivityPointerBase in SQL, where the MessageID exists as one of the columns for each record. This Message ID conforms to the format “<x@y.z>”, including the greater than and less than symbols around the data. A real example of a Message ID would look like this:
<BN6PR21MB062536CDFCD13267532F0EB1EE2E0@BN6PR21MB0625.namprd21.prod.outlook.com>
Now that we understand that these unique Message ID values are stored in SQL for each activity item, we can then examine how Exchange handles forwards and replies. For every Exchange item, behind-the-scenes values can be examined that give more data on the individual properties that define what the item is. These values can be basic data such as subject, body, recipients, and received time, but also contain more esoteric properties such as messageid, references, thread-index and content-type.
These properties can be accessed multiple different ways, but the most common is to view the message headers of the item in Outlook/OWA or to use an application such as EWS editor to be able to examine the properties of items in a mailbox directly. Here is an example of a section of message headers for an email I sent through Dynamics:
From: Cody Dinwiddie <codin@OBFUSCATED.onmicrosoft.com>
To: Cody Dinwiddie <codin@microsoft.com>
Subject: [EXTERNAL] Test Email
Thread-Topic: Test Email
Thread-Index: AQscuKgXz0bUld+ni9oW2WvWgJcWog==
Date: Tue, 1 Sep 2020 19:35:55 +0000
Message-ID:
<142408F2977449AE9A3EB234870D156A1D6809716A2B@CODIN.OBFUSCATED.ONMICROSOFT.COM>
Accept-Language: en-US
Since this message was sent through Dynamics, I know that there is an ActivityPointerBase record in Dynamics SQL that contains all of the values the email uses including the MessageID above. In fact, I can do an advanced find to find the email based on the Message ID:
Now the we understand how Dynamics stores emails and that Exchange items have properties that can be examined, we will explore In-Reply-To Correlation in the next entry in our series.
Server-Side Synchronization Series Part 4: In-Reply-To Correlation and Forwarding Emails
This part of our blog series will cover the most common correlation method used for automatic promotion for user mailboxes: In-Reply-To correlation.
From our previous entry, we have an email sent from Dynamics that has the following Message-ID:
<142408F2977449AE9A3EB234870D156A1D6809716A2B@CODIN.OBFUSCATED.ONMICROSOFT.COM>
Exchange handles replies and forwards a bit differently than sending brand new emails. Let’s reply to this email from my inbox and see what the reply’s message headers look like:
From: Cody Dinwiddie <codin@microsoft.com>
To: Cody Dinwiddie <codin@OBFUSCATED.onmicrosoft.com>
Subject: Re: Test Email
Thread-Topic: Test Email
Thread-Index: AQscuKgXz0bUld+ni9oW2WvWgJcWopbHxZqC
Date: Tue, 1 Sep 2020 20:07:11 +0000
Message-ID:
<CH2PR21MB1463D383FECE14183B853DF7AE2E0@CH2PR21MB1463.namprd21.prod.outlook.com>
References:
<142408F2977449AE9A3EB234870D156A1D6809716A2B@CODIN.OBFUSCATED.ONMICROSOFT.COM>
In-Reply-To:
<142408F2977449AE9A3EB234870D156A1D6809716A2B@CODIN.OBFUSCATED.ONMICROSOFT.COM>
Accept-Language: en-US
This message has some additional fields than what we saw on a brand new email: “References” and “In-Reply-To”. Notice how the MessageID is unique, but the In-Reply-To value is identical to the message id of our original email (the one we replied to). When Server-Side Sync reaches out to Exchange and pulls all of the emails since they last spoke, the following happens (simplified overview):
- Evaluate each email one by one to determine if any values meet promotion criteria
- If an email matches in-reply-to to an existing messageid, evaluate the message to ensure other fields are valid
- Are the email addresses for the sender and the recipients in a valid format? (x@y.z)
- If not, reject as invalid email format
- Does one of the email addresses in the recipient list match a system user or queue?
- If not, reject as NoRecipientMatch
- Is the sender of the email tied to a system user or queue?
- If so, check the setting “Track Emails Sent between Dynamics 365 users as two activities”
- If unchecked, reject the message with InternalEmailReject
- (this is tied to “Track Emails Sent between Dynamics 365 users as two activities”, which we will go over later in this series)
- If so, check the setting “Track Emails Sent between Dynamics 365 users as two activities”
- Are the email addresses for the sender and the recipients in a valid format? (x@y.z)
- If the check conditions all pass, we promote the item to Dynamics and attempt the creation of an Email record.
Since we have a match, We will create the record in Dynamics:
Remember when I said that Exchange also applies “In-Reply-To” values to forwards as well? Let’s take a look at the headers of a forward of the same forwarded mail:
From: Cody Dinwiddie <codin@microsoft.com>
To: John Doe <jodoe@OBFUSCATED.onmicrosoft.com>
Subject: Fw: Test Email
Thread-Topic: Test Email
Thread-Index: AQscuKgXz0bUld+ni9oW2WvWgJcWopbHyrhB
Date: Tue, 1 Sep 2020 20:25:32 +0000
Message-ID:
<CH2PR21MB1463E1A0575C6B54502AADC3AE2E0@CH2PR21MB1463.namprd21.prod.outlook.com>
References:
<142408F2977449AE9A3EB234870D156A1D6809716A2B@CODIN.OBFUSCATED.ONMICROSOFT.COM>
In-Reply-To:
<142408F2977449AE9A3EB234870D156A1D6809716A2B@CODIN.OBFUSCATED.ONMICROSOFT.COM>
Accept-Language: en-US
All users are setup to promote in-reply-to matches by default. In the above scenario, if John Doe is a Dynamics user setup to have incoming email evaluated through Server-Side Sync, John will see the above email and automatically promote it to Dynamics, thinking it is a direct reply. This scenario is not very common, but can still occur, so it’s really important to understand the properties we evaluate for automatic promotion into Dynamics.
Due to this behavior, I worked with my colleague Ben Merrill to develop a plug-in that evaluates inrpelyto correlated mail and prevents forwards from promoting to the instance if the sender of the original forwarded mail is not a recipient on the forward. You can find this code here:
IncomingInReplyToEmailQuarantine
Be aware that this sample code is provided for the purpose of illustration only and is not intended to be used in a production environment. Please review the coded for details on its use and trigger mechanisms.
In our next series, we will dive into Tracking Token and Smart Matching correlation. See you soon!
Monitoring the Power Platform: Model Driven Apps – Monitor Tool Part 1: Messages and Scenarios
Summary
Monitoring Dynamics 365 or Model Driven Applications is not a new concept. Understanding where services are failing, users are running into errors, where form and business processes could be tuned for performance are key drivers for most if not all businesses, from small companies to enterprises. Luckily, the Dynamics 365 platform provides many tools to help audit and monitor business and operational events.
This article will cover user events and where they are sourced from. From there, we will dive into the Monitor tool and look at individual messages within. We will work with a few sample scenarios and see what we can gain from markers and messages within the Monitor tool.
Collecting User Events
Before we discuss techniques on how to capture events in Dynamics 365 let’s examine some meaningful events. From the client perspective this may include performance counters and metrics, user click events and navigation. Other data points include geolocations and preferences of users. Luckily, client events are easier to capture and we have many tools including browser based (Developer Tools) to applications (Fiddler) that are readily available. Some features of the platform allow for collecting markers while other events of interest we will have to supplement with custom delivery mechanisms.
For server side, external integrations and execution context contain identifiers for response codes that may require additional validation. For sandboxed plug-ins and custom workflow activities, we are limited somewhat to what tools we can leverage.
Upcoming articles will detail how to collect and push events of interest to a central area for analytics.
NOTE: The rest of this article will cover collecting and analyzing messages focused on the client. That said, server side events play a major role and can impact the client experience. I’ll address server side events in another article pertaining to Azure Application Insights and Model Driven Apps. In the meantime, .
AUTHOR NOTE: Click each image to enlarge for detail
The Monitor Tool
The Monitor tool can be launched from the Power Apps Maker Portal. Once launched, the Play Model Driven App button can be pressed to begin a session attached to the tool.
The Monitor tool can also be started by adding “&monitor=true” to the URL of your Model Driven Application.
After consenting or allowing to start a session, the Monitor tool will light up rapidly with various messages. Similar to the , each row can be further drilled into for investigation.
article on the Monitor Tool, titled provides a through deep dive including sample scenarios.
I highly suggest reviewing and keeping it close by for reference.
Key Performance Indicators
Key Performance Indicators represent major lifecycle events within a particular user action, such as loading a form. Consider the image below.
By sorting the records on the “KPI” category, these events begin to emerge. The image below shows the major lifecycle events or KPIs for a standard form load within Dynamics 365. Beginning with PageNavigationStart and ending with RenderedEditReady, these events represent the completion of a form load.
Scenario: Determining a Form’s Load Impact
Consider the scenario of a user logging into the system and opening a lead form for the first time. When performing this action, the form and data have not had a chance to be cached or stored locally which results in all items needed to be downloaded. This is sometimes referenced as a cold load. Reviewing the timeline event “FullLoad” we can determine what type of load the form rendered as.
Now, once captured, the user opens the fly out window to choose another lead record but using the same form. Again using the “FullLoad” KPI timeline event we can see the LoadType is now Two.
Finally, imagine the user needs to navigate back to the original lead record opened on the same form. We can see now the LoadType is now Three. Comparing this to the LoadType Zero image above, the entityId is the same.
Here is a sample scenario in full showing the differences in loading new and existing records and how changing a form can impact network requests to the server.
Attribution
On certain Key Performance Indicators a property is included called “Attribution” which represents specific events within a user action. This includes the commands or actions executed within a KPI. For example, during a form load lifecycle, controls are rendered, ribbon rule are evaluated, onload event handlers are executed, etc. The Attribution property will specify and group, in chronological order, which events happened. An added bonus is the duration is also shown for each event along with if any synchronous calls were made. Consider the image below.
Scenario: Locating dynamically added form changes and events
The image above shows for the FullLoad KPI. In this image we see three main groups of events: CustomControl (form controls), RuleEvaluation (ribbon rules) and onload (form event handlers). What’s interesting here is each of these is grouped, but also the solution they are part of and event solution layering is present. The RuleEvaluation and onload groups above both show an unmanaged or “Active” layer that contained customizations.
Compare that image with the one below.
A scenario came up during an investigation into the an increased duration of a form save. To begin, we went through the user events as normal with the Monitor tool running.
Upon review, you can see additional events occurred, tabstatechanged and onsave. The onsave was expected due to the registered event handler on the form. However the tabstatechange was not, this was found to be due to a recent code addition that triggered the setDisplayState of the tab.
if (control.getName() == tabName) {
control.setDisplayState("expanded");
}
By reviewing the Attribution property we were able to identify what caused the increase of 5 seconds.
Zone Activity and Other Data Points
Within Key Performance Indicators, are other data points that prove useful when debugging performance related issues. Latency and Throughput are shown as well as timings for network related calls and custom script executions. Within the ZoneActivity property we see events grouped by web service calls, and events. The CustomScriptTime shows the duration of all of the registered event handlers that fired during this particular Key Performance Indicator.
Performance Messages
Performance categorized messages detail potential performance issues that a user may run into. At the time of this writing, I’ve uncovered only synchronous XHR calls being sent but I anticipate growth here.
Scenario: Locating and Evaluating Synchronous Resource Timings
Requests from a Model Driven Application represent outgoing calls from the client to another source. These calls can occur either synchronously meaning the thread executing the call waits for the response, or asynchronously meaning the thread continues and listens for the response. Its preferred to eliminate all sync calls to reduce any potential disruption to a user’s experience within the application.
The Monitor tool helps by identifying these requests and calling them out. These call outs can be found in the “Performance” category as shown below.
Examining the performance entry, we can see the “dataSource” property shows the XHR URL. However, it doesn’t show the source of the call which is needed to better understand how and why the request was made.
Here is a gif showing how to use the Monitor tool, coupled with the Browser Developer Tools to locate the line of code that needs to be updated.
Using these messages, along with outputs from Power Apps Checker, we can begin to uncover gaps in code analysis. , I’ll cover in depth an approach to help identify and remediate these gaps.
Next Steps
This article describes where user and platform events may originate from and how they can be monitored. Gaining insights and understanding into the SaaS version of Dynamics 365 allows us to uncover the black box and find answers to some of our questions. Think about how the Monitor tool can be used to find out where API calls may have started and coupled with other articles in this series, how we can correlate to provide a true end to end monitoring solution. will cover how we can extract and analyze the sessions further.
If you are interested in learning more about , which includes a , please contact your Technical Account Manager or Microsoft representative for further details.
Your feedback is extremely valuable so please leave a comment below and I’ll be happy to help where I can! Also, if you find any inconsistencies, omissions or have suggestions, .
Index
Monitoring the Power Platform: Model Driven Apps – Monitor Tool Part 2: Session Consumption and Analytics
Summary
Monitoring Dynamics 365 or Model Driven Applications is not a new concept. Understanding where services are failing, users are running into errors, where form and business processes could be tuned for performance are key drivers for most if not all businesses, from small companies to enterprises. Luckily, the Dynamics 365 platform provides many tools to help audit and monitor business and operational events.
This article will cover collecting, querying and analyzing user interface events, specifically from the . Azure DevOps with Monitor output. We’ll look at consuming and storing outputs for visualizations and analytics with Kusto queries. Finally, samples will be provided to parse and load session messages into Azure Application Insights and Azure Log Analytics.
The Monitor Tool
The Monitor Tool allows users and team members to collect messages and work together in debugging sessions. To begin, the Monitor Tool can be launched from the Power Apps Maker Portal. Once launched, the Play Model Driven App button can be pressed to begin a session attached to the tool.
AUTHOR’S NOTE: Click on each image to enlarge for more detail
The Monitor Tool can also be started by adding “&monitor=true” to the URL of your Model Driven Application.
After consenting or allowing to start a session, the Monitor Tool will light up rapidly with various messages. Similar to , each row can be further drilled into for investigation.
article on the Monitor Tool, titled provides a through deep dive including sample scenarios. I highly suggest reviewing and keeping close by for reference.
Thoughts on Canvas Apps
The Monitor tool works with Power Apps Canvas Driven Apps as shown in While this article is focused on Model Driven Apps, remember these techniques can also be utilized to serve Canvas Apps as well.
Consuming Monitor Sessions
Each time the Monitor tool is opened, a new session is created. Within each session are events that describe actions taken within the session as well as other helpful messages. Storing these sessions allow support teams to better understand errors and issues that arise during testing and production workloads. The previous article, covers scenarios that support users can use to better understand the data points delivered in the Monitor Tool.
The Monitor tool can also help analysts who want to learn more about the platform. For instance, user tendencies such as how long they spent on a page and which controls they interacted with in Canvas Driven Apps. For testing, the tool can help with non functional test strategies like A/B testing. Analyzing performance messages can point to potential code coverage gaps or advise on user experience impact. Network calls can be securitized to determine if queries can be optimized or web resources minified. The Monitor tool, in my opinion, really can open up a new view on how the platform is consumed and how users react with it.
Attaching to Azure DevOps Work Items
The Monitor Tool download artifacts work nicely with Azure DevOps Work Items. They can be attached to Bugs, Tasks and even Test Cases when performing exploratory or other types of tests.
Working with test cases within Azure DevOps, Analysts craft work items with use cases and expected outcomes to deliver to Makers and Developers. Specifically with Test Cases, Quality Analysts can leverage the Monitor Tool in conjunction with the . This allows for robust test cases complete with steps, screenshots, client information and the Monitor Tool output attached. Consider the gif below showing an example of using both the browser extension and the Monitor tool output.
In that example, we see an analyst has found a performance issue with a Dynamics 365 form. The analyst logged a new bug, included annotations and screenshots and the Monitor tool output. A developer can be assigned, pick this up and begin working on the bug. By having the Monitor tool output the developer can now see each call made and review the Attributions within the respective KPI. For more information, refer to the Attribution section within .
Storing Monitoring Sessions
The Monitor tool output comes in two flavors: CSV and JSON. Both make for light weight storage and are fairly easy to parse as shown later. These files can be attached to emails or stored in a shared location like a network drive.
Power BI and CSV Files
The csv files downloaded from the Monitor tool can be added Azure Blob Storage or stored locally and displayed in a Power BI Dashboard. This allows for analysts and support teams to drill down into sessions to gain further insights. The csv files can work both locally with Power BI Desktop and online. The below image shows a sample taken from a Canvas App Monitor Session. Additional information and samples can be found in
Experimenting with Extraction for Analytics
Storing outputs in Azure Blob Storage
During this writing and the writing of the , I began to collect outputs from both and storing within Azure Blob Storage containers. There are multiple reasons why I chose to utilize Azure Blob Storage, mainly cost but also interoperability with other services such as Power BI, Azure Data Lake and Azure Event Grid.
Azure Blob Storage also integrates very well with both , and . Each of these include a triggering mechanism on a new blob added to a container, working as a sort of drop folder. This may the choice to use Azure Blob Storage easy for me but I will also point out that specifically Power Automate Flows also can be triggered from or . This allows Makers to stay within the Microsoft 365 ecosphere and avoid spinning up multiple Azure services if desired.
Extracting to Log Stores
Extracting the messages with in the Monitor tool to a log store allows for analysts and support teams to parse and query the sessions. Determining how we want to store these messages will determine what services we leverage.
Choosing Azure Application Insights
If we want I’d suggest Azure Application Insights. Application Insights will allow for pushing messages to specialized tables that feed dashboards and features native to the service such as End to End Transactions and Exception parsing.
Network messages can be stored in the requests or dependencies tables, which are designed, along with page views, to visual a typical web application’s interactions. Fetch network messages, representing calls to an API, fit nicely into the requests table as shown below:
Dependencies on the other hand can represent dependent web resources.
Using Azure Function to serve data
Azure Application Insights works well with microservices built using Azure Functions. A benefit of Azure Functions is the ability to . For more information and a helpful . Below is the method signature of a sample Azure Function:
[FunctionName("BlobTriggeredMonitorToolToApplicationInsights")] public void Run([BlobTrigger("powerapps-monitortool-outputs/{name}", Connection = "")]Stream myBlob, string name, ILogger log)
you’ll see that the function takes the JSON payload, parses it and determines how to add to Azure Application Insights. Depending on the messageCategory property, it will funnel messages to custom events, requests or dependencies.
As always, review the Telemetry Client for ideas and techniques to enrich messages sent to Azure Application Insights. Also, if desired, review .
Choosing Azure Log Analytics
Azure Log Analytics allow for custom tables that provide the greatest flexibility. The Data Collector API has a native connector to Power Automate that allows Makers to quickly deliver Monitor messages with a no or low code solution. Both Power Automate and Azure Logic Apps both offer triggers on create of a blob providing flexibility on which service to choose.
Using Power Automate to serve data
To work with Power Automate, begin by creating a new Power Automate flow. Set the trigger type to use the Azure Blob Storage trigger action ““. If a connection hasn’t been established, create a connection. Once created locate the blob container to monitor. This container will be our drop folder.
The trigger is only designed to tell us a new blob is available, so the next step is to get the blob content. Using the FileLocator property we can now get the serialized messages and session information and deliver to Log Analytics.
Within Power Automate, search for the “” action. Set the JSON Request body field to be the File Content value from the “” action.
The advantage here is with three actions and no code written I am able to listen to a drop folder for output files and send to Azure Log Analytics. The native JSON serialization option from the Monitor tool really serves us well here, allowing a seamless insertion into our custom table.
Ideally we would expand the Power Automate flow to parse the JSON and iterate through messages to allow for individual entries into the table.
Just remember the content maybe encoded to “octet-stream” and will need to be converted.
Sample Kusto Queries
Below are sample Kusto queries designed for Azure Application Insights.
General Messages
//Review Performance Messages customEvents | extend cd=parse_json(customDimensions) | where cd.messageCategory == "Performance" | project session_Id, name, cd.dataSource
Browser Requests
//Request Method, ResultCode, Duration and Sync requests | extend cd=parse_json(customDimensions) | extend data=parse_json(tostring(cd.data)) | project session_Id, name, data.method, resultCode, data.name, data.duration, data.sync, cd.fileName
//Request Method, ResultCode, Duration and Resource Timings requests | extend cd=parse_json(customDimensions) | extend data=parse_json(tostring(cd.data)) | project session_Id, name, data.method, resultCode, data.name, data.duration, data.startTime, data.fetchStart, data.domainLookupStart, data.connectStart, data.requestStart, data.responseStart, data.responseEnd
Review the to better understand what these markers are derived from.
Key Performance Indicators
pageViews | extend cd=parse_json(customDimensions) | extend cm=parse_json(customMeasurements) | extend data=parse_json(tostring(cd.data)) | extend attribution=parse_json(tostring(data.Attribution)) | where name=="FullLoad" | order by tostring(data.FirstInteractionTime), toint(cm.duration) | project session_Id, name, data.FirstInteractionTime,cm.duration, attribution
Sample Code
Next Steps
In this article we have covered how to work with the Monitor tool output files. Viewing within Power BI Dashboards, attaching to DevOps work items and storing in Azure backed log stores are all possibilities. Included and Kusto queries to help you get started have also been provided.
This article showcases use cases and strategies for working with the Monitor tool but really only represents the tip of the iceberg. Continue collecting, examining and churning the output for deep insight into user and platform trends.
If you are interested in learning more about , which includes a , please contact your Technical Account Manager or Microsoft representative for further details.
Your feedback is extremely valuable so please leave a comment below and I’ll be happy to help where I can! Also, if you find any inconsistencies, omissions or have suggestions, .
Index
Replacing an expired certificate in Dynamics 365 CE environment with AD FS – part 1
Part 1: Requesting, creating and importing a new certificate
Part 2: Configuring the new certificate in Dynamics and AD FS
CONTEXTUALIZATION AND SCENARIO:
In this article we will cover the standard procedures for replacing an expired certificate in a Dynamics 365 CE environment with federation through Active Directory Federation Services.
It is common (and expected) that in a scenario similar to this, the environment stops responding due to the fact that the certificate is expired, a fundamental security criterion of the federation.
For the purposes of scenario, I used a single pre-existing virtual machine in Hyper-V running Windows Server 2012 R2 Datacenter Edition with SQL Server 2014 SP2 Enterprise Edition and Dynamics 365 v8.2 as FULL SERVER. As it is a laboratory for educational purposes and with only one virtual machine, it obviously performs all services, including domain controller, DNS, certificate and federation.
Dynamics and AD FS-specific services run under the context of a service user, here called SVC_DYN. Therefore, we assumed that the environment was completely configured and functional until the certificate expiration date. We will not address the creation of infrastructure from scratch, but the procedures for replacing the certificate in order to restore the operation of the services in a pre-existing environment. It therefore requires that the reader already have prior knowledge of configuring AD FS, certificates and Dynamics itself.
As both Dynamics and AD FS are running on the same server, we specify the Dynamics HTTPS port at 444 insted and leave the AD FS port at 443 as the default.
In the federation of our lab, we have the internal relying-party as “internalcrm” and external as the name of the CRM organization itself in addition to “dev”. Therefore, the expected access URLs are (remember, they won’t work outside this lab):
Internal:
https://internalcrm.crmadmin.com.br:444
External:
https://microsoft-dynamics.crmadmin.com.br:444/
https://dev.crmadmin.com.br:444
Our organization is called Microsoft-Dynamics. The internal access will support SSO, while the external will ask for credentials. We will issue a new certificate from a self-authority but in real scenarios you may acquire a valid one from a trusted Certificate Authority.
Ok, that’s said, let’s start the lab.
CHECKING THE ISSUES
Figure 1: user unable to access Dynamics from internal or external URLs
In our scenario it happens (fig 1) due the certificate expired. The same occurs in both internal or external URLs. We can see that Dynamics is working well if we use the localhost:5555 URL (fig 2). It means, when we use localhost:[port] we are using without HTTPS, so the platform responds but only internally and without the proper security:
Figure 2: Dynamics working well using localhost:[port]
Let’s take a look at the certificate in some places. First, opening the IIS, pointing to the CRM Website and clicking in Bindings we can see that the certificate is really expired (fig 3):
Figure 3: IIS Dynamics Website with expired certificate bound
As we’re using a Wildcard certificate, the same certificate is applied to AD FS, so we can check also there (fig 4 and 5):
Figures 4 and 5: AD FS with expired certificate bound
CREATING A NEW CERTIFICATE
As the first step, open Manage Computer Certificates, or just CTRL+R and certlm.msc and then Go to Certificates -> Local Computer -> Personal -> Certificates -> All Tasks -> Advanced Operations -> Create Custom Request… (fig 6):
Figure 6: Certificates
In the Certificate Enrollment screen -> Before You Begin, click Next. In the next screen, in Select Certificate Enrollment Policy, select Proceed without enrollment policy under Custom Request (fig 7):
Figure 7: Custom Request -> Proceed without enrollment policy
In the next screen, in Template, select (no template) Legacy key and leave Request format as PKCS #10 (fig 8). CAUTION: the CNG key is incompatible with Dynamics CRM when creating custom certificate requests and should not be used.
Figure 8: Custom Request -> legacy key and request format
In Certificate Information, expand Details then click Properties (fig 9):
Figure 9: Custom Request -> Details -> Properties
In Certificate Properties -> General tab -> provide values for Friendly name and Description (fig 10):
Figure 10: Certificate Properties -> General tab
In Subject tab, select the attributes to customize in the certificate, enter the value of the attribute and click Add (fig 11). In this example, we are generating a wildcard certificate, so make sure that in Subject name you have a CN line (Common Name) with the proper value in the format like *.domain.com: this will be the the wildcard value:
Figure 11: Certificate Properties -> Subject tab -> custom attributes
In Extensions tab, expand Key usage and add Digital signature and Key encipherment and then click Apply (fig 12):
Figure 12: Certificate Properties -> Extensions tab
In Private Key tab, expand Key Options and select the value 2048 in Key size and check Mark private key exportable. Expand Key type and select Exchange (fig 13):
Figure 13: Certificate Properties -> Private Key tab
Click Apply and OK. In the next screen of Certificate Information click Next (fig 14):
Figure 14: Certificate Enrollment -> Certificate Information
Provide a location and name to save the certificate request file. In File Format, select Base 64 and then click Finish (fig 15):
Figure 15: Certificate Enrollment -> Save certificate request
Once the certificate request file is saved, open it in Notepad, press CTRL+A to select all and copy the content to clipboard (fig 16):
Figure 16: Copying certificate request file content
As I told in the beginning, our environment has everything we need to proceed with certificate request and creation. So let’s access the Active Directory Certificate Services via web browser, typing in the address bar http://localhost/certsrv and then clicking in Request a certificate (fig 17):
Figure 17: Requesting a certificate
In the next screen, click in advanced certificate request (fig 18):
Figure 18: Advanced certificate request
In the next screen, click in Submit a certificate request by using a base-64-encoded CMC or PKCS #10 file (fig 19):
Figure 19: Submitting a certificate request…
In Saved Request, paste the content you’ve copied from Notepad in the figure 16. In Certificate Template, select Web Server then click Submit (fig 20):
Figure 20: Submit a certificate request
After submitted, select Base 64 encoded and click Download certificate (fig 21):
Figure 21: Downloading the certificate file
Provide a name and location to save the certificate file (fig 22):
Figure 22: Saving the certificate file
Good! You’ve created the new certificate file self-issued and valid for 2 years.
IMPORTING THE NEW CERTIFICATE
Open Manage Computer Certificates or just CTRL+R and type certlm.msc like you did before. Before import the new certificate you may see the old certificate in place yet. In order to avoid confusion delete it, as it is expired and unable to use anymore (fig 23):
Figure 23: Saving the certificate file
Go to Certificate Manager -> Certificates (Local Computer) -> Personal -> Certificates -> All Tasks -> Import… (fig 24):
Figure 24: Importing new certificate
You will see a Welcome screen with store location set as Local Machine and unable to change. Click Next. In the next screen, browse and select the certificate file you’ve downloaded and click Next (fig 25):
Figure 25: Selecting the certificate file
In the Certificate Store screen, select Place all certificates in the following store and select Personal, then click Next (fig 26). In the next screen, click Finish:
Figure 26: Selecting the store
Now you’ll be able to see the new certificate imported (fig 27):
Figure 27: Certificate imported successfully
In the Part 2 we will cover the procedures to configure the new certificate to work properly in Dynamics and AD FS.
Walter Carlin
Senior Customer Engineer – Dynamics 365 Customer Engagement – Microsoft Corp.
Hands-On with Columnstore Indexes: Part 3 Maintenance and Additional Options
The series so far:
- Hands-On with Columnstore Indexes: Part 1 Architecture
- Hands-On with Columnstore Indexes: Part 2 Best Practices and Guidelines
- Hands-On with Columnstore Indexes: Part 3 Maintenance and Additional Options
Like other indexes, columnstore indexes require maintenance as they can become fragmented with time. A well-architected columnstore index structure that is loaded via best practices will tend not to get fragmented quickly, especially if updates and deletes are avoided.
A discussion of columnstore index structure and maintenance would not be complete without a look into nonclustered columnstore indexes, which allow the columnstore structure to be built on top of an OLTP table with a standard clustered index. Similarly, it is possible to create memory-optimized columnstore indexes. Therefore, it is worth exploring how combining these technologies affects performance and data storage.
Columnstore Index Maintenance
Like with standard B-tree indexes, a columnstore index may be the target of a rebuild or reorganize operation. The similarities end here, as the function of each is significantly different and worth considering carefully prior to using either.
There are two challenges addressed by columnstore index maintenance:
- Residual open rowgroups or open deltastores after write operations complete.
- An abundance of undersized rowgroups that accumulate over time
Open rowgroups and deltastores are the results of write operations: insert, update, and delete. A columnstore index that is primarily bulk loaded and rarely (or never) the target of updates/deletes will not suffer from this. A columnstore index that is the target of frequent updates and deletes will have an active deltastore and many rows that are flagged for deletion.
A key takeaway here is that inserting data into a columnstore index in order and avoiding other write operations will improve the efficiency of the index and reduce the need for index maintenance.
Undersized rowgroups will occur naturally over time as rows are bulk loaded in quantities less than 220 at a time. The cost of having extra rowgroups is not significant at first, but over time can become burdensome. An easy way to gauge this is to compare the number of rowgroups with the hypothetical minimum needed to store the data.
For example, the demo table from the previous article contains 23,141,200 rows. In an optimal columnstore index, we would have 23,141,200 / 1,048,576 rowgroups, or 22.069 (rounded up to 23 since we cannot have a fractional rowgroup). If in a few months this table had 25 rowgroups, then there would be little need to be concerned. Alternatively, if there were 500 rowgroups, then it would be worth considering:
- Rebuilding the index.
- Reviewing the data load process for efficiency.
We can view the state of rowgroups by querying sys.dm_db_column_store_row_group_physical_stats:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SELECT tables.name AS table_name, indexes.name AS index_name, partitions.partition_number, dm_db_column_store_row_group_physical_stats.row_group_id, dm_db_column_store_row_group_physical_stats.total_rows, dm_db_column_store_row_group_physical_stats.deleted_rows, dm_db_column_store_row_group_physical_stats.state_desc, dm_db_column_store_row_group_physical_stats.trim_reason_desc FROM sys.dm_db_column_store_row_group_physical_stats INNER JOIN sys.indexes ON indexes.index_id = dm_db_column_store_row_group_physical_stats.index_id AND indexes.object_id = dm_db_column_store_row_group_physical_stats.object_id INNER JOIN sys.tables ON tables.object_id = indexes.object_id INNER JOIN sys.partitions ON partitions.partition_number = dm_db_column_store_row_group_physical_stats.partition_number AND partitions.index_id = indexes.index_id AND partitions.object_id = tables.object_id WHERE tables.name = ‘fact_order_BIG_CCI’ ORDER BY indexes.index_id, dm_db_column_store_row_group_physical_stats.row_group_id; |
The results provide a bit more information about rowgroups and their contents:
Note that the row group IDs are repeated as they are spread across 5 partitions. The combination of row_group_id and partition_number will be unique within a given columnstore index that has table partitioning enabled.
This index is close to optimal, with most rowgroups full or nearly full. The data above indicates to an operator that no index maintenance is needed at this time. The trim reason tells us why a rowgroup was closed before being filled to the maximum allowed size.
We can add some churn into the index for use in the further demo below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
UPDATE fact_order_BIG_CCI SET Quantity = 1 FROM dbo.fact_order_BIG_CCI WHERE fact_order_BIG_CCI.[Order Key] >= 8000101 AND fact_order_BIG_CCI.[Order Key] < 8001001; GO DELETE fact_order_BIG_CCI FROM dbo.fact_order_BIG_CCI WHERE fact_order_BIG_CCI.[Order Key] >= 80002001 AND fact_order_BIG_CCI.[Order Key] < 80002501; GO INSERT INTO dbo.fact_order_BIG_CCI SELECT TOP 1 * FROM dbo.fact_order_BIG_CCI GO 100 |
As a reminder, these types of small write operations are not optimal for a columnstore index. Rerunning the query against sys.dm_db_column_store_row_group_physical_stats reveals some changes. Filtering by state_desc and deleted_rows reduces the rows returned, which is especially useful against a large columnstore index:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
SELECT tables.name AS table_name, indexes.name AS index_name, partitions.partition_number, dm_db_column_store_row_group_physical_stats.row_group_id, dm_db_column_store_row_group_physical_stats.total_rows, dm_db_column_store_row_group_physical_stats.deleted_rows, dm_db_column_store_row_group_physical_stats.state_desc, dm_db_column_store_row_group_physical_stats.trim_reason_desc FROM sys.dm_db_column_store_row_group_physical_stats INNER JOIN sys.indexes ON indexes.index_id = dm_db_column_store_row_group_physical_stats.index_id AND indexes.object_id = dm_db_column_store_row_group_physical_stats.object_id INNER JOIN sys.tables ON tables.object_id = indexes.object_id INNER JOIN sys.partitions ON partitions.partition_number = dm_db_column_store_row_group_physical_stats.partition_number AND partitions.index_id = indexes.index_id AND partitions.object_id = tables.object_id WHERE tables.name = ‘fact_order_BIG_CCI’ AND (dm_db_column_store_row_group_physical_stats.state_desc <> ‘COMPRESSED’ OR dm_db_column_store_row_group_physical_stats.deleted_rows > 0) ORDER BY indexes.index_id, dm_db_column_store_row_group_physical_stats.row_group_id; |
The results are as follows:
An existing rowgroup has been updated to show that it contains 900 deleted rows. These rows will remain in the columnstore index until rebuilt. A new deltastore has been created to store the 1000 rows that were recently inserted. It is open, indicating that more rows can be added as needed. This allows the 1000 insert operations executed above to be combined into a single rowgroup, rather than lots of smaller ones.
Reorganize
A clustered columnstore index may be reorganized as an online operation. As such, this maintenance can be performed anytime without blocking access to it. This operation will take whatever is in the deltastore and process it immediately, as though the tuple mover was running and handling it.
The primary benefit of reorganizing a columnstore index is to speed up OLAP queries by removing the deltastore rowstore records and moving them to the columnstore index structure. Queries no longer need to read data from the deltastore to get the data they need. In addition, since the index is highly compressed, the memory required to read the data needed to satisfy a query is smaller.
The syntax to reorganize a columnstore index is similar to that for a standard B-tree index:
|
ALTER INDEX CCI_fact_order_BIG_CCI ON dbo.fact_order_BIG_CCI REORGANIZE; |
Reviewing the physical stats for this index shows the following results: 
No changes! The reason that there is still an open rowgroup is that SQL Server is waiting for more rows to be added before compressing and adding it to the columnstore index. Having a large volume of tiny rowgroups would be inefficient as the ideal size is 220 rows, and the closer we can get to that magic number, the better.
We can force the tuple mover to process the deltastore, close it, and commit it to compressed data within the columnstore index by using the following variant on the reorganize statement:
|
ALTER INDEX CCI_fact_order_BIG_CCI ON dbo.fact_order_BIG_CCI REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON); |
The results look like this:
A rowgroup that is labelled with the state TOMBSTONE indicates that it was a part of a deltastore and was forcefully compressed into a columnstore rowgroup. The residual rowgroup no longer has any non-deleted rows in it and therefore is labelled with the ominous tombstone state. These are intermediary rowgroups that will be deallocated. We can force this along with another reorganization command, and the tombstone rowgroup is gone.
Note that there is no need to force this change. Doing so demonstrates how automated processes can be started to fast-track a given result. Given time, this cleanup will occur anyway. Therefore there is no compelling reason to do this in a production environment.
The final version of the columnstore index can be viewed by removing the filters on state_desc and deleted_rows:
There are 23 compressed rowgroups, and the trim reason for the final rowgroup has been changed to REORG, indicating that it was opened and closed via an index reorganization process. Note that the index has the same number of rowgroups as earlier and row counts that are more than acceptable for OLAP operations.
Rebuild
Rebuilding a clustered columnstore index is an offline operation until SQL Server 2019 when the ability to rebuild online was introduced. As with a B-tree index, the rebuild will create a brand new columnstore index from the underlying data. Doing so will:
- Eliminate all data in the deltastore.
- Eliminate all deleted rows.
- Reduce the number of compressed rowgroups, if large numbers of smaller ones exist.
This operation is expensive and should be reserved for a dedicated maintenance window and only when the index has become so fragmented that no other options exist.
Note that the order of data is not changed when the columnstore index is rebuilt. Similar to when our demo table was first created, if no order is enforced, then SQL Server will build a new index on top of our data as-is. If the columnstore index table has been maintained by adding data in order over time, then odds are good that no special work will be needed to re-order it.
The columnstore index we have been tinkering with thus far can be rebuilt like this:
|
ALTER INDEX CCI_fact_order_BIG_CCI ON dbo.fact_order_BIG_CCI REBUILD WITH (ONLINE = ON); |
Note again that the ONLINE option is not available prior to SQL Server 2019. For this table, the rebuild takes about 2 minutes. Reviewing all rowgroups in the table reveals the following:
The deleted rows are gone, and rows from other rowgroups were pushed into the vacancy left behind. While there are still some rowgroups that are not completely full, this index is quite efficiently structured overall.
If table partitioning is in use, then index rebuilds become significantly less expensive as the active partition(s) can be targeted for a rebuild and the rest ignored. In a table where a large portion of the data is old and unchanged, this can save immense system resources, not to mention time!
The syntax to rebuild a specific partition is as follows:
|
ALTER INDEX CCI_fact_order_BIG_CCI ON dbo.fact_order_BIG_CCI REBUILD PARTITION = 5 WITH (ONLINE = ON); |
When executed, the rebuild will only target partition 5. If this is the active partition and the others contain older static data, then this will be the only partition with fragmentation, assuming no unusual change has occurred on older data. The rebuild of partition 5 shown above took about 20 seconds to complete.
The simplest way to measure when a columnstore index needs a rebuild is to measure the quantity of deleted rows and compare it to the total rows for each partition. If a partition contains 1 million rows of which none are deleted, then it’s in great shape! If a partition contains 1 million rows, of which 200k are flagged for deletion, then a rebuild would be a good idea to remove those soft-deleted rows from the index.
The following query groups columnstore index metadata by table, index, and partition, allowing for a relatively simple, but useful view of fragmentation:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT tables.name AS table_name, indexes.name AS index_name, partitions.partition_number, SUM(dm_db_column_store_row_group_physical_stats.total_rows) AS total_rows, SUM(dm_db_column_store_row_group_physical_stats.deleted_rows) AS deleted_rows FROM sys.dm_db_column_store_row_group_physical_stats INNER JOIN sys.indexes ON indexes.index_id = dm_db_column_store_row_group_physical_stats.index_id AND indexes.object_id = dm_db_column_store_row_group_physical_stats.object_id INNER JOIN sys.tables ON tables.object_id = indexes.object_id INNER JOIN sys.partitions ON partitions.partition_number = dm_db_column_store_row_group_physical_stats.partition_number AND partitions.index_id = indexes.index_id AND partitions.object_id = tables.object_id WHERE tables.name = ‘fact_order_BIG_CCI’ GROUP BY tables.name, indexes.name, partitions.partition_number |
The results of this query are as follows:
This specific example provides insight into an index that requires no maintenance. If the count of deleted rows was high enough, though, then a rebuild of that partition could save storage, memory, and make queries against that data faster. Note that deleted_rows includes both rows that were deleted and those that were updated, since an update is modelled as a delete followed by an insert.
Additional Index Maintenance Notes
In general, a common plan for index maintenance is as follows:
- Perform an index reorganization with the
compress_all_row_groupsoption on after any large data load, or on a semi-regular basis. This is inexpensive, online, and will generally keep an index defragmented enough for efficient use for quite a long time. - Perform infrequent index rebuilds on heavily fragmented partitions (if partitioned) or the entire index (if not). Perform this online, if available, as it will eliminate the need for either a maintenance window or data trickery to avoid a maintenance window.
If a columnstore index is loaded solely via inserts (no deletes or updates) and the data is consistently inserted in order, then fragmentation will occur very slowly over time, and index rebuilds will be rarely (if ever) needed.
OLAP databases are uniquely positioned to take advantage of this fact, as their data loads are typically isolated, primarily inserts, and performed at designated intervals. This differs from OLTP databases, that are often the target of deletes, updates, software releases, and other operations that can quickly cause heavy fragmentation.
Nonclustered Columnstore Indexes
The discussion of columnstore indexes thus far has focused on clustered columnstore indexes that target a primarily OLAP workload. SQL Server also supports nonclustered columnstore indexes. A table may only have one columnstore index on it at a time, regardless of whether it is clustered or nonclustered. A nonclustered columnstore index functions similarly to a clustered columnstore index in terms of architecture, data management, the deltastore, and other nuances discussed thus far in this article.
To illustrate the effects of a nonclustered columnstore index, consider the following query:
|
SELECT SUM([Quantity]) FROM dbo.fact_order_BIG WHERE [Order Date Key] >= ‘1/1/2016′ AND [Order Date Key] < ‘2/1/2016′; |
This is executing against a table without a columnstore index, but with a nonclustered index on [Order Date Key]. When executed, there is a bit of waiting, and the result is returned. The following is the IO for the query:
That sort of IO is not acceptable against a busy OLTP table and will cause latency and contention for other users that are reading and writing data to this table. If this is a common query, then a nonclustered columnstore index can address it:
|
CREATE NONCLUSTERED COLUMNSTORE INDEX NCI_fact_order_BIG_Quantity ON dbo.fact_order_BIG ([Order Date Key], Quantity); |
By choosing only the columns needed to satisfy the query, the need to maintain a larger and more expensive structure on top of an OLTP data store is avoided. Once complete, the IO for the test query above is significantly reduced:
Note that while IO is 1/10th of the previous cost and the query executed much faster, IO is still high and the details above indicate that no segments were skipped.
Similar to the clustered columnstore index, data order bit us here. If the underlying table were ordered primarily by [Order Date Key], then segments could be skipped as part of query execution. Without that implicit ordering, the efficiency of a nonclustered columnstore index is hindered far more than it would have been if a clustered columnstore index were used.
This poses a challenge to a database architect: Is placing a nonclustered columnstore index on top of an OLTP workload worth the effort? Would a covering index be better? This question can be tested:
|
DROP INDEX NCI_fact_order_BIG_Quantity ON dbo.fact_order_BIG; CREATE NONCLUSTERED INDEX NCI_fact_order_BIG_Quantity ON dbo.fact_order_BIG ([Order Date Key]) INCLUDE (Quantity); |
The new index sorts on [Order Date Key] and adds Quantity as an include column. The following is the IO after this change
A covering nonclustered B-tree index provided more efficient coverage of this query than the nonclustered columnstore index.
Ultimately, the challenge presented here is to separate OLAP and OLTP workloads. Even if a nonclustered columnstore index can be built that is well-ordered and satisfies a query efficiently, it will become fragmented quickly if write operations target the table with frequent inserts, updates, and deletes. The best possible solution to this problem is to separate heavy OLAP workloads from OLTP into their own databases and tables.
The test query above filtered a single column and returned one other. Typically, the hole that administrators and developers fall into with OLAP against OLTP is that it is never just one column. Once reporting against OLTP data ensues, there often ends up being many analytics against many columns that result in the creation of many covering indexes to handle each scenario. Eventually, an OLTP table becomes overburdened by covering indexes and hefty reporting queries that cause contention against critical OLTP workloads.
While nonclustered columnstore indexes exist and can provide targeted relief for expensive OLAP queries, they require heavy testing prior to implementing to ensure that they are efficient, ordered, and can outperform a classic covering index. It is also necessary to ensure that an OLTP database does not become overburdened with OLAP workloads over time.
This is a significant challenge that many administrators and developers struggle with regularly. Ultimately, if reporting data and queries can be separated into their own data store, then we can properly implement a clustered columnstore index. Data can then be architected and ordered to optimize solely for OLAP workloads without the need to be concerned for the impact of those analytics against other real-time transactions.
Memory-Optimized Columnstore Indexes
Because columnstore indexes are compact structures, the ability to natively store them in-memory becomes possible. The example table in this article was 5GB when stored via a classic B-tree clustered index. While not massive, this is also not trivial. If the table were to grow by 5GB every year, then reading the entire thing into memory may not be worth it. Alternatively, the 100MB used for the columnstore index is trivial with regards to memory consumption.
If reporting against a columnstore index requires speed, then converting it into a memory-optimized table is an option for potentially improving performance. Note that data compression in-memory is not as efficient as on disk. As a result, results may vary depending on available memory. Typically, memory-optimized data size will be at least double the size of a disk-based columnstore index.
On a server with limited memory, this will pose a challenge, and testing will be necessary to determine if this is a worthwhile implementation of memory-optimized tables or not. The following is the basic T-SQL syntax needed to create a new memory-optimized columnstore index table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE TABLE dbo.fact_order_BIG_CCI_MEM_OPT ( [Order Key] [bigint] NOT NULL CONSTRAINT PK_fact_order_BIG_CCI_MEM_OPT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000), [City Key] [int] NOT NULL, [Customer Key] [int] NOT NULL, [Stock Item Key] [int] NOT NULL, [Order Date Key] [date] NOT NULL, [Picked Date Key] [date] NULL, [Salesperson Key] [int] NOT NULL, [Picker Key] [int] NULL, [WWI Order ID] [int] NOT NULL, [WWI Backorder ID] [int] NULL, [Description] [nvarchar](100) NOT NULL, [Package] [nvarchar](50) NOT NULL, [Quantity] [int] NOT NULL, [Unit Price] [decimal](18, 2) NOT NULL, [Tax Rate] [decimal](18, 3) NOT NULL, [Total Excluding Tax] [decimal](18, 2) NOT NULL, [Tax Amount] [decimal](18, 2) NOT NULL, [Total Including Tax] [decimal](18, 2) NOT NULL, [Lineage Key] [int] NOT NULL, INDEX CCI_fact_order_BIG_CCI_MEM_OPT CLUSTERED COLUMNSTORE) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); |
Note that a primary key is required on all memory-optimized tables. Now, this table can be loaded with test data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
INSERT INTO dbo.fact_order_BIG_CCI_MEM_OPT SELECT [Order Key] + (250000 * ([Day Number] + ([Calendar Month Number] * 31))) AS [Order Key] ,[City Key] ,[Customer Key] ,[Stock Item Key] ,[Order Date Key] ,[Picked Date Key] ,[Salesperson Key] ,[Picker Key] ,[WWI Order ID] ,[WWI Backorder ID] ,[Description] ,[Package] ,[Quantity] ,[Unit Price] ,[Tax Rate] ,[Total Excluding Tax] ,[Tax Amount] ,[Total Including Tax] ,[Lineage Key] FROM Fact.[Order] CROSS JOIN Dimension.Date WHERE Date.Date <= ‘2013-04-10′; |
This insert executes for about ten seconds on my local server and throws the following error:
Ouch! Reducing the date range should allow for a test data set to be created without running my server out of memory. To do this, ‘2013-04-10’ will be replaced with ‘2013-02-01’. The result is a table that can be queried like before using a test query:
|
SELECT SUM([Quantity]) FROM dbo.fact_order_BIG_CCI_MEM_OPT WHERE [Order Date Key] >= ‘2016-01-01′ AND [Order Date Key] < ‘2016-02-01′; |
The results are returned with zero IO:
As expected, querying a table that resides in memory results in zero reads. Despite that, it still takes time to read data from memory, and therefore this is not a freebie! Here is the storage footprint for the table:
Another Ouch! More space is consumed for 15 days of data than for the entire clustered columnstore index implemented on disk earlier. While additional optimization is possible, the overhead for maintaining the primary key on the data is not cheap, and it is hard to justify this cost unless there will be other uses for the memory-optimized data as well.
Because a columnstore index is relatively small to start with, much of it can fit into the buffer cache and be available in memory to service queries against it. The memory-optimized columnstore index will need to outperform its counterpart when in the cache, which is possible, but less impressive.
While this can trigger a lengthy discussion of memory-optimized objects, we are better served by avoiding it as the benefits will be limited to highly niche cases that are out of the scope of a general columnstore index dive.
When considering a memory-optimized columnstore index, the following will assist in making a decision:
- Is speed currently acceptable? If so, do not put into memory.
- Is extra memory available for the table, if a memory-optimized option is chosen?
- Can the active partition be segregated and placed in memory and the rest of a table on disk?
- Can the table benefit from
SCHEMA_ONLYdurability? I.e., is the table disposable if the server is restarted? - Can testing validate that a memory-optimized table will perform significantly better?
Memory-optimized tables were built by Microsoft to specifically address the problem of highly concurrent OLTP workloads. One of the greatest selling points of memory-optimized tables is the elimination of locking, latching, and deadlocks. These should rarely be concerns for large OLAP workloads. With this in mind, putting a columnstore index into memory will rarely be worth the effort. In some special cases, it will provide speed boosts, but significant testing is needed to confirm this.
In addition, all of the various limitations imposed on memory-optimized objects apply here, which may also sway one’s opinion to use them. This page provides up-to-date details on what features are supported or not for memory-optimized tables:
Conclusion
The more I explored columnstore indexes, the more I appreciated them. Many of the optimizations and tweaks that can allow for OLAP data loads to be processed even faster are not well documented. Still, they can significantly improve the speed of data loads, archiving processes, and reporting. This is a feature that will continue to grow in popularity as data continues to grow and will add value to any organization that wants to maintain control over their reporting data.
There is no room here for “set it and forget it”. Implementing columnstore indexes should be treated with the same level of care as architecting new OLTP tables. The results will perform well, even for massive quantities of data. In addition, maintenance, upgrades, and software releases will be faster, easier, and less risky.
Columnstore is one of my favorite features in SQL Server. It has revitalized how OLAP data is stored and accessed, allowing for billions of rows of data to be efficiently stored and quickly accessed in ways that previously were challenging to achieve without moving data into other analytics tools. The biggest bonus of retaining OLAP data in SQL Server is that we (the developers, administrators, and analysts) can keep control over analytic data architecture and ensure optimal performance as organizational and software changes continually happen.
Heaps in SQL Server: Part 2 Optimizing Reads
The series so far:
Heaps are not necessarily the developer’s favourite child, as they are not very performant, especially when it comes to selecting data (most people think so!). Certainly, there is something true about this opinion, but in the end, it is always the workload that decides it. In this article, I describe how a Heap works when data are selected. If you understand the process in SQL Server when reading data from a Heap, you can easily decide if a Heap is the best solution for your workload.
Advanced Scanning
As you may know, Heaps can only use Table Scans to retrieve data which are requested by the client software. In SQL Server Enterprise, the advanced scan feature allows multiple tasks to share full table scans. If the execution plan of a Transact-SQL statement requires a scan of the data pages in a table, and the Database Engine detects that the table is already being scanned for another execution plan, the Database Engine joins the second scan to the first, at the current location of the second scan. The Database Engine reads each page one time and passes the rows from each page to both execution plans. This continues until the end of the table is reached.
At that point, the first execution plan has the complete results of a scan, but the second execution plan must still retrieve the data pages that were read before it joined the in-progress scan. The scan for the second execution plan then wraps back to the first data page of the table and scans forward to where it joined the first scan. Any number of scans can be combined like this. The Database Engine will keep looping through the data pages until it has completed all the scans. This mechanism is also called “merry-go-round scanning” and demonstrates why the order of the results returned from a SELECT statement cannot be guaranteed without an ORDER BY clause.
Select data in a Heap
Since a Heap has no index structures, Microsoft SQL Server must always read the entire table. Microsoft SQL Server solves the problem with predicates with a FILTER operator (Predicate Pushdown). For all examples shown in this article, I created a table with ~ 4,000,000 data records from my demo database [CustomerOrders]. After restoring the database, run the code to create the new table, CustomerOrderList.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
– Create a BIG table with ~4.000.000 rows SELECT C.ID AS Customer_Id, C.Name, A.CCode, A.ZIP, A.City, A.Street, A.[State], CO.OrderNumber, CO.InvoiceNumber, CO.OrderDate, CO.OrderStatus_Id, CO.Employee_Id, CO.InsertUser, CO.InsertDate INTO dbo.CustomerOrderList FROM CustomerOrders.dbo.Customers AS C INNER JOIN CustomerOrders.dbo.CustomerAddresses AS CA ON (C.Id = CA.Customer_Id) INNER JOIN CustomerOrders.dbo.Addresses AS A ON (CA.Address_Id = A.Id) INNER JOIN CustomerOrders.dbo.CustomerOrders AS CO ON (C.Id = CO.Customer_Id) ORDER BY C.Id, CO.OrderDate OPTION (MAXDOP 1); GO |
When data is read from a Heap, a TABLE SCAN operator is used in the execution plan – regardless of the number of data records that have to be delivered to the client.
Figure 1: SELECT * FROM dbo.CustomerList
When Microsoft SQL Server reads data from a table or an index, this can be done in two ways:
- The data selection follows the B-tree structure of an index
- The data is selected in accordance with the logical arrangement of data pages
Figure 2: Reading data in a B-Tree basically follows the index structure
In a Heap, the reading process takes place in the order in which data was saved on the data pages. Microsoft SQL Server reads information about the data pages of the Heap from the IAM page of a table, which is described in the article “Heaps in SQL Server: Part 1 The Basics”.
Figure 3: Reading data in a Heap follows the logical order of data pages
After the “route” for reading the data has been read from the IAM, the SCAN process begins to send the data to the client. This technique is called “Allocation Order Scan” and can be observed above all at Heaps.
If the data is limited by a predicate, the way of working does not change. Since the data is unsorted in a Heap, Microsoft SQL Server must always search the complete table (all data pages).
|
SELECT * FROM dbo.CustomerOrderList WHERE Customer_Id = 10; GO |
Figure 4: Scan over the whole table
The filtering is called “predicate pushdown”. Before further processes are processed, the number of data records is reduced as much as possible! A predicate pushdown can be made visible in the execution plan using trace flag 9130!
|
SELECT * FROM dbo.CustomerOrderList WHERE Customer_Id = 10 OPTION (QUERYTRACEON 9130); GO |
Figure 5: FILTER Operator for selected data
Advantages of reading from Heaps
Heaps appear to be inferior to an index when reading data. However, this statement only applies if the data is to be limited by a predicate. In fact, when reading the complete table, the Heap has two – in my view – significant advantages:
- No B-tree structure has to be read; only the data pages are read.
- If the Heap is not fragmented and has no forwarded records (described in a later article), Heaps can be read sequentially. Data is read from the storage in the order in which they were entered.
- An index always follows the pointers to the next data page. If the index is fragmented, random reads occur that are not as powerful as sequential read operations.
Figure 6: Reading from a B-Tree
Disadvantages when reading from Heaps
One of the biggest drawbacks when reading data from a Heap is the IAM scan while reading the data. Microsoft SQL Server must hold a lock to ensure that the metadata of the table structure is not changed during the read process.
The code shown below creates an extended event that records all the locks set in a transaction. The script only records activity for a predefined user session, so be sure to change the user session ID in the script to match yours.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
– Create an XEvent for analysis of the locking CREATE EVENT SESSION [Track Lockings] ON SERVER ADD EVENT sqlserver.lock_acquired (ACTION (package0.event_sequence) WHERE ( sqlserver.session_id = 55 AND mode = 1 ) ), ADD EVENT sqlserver.lock_released (ACTION (package0.event_sequence) WHERE ( sqlserver.session_id = 55 AND mode = 1 ) ), ADD EVENT sqlserver.sql_statement_completed (ACTION (package0.event_sequence) WHERE (sqlserver.session_id = 55) ), ADD EVENT sqlserver.sql_statement_starting (ACTION (package0.event_sequence) WHERE (sqlserver.session_id = 55) ) WITH ( MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = ON, STARTUP_STATE = OFF ); GO ALTER EVENT SESSION [Track Lockings] ON SERVER STATE = START; GO |
When you run the SELECT statement from the first demo, the Extended Event session will record the following activities:
Figure 7: Holding a SCH_S-Lock when reading data from a Heap
The lock is not released until the SCAN operation has been completed.
NOTE: If Microsoft SQL Server chooses a parallel plan when executing the query, EVERY thread holds a SCH‑S lock on the table.
In a highly competitive system, such locks are not desirable because they serialize operations. The larger the Heap, the longer the locks will prevent further metadata operations:
- Create indexes
- Rebuild indexes
- Add or delete columns
- TRUNCATE operations
- …
Another “shortcoming” of Heaps can be the high number of I/O if only small amounts of data have to be selected. Here, however, it is advisable to use a NONCLUSTERED INDEX to optimize these operations.
Optimize SELECT operations
As stated earlier, Microsoft SQL Server must always read the table completely when executing a SELECT statement. Since Microsoft SQL Server performs an allocation order scan, the data is read in the order of its logical locations.
Use of the TOP operator
With the help of the TOP(n) operator, you will be lucky if the affected data records have been saved on the first data pages of the Heap.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
– Select the very first record SELECT TOP (1) * FROM dbo.CustomerOrderList OPTION (QUERYTRACEON 9130); GO – Select the very first record with a predicate – which determines a record at the beginning of – the Heap SELECT TOP (1) * FROM dbo.CustomerOrderList WHERE Customer_Id = 1 OPTION (QUERYTRACEON 9130, MAXDOP 1); GO – Select the very first record with a predicate – which determines a record at any position in – the Heap SELECT TOP (1) * FROM dbo.CustomerOrderList WHERE Customer_Id = 22844 OPTION (QUERYTRACEON 9130); GO |
The above code performs three queries using the TOP operator. The first query finds the physically first data record that was saved in the table.
Figure 8: Table scan for determining the first data record – 1 I/O
As expected, the execution plan uses a table scan operator. It is the only operator that can be used for a Heap. However, the TOP operator prevents the table from being searched completely. When the number of data records to be delivered to the client has been reached, the TOP operator terminates the connection to the table scan, and the process is finished.
NOTE: Although only one data record is to be determined, a SCH-S lock is set on the table!
The second query also only requires 1 I/O. However, this is because the record you are looking for is the first record in the table. However, Microsoft SQL Server must use a FILTER operator for the predicate. The TOP operator terminates the subsequent operations immediately after receiving the first data record. 
Figure 9: Search with predicate must search 1 data record
The third query uses a predicate that only finds a data record at the 1,875th position. In this situation, Microsoft SQL Server must read many more data pages before the process has the desired result set and automatically ends the process. 
Figure 10: Search with predicate must search 1,875 records
The bottom line is that a TOP operator can be helpful; in practice, this is rather not the case, since the number of data pages to be read always depends on the logical position of the data record.
Compression
Apparently, it is not possible to reduce the I/O for operations in a Heap (all data pages must always be read!), The compression of the data helps to reduce I/O.
Microsoft SQL Server provides two types of data compression for possible reduction:
- Row compression
- Page compression
NOTE: The options “ColumnStore” and “ColumnStore_Archive” are also available for data compression. However, these types of compression cannot be applied to a Heap, but only to columnstore indexes!
For Heaps and partitioned Heaps, data compression can be a distinct advantage in terms of I/O. However, there are a few special features that need to be considered when compressing data in a Heap. When a Heap is configured to compress at the page level, the compression is done in the following ways:
- The mapping of new data pages in a Heap as part of DML operations only uses page compression after the Heap has been re-created.
- Changing the compression setting for a Heap forces all nonclustered indexes to be rebuilt because the position mappings must be rewritten.
ROWorPAGEcompression can be activated and deactivated online or offline.- Enabling compression for a Heap online is done with a single thread.
The system procedure [sp_estimate_data_compression_savings] can be used to determine whether the compression of tables and / or indexes actually has an advantage.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
– Evaluate the savings by compression of data DECLARE @Result TABLE ( Data_Compression CHAR(4) NOT NULL DEFAULT ‘—‘, object_name SYSNAME NOT NULL, schema_name SYSNAME NOT NULL, index_id INT NOT NULL, partition_number INT NOT NULL, current_size_KB BIGINT NOT NULL, request_size_KB BIGINT NOT NULL, sample_size_KB BIGINT NOT NULL, sample_request_KB BIGINT NOT NULL ); INSERT INTO @Result (object_name, schema_name, index_id, partition_number, current_size_KB, request_size_KB, sample_size_KB, sample_request_KB) EXEC sp_estimate_data_compression_savings @schema_name = ‘dbo’, @object_name = ‘CustomerOrderList’, @index_id = 0, @partition_number = NULL, @data_compression = ‘PAGE’; UPDATE @Result SET Data_Compression = ‘PAGE’ WHERE Data_Compression = ‘—‘; – Evaluate the savings by compression of data INSERT INTO @Result (object_name, schema_name, index_id, partition_number, current_size_KB, request_size_KB, sample_size_KB, sample_request_KB) EXEC sp_estimate_data_compression_savings @schema_name = ‘dbo’, @object_name = ‘CustomerOrderList’, @index_id = 0, @partition_number = NULL, @data_compression = ‘ROW’; UPDATE @Result SET Data_Compression = ‘ROW’ WHERE Data_Compression = ‘—‘; SELECT Data_Compression, current_size_KB, request_size_KB, (1.0 - (request_size_KB * 1.0 / current_size_KB * 1.0)) * 100.0 AS percentage_savings FROM @Result; GO |
The above script is used to determine the possible savings in data volume for row and data page compression.
Figure 11: Savings of more than 30% are possible
The next script inserts all records from the [dbo].[CustomerOrderList] table into a temporary table. Both I/O and CPU times are measured. The test is performed with uncompressed data, page compression and row compression.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
ALTER TABLE dbo.CustomerOrderList REBUILD WITH (DATA_COMPRESSION = NONE); GO – IO and CPU without compression SELECT * INTO #Dummy FROM dbo.CustomerOrderList GO DROP TABLE #Dummy; GO ALTER TABLE dbo.CustomerOrderList REBUILD WITH (DATA_COMPRESSION = PAGE); GO – IO and CPU without compression SELECT * INTO #Dummy FROM dbo.CustomerOrderList GO DROP TABLE #Dummy; GO ALTER TABLE dbo.CustomerOrderList REBUILD WITH (DATA_COMPRESSION = ROW); GO – IO and CPU without compression SELECT * INTO #Dummy FROM dbo.CustomerOrderList GO DROP TABLE #Dummy; GO |
The measurements show the problem of data compression. The I/O is reduced significantly; however, the savings potential is “eaten up” by the increased CPU usage.
The bottom line for data compression is that it can certainly be an option to reduce I/O. Unfortunately, this “advantage” is quickly nullified by the disproportionate consumption of other resources (CPU). If you have a system with sufficiently fast processors in large numbers, you should consider this option. Otherwise, you should choose CPU resources; this applies all the more to systems with a large number of small transactions.
You should also check your application very carefully before using compression techniques. Microsoft SQL Server creates execution plans that take into account the estimated I/O. Is significantly less I/O generated by the use of compression, it can lead to an execution plan being changed (a HASH or MERGE operator becomes a NESTED LOOP operator). Anyone who believes that data compression saves memory (both for the buffer pool and for SORT operations) is wrong!
- Data is always decompressed in the buffer pool.
- A SORT operator calculates its memory requirements based on the data records to be processed
Partitioning
With the help of partitioning, tables are divided horizontally. Several groups are created that lie within the boundaries of partitions.
NOTE: Partitioning is a very complex topic and cannot be covered in detail in this series of articles. More information on partitioning can be found in the online documentation for Microsoft SQL Server.
A valued colleague from Norway (Cathrine Wilhelmsen (b | t), has also written a very remarkable series of articles on partitioning, which can be found here.
The advantage of partitioning Heaps can only take effect if the Heap is used to search for predicate patterns that match the partition key. Unless you’re looking for the partition key, partitioning can’t really help you find data.
For the following demo users often search for orders in the [dbo].[CustomerOrderList].
|
– Find all orders from 2016 SELECT * FROM dbo.CustomerOrderList WHERE OrderDate >= ‘20160101’ AND OrderDate <= ‘20161231’ ORDER BY Customer_Id, OrderDate DESC OPTION (QUERYTRACEON 9130); GO |
Microsoft SQL Server had to search the entire table to run the query. This is noticeable in the I/O as well as in the CPU load! 
Figure 12: TABLE SCAN over 4,000,000 records
Figure 13: Statistics formatted with https://statisticsparser.com
Without an index, there is no way to reduce I/O or CPU load. A reduction can only be achieved by reducing the amount of data to be read. For this reason, the table gets partitioned so that a separate partition is used for each year.
NOTE: Partitioning is not a mandatory topic for this article, but I want to allow replay of the demos for the readers. Please excuse me that I am only describing the functionality of the code but not the partitioning as such.
In the first step, create a filegroup for every order year in the database. For a better performance, every order year is located on a separate database file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
– We create for each year up to 2019 one filegroup –and add one file for each filegroup! DECLARE @DataPath NVARCHAR(256) = CAST(SERVERPROPERTY(‘InstanceDefaultDataPath’) AS NVARCHAR(256)); DECLARE @stmt NVARCHAR(1024); DECLARE @Year INT = 2000; WHILE @Year <= 2019 BEGIN SET @stmt = N‘ALTER DATABASE CustomerOrders ADD FileGroup ‘ + QUOTENAME(N‘P_’ + CAST(@Year AS NCHAR(4))) + N‘;’; RAISERROR (‘Statement: %s’, 0, 1, @stmt); EXEC sys.sp_executeSQL @stmt; SET @stmt = N‘ALTER DATABASE CustomerOrders ADD FILE ( NAME = ‘ + QUOTENAME(N‘Orders_’ + CAST(@Year AS NCHAR(4)), ””) + N‘, FILENAME = ‘” + @DataPath + N‘ORDERS_’ + CAST(@Year AS NCHAR(4)) + N‘.ndf’‘, SIZE = 128MB, FILEGROWTH = 128MB ) TO FILEGROUP ‘ + QUOTENAME(N‘P_’ + CAST(@Year AS NCHAR(4))) + N‘;’; RAISERROR (‘Statement: %s’, 0, 1, @stmt); EXEC sys.sp_executeSQL @stmt; SET @Year += 1; END GO |
You can see that each year has its own filegroup and file after running the script:
Figure 14: Each Order Year has its own database file
The next step is to create a partition function that ensures that the order year is correctly assigned and saved.
|
CREATE PARTITION FUNCTION pf_OrderDate(DATE) AS RANGE LEFT FOR VALUES ( ‘20001231’, ‘20011231’, ‘20021231’, ‘20031231’, ‘20041231’, ‘20051231’, ‘20061231’, ‘20071231’, ‘20081231’, ‘20091231’, ‘20101231’, ‘20111231’, ‘20121231’, ‘20131231’, ‘20141231’, ‘20151231’, ‘20161231’, ‘20171231’, ‘20181231’, ‘20191231’ ); GO |
Finally, in order to connect the partition function with the filegroups, you need the partition scheme, which gets generated with the next script.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PARTITION SCHEME [OrderDates] AS PARTITION pf_OrderDate TO ( [P_2000], [P_2001], [P_2002], [P_2003], [P_2004], [P_2005], [P_2006], [P_2007], [P_2008], [P_2009], [P_2010], [P_2011], [P_2012], [P_2013], [P_2014], [P_2015], [P_2016], [P_2017], [P_2018], [P_2019] ,[PRIMARY] ); GO |
When the Partition schema exists, you can now distribute the data from the table over all the partitions based on the year of the order. To move a non-partitioned Heap into a partition schema, you need to build a Clustered Index based on the partition schema and drop it afterwards.
|
CREATE CLUSTERED INDEX cix_CustomerOrderList_OrderDate ON dbo.CustomerOrderList (OrderDate) ON OrderDates(OrderDate); GO DROP INDEX cix_CustomerOrderList_OrderDate ON dbo.CustomerOrderList; GO |
The data for the individual years are split up to 20 years and the result looks as follows
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
– Let’s combine all information to an overview SELECT p.partition_number AS [Partition #], CASE pf.boundary_value_on_right WHEN 1 THEN ‘Right / Lower’ ELSE ‘Left / Upper’ END AS [Boundary Type], prv.value AS [Boundary Point], stat.row_count AS [Rows], fg.name AS [Filegroup] FROM sys.partition_functions AS pf INNER JOIN sys.partition_schemes AS ps ON ps.function_id=pf.function_id INNER JOIN sys.indexes AS si ON si.data_space_id=ps.data_space_id INNER JOIN sys.partitions AS p ON ( si.object_id=p.object_id AND si.index_id=p.index_id ) LEFT JOIN sys.partition_range_values AS prv ON ( prv.function_id=pf.function_id AND p.partition_number= CASE pf.boundary_value_on_right WHEN 1 THEN prv.boundary_id + 1 ELSE prv.boundary_id END ) INNER JOIN sys.dm_db_partition_stats AS stat ON ( stat.object_id=p.object_id AND stat.index_id=p.index_id AND stat.index_id=p.index_id AND stat.partition_id=p.partition_id AND stat.partition_number=p.partition_number ) INNER JOIN sys.allocation_units as au ON ( au.container_id = p.hobt_id AND au.type_desc =‘IN_ROW_DATA’ ) INNER JOIN sys.filegroups AS fg ON fg.data_space_id = au.data_space_id ORDER BY [Partition #]; GO |
Many thanks to Kendra Little for the basic idea of this query.
Figure 15: Heap with partitioned data
If the previously executed query is carried out based on partitioning on the predicate column, this results in a completely different runtime behaviour:
|
– Find all orders from 2016 SELECT * FROM dbo.CustomerOrderList WHERE OrderDate >= ‘20160101’ AND OrderDate <= ‘20161231’ ORDER BY Customer_Id, OrderDate DESC OPTION (QUERYTRACEON 9130); GO |
Microsoft SQL Server uses the boundaries for the partitions to identify the partition in which the values to be found can appear. Other partitions are no longer considered and are, therefore, “excluded” from the table scan. 
Figure 16: Excluding unneeded partitions
The runtime has not changed significantly (the number of data records sent to the client has not changed!), but you can see very well that the CPU load has been reduced by approximately 25%.
Figure 17: Decreased logical reads due to eliminated partitions
If the whole workload is focused on I/O and not on the CPU load, the last possibility for reduction is to compress the data at the partition level!
|
ALTER TABLE dbo.CustomerOrderList REBUILD PARTITION = 17 WITH (DATA_COMPRESSION = ROW); GO |
Figure 18: Logical reads with partitioning and compression turned on
Summary
Heaps are hopelessly inferior indexes when it comes to selectively extracting data. However, if – for example a data warehouse – large amounts of data have to be processed, a Heap might perform better. Hopefully, I have been able to demonstrate the requirements and technical possibilities for improvements when you have to (maybe will) deal with Heaps.
Hands-On with Columnstore Indexes: Part 2 Best Practices and Guidelines
The series so far:
- Hands-On with Columnstore Indexes: Part 1 Architecture
- Hands-On with Columnstore Indexes: Part 2 Best Practices and Guidelines
A discussion of how columnstore indexes work is important for making the best use of them, but a practical, hands-on discussion of reality and how they are used in production environments is key to making the most of them. There are many ways that data load processes can be tweaked to dramatically improve query performance and increase scalability.
The following is a list of what I consider to be the most significant tips, tricks, and best practices for designing, loading data into, and querying columnstore indexes. As always, test all changes thoroughly before implementing them.
Columnstore indexes are generally used in conjunction with big data, and having to restructure it after-the-fact can be painfully slow. Careful design can allow a table with a columnstore index to stand on its own for a long time without the need for significant architectural changes.
Column Order is #1
Rowgroup elimination is the most significant optimization provided by a columnstore index after you account for compression. It allows large swaths of a table to be skipped when reading data, which ultimately facilitates a columnstore index growing to a massive size without the latency that eventually burdens a classic B-tree index.
Each rowgroup contains a segment for each column in the table. Metadata is stored for the segment, of which the most significant values are the row count, minimum column value, and maximum column value. For simplicity, this is akin to having MIN(), MAX(), and COUNT(*) available automatically for all segments in all rowgroups in the table.
Unlike a classic clustered B-tree index, a columnstore index has no natural concept of order. When rows are inserted into the index, they are added in the order that you insert them. If rows are inserted from ten years ago, then they will be added to the most recently available rowgroups. If rows are then inserted from today, they will get added on next. It is up to you as the architect of the table to understand what the most important column is to order by and design schema around that column.
For most OLAP tables, the time dimension will be the one that is filtered, ordered, and aggregated by. As a result, optimal rowgroup elimination requires ordering data insertion by the time dimension and maintaining that convention for the life of the columnstore index.
A basic view of segment metadata can be viewed for the date column of our columnstore index as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SELECT tables.name AS table_name, indexes.name AS index_name, columns.name AS column_name, partitions.partition_number, column_store_segments.segment_id, column_store_segments.min_data_id, column_store_segments.max_data_id, column_store_segments.row_count FROM sys.column_store_segments INNER JOIN sys.partitions ON column_store_segments.hobt_id = partitions.hobt_id INNER JOIN sys.indexes ON indexes.index_id = partitions.index_id AND indexes.object_id = partitions.object_id INNER JOIN sys.tables ON tables.object_id = indexes.object_id INNER JOIN sys.columns ON tables.object_id = columns.object_id AND column_store_segments.column_id = columns.column_id WHERE tables.name = ‘fact_order_BIG_CCI’ AND columns.name = ‘Order Date Key’ ORDER BY tables.name, columns.name, column_store_segments.segment_id; |
The results provide segment metadata for the fact_order_BIG_CCI table and the [Order Date Key] column:
Note the columns min_data_id and max_data_id. These ID values link to dictionaries within SQL Server that store the actual minimum and maximum values. When queried, the filter values are converted to IDs and compared to the minimum and maximum values shown here. If a segment contains no values needed to satisfy a query, it is skipped. If a segment contains at least one value, then it will be included in the execution plan.
The image above highlights a BIG problem here: the minimum and maximum data ID values are the same for all but the last segment. This indicates that when the columnstore index was created, the data was not ordered by the date key. As a result, all segments will need to be read for any query against the columnstore index based on the date.
This is a common oversight, but one that is easy to correct. Note that a clustered columnstore index does not have any options that allow for order to be specified. It is up to the user to make this determination and implement it by following a process similar to this:
- Create a new table.
- Create a clustered index on the column that the table should be ordered by.
- Insert data in the order of the most significant dimension (typically date/time).
- Create the clustered columnstore index and drop the clustered B-Tree as part of its creation.
- When executing data loads, continue to insert data in the same order.
This process will create a columnstore index that is ordered solely by its most critical column and continue to maintain that order indefinitely. Consider this order to be analogous to the key columns of a classic clustered index. This may seem to be a very roundabout process, but it works effectively. Once created, the columnstore index can be inserted into using whatever key order was originally defined.
The lack of order in fact_order_BIG_CCI can be illustrated with a simple query:
|
SET STATISTICS IO ON; GO SELECT SUM([Quantity]) FROM dbo.fact_order_BIG_CCI WHERE [Order Date Key] >= ‘2016/01/01′ AND [Order Date Key] < ‘2016/02/01′; |
The results return relatively quickly, but the IO details tell us something is not quite right here:
Note that 22 segments were read, and one was skipped, despite the query only looking for a single month of data. Realistically, with many years of data in this table, no more than a handful of segments should need to be read in order to satisfy such a narrow query. As long as the date values searched for appear in a limited set of rowgroups, then the rest can be automatically ignored.
With this mistake identified, let’s drop fact_order_BIG_CCI and recreate it by following this set of steps instead:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
DROP TABLE dbo.fact_order_BIG_CCI; CREATE TABLE dbo.fact_order_BIG_CCI ( [Order Key] [bigint] NOT NULL, [City Key] [int] NOT NULL, [Customer Key] [int] NOT NULL, [Stock Item Key] [int] NOT NULL, [Order Date Key] [date] NOT NULL, [Picked Date Key] [date] NULL, [Salesperson Key] [int] NOT NULL, [Picker Key] [int] NULL, [WWI Order ID] [int] NOT NULL, [WWI Backorder ID] [int] NULL, [Description] [nvarchar](100) NOT NULL, [Package] [nvarchar](50) NOT NULL, [Quantity] [int] NOT NULL, [Unit Price] [decimal](18, 2) NOT NULL, [Tax Rate] [decimal](18, 3) NOT NULL, [Total Excluding Tax] [decimal](18, 2) NOT NULL, [Tax Amount] [decimal](18, 2) NOT NULL, [Total Including Tax] [decimal](18, 2) NOT NULL, [Lineage Key] [int] NOT NULL); CREATE CLUSTERED INDEX CCI_fact_order_BIG_CCI ON dbo.fact_order_BIG_CCI ([Order Date Key]); INSERT INTO dbo.fact_order_BIG_CCI SELECT [Order Key] + (250000 * ([Day Number] + ([Calendar Month Number] * 31))) AS [Order Key] ,[City Key] ,[Customer Key] ,[Stock Item Key] ,[Order Date Key] ,[Picked Date Key] ,[Salesperson Key] ,[Picker Key] ,[WWI Order ID] ,[WWI Backorder ID] ,[Description] ,[Package] ,[Quantity] ,[Unit Price] ,[Tax Rate] ,[Total Excluding Tax] ,[Tax Amount] ,[Total Including Tax] ,[Lineage Key] FROM Fact.[Order] CROSS JOIN Dimension.Date WHERE Date.Date <= ‘2013-04-10′ ORDER BY [Order].[Order Date Key]; CREATE CLUSTERED COLUMNSTORE INDEX CCI_fact_order_BIG_CCI ON dbo.fact_order_BIG_CCI WITH (MAXDOP = 1, DROP_EXISTING = ON); |
Note that only three changes have been made to this code:
- A clustered B-tree index is created prior to any data being written to it.
- The
INSERTquery includes anORDERBYso that data is ordered by[Order Date Key]as it is added to the columnstore index. - The clustered B-tree index is swapped for the columnstore index at the end of the process.
When complete, the resulting table will contain the same data as it did at the start of this article, but physically ordered to match what makes sense for the underlying data set. This can be verified by rerunning the following query:
|
SELECT SUM([Quantity]) FROM dbo.fact_order_BIG_CCI WHERE [Order Date Key] >= ‘2016-01-01′ AND [Order Date Key] < ‘2016-02-01′; |
The results show significantly improved performance:
This time, only one segment was read, and 22 were skipped. Reads are a fraction of what they were earlier. This is a significant improvement and allows us to make the most out of a columnstore index.
The takeaway of this experiment is that order matters in a columnstore index. When building a columnstore index, ensure that order is created and maintained for whatever column will be the most common filter by:
- Order the data in the initial data load. This can be accomplished by either:
- Creating a clustered B-tree index on the ordering column, populating all initial data, and then swapping it with a columnstore index.
- Create the columnstore index first, and then insert data in the correct order of the ordering column.
- Insert new data into the columnstore index using the same order every time.
Typically, the correct data order will be ascending, but do consider this detail when creating a columnstore index. If for any reason descending would make sense, be sure to design index creation and data insertion to match that order. The goal is to ensure that as few rowgroups need to be scanned as possible when executing an analytic query. When data is inserted out-of-order, the result will be that more rowgroups need to be scanned in order to fulfill that query. This may be viewed as a form of fragmentation, even though it does not fit the standard definition of index fragmentation.
Partitioning & Clustered Columnstore Indexes
Table partitioning is a natural fit for a large columnstore index. For a table that can contain row counts in the billions, it may become cumbersome to maintain all of the data in a single structure, especially if reporting needs rarely access older data.
A classic OLAP table will have both newer and older data. If common reporting queries only access a recent day, month, quarter, or year, then maintaining the older data in the same place may be unnecessary. Equally important is the fact that in an OLAP data store, older data typically does not change. If it does, it’s usually the result of software releases or other one-off operations that fall within the bounds of our world.
Table partitioning places data into multiple filegroups within a database. The filegroups can then be stored in different data files in whatever storage locations are convenient. This paradigm provides several benefits:
- Partition Elimination: Similar to rowgroup elimination, partition elimination allows partitions with unneeded data to be skipped. This can further improve performance on a large columnstore index.
- Faster Migrations: If there is a need to migrate a database to a new server or SQL Server version, then older partitions can be backed up and copied to the new data source ahead of the migration. This reduces the downtime incurred by the migration as only active data needs to be migrated during the maintenance/outage window.
Similarly, partition switching can allow for data to be moved between tables exceptionally quickly.
- Partitioned Database Maintenance: Common tasks such as backups and index maintenance can be targeted at specific partitions that contain active data. Older partitions that are static and no longer updated may be skipped.
- No Code Changes: Music to the ears of any developer: Table partitioning is a database feature that is invisible to the consumers of a table’s data. Therefore, the code needed to retrieve data before and after partitioning is added will be the same.
- Partition Column = Columnstore Order Column: The column that is used to organize the columnstore index will be the same column used in the partition function, making for an easy and consistent solution.
The fundamental steps to create a table with partitioning are as follows:
- Create filegroups for each partition based on the columnstore index ordering column.
- Create database files within each filegroup that will contain the data for each partition within the table.
- Create a partition function that determines how the data will be split based on the ordering/key column.
- Create a partition schema that binds the partition function to a set of filegroups.
- Create the table on the partition scheme defined above.
- Proceed with table population and usage as usual.
The example provided in this article can be recreated using table partitioning, though it is important to note that this is only one way to do this. There are many ways to implement partitioning, and this is not intended to be an article about partitioning, but instead introduce the idea that columnstore indexes and partitioning can be used together to continue to improve OLAP query performance.
Create New Filegroups and Files
Partitioned data can be segregated into different file groups and files. If desired, then a script similar to this would take care of the task:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
ALTER DATABASE WideWorldImportersDW ADD FILEGROUP WideWorldImportersDW_2013_fg; ALTER DATABASE WideWorldImportersDW ADD FILEGROUP WideWorldImportersDW_2014_fg; ALTER DATABASE WideWorldImportersDW ADD FILEGROUP WideWorldImportersDW_2015_fg; ALTER DATABASE WideWorldImportersDW ADD FILEGROUP WideWorldImportersDW_2016_fg; ALTER DATABASE WideWorldImportersDW ADD FILEGROUP WideWorldImportersDW_2017_fg; ALTER DATABASE WideWorldImportersDW ADD FILE (NAME = WideWorldImportersDW_2013_data, FILENAME = ‘C:\SQLData\WideWorldImportersDW_2013_data.ndf’, SIZE = 200MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1GB) TO FILEGROUP WideWorldImportersDW_2013_fg; ALTER DATABASE WideWorldImportersDW ADD FILE (NAME = WideWorldImportersDW_2014_data, FILENAME = ‘C:\SQLData\WideWorldImportersDW_2014_data.ndf’, SIZE = 200MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1GB) TO FILEGROUP WideWorldImportersDW_2014_fg; ALTER DATABASE WideWorldImportersDW ADD FILE (NAME = WideWorldImportersDW_2015_data, FILENAME = ‘C:\SQLData\WideWorldImportersDW_2015_data.ndf’, SIZE = 200MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1GB) TO FILEGROUP WideWorldImportersDW_2015_fg; ALTER DATABASE WideWorldImportersDW ADD FILE (NAME = WideWorldImportersDW_2016_data, FILENAME = ‘C:\SQLData\WideWorldImportersDW_2016_data.ndf’, SIZE = 200MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1GB) TO FILEGROUP WideWorldImportersDW_2016_fg; ALTER DATABASE WideWorldImportersDW ADD FILE (NAME = WideWorldImportersDW_2017_data, FILENAME = ‘C:\SQLData\WideWorldImportersDW_2017_data.ndf’, SIZE = 200MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1GB) TO FILEGROUP WideWorldImportersDW_2017_fg; |
The file and filegroup names are indicative of the date of the data being inserted into them. Files can be placed on different types of storage or in different locations, which can assist in growing a database over time. It can also allow for faster storage to be used for more critical data, whereas slower/cheaper storage can be used for older/less-used data.
Create a Partition Function
The partition function tells SQL Server on what boundaries to split data. For the example presented in this article, [Order Date Key], a DATE column will be used for this task:
|
CREATE PARTITION FUNCTION fact_order_BIG_CCI_years_function (DATE) AS RANGE RIGHT FOR VALUES (‘2014-01-01′, ‘2015-01-01′, ‘2016-01-01′, ‘2017-01-01′); |
The result of this function will be to split data into 5 ranges:
Date < 2014-01-01
Date >= 2014-01-01 & Date < 2015-01-01
Date >= 2015-01-01 & Date < 2016-01-01
Date >= 2016-01-01 & Date < 2017-01-01
Date >= 2017-01-01
Create a Partition Scheme
The partition scheme tells SQL Server where data should be physically stored, based on the function defined above. For this demo, a partition scheme such as this will give us the desired results:
|
CREATE PARTITION SCHEME fact_order_BIG_CCI_years_scheme AS PARTITION fact_order_BIG_CCI_years_function TO (WideWorldImportersDW_2013_fg, WideWorldImportersDW_2014_fg, WideWorldImportersDW_2015_fg, WideWorldImportersDW_2016_fg, WideWorldImportersDW_2017_fg); |
Each date range defined above will be assigned to a filegroup, and therefore a database file.
Create the Table
All steps performed previously to create and populate a large table with a columnstore index are identical, except for a single line within the table creation:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE dbo.fact_order_BIG_CCI ( [Order Key] [bigint] NOT NULL, [City Key] [int] NOT NULL, [Customer Key] [int] NOT NULL, [Stock Item Key] [int] NOT NULL, [Order Date Key] [date] NOT NULL, [Picked Date Key] [date] NULL, [Salesperson Key] [int] NOT NULL, [Picker Key] [int] NULL, [WWI Order ID] [int] NOT NULL, [WWI Backorder ID] [int] NULL, [Description] [nvarchar](100) NOT NULL, [Package] [nvarchar](50) NOT NULL, [Quantity] [int] NOT NULL, [Unit Price] [decimal](18, 2) NOT NULL, [Tax Rate] [decimal](18, 3) NOT NULL, [Total Excluding Tax] [decimal](18, 2) NOT NULL, [Tax Amount] [decimal](18, 2) NOT NULL, [Total Including Tax] [decimal](18, 2) NOT NULL, [Lineage Key] [int] NOT NULL) ON fact_order_BIG_CCI_years_scheme([Order Date Key]); |
Note the final line of the query that assigns the partition scheme created above to this table. When data is written to the table, it will be written to the appropriate data file, depending on the date provided by [Order Date Key].
Testing Partitioning
The same query used to test a narrow date range can illustrate the effect that table partitioning can have on performance:
|
SELECT SUM([Quantity]) FROM dbo.fact_order_BIG_CCI WHERE [Order Date Key] >= ‘2016-01-01′ AND [Order Date Key] < ‘2016-02-01′; |
The following is the IO for this query:
Instead of reading one segment and skipping 22 segments, SQL Server read one segment and skipped two. The remaining segments reside in other partitions and are automatically eliminated before reading from the table. This allows a columnstore index to have its growth split up into more manageable portions based on a time dimension. Other dimensions can be used for partitioning, though time is typically the most natural fit.
Final Notes on Partitioning
Partitioning is an optional step when implementing a columnstore index but may provide better performance and increased flexibility with regards to maintenance, software releases, and migrations.
Even if partitioning is not implemented initially, a table could be created after-the-fact and data migrated into it from the original table. Data movement such as this could be challenging in an OLTP environment, but in an OLAP database where writes are isolated, it is possible to use that period of no change to create, populate, and swap to a new table with no outage to the reporting applications that use the table.
Avoid Updates
This is worth a second mention: Avoid updates at all costs! Columnstore indexes do not treat updates efficiently. Sometimes they will perform well, especially against smaller tables, but against a large columnstore index, updates can be extremely expensive.
If data must be updated, structure it as a single delete operation followed by a single insert operation. This will take far less time to execute, cause less contention, and consume far fewer system resources.
The fact that updates can perform poorly is not well documented, so please put an extra emphasis on this fact when researching the use of columnstore indexes. If a table is being converted from a classic rowstore to a columnstore index, ensure that there are no auxiliary processes that update rows outside of the standard data load process.
Query Fewer Columns
Because data is split into segments for each column in a rowgroup, querying fewer columns means that less data needs to be retrieved in order to satisfy the query.
If a table contains 20 columns and a query performs analytics on 2 of them, then the result will be that 90% of the segments (for other columns) can be disregarded.
While a columnstore index can service SELECT * queries somewhat efficiently due to their high compression-ratio, this is not what a columnstore index is optimized to do. Like with standard clustered indexes, if a report or application does not require a column, then leave it out of the query. This will save memory, speed up reports, and make the most of columnstore indexes, which are optimized for queries against large row counts rather than large column counts.
Columnstore Compression vs. Columnstore Archive Compression
SQL Server provides an additional level of compression for columnstore indexes called Archive Compression. This shrinks the data footprint of a columnstore index further but incurs an additional CPU/duration cost to read the data.
Archive compression is meant solely for older data that is accessed infrequently and where the storage footprint is a concern. This is an important aspect of archive compression: Only use it if storage is limited, and reducing the data footprint is exceptionally beneficial. Typically, standard columnstore index compression will shrink data enough that additional savings may not be necessary.
Note that if a table is partitioned, compression can be split up such that older partitions are assigned archive compression, whereas those partitions with more frequently accessed data are assigned standard columnstore compression.
For example, the following illustrates the storage footprint of the table used in this article:
23.1 million rows are squeezed into 108MB. This is exceptional compression compared to the OLTP variant:
That is a huge difference! The columnstore index reduced the storage footprint from 5GB to 100MB. In a table where columns have frequently repeated values, expect to see exceptional compression ratios such as this. The less fragmented the columnstore index, the smaller the footprint becomes, as well. This columnstore index has been targeted with quite a bit of optimization throughout this article, so its fragmentation at this point in time is negligible.
For demonstration purposes, archive compression will be applied to the entire columnstore index using the following index rebuild statement:
|
ALTER INDEX CCI_fact_order_BIG_CCI ON dbo.fact_order_BIG_CCI REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE, ONLINE = ON); |
Note that the only difference is that the data compression type has been changed from columnstore to columnstore_archive. The following are the storage metrics for the table after the rebuild completes:
The data size has been reduced by another 25%, which is very impressive!
Archive compression is an excellent way to reduce storage footprint on data that is either:
- Accessed infrequently
- Can tolerate potentially slower execution times.
Only implement it, though, if storage is a concern and reducing data storage size is important. If using archive compression, consider combining it with table partitioning to allow for compression to be customized based on the data contained within each partition. Newer partitions can be targeted with standard columnstore compression, whereas older partitions can be targeted with archive compression.
Conclusion
The organization of data as it is loaded into a columnstore index is critical for optimizing speed. Data that is completely ordered by a common search column (typically a date or datetime) will allow for rowgroup elimination to occur naturally as the data is read. Similarly, querying fewer columns can ensure that segments are eliminated when querying across rowgroups. Lastly, implementing partitioning allows for partition elimination to occur, on top of rowgroup and segment elimination.
Combining these three features will significantly improve OLAP query performance against a columnstore index. In addition, scalability will be significantly improved as the volume of data needed to service a query will only ever be massive if there is a clear need to pull massive amounts of data. Otherwise, standard reporting needs that manage daily, weekly, monthly, quarterly, or annual analytics will not need to query any more data than is needed to return their results.