Monitoring Tools and Resources Available in SQL Server

Know the Monitoring Tools and Resources Available in SQL Server
The primary monitoring tools you will use for SQL Server are the Reliability and Performance Monitor and the SQL Server Profiler. But there are other resources also available for monitoring SQL Server. These include:
Query Analyzer: This Query analyzer provides different info according to the template selected based on the usability and requirement i.e TSQL_Duration, TSQL_Count, TSQL_Locks etc.
Activity Monitor: This monitor provides information on current users, processes, and locks, as discussed in “Managing Server Activity”. To display Activity Monitor, use the Object Explorer view to access an instance of the Database Engine. Right-click the Database Engine instance and then select Activity Monitor.
Replication Monitor: This monitor provides details on the status of SQL Server replication and allows you to configure replication alerts. To display Replication Monitor, use the Object Explorer view to access an instance of the Database Engine. Right-click the Replication node and then select Launch Replication Monitor.
SQL Server logs: The information in these event logs allows you to view informational, auditing, warning, and error messages that can help you troubleshoot SQL Server problems. To access the server logs, use the Object Explorer view to access an instance of the Database Engine. Expand the server node and the Management node. Under the Management node, expand the SQL Server Logs node and then double-click the log you want to examine.
Job Activity Monitor: This monitor provides details on the status of SQL Server Agent jobs. To display Job Activity Monitor, use the Object Explorer view to access an instance of the Database Engine. Expand the server node and the SQL Server Agent node, and then double-click Job Activity Monitor.
SQL Server Agent logs: The information in these event logs allows you to view informational, auditing, warning, and error messages that can help you troubleshoot SQL Server Agent problems. To access agent logs, use the Object Explorer view to access an instance of the Database Engine. Expand the server node and the SQL Server Agent node. Under the SQL Server Agent node, expand the Error Logs node and then double-click the log you want to examine.
Note that SQL Server documentation refers to the SQL Server and SQL Server Agent logs as error logs. In their current implementation, however, the logs are more accurately called event logs, which is the terminology used in this chapter. Similar to event logs in Microsoft Windows, these logs in SQL Server contain informational and security messages as well as error messages.
Event logs: The information in the event logs allows you to troubleshoot systemwide problems, including SQL Server and SQL Server Agent problems. To access event logs, click Start, click Administrative Tools, and then select Event Viewer.
DBCC statements: This set of commands allows you to check SQL Server statistics, to trace activity, and to check database integrity.
sp_helpdb: This stored procedure displays information about databases.
sp_helpindex: This stored procedure reports information about indexes on a table or view.
sp_helpserver: This stored procedure provides information in SQL Server instances configured for remote access or replication.
sp_monitor: This stored procedure shows key SQL Server usage statistics, such as CPU idle time and CPU usage.
sp_spaceused: This stored procedure shows an estimate of disk space used by a table, indexed view, or Service Broker queue in the current database.
sp_who: This stored procedure shows a snapshot of current SQL Server users and processes.
sp_who2: This stored procedure shows a snapshot of current SQL Server users and processes with more detailed information.
sys.dm_tran_locks: This dynamic management view shows information about object locks.
Note that the sys.dm_tran_locks view replaces the sp_lock stored procedure.
DMV/DMF: Dynamic management views/functions provided the database and OS level information’s
Example: select * from sys.dm_os_performance_counters  this dmv provides operating system level counters like Buffer cache hit ratio, Page reads/sec, Page life expectancy etc
select * from sys.dm_db_missing_index_details  this dmv provide the missing index details at database level
Built in function/Global variables(tSql): built in functions show the database information based on the usability
@@CPU_BUSY contains the amount of time the CPU has been executing SQL Server code
@@CONNECTIONS contains the number of SQL Server connections or attempted connections
@@PACKET_ERRORS contains the number of network packets occurring on SQL Server connections
Trace Flags (tSQL): Trace flags display information about a specific activity within the server and are used to diagnose problems or performance issues (for example, deadlock chains).
Database Engine Tuning Advisor: Database Engine Tuning Advisor analyzes the performance effects of Transact-SQL statements executed against databases you want to tune. Database Engine Tuning Advisor provides recommendations to add, remove, or modify indexes, indexed views, and partitioning.