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.

DBCC (Database Consistency Check)

DBCC is very important command to check for specific Server/Database physical or logical information.

we have many DBCC commands, we use them as per the requirement and information we need.

some of them are:
1. DBCC INPUTBUFFER(SPID) ---> Where SPID is an integer values which indicate to specific sql server command reunning behind it.
Syntex:
DBCC INPUTBUFFER(77)

Result in Column EventInfo:
select TOP 10 * from sys.objects

The alternative for DBCC INPUTBUFFER is fn_get_sql(sql_handle) which require and sql_handle of that specific spid
Syntex:
First we need to find out sql handle of that spid(77) using below command then we need to pass that sql_handle value to fn_get_sql funciton.
select sql_handle from sys.sysprocesses where spid = 77
select * from ::fn_get_sql(0x01001E0060C9581A40622EB90000000000000000)
Reslut in Column text
select TOP 10 * from sys.objects


SOME OTHER COMMANDS ARE:


2. DBCC CHECKDB ----> Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

i. DBCC CHECKALLOCK
ii. DBCC CHECKTABLE
iii. DBCC CHECKCATALOG
iv: Validates the contents of every indexed view in the database.
v: Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in
the file system using FILESTREAM.
vi: Validates the Service Broker data in the database.

CHECKDB with some Other options (a) DBCC CHECKDB (AdventureWorks2012, NOINDEX);(b) DBCC CHECKDB WITH NO_INFOMSGS; DBCC CHECKDB ('DB Name') WITH NO_INFOMSGS

3. DBCC useroptions ---> Returns the SET options active (set) for the current connection.
4. DBCC SHOW_STATISTICS('tbl_Employee', 'IX_tbl_Employee_EMPID') ---> to check the statistics info of index on a table
5. DBCC SHOWCONTIG ---> to check fragmentation information
6. DBCC OUTPUTBUFFER (71) ---> Input paramter is @@SPID , this command Returns the current output buffer in hexadecimal and ASCII
format for the specified session_id
7. DBCC PROCCACHE ---> Displays information in a table format about the procedure cache.
8. DBCC SQLPERF(LOGSPACE) ---> Displays Log File information of all available database on a given server
Output of this command look like:
Database Name Log Size(MB) Log Space Used (%) Status
TestDB 7.429688 16.41693 0
9. DBCC TRACESTATUS(-1); ---> Displays the status of trace flags.
10. DBCC OPENTRAN: ---> Displays information about the oldest active transaction and the oldest distributed and
nondistributed replicated transactions

There are other DBCC commands also we use these commands as and when required depends on the Usability and requirement.