You design a Business Intelligence (BI) solution by using SQL Server 2008. Your solution includes relational and analysis services.
The solution has a cube that is queried by more than 650 users. During peak hours, more than 100 active connections are open on the cube at any given time. Users connect to and query the cube by using custom-built applications. You need to view the connection details and the application name that is used to connect to the cube of all users. What should you do?
A.
Use the Resource Governor.
B.
Use the Database Tuning Advisor.
C.
Use the Analysis Services performance counters.
D.
Prepare a report by using a dynamic management view.
Explanation:
Tip: "to view the connection details" = "dynamic management view"http://msdn.microsoft.com/en-us/library/ms188754.aspx
Dynamic Management Views and Functions (Transact-SQL)
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
Important
Dynamic management views and functions return internal, implementation-specific state data. Their schemas and the data they return may change in future releases of SQL Server. Therefore, dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in this release. For example, in future releases of SQL Server, Microsoft may augment the definition of any dynamic management view by adding columns to the end of the column list. We recommend against using the syntax SELECT * FROM dynamic_management_view_name in production code because the number of columns returned might change and break your application.There are two types of dynamic management views and functions:
– Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
– Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.Querying Dynamic Management Views
Dynamic management views can be referenced in Transact-SQL statements by using two-part, three-part, or four-part names. Dynamic management functions on the other hand can be referenced in Transact-SQL statements by using either two-part or three-part names. Dynamic management views and functions cannot be referenced in Transact-SQL statements by using one-part names.
All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. When you use a dynamic management view or function, you must prefix the name of the view or function by using the sys schema. For example, to query the dm_os_wait_stats dynamic management view, run the following query:SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats;
GO