SQL Server Storage Engine & Tools (SSQA.net)

Do you know how many Monitoring Tools and Resources available in SQL Server?

Monitoring Tools and Resources are essential for performance tuning & better manageability in SQL Server.

Many times the users think that collection and analysis of performance data is not a day-to-day job, also I feel that it is difficult at best to understand and correct the items in a timely manner. There is no doubt that there are many herds of monitoring tools for SQL Server, but they will be effective only if you use them wisely and define the requirement to meet the needs for purpose. The handful of tools from Microsoft includes Profiler, Sysmon\Perfmon and the Database Engine Tuning Advisor\Index Tuning Wizard. 

Similarly 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. As we talked about tools these resources (aka hidden gems) from SQL Server Management Studio (SSMS) and BI Development Studio (BIDS) will leverage the performance monitoring data analysis. Say if you do not have performance problems then it doesn't mean than you need not to collect the monitoring data. As the problem will occur from adding more users or applications to your SQL Server, turn to these products as a means to improve the overall ROI on your platform. When we talk about third party tools then most of the products will have trial versions that can help you to test the product to see whether they meet your needs on building the business application support. As a best practice it is better to mix and match these tools along with freely available Microsoft related tools.

So coming back to the resources that are available for free using SSMS are as follows (these are listed as per my favourites):

• Activity Monitor
  Do you know that Activity Monitor was available back in SQL Server 2000 days too!

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. In order to get information using Activity Monitor then the user must have VIEW SERVER STATE permissions. The shortcut key to open the Activity Monitor from Management Studio you can use CTRL+ALT A and from object explorer right-click the instance name, and then select Activity Monitor.

You can set  the Activity Monitor refresh interval by configuring how often Activity Monitor obtains new information about instance activity. Activity Monitor runs queries on the monitored instance to obtain information for the Activity Monitor display panes. When the refresh interval is set to less than 10 seconds, the time that is used to run these queries can affect server performance. To set the value, right-click Overview, select Refresh Interval, and then select the interval in which Activity Monitor should obtain new instance information.

• Job Activity Monitor

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. Back in SQL Server 2000 days such an activity can only obtained by querying sysjobhistory system table and now similarly SQL Server Agent creates a new session each time the service starts. When a new session is created, the sysjobactivity table in the msdb database is populated with all the existing defined jobs, and this table preserves the last activity for jobs when SQL Server Agent is restarted. Each session records SQL Server Agent normal job activity from the start of the job to its finish. Information about these sessions is stored in the syssessions table of the msdb database.

Similar activity can be monitored using TSQL for the current session by using the stored procedure sp_help_jobactivity.

• Database Mirroring 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.

A database mirroring monitoring job, Database Mirroring Monitor Job, operates in the background, independently of Database Mirroring Monitor. SQL Server Agent calls Database Mirroring Monitor Job at regular intervals, the default is once a minute, and the job calls a stored procedure that updates mirroring status. If you use SQL Server Management Studio to start a mirroring session, Database Mirroring Monitor Job is created automatically. However, if you only use ALTER DATABASE <database_name> SET PARTNER to start mirroring, you must create the job by running a stored procedure.

• 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 Profiler or SQL Trace

I believe no introduction is needed for SQL Server Profiler that 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.

• SQL Server & Windows logs

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. 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. Within that node expand the SQL Server Logs node and then double-click the log you want to examine.

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.

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.

I always refer the users to look at SQLAgent job log whenever a question is posted about job failure and these will allow you to troubleshoot systemwide problems, including SQL Server and SQL Server Agent problems.

• Dynamic Management Views (DMVs)

One of the best and favourite ones for me to get server state, DMVs & 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. Here is another magic from BOL to define more about Dynamic management views and functions that have been organized into the following categories.

Change Data Capture Related Dynamic Management Views	Query Notifications Related Dynamic Management Views
Common Language Runtime Related Dynamic Management Views	Replication Related Dynamic Management Views
Database Mirroring Related Dynamic Management Views	Resource Governor Dynamic Management Views
Database Related Dynamic Management Views	Service Broker Related Dynamic Management Views
Execution Related Dynamic Management Views and Functions	SQL Server Extended Events Dynamic Management Views
Full-Text Search Related Dynamic Management Views	SQL Server Operating System Related Dynamic Management Views
Index Related Dynamic Management Views and Functions	Transaction Related Dynamic Management Views and Functions
I/O Related Dynamic Management Views and Functions	Security Related Dynamic Management Views
Object Related Dynamic Management Views and Functions

Note: Over a period of time the schemas and data returned by DMVs may change in the future releases of SQL Server, such as the DMVs that are new in SQL Server 2008 will not work or return data for 2005 instances.

 

• DataBase Console Command (DBCC) statements

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.

BOL has got vast information on these DBCC statements to refer and few of them are as follows:

Maintenance

DBCC CLEANTABLE	DBCC INDEXDEFRAG
DBCC DBREINDEX	DBCC SHRINKDATABASE
DBCC DROPCLEANBUFFERS	DBCC SHRINKFILE
DBCC FREEPROCCACHE	DBCC UPDATEUSAGE

 

Informational

 

DBCC INPUTBUFFER	DBCC SHOWCONTIG
DBCC OPENTRAN	DBCC SQLPERF
DBCC OUTPUTBUFFER	DBCC TRACESTATUS
DBCC PROCCACHE	DBCC USEROPTIONS
DBCC SHOW_STATISTICS

Validational

 

DBCC CHECKALLOC	DBCC CHECKFILEGROUP
DBCC CHECKCATALOG	DBCC CHECKIDENT
DBCC CHECKCONSTRAINTS	DBCC CHECKTABLE
DBCC CHECKDB

Miscellaneous

 

DBCC dllname (FREE)	DBCC HELP
DBCC FREESESSIONCACHE	DBCC TRACEOFF
DBCC FREESYSTEMCACHE	DBCC TRACEON

A note about context of these DBCC statements that when these are executed then DB engine creates a database snapshot and brings it to a transactionally consistent state. The DBCC command then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped. Also to workout such actions sometimes an internal database snapshot is not required or cannot be created. When this occurs, the DBCC command executes against the actual database. If the database is online, the DBCC command uses table-locking to ensure the consistency of the objects that it is checking. This behavior is the same as if the WITH TABLOCK option were specified.

DMV sys.dm_exec_requests catalog view contains information about the progress and the current phase of execution of the DBCC CHECKDB, CHECKFILEGROUP, and CHECKTABLE commands. The percent_complete column indicates the percentage complete of the command, and the command column reports the current phase of the execution of the command.

• System & DB engine Stored Procedures (master database)

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.

As of now there are many more to refer on tools & resources, so I will continue in upcoming blog posts and this is a good start to bring such a good availability of tools in SQL Server arena