Bài giảng Cơ sở dữ liệu nâng cao - Chapter 8: Monitoring SQL server. Chương này trình bày một số nội dung: The goal of monitoring, performance monitor, dynamic management views, monitoring events, event notifications,...
AMBIENT/
Chủ đề:
Nội dung Text: Bài giảng Cơ sở dữ liệu nâng cao - Chapter 8: Monitoring SQL server
- Monitoring SQL Server
- The Goal of Monitoring
The goal of monitoring databases is to see:
What’s going on inside SQL Server,
How effectively SQL Server is using the server resources
(CPU, Memory, I/O).
The information enables DBA identify abnormal activities
- The Goal of Monitoring
Once you define your monitoring goals you should select
the appropriate tools for monitoring.
The following list describes basic monitoring tools to
view the current activities:
Performance Monitor: a useful tool that tracks resource use
on Microsoft operating systems.
It can monitor resource usage for the server and provide
information specific to SQL Server either locally or for a
remote server
SQL Profiler: a graphical application that enables you to
capture a trace of events that occurred in SQL Server.
- The Goal of Monitoring
The following list describes the basic monitoring tools:
SQL Trace: the TSQL stored procedure way to invoke a
SQL Server trace without having to start up the SQL
Profiler application. It requires a little more work to set up,
but it’s a lightweight way to capture a trace
It’s scriptable enables the automation of trace capture
Default trace: a light weight trace that runs in a continuous
loop and captures a small set of key database and server
events.
useful in diagnosing events that may have occurred when no
other monitoring was in place.
- The Goal of Monitoring
The following list describes the basic monitoring tools:
Activity Monitor: a tool graphically displays the following
information:
Processes running on an instance of SQL Server
Locks
User activity
Blocked processes
Dynamic management views: return server state
information that can be used to monitor the health of a
server instance, diagnose problems, and tune performance.
TransactSQL: Some system stored procedures provide
useful information for SQL Server monitoring, such as
sp_who,sp_who2,sp_lock, and several others.
- Performance Monitor
Performance Monitor is an important tool because it
enables to know:
How SQL Server is performing
How Windows is performing.
Three server resources needs to be monitored:
CPU
Memory
I/O
- Performance Monitor
CPU Resource Counters:
Several counters show the state of the available CPU
resources.
Bottlenecks due to CPU resource shortages are frequently
caused by problems such as:
More users than expected
One or more users running very expensive queries
Routine operational activities such as index rebuilding.
- Performance Monitor
CPU Resource Counters:
The following counters will help to find the cause of the
bottleneck so that to identify that the bottleneck is a CPU
resource issue:
Processor: % Processor Time: displays the total percentage of
time spent processing nonidle threads. On a multipleprocessor
machine, each individual processor can be monitored
independently.
Process: % Processor Time (sqlservr): can be used to determine
how much of the total processing time can be attributed to SQL
Server.
System: Processor Queue Length: displays the number of
threads waiting to be processed by a CPU.
- Performance Monitor
Disk Activity:
SQL Server relies on the Windows operating system to
perform I/O operations.
The disk system handles the storage and movement of data
on your system. Disk I/O is frequently the cause of
bottlenecks in a system.
Need to observe many factors in determining the
performance of the disk system
Several disk counters return disk Read and Write
performance information, as well as data transfer
information, for each physical disk or all disks.
- Performance Monitor
Memory Counters:
Used by the DBA to get an overall picture of database I/O.
A lack of memory will have a direct impact on disk activity.
When optimizing a server, adding memory should always
be considered.
These are some Memory counters:
Memory: Pages/Sec: measures the number of pages per second
that re paged out of memory to disk or paged into memory from
disk.
Memory: Available Bytes: indicates how much memory is
available to processes.
Process: Working Set (sqlservr) The SQL Server instance of
the Working Set counter shows how much memory is in use by
SQLServer.
- Performance Monitor
Memory Counters:
SQL Server: Buffer Manager: Buffer Cache Hit Ratio
measures the percentage of time that data was found in the
buffer without having to be read from disk.
This counter should be very high, optimally 90% or better.
When it is less than 90%, disk I/O will be too high, putting an
added burden on the disk subsystem.
SQL Server: Buffer Manager: Page Life Expectancy returns
the number of seconds a data page will stay in the buffer
without being referenced by a data operation.
The minimum value for this counter is approximately 300
seconds.
This counter along with the Buffer Cache Hit Ratio counter, is
probably the best indicator of SQL Server memoryhealth.
- Performance Monitor
SQL Server Counters:
After installing SQL Server, a plethora of SQL Server
performance objects and counters are configured to assist in
the performance monitoring and optimization of SQL
Server.
These are some SQL Server–specific counters:
SQL Server: General Statistics: User Connections – displays the
number of user connections that are currently connected to SQL
Server.
This counter is useful in monitoring and tracking connection
trends to ensure that the server is configured to adequately
handle all connections.
SQL Server: Locks: Average Wait Time monitor and track the
average amount of time that user requests for data resources
have to wait because of concurrent blocks to the data.
- Dynamic Management Views
SQL Server 2008 provides many Dynamic Management
Views (DMVs) that can be used in the gathering of
baseline information and for diagnosing performance
problems.
These views offer the same information as
Performance counters
Specific database performance information.
- Dynamic Management Views
sys.dm_os_performance_counters : this view provides the
information such as Performance Monitor, except that the
information is returned in a relational format and the values returned
are instantaneous.
sys.dm_db_index_physical_stats: returns information about the
indexes on a table, including:
The amount of data on each data page
The amount of fragmentation at the leaf and nonleaf level of the
indexes
The average size of records in an index.
sys.dm_db_index_usage_stats: collects cumulative index usage data.
This view can be used to identify which indexes are seldom
referenced and, thus, may be increasing overhead without improving
Read performance.
- Monitoring Events
The following list describes the different features you can
use to monitor events that happened in the Database
Engine:
Default Trace:
This trace is always on and captures a very minimal set of light
weight events.
SQL Trace: You have to specify which Database Engine
events you want to trace when you define the trace. There
are two ways to access the trace data:
Using SQL Server Profiler: a graphical user interface
Through TSQL system stored procedures
- Monitoring Events
SQL Server Profiler:
It’s a graphical tool that lets system administrators monitor
and record database and server activities.
Users can monitor the following events using SQL Server
Profiler:
Login connections, attempts, failures, and disconnections
CPU use of a batch
Deadlock problems
All DML statements (SELECT, INSERT, UPDATE, and
DELETE)
The start or end of a stored procedure
- Monitoring Events
Working with SQL Server Profiler:
Defining a Trace:
Launch SQL Server Profiler.
Connect to the SQL Server instance.
Define how you want to see the trace data in the Trace
Definition dialog box.
Click the Events Selection tab to select the trace events and data
columns to capture.
After the trace is fully defined, click Run to launch the trace.
- Monitoring Events
SQL Server Profiler:
- Monitoring Events
SQL Server Profiler:
- Monitoring Events
Working with SQL Server Profiler:
Starting, Pausing, and Stopping a Trace: After a trace is
running, you can control it from within Profiler.
When you click Pause, the data gathering is suspended at the
server level. Any events that occur while the trace is paused are
not captured.
Stopping a trace closes the trace session.
Thêm vào BST
Báo xấu
Download
Vui lòng tải xuống để xem tài liệu đầy đủ
