Monitoring SQL Server: Bài giảng Cơ sở dữ liệu nâng cao

Monitoring SQL Server

The Goal of Monitoring The goal of monitoring databases is to see:

(CPU, Memory, I/O).

The information enables DBA identify abnormal activities

What’s going on inside SQL Server, How effectively SQL Server is using the server resources

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:

information:

 Processes running on an instance of SQL Server  Locks  User activity  Blocked processes

Activity Monitor: a tool graphically displays the following

information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

Dynamic management views: return server state

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:

resources.

Several counters show the state of the available CPU

caused by problems such as:  More users than expected  One or more users running very expensive queries  Routine operational activities such as index rebuilding.

Bottlenecks due to CPU resource shortages are frequently

Performance Monitor CPU Resource Counters:

 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.

The following counters will help to find the cause of the bottleneck so that to identify that the bottleneck is a CPU resource issue:

Performance Monitor Disk Activity:

perform I/O operations.

SQL Server relies on the Windows operating system to

on your system. Disk I/O is frequently the cause of bottlenecks in a system.

The disk system handles the storage and movement of data

performance of the disk system

Need to observe many factors in determining the

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.

 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.

These are some Memory counters:

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:

performance objects and counters are configured to assist in the performance monitoring and optimization of SQL Server.

After installing SQL Server, a plethora of SQL Server

 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.

These are some SQL Server–specific counters:

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.

 Using SQL Server Profiler: a graphical user interface  Through TSQL system stored procedures

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:

Monitoring Events SQL Server Profiler:

and record database and server activities.

It’s a graphical tool that lets system administrators monitor

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

Users can monitor the following events using SQL Server

Monitoring Events Working with SQL Server Profiler:

 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.

Defining a Trace:

Monitoring Events SQL Server Profiler:

Monitoring Events Working with SQL Server Profiler:

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.

Starting, Pausing, and Stopping a Trace: After a trace is

Monitoring Events Working with SQL Server Profiler:

trace definition or the data it generates.

 Saving a Trace Definition:

 After you create a new trace inside Profiler that contains the

events, data columns, and filters that you want, click Run and then immediately stop the trace.

 Under the File menu, go to the option Export, Script Trace

Definition to generate a TransactSQL batch to create a trace.

 Use this batch as the basis for a stored procedure that SQL

Server Agent calls to manage the trace.

Saving a Trace Log: There are a variety of ways to save a

Event Notifications Event Notifications are database objects that send

information about server and database events to a Service Broker.

Unlike creating traces, event notifications can be used to perform an action inside an instance of SQL Server in response to events.

Event Notifications The following steps are used to subscribe to an event

regarding the event. In addition, a queue requires the Service Broker service in order to receive the message.

Create the Service Broker queue that will receive the details

procedure and activate it when the event message is in the queue to take a certain action.

Create an event notification. You can create a stored

Troubleshooting SQL Server

Management Studio: This tool enables you to perform most of your management tasks and to run queries

Some of the more common and advanced features can be

used for administration: Reports Configuring SQL Server Filtering Objects Error Logs Activity Monitor Monitoring Processes in TSQL

Troubleshooting SQL Server

Reports:

Server management environment is the integrated reports that help a DBA in each area of administration.

 Standard reports: are provided for server instances, databases,

logins, and the Management tree item.

 Serverlevel reports: give you information about the instance of

SQL Server and the operating system.

 Databaselevel reports: drill into information about each

database.

One of the most impressive enhancements to the SQL

You must have access to each database you wish to report on, or your login must have enough rights to run the server level report.

Troubleshooting SQL Server

Reports:

 User can access serverlevel reports from the Object Explorer

window in Management Studio by rightclicking an instance of SQL Server and selecting Reports from the menu.

 A report favorite at the server level is the Server Dashboard

Server Reports:

Troubleshooting SQL Server Reports:

Server Reports:

Troubleshooting SQL Server

Reports:

 The Server Dashboard report gives user a wealth of information

about your SQL Server 2008 instance:

 What edition and version of SQL Server.  Anything for that instance that is not configured to the default

SQL Server settings.

 The I/O and CPU statistics by type of activity (e.g., ad hoc

queries, Reporting Services, and soon).

 Highlevel configuration information such as whether the

instance is clustered or using AWE.

Server Reports:

Troubleshooting SQL Server

