SQL Server Monitoring (monitoring) architecture

SQL Server Monitoring (monitoring) Architecture

In daily management of SQL Server, having SQL Server run efficiently and with good performance is what DBAs need to do. DBAs need to understand the daily performance, analyze and tune performance, and monitor the deployment of the on-line environment. Monitoring (monitoring) is a major theme of the SQL Server database engine, and it is necessary to understand the monitoring architecture of the entire database engine and how to monitor it comprehensively. The author combines the introduction of MSDN with my own understanding of the architecture of the monitoring (monitoring) and monitoring events (monitoring events).

Monitoring (Monitoring )

Monitoring (monitoring) topics include:

in SQL Server monitoring SQL Server in utilities instance of SQL Server Utility )

describes the monitoring features in the SQL Server utility.

LogFile Viewer (log file Viewer )

describes methods that display record information about SQL Server components.

Monitor Resource Usage (monitoring Resource usage )

contains information about using Windows System Monitor to track resource usage in SQL Server.

Monitoring Event (Monitoring events )

contains information about using SQL Server Profiler and SQL Trace to monitor SQL Server events.

data collection (Collection )

contains information about using the SQL Server Data Collector to obtain and save data that is collected from multiple sources.

SQL Server Extended Events (SQL Server Extended events )

in SQL Server 2008 and later versions, you can use extended events to troubleshoot performance issues. It is an event infrastructure that applies to server systems. Extended events can further explore how SQL Server works internally, and can be used in complex troubleshooting scenarios.

Monitoring Events (monitoringevents )

Monitoring events (monitoring events) mainly include:

SQL Trace (SQL trace )

Microsoft SQL Server provides a Transact-SQL system stored procedure to create a trace of an instance of the SQL Server database engine. Instead of using SQL Server Profiler, you can use these system stored procedures to manually create traces from your own applications. This enables you to write custom applications for the specific needs of your enterprise.

SQL Server Profiler

Microsoft SQL Server Profiler is a graphical user interface for SQL tracing that is used to monitor the database engine or instances of analysis Services. You can capture data about each event and save it to a file or table for later analysis. For example, you can monitor your production environment to see which stored procedures affect performance because of slow execution.

Incident Notification (event notifications )

Describes an event notification that can send service Broker information about server and database events as a special category database object.

SQL Server provides SQL trace (SQL Trace) and event notification (notifications) To monitor events that occur in the database engine.

By logging specified events,SQL tracing can help you troubleshoot performance issues, audit database activity, collect sample data for test environments, debug Transact-SQL statements and stored procedures, and collect data for profiling tools. SQL Trace runs can be accessed through SQL Server Profiler(a graphical user interface) or Transact-SQL system stored procedures entered at the command line, which captures events when an event occurs in the database engine. The captured event is an instance of an event class that you choose to monitor in a process called a trace. Each event class includes an event name and multiple data columns that describe the properties of the event class that occurred in a particular case. When an event in the specified event class in the trace occurs, the SQL trace logs the event name and associated data to the trace file.

SQL tracing is typically accessed through SQL Server Profiler. SQL tracing can also be accessed using Transact-SQL system stored procedures. SQL Server Profiler can use all of the event capture features of SQL tracing and Add trace table information, save trace definitions as templates, extract query plans and deadlock events as separate XML files, and replay trace results for diagnostic and optimization.

Event Notifications Send information about many of the same events captured by SQL Trace to the service Broker services. However, event notifications are different from tracing, which can be used to respond to events within SQL Server to perform operations. Because event notifications are executed asynchronously, these operations do not occupy any resources defined by the immediate transaction.

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1585941

SQL Server Monitoring (monitoring) architecture