Practically every SQL Server Database Administrator operating in an on-premises environment is familiar with Transact-SQL DBCC statements that serve the role of Database Console Commands. However, there are some additional considerations that should be considered when using them in the context of Azure SQL Database deployments. In this article, I provide an overview of their primary characteristics and explore their role in Database-as-a-Service scenarios.
While the acronym DBCC was originally derived from the term Database Consistency Checker, this designation has been changed to Database Console Commands, in order to reflect much more versatile capabilities available via DBCC statements. Based on these capabilities, DBCC statements can be grouped into the following categories:
◉ Maintenance: Intended to perform database (DBCC SHRINKDATABASE), index and tables (DBCC DBREINDEX, DBCC INDEXDEFRAG, DBCC CLEANTABLE, DBCC UPDATEUSAGE), or storage and cache (DBCC SHRINKFILE, DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE) maintenance tasks.
◉ Validation: Intended to perform validation of the state of databases, their metadata, and indexes (DBCC CHECKDB, DBCC CHECKCATALOG), tables (DBCC CHECKCONSTRAINTS, DBCC CHECKTABLE, and DBCC CHECKIDENT), indexes, filegroups (DBCC CHECKFILEGROUP), as well as allocation of database pages (DBCC CHECKALLOC).
◉ Informational: Intended to collect variety of information, including the most recent statement in a given session (DBCC INPUTBUFFER, DBCC OUTPUTBUFFER), a list of active transactions (DBCC OPENTRAN), content of the procedure cache (DBCC PROCCACHE), current query optimization statistics for a table or indexed view (DBCC SHOW_STATISTICS), fragmentation of data and indexes for a table or view (DBCC SHOWCONTIG), transaction log space usage statistics (DBCC SQLPERF), status of trace flags (DBCC TRACESTATUS), or listing of SET options applicable to the current connection (DBCC USEROPTIONS).
◉ Miscellaneous: Intended to cover other SQL Server-related functionality, such as management of trace flags (DBCC TRACEON and DBCC TRACEOFF), flushing of the distributed query connection cache leveraged by distributed queries targeting an instance of Microsoft SQL Server (DBCC FREESESSIONCACHE), flushing of all unused cache entries from all cashes (DBCC FREESYSTEMCACHE), generating a schema only clone of a database (DBCC CLONEDATABASE), or displaying syntax information of DBCC commands (DBCC HELP). This category also includes the command that allows you to unload an arbitrary extended stored procedure DLL from memory (DLL FREE).
Several of these commands (such as DBCC FREE, DBCC FREESYSTEMCACHE, or DBCC FREESESSIONCACHE) are not available in Azure SQL Database simply because they operate on the SQL Server instance level or are used to supplement features that Azure SQL Database does not support. There are also a few others that provide significant benefits applicable in traditional, on-premises scenarios, which might be potentially less critical when it comes to managed services. One somewhat surprising example of this paradigm is DBCC CHECKDB, which is commonly used in order to address database corruption issues. In managed scenarios, databases are constantly monitored for any errors and unhandled exceptions that indicate potential data integrity issues. Resulting alerts are automatically routed to the engineering teams, which take care of investigation and remediation tasks. For example, Azure SQL Database is capable of detecting lost writes, which historically has been the most common cause of data corruption. Any mismatch between the data on disk and its expected content during a read operation (based on the Log Sequence Number associated with the preceding write) triggers an alert automatically notifying the engineering teams. Another important mechanism that contributes to resiliency of Azure SQL Database is automatic page repair, included in the Premium Tier databases and Standard Tier databases with geo-secondaries. This capability addresses data integrity issues by replacing corrupt pages with valid ones from another database replica. In addition, all databases are, by default, configured to perform page verification by calculating their checksums and storing results in page headers. Customers still have the option to run DBCC CHECKDB on their own, however, considering that the Azure SQL Database engineering teams put significant effort into managing data integrity, it might make sense to limit its frequency (to account for its performance and cost implications).
Functional differences between Azure SQL Database (single database and elastic pools) and Managed Instance affect to some extent DBCC statements they support. In particular, since August 2019, Managed Instance offers the ability to implement global trace flags (460, 2301, 2389, 2390, 2453, 2467, 7471, 8207, 9389,1 0316, and 11024), which status can be controlled and viewed by the corresponding DBCC statements (DBCC TRACEON, DBCC TRACEOFF, and DBCC TRACESTATUS). Global trace flags are set on the server level and are used to customize its characteristics or behavior, such as, for example, triggering auto-update of statistic or indexing internal memory-optimized staging temporal tables (for details regarding their functionality, refer to Microsoft Docs). Note, however, that, as of January 2020, neither Manage Instance nor Azure SQL Database single database or elastic pools support session trace flags.
Despite these few instances of DBCC commands that warrant special considerations, majority of them (including DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKCONSTRAINTS, DBCC CHECKFILEGROUP, DBCC CHECKIDENT, DBCC CHECKTABLE, DBCC CLEANTABLE, DBCC INPUTBUFFER, DBCC OPENTRAN, DBCC PROCCACHE, DBCC SHOW_STATISTICS, DBCC SHRINKDATABASE, DBCC SHRINKFILE, DBCC UPDATEUSAGE, and DBCC USEROPTIONS) are fully supported on Azure SQL Database. There is even one (DBCC FLUSHAUTHCACHE, used for flushing of the database authentication cache containing information about logins and firewall rules for the current database instance) that has been designed specifically for Azure SQL Database.
While the acronym DBCC was originally derived from the term Database Consistency Checker, this designation has been changed to Database Console Commands, in order to reflect much more versatile capabilities available via DBCC statements. Based on these capabilities, DBCC statements can be grouped into the following categories:
◉ Maintenance: Intended to perform database (DBCC SHRINKDATABASE), index and tables (DBCC DBREINDEX, DBCC INDEXDEFRAG, DBCC CLEANTABLE, DBCC UPDATEUSAGE), or storage and cache (DBCC SHRINKFILE, DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE) maintenance tasks.
◉ Validation: Intended to perform validation of the state of databases, their metadata, and indexes (DBCC CHECKDB, DBCC CHECKCATALOG), tables (DBCC CHECKCONSTRAINTS, DBCC CHECKTABLE, and DBCC CHECKIDENT), indexes, filegroups (DBCC CHECKFILEGROUP), as well as allocation of database pages (DBCC CHECKALLOC).
◉ Informational: Intended to collect variety of information, including the most recent statement in a given session (DBCC INPUTBUFFER, DBCC OUTPUTBUFFER), a list of active transactions (DBCC OPENTRAN), content of the procedure cache (DBCC PROCCACHE), current query optimization statistics for a table or indexed view (DBCC SHOW_STATISTICS), fragmentation of data and indexes for a table or view (DBCC SHOWCONTIG), transaction log space usage statistics (DBCC SQLPERF), status of trace flags (DBCC TRACESTATUS), or listing of SET options applicable to the current connection (DBCC USEROPTIONS).
◉ Miscellaneous: Intended to cover other SQL Server-related functionality, such as management of trace flags (DBCC TRACEON and DBCC TRACEOFF), flushing of the distributed query connection cache leveraged by distributed queries targeting an instance of Microsoft SQL Server (DBCC FREESESSIONCACHE), flushing of all unused cache entries from all cashes (DBCC FREESYSTEMCACHE), generating a schema only clone of a database (DBCC CLONEDATABASE), or displaying syntax information of DBCC commands (DBCC HELP). This category also includes the command that allows you to unload an arbitrary extended stored procedure DLL from memory (DLL FREE).
Several of these commands (such as DBCC FREE, DBCC FREESYSTEMCACHE, or DBCC FREESESSIONCACHE) are not available in Azure SQL Database simply because they operate on the SQL Server instance level or are used to supplement features that Azure SQL Database does not support. There are also a few others that provide significant benefits applicable in traditional, on-premises scenarios, which might be potentially less critical when it comes to managed services. One somewhat surprising example of this paradigm is DBCC CHECKDB, which is commonly used in order to address database corruption issues. In managed scenarios, databases are constantly monitored for any errors and unhandled exceptions that indicate potential data integrity issues. Resulting alerts are automatically routed to the engineering teams, which take care of investigation and remediation tasks. For example, Azure SQL Database is capable of detecting lost writes, which historically has been the most common cause of data corruption. Any mismatch between the data on disk and its expected content during a read operation (based on the Log Sequence Number associated with the preceding write) triggers an alert automatically notifying the engineering teams. Another important mechanism that contributes to resiliency of Azure SQL Database is automatic page repair, included in the Premium Tier databases and Standard Tier databases with geo-secondaries. This capability addresses data integrity issues by replacing corrupt pages with valid ones from another database replica. In addition, all databases are, by default, configured to perform page verification by calculating their checksums and storing results in page headers. Customers still have the option to run DBCC CHECKDB on their own, however, considering that the Azure SQL Database engineering teams put significant effort into managing data integrity, it might make sense to limit its frequency (to account for its performance and cost implications).
Functional differences between Azure SQL Database (single database and elastic pools) and Managed Instance affect to some extent DBCC statements they support. In particular, since August 2019, Managed Instance offers the ability to implement global trace flags (460, 2301, 2389, 2390, 2453, 2467, 7471, 8207, 9389,1 0316, and 11024), which status can be controlled and viewed by the corresponding DBCC statements (DBCC TRACEON, DBCC TRACEOFF, and DBCC TRACESTATUS). Global trace flags are set on the server level and are used to customize its characteristics or behavior, such as, for example, triggering auto-update of statistic or indexing internal memory-optimized staging temporal tables (for details regarding their functionality, refer to Microsoft Docs). Note, however, that, as of January 2020, neither Manage Instance nor Azure SQL Database single database or elastic pools support session trace flags.
Despite these few instances of DBCC commands that warrant special considerations, majority of them (including DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKCONSTRAINTS, DBCC CHECKFILEGROUP, DBCC CHECKIDENT, DBCC CHECKTABLE, DBCC CLEANTABLE, DBCC INPUTBUFFER, DBCC OPENTRAN, DBCC PROCCACHE, DBCC SHOW_STATISTICS, DBCC SHRINKDATABASE, DBCC SHRINKFILE, DBCC UPDATEUSAGE, and DBCC USEROPTIONS) are fully supported on Azure SQL Database. There is even one (DBCC FLUSHAUTHCACHE, used for flushing of the database authentication cache containing information about logins and firewall rules for the current database instance) that has been designed specifically for Azure SQL Database.
Posts
0 comments:
Post a Comment