SQL Server Performance Auditing , Profiling , Monitoring , Logging , Troubleshooting Tools

There are a handful of monitoring tools from Microsoft includes Profiler, Sysmon\Perfmon and the Database Engine Tuning Advisor\Index Tuning Wizard which are already well-known.

Activity Monitor to obtain information about SQL Server processes and how these processes affect the current instance of SQL Server. This is similar to Task Manager for Windows that provides overview displays of the percent of processor time, number of waiting tasks, database I/O (megabytes per second), and batch requests (number per second), which is shown with graphical representation. Also the Active User Tasks, Resource waits, Data file I/O and recent expensive queries.

Using SQL Server Management Studio (SSMS) you can monitor the current activity of all defined jobs on an instance of SQL Server by using SQL Server Agent Job Activity Monitor.

You can monitor a mirrored database during a mirroring session to verify whether and how well data is flowing. To set up and manage monitoring for one or more of the mirrored databases on a server instance, you can use either Database Mirroring Monitor or the sp_dbmmonitor system stored procedures.

Replication monitor provides details on the status of SQL Server replication and allows you to configure replication alerts.

SQL Server Profiler tracks engine process events, such as the start of a batch or a transaction, enabling you to monitor server and database activity (for example, deadlocks, fatal errors, or login activity). You can capture SQL Server Profiler data to a SQL Server table or a file for later analysis, and you can also replay the events captured on SQL Server step by step, to see exactly what happened.

Monitoring the logs is also another tool to provide you the lead to see the activity on the SQL Server, the information in these event logs allows you to view informational, auditing, warning, and error messages that can help you troubleshoot SQL Server problems.

In addition to the SQL Server error log Windows application/system/security event log provides an overall picture of events occurring on the Windows Server and Windows operating systems as a whole, as well as events in SQL Server, SQL Server Agent, and full-text search. It contains information about events in SQL Server that is not available elsewhere. You can use the information in the error log to troubleshoot SQL Server-related problems.

The information in SQL Server Agent Logs allows you to view informational, auditing, warning, and error messages that can help you troubleshoot SQL Server Agent problems.

The best way to get server state, Dynamic Management Views & DMFs return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. There are two types of dynamic management views and functions: Server-scoped dynamic management views and functions that requires VIEW SERVER STATE permission on the server. Database-scoped dynamic management views and functions that requires VIEW DATABASE STATE permission on the database. 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.

DataBase Console Commands (DBCC) are commonly used statements by every DBA and many times the users whenever a problem is repoted, these are grouped into 4 categories: Maintenance, Informational, Validation and Miscellaneous.

For many administrative and informational purpose the stored procedures that consists in SQL Server system database 'master'. Few of these are listed below that are commonly used in day-to-day monitoring activity, such as sp_helpdb, sp_helpserver, sp_spaceused and sp_who or sp_who2 (undocumented). There are many more system and DB engine related stored procedures that are used for general maintenance of an instance of SQL Server.

Reference: Above article is a summarized excerpt of the original article on SSQA.net