DBCC Commands for SQL Server Analysis Service 2016

By: Scott Murray | Comments | Related: > SQL Server 2016

Problem

I hear that SQL Server Analysis Service (SSAS) 2016 now includes a DBCC command; what does it do and how do I execute it?

Solution

The SQL Server DBCC statement has finally come to Analysis Services. With the release of CTP preview of SQL 2016, a new Database Consistency Checker has been added to the SSAS XMLA command set. Of course much of the SSAS hype with 2016 centers around Tabular models and DAX language enhancements and improvements. However, tucked in with all the fun DAX functionality is the new ability to run a consistency check against SSAS objects. I should note that SQL 2016 is now only in Preview mode, so some items may change between now and General Availability.

The DBCC command is available for use against both tabular and multidimensional SSAS databases and will check for consistency and corruption within the database. Specifically, in tabular mode, the DBCC command looks for corruption in objects, segments, statistics, compression, dictionaries, and column stats while in multidimensional mode, the DBCC checks for issues with indexes and statistics, validates metadata, and seeks out physical corruption.

We will use the Adventure Works databases as the basis for our SSAS example. The 2014 versions (2016 sample databases are not available as of yet) of the regular and data warehouse databases, along with the SSAS cube database backups are available on Codeplex: https://msftdbprodsamples.codeplex.com/releases/view/125550. Once you download and install the SQL Server and SSAS databases, we will subsequently upgrade the SSAS database to SQL 2016. Then we can use SQL Server 2016 Management Studio to execute our DBCC commands.

Running DBCC SSAS command

In order to run the SSAS DBCC command you must be an Administrator on your server or a SSAS database administrator. The first steps to executing the DBCC command is to open SQL 2016 Management Studio and connect to the SQL 2016 SSAS database. Note that the DBCC command can only be run against a SQL 2016 SSAS databases and is not backward compatible.

The basic syntax for the DBCC command is shown in the below code section; notice XMLA is used. Also, take note that we need to provide the command with the object IDs (not name) that we would like to check, you must provide the DatabaseID, but you can also specify CubeID, MeasureGroupID, and PartitionID to run the DBCC against just those objects. If you do not specify CubeID, MeasureGroupID, and PartitionID, you can just delete those lines.

<DBCC xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
	<Object>
		<DatabaseID></DatabaseID>
		<CubeID></CubeID>
		<MeasureGroupID></MeasureGroupID>
		<PartitionID></PartitionID>
	</Object>
</DBCC>

The below two screen prints show how you can get object IDs by right mouse clicking the object in management studio and then select properties.

Thus based on the above screen prints, we are able to develop the below DBCC check against our local SSAS Adventure works cube. This statement is run in Management Studio while connected to a SSAS instance. Be sure you create the query using the XMLA option (and not MDX).

The DBCC statement can be run against any of the 4 levels listed in the statement. As you move higher up the hierarchy, you can remove the lower level objects from the statement. Thus, in the above screen print, we are not running the DBCC statement against a specific partition or a specific measure group. It is only running against the Adventure Works DW cube.

If no issues are found, then the results of the DBCC will look similar to the below screen prints.

The Results tab will display an empty XML results set.

The Messages tab provides details on what items were checked.

Types of DBCC Errors for SSAS

Of course the small AdventureWorks SSAS database is squeaky clean and does not experience any errors. However, errors may be returned from the DBCC statement. Here is how you handle those errors:

Most Severe:

Table metadata corruption > resolution = restore from a backup or redeploy project after deleting the SSAS database
Corruption in the storage layer > resolution = restore from a backup or redeploy project after deleting the SSAS database
Table statistics are corrupt > resolution = restore from a backup or redeploy project after deleting the SSAS database
Partition segment is corrupted> resolution = restore from a backup or redeploy project after deleting the SSAS database

Moderately Server:

System table is missing > resolution = reprocess object and related dependent objects
Partition segment statistics corrupted > resolution = reprocess object and related dependent objects
Error occurred during consistency check of the partition > resolution = reprocess object and related dependent objects

Based on the results of the DBCC statement, a cube designer, whether tabular or multidimensional, can easily check if corruption exists within a SSAS database.

Conclusion

New in SSAS 2016, the SSAS DBCC command provides detailed information about potential corruption issues in tabular or multidimensional cubes. The DBCC command can be run at the database, cube, partition, or measure group level and is run via a XMLA query. If errors are reported by the DBCC command, a cube administrator can use the error messages to determine if restoring from a backup or redeploying from project is necessary. Alternately, the errors may just point to requiring a cube to be reprocessed. The SSAS DBCC command is a welcome addition to the SSAS tool set.

Next Steps

Dealing with a Corruption in SSAS - http://blogs.msdn.com/b/karang/archive/2010/08/11/how-to-deal-with-corruption-in-analysis-services.aspx

About the author

Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips