One new feature of SQL Server Analysis Services (SSAS) 2016 will be well received by DBAs and the SQL Server business intelligence developer community: the database consistency checker (DBCC) CHECKDB command. The command provides the ability to perform a database consistency check against tabular and multidimensional databases. DBCC CHECKDB consists of a single XMLA command that checks for corruption issues across the analysis database or on any individual object. The DBCC command takes an object definition and returns either an empty result set or a detailed error message if corruption is identified for an analysis service object.
To check for index corruption issues in an SSAS multidimensional database, you can execute the DBCC CHECKDB command to:
- validate metadata;
- scan segments to look for physical corruption issues; and
- check segment statistics and indexes.
To check for index corruption issues in an SSAS tabular database, you can execute the DBCC CHECKDB command to:
- check dictionaries;
- validate compression;
- check column statistics; and
- check segment statistics.
Note that an SSAS server restart might be required to pick up any corruption issues that would have occurred within the analysis service database since the time the SSAS instance was last restarted. Just reconnecting to an analysis server instance will not be enough to pick up the changes or to identify new corruption issues if any exist. Hence, it is advisable to perform DBCC checks during the routine maintenance window.
You must be a server administrator (i.e., a member of the server role) to run the DBCC CHECKDB command on an SSAS database or object to identify corruption issues within a tabular or multidimensional database. Figure 1 provides an example of how to execute a DBCC command to run against an SSAS database.
- Open SQL Server Management Studio (SSMS) and connect to a SQL Server Analysis Services 2016 instance.
- Open a new XMLA query window, duplicate the sample code from Figure 1 to perform a DBCC check against the AdventureWorksDW analysis services sample database, which is a tabular or a multidimensional database.
Figure 2 shows an example of how to create a DBCC command that runs against an SSAS database object. In the XMLA query window, duplicate the sample XMLA code from Figure 2 to run a DBCC command against an analysis services object within the analysis service database:
You can view the DBCC output by using SQL Server 2016 Profiler or Analysis Services xEvents. Note that DBCC error messages are not written to a Windows application log or to a MSMDSRV.LOG file.
DBCC checks against analysis services will look for physical and logical data corruption issues that generally occur when orphaned members exist within a segment. However, the DBCC command skips remote, unprocessed or empty partitions. The DBCC command runs in a read transaction mode and partition checks are run in parallel at the same time.
Figure 3 shows sample output generated by the successful execution of the DBCC CHECKDB command on the AdventureWorksDW database.
In the results tab shown above, the DBCC command will show an empty result set if no corruption issues were detected within the analysis service object or in an analysis service database. Figure 4 shows the same output.
In the messages tab shown above, you will see detailed information even though it is not always reliable for smaller analysis services databases. Sometimes, status messages are trimmed to indicate that the DBCC command executed successfully. Figure 5 shows a sample result set. However, as noted above, it is highly recommended to execute the DBCC command after restarting a SSAS instance so it could pick up any corruption errors that could have occurred since the last restart of the SSAS instance.
To trace a DBCC XMLA output, open SQL Server 2016 Profiler and start a new trace, which includes events such as progress report events (e.g., progress report begin, progress report current, progress report end, progress report error). Then execute the DBCC XMLA command in SSMS to see the DBCC output results in detail within SQL Server 2016 Profiler.
SQL Server must-have management tools
SQL Server Management Studio secrets
Restoring a corrupt database