Reports:

 Launch this report by rightclicking the database name in the

Object Explorer window in Management Studio.

 With these reports, user can see information that pertains to the selected database. For example, it shows all the transactions currently running against a database, users being blocked, or disk utilization for a given database

Database Reports:

Troubleshooting SQL Server

Reports:

Database Reports:

Troubleshooting SQL Server

Configuring SQL Server:

 SQL Configuration Manager  The sp_configure stored procedure

2 ways for SQL Server configuration:

 The sp_configure stored procedure  The Server Properties screen.

2 ways for the Database Engine configuration:

Troubleshooting SQL Server

Filtering objects

Beginning with SQL Server 2005, Management Studio uses a new object model called SQL Server Management Objects (SMO) to retrieve the list of objects

 Select the node of the tree that you wish to filter and click the

Filter icon in the Object Explorer.

 In the Object Explorer Filter Settings dialog, filter by name, schema, or when the object was created. The Operator drop down box enables to select how wish to filter, and then type the name in the Value column.

To filter the objects in Management Studio:

Troubleshooting SQL Server

Filtering objects

Troubleshooting SQL Server

Error log:

thing should be done is connecting to the server and looking at the SQL Server instance error logs and the Windows event logs.

When something goes wrong with an application, the first

To view the logs, rightclick SQL Server Logs under the Management tree and select View  SQL Server and Windows Log to open “Log File Viewer” screen

you want to bring into the view or can consolidate logs from SQL Server, Agent, Database Mail, and the Windows Event Files.

From this screen, user can check and uncheck log files that

Troubleshooting SQL Server

Error log:

Troubleshooting SQL Server

Activity monitoring: give a view of current connections on an instance. The monitor can be used to determine whether you have any processes blocking other processes. To open the Activity Monitor in Management Studio, right

click on the Server in the Object Explorer, then select Activity Monitor.

Troubleshooting SQL Server

Activity monitoring:

 The top section shows 4 graphs:

 Processor time  Waiting tasks  Database i/o  Batch requests/sec

 There are 4 lists under the graphs:

 Processes  Resource Waits  Data File I/O  Recent Expensive Queries.

In the view:

Troubleshooting SQL Server

Activity monitoring:

Troubleshooting SQL Server

Process monitoring:

process.

 To see all the connections to your server, run sp_who2 without

any parameters.

 To see only the active connections to your server, execute this

command: sp_who2 ‘active’

User can also monitor the activity of your server via TSQL. sp_who: returns who is connecting to your instance, sp_who2: gives you much more information about each

to troubleshoot the Database Engine.

sys.dm_exec_connections: gives more information to help

Troubleshooting SQL Server

Process monitoring:

enables to see what SQL command an individual process ID is running.

 The command accepts only a single input parameter, which is the process id for the connection that you’d like to diagnose

 Ex: DBCC INPUTBUFFER (55)

DBCC INPUTBUFFER: is a great DBCC command that

Performance Tuning

The goal of monitoring databases is to assess how a

server is performing.

Effective monitoring current performance is go isolate processes that are causing problems, and gathering data continuously over time to track performance trends.

Performance Tuning

Monitoring SQL Server lets you do the

following: Determine whether you can improve performance. Evaluate user activity. Troubleshoot any problems or debug application

components, such as stored procedures.

Performance Tuning

Monitoring lets administrators identify

performance trends to determine if changes are necessary.

Performance Tuning

To monitor SQL Server effectively  should clearly identify your reason for monitoring. Establish a baseline for performance. Identify performance changes over time. Diagnose specific performance problems. Identify components or processes to optimize. Compare the effect of different client

applications on performance.

Audit user activity.

Performance Tuning

To monitor SQL Server effectively  should clearly identify your reason for monitoring. Test a server under different loads. Test

database architecture.

Test maintenance schedules. Test backup and restore plans. Determining when to modify your hardware

configuration.

Performance Tuning

The performance of enterprise database

systems depends on: A effective configuration of physical design

strunctures in the databases that compose those systems.

These physical design structures include

indexes, clustered indexes, indexed views, and partitions, whose purpose is to enhance performance and manageability of databases.

Performance Tuning

SQL Server provides Database Engine Tuning Advisor a tool that analyzes the performance effects of workloads (a set of TransactSQL statements that executes against databases you want to tune) on one or more databases.