Page 165 - DCAP508_DATABASE_ADMINISTRATION

Unit 11: Monitoring SQL Server for Performance

events occurring on the Windows NT 4.0 and Windows 2000 system as a whole, as well as Notes
events in SQL Server, SQL Server Agent, and full-text search.

5. sp_who: Reports snapshot information about current SQL Server users and processes,
including the currently executing statement and whether the statement is blocked. This is
a Transact-SQL alternative to viewing user activity in the current activity window in SQL
Server Enterprise Manager.
6. sp_lock: Reports snapshot information about locks, including the object ID, index ID, type
of lock, and type or resource to which the lock applies. This is a Transact-SQL alternative
to viewing lock activity in the current activity window in SQL Server Enterprise Manager.

7. sp_spaceused: Displays an estimate of the current amount of disk space used by a table (or
a whole database). This is a Transact-SQL alternative to viewing database usage in SQL
Server Enterprise Manager.
8. sp_monitor: Displays statistics, including CPU usage, I/O usage, and the amount of time
idle since sp_monitor was last executed.
9. DBCC statements: Enables you to check performance statistics and the logical and physical
consistency of a database.

10. Built-in functions: Display snapshot statistics about SQL Server activity since the server
was started; these statistics are stored in predefined SQL Server counters. For example,
@@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; and @@PACKET_ERRORS contains the number of network packets occurring
on SQL Server connections.

11. SQL Profiler stored procedures and functions: Use Transact-SQL stored procedures to
gather SQL Profiler statistics.

12. Trace flags: Display information about a specific activity within the server and are used to
diagnose problems or performance issues (for example, deadlock chains).
13. Simple Network Management Protocol (SNMP): Simple Network Management Protocol
(SNMP) is an application protocol that offers network management services. Using SNMP,
you can monitor an instance of SQL Server across different platforms (for example, Windows
NT 4.0, Windows 98, and UNIX). With SQL Server and the Microsoft SQL Server Management
Information Base (MSSQL-MIB), you can use SNMP applications to monitor the status of
SQL Server installations. You can monitor performance information, access databases,
and view server and database configuration parameters.
We will study about all these tools for monitoring performance in the detail in the next unit.

Task Name any five monitoring tools and list its main functions
Using SQL Profiler System Stored Procedures

The key difference between the two main monitoring tools, SQL Profiler and System Monitor,
is that SQL Profiler monitors engine events while System Monitor monitors resource usage
associated with server processes. For example, SQL Profiler can be used to monitor deadlocks
events, including the users and objects involved in the deadlock. System Monitor can be used to
monitor the total number of deadlocks occurring in a database or on a specific object.

LOVELY PROFESSIONAL UNIVERSITY 159