监控 SQL Server 的运行状况（Monitor the running status of SQL Server）.doc

监控 SQL Server 的运行状况（Monitor the running status of SQL Server） Appendix A: monitor the running status of SQL Server Open the low bandwidth view Language filter: all Visual Basic C # C + + J # JScript XAML F # Appendix A: monitor the running status of SQL Server Microsoft SQL Server 2005 provides tools to monitor databases. One way to do this is to dynamically manage views. The server status information returned by the dynamic management view (DMV) and the dynamic management function (DMF) can be used to monitor the running status, diagnostic problems, and optimization of server instances. The routine server dynamic management objects include: Dm_db_ * : database and database objects Dm_exec_ * : executes user code and associated connections Dm_os_ * : memory, lock, and timing Dm_tran_ * : transaction and isolation Dm_io_ * : network and disk input/output This section describes some of the common queries that run on these dynamic management views and functions to monitor SQL Server performance. The example query You can run the following queries to get all the DMV and DMF names: Copy the code SELECT * FROM sys. System_objects WHERE the name LIKE dm_ % The ORDER BY name Monitoring CPU bottlenecks CPU bottlenecks are usually caused by the following reasons: the query plan is not optimal, poorly configured, poorly designed, or insufficient hardware resources. The following common queries can help you determine what causes CPU bottlenecks. The following query gives you an insight into what batch or process of the current cache consumes most of the CPU resources. Copy the code SELECT the TOP 50 The SUM (qs) total_worker_time) AS total_cpu_time, The SUM (qs) execution_count) AS total_execution_count, COUNT (*) AS number_of_statements, Qs. Sql_handle The FROM sys. Dm_exec_query_stats AS qs GROUP BY qs. Sql_handle The ORDER BY the SUM (qs) total_worker_time DESC) The following query shows the total CPU utilization of the cache plan (with the SQL text). Copy the code The SELEC