Monitor Data-tier Applications
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
A data-tier application (DAC) can be monitored from the Utility Explorer and Object Explorer in SQL Server Management Studio (SSMS), along with system views and tables. In addition, all objects in the database contained in the DAC can be monitored using standard database and Database Engine monitoring techniques.
If you deploy a DAC to a managed instance of the Database Engine, information about the deployed DAC is incorporated into the SQL Server Utility the next time the utility collection set is sent from the instance to the utility control point. You can then view basic health information about the DAC by using the Management Studio Utility Explorer.
The SSMS Object Explorer displays basic configuration information about each DAC deployed to an instance of the Database Engine, regardless of whether the instance is managed in the SQL Server Utility. Also, the database associated with a deployed DAC can be monitored using the same procedures for monitoring any database.
The Deployed Data-tier Applications detail page in the Management Studio Utility Explorer displays a dashboard that reports the resource utilization of all DACs that have been deployed to managed instances of the Database Engine. The top pane of the details page lists each deployed DAC with visual indicators showing whether their utilization of CPU and file resources are outside the policies defined for the SQL Server Utility. If you select any DAC in the list view, further details are displayed in tabs in the bottom pane of the page. For more information about the information presented on the details page, see Deployed Data-tier Application Details (SQL Server Utility).
After using the Deployed Data-tier Applications detail page to quickly identify any DACs that are either under-utilizing or stressing their hardware resource, you can make plans to address any problems. Multiple DACs that are not fully utilizing their current hardware resources could be consolidated to a single server, freeing some of the servers for other uses. If a DAC is stressing the resources on its current server, the DAC can be moved to a larger server, or additional resources can be added to the current server.
The minimum and maximum limits for resource usage are defined by application monitoring policies defined in the Utility Administration details page. Database administrators can tailor the policies to match the limits established by their organizations. For example, one company might set 75% as the maximum CPU utilization for a DAC, while another company might set the maximum at 80%. For more information about setting application monitoring policies, see Utility Administration (SQL Server Utility).
To view the Deployed Data-tier Applications detail page:
Select the View/Utility Explorer menu.
Connect the Utility Explorer to the utility control point (UCP).
Select the View/Utility Explorer Details menu.
Select the Deployed Data-tier Applications node in the Utility Explorer.
The information in the Deployed Data-tier Applications detail page comes from the data in the utility management data warehouse, which defaults to collecting the data every 15 minutes. The interval can also be tailored using the Utility Administration details page.
The SSMS Object Explorer displays basic configuration information about each DAC deployed to an instance of the Database Engine. This includes both managed instances that have been enrolled in the SQL Server Utility, and stand-alone instances that cannot be viewed in the Utility Explorer.
To view the details of a DAC deployed to an instance of the Database Engine:
Select the View/Object Explorer menu.
Connect to the instance of the Database Enginefrom the Object Explorer pane.
Select the View/Object Explorer Details menu.
Select the server node in Object Explorer that maps to the instance, and then navigate to the Management\Data-tier Applications node.
The list view in the top pane of the details page lists each DAC deployed to the instance of the Database Engine. Select a DAC to display the information in the detail pane at the bottom of the page.
The right-click menu of the Data-tier Applications node is also used to deploy a new DAC or delete an existing DAC.
The msdb.dbo.sysdac_history_internal system table records the success or failure of all DAC management actions performed on an instance of the Database Engine. The table records the time each action occurred, and which login initiated the action. For more information, see sysdac_history_internal (Transact-SQL).
The DAC system views report basic catalog information. For more information, see Data-tier Application Views (Transact-SQL).