Cơ sở dữ liệu - Chapter 8: Monitoring sql server

Chapter 8Monitoring SQL Server The Goal of MonitoringThe 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 activitiesThe Goal of MonitoringOnce 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 serverSQL Profiler: a graphical application that enables you to capture a trace of events that occurred in SQL Server.The Goal of MonitoringThe following list describes the basic monitoring tools: SQL Trace: the T-SQL 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 traceIt’s scriptable  enables the automation of trace captureDefault 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 MonitoringThe following list describes the basic monitoring tools: Activity Monitor: a tool graphically displays the following information:Processes running on an instance of SQL ServerLocksUser activityBlocked processesDynamic management views: return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. Transact-SQL: Some system stored procedures provide useful information for SQL Server monitoring, such as sp_who,sp_who2,sp_lock, and several others.Performance MonitorPerformance Monitor is an important tool because it enables to know:How SQL Server is performingHow Windows is performing.Three server resources needs to be monitored:CPUMemoryI/OPerformance MonitorCPU 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 expectedOne or more users running very expensive queriesRoutine operational activities such as index rebuilding.Performance MonitorCPU 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 non-idle threads. On a multiple-processor 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 MonitorDisk 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 systemSeveral disk counters return disk Read and Write performance information, as well as data transfer information, for each physical disk or all disks. Performance MonitorMemory 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 MonitorMemory 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 MonitorSQL 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 ViewsSQL 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 countersSpecific database performance information. Dynamic Management Viewssys.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 pageThe amount of fragmentation at the leaf and non-leaf level of the indexesThe 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 EventsThe 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 interfaceThrough T-SQL system stored proceduresMonitoring EventsSQL 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 disconnectionsCPU use of a batchDeadlock problemsAll DML statements (SELECT, INSERT, UPDATE, and DELETE)The start or end of a stored procedureMonitoring EventsWorking 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 EventsSQL Server Profiler: Monitoring EventsSQL Server Profiler: Monitoring EventsWorking 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.Monitoring EventsWorking with SQL Server Profiler: Saving a Trace Log: There are a variety of ways to save a 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 Transact-SQL batch to create a trace.Use this batch as the basis for a stored procedure that SQL Server Agent calls to manage the trace.Event NotificationsEvent 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 NotificationsThe following steps are used to subscribe to an eventCreate the Service Broker queue that will receive the details regarding the event. In addition, a queue requires the Service Broker service in order to receive the message.Create an event notification. You can create a stored procedure and activate it when the event message is in the queue to take a certain action.Troubleshooting SQL ServerManagement Studio: This tool enables you to perform most of your management tasks and to run queriesSome of the more common and advanced features can be used for administration:ReportsConfiguring SQL ServerFiltering ObjectsError LogsActivity MonitorMonitoring Processes in T-SQLTroubleshooting SQL ServerReports:One of the most impressive enhancements to the SQL 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. Server-level reports: give you information about the instance of SQL Server and the operating system. Database-level reports: drill into information about each database. 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 ServerReports:Server Reports: User can access server-level reports from the Object Explorer window in Management Studio by right-clicking an instance of SQL Server and selecting Reports from the menu. A report favorite at the server level is the Server DashboardTroubleshooting SQL ServerReports:Server Reports: Troubleshooting SQL ServerReports: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).High-level configuration information such as whether the instance is clustered or using AWE.Troubleshooting SQL ServerReports:Database Reports: Launch this report by right-clicking 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 databaseTroubleshooting SQL ServerReports:Database Reports:Troubleshooting SQL ServerConfiguring SQL Server:2 ways for SQL Server configuration:SQL Configuration ManagerThe sp_configure stored procedure 2 ways for the Database Engine configuration:The sp_configure stored procedure The Server Properties screen.Troubleshooting SQL ServerFiltering objectsBeginning with SQL Server 2005, Management Studio uses a new object model called SQL Server Management Objects (SMO) to retrieve the list of objectsTo filter the objects in Management Studio: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.Troubleshooting SQL ServerFiltering objectsTroubleshooting SQL ServerError log:When something goes wrong with an application, the first thing should be done is connecting to the server and looking at the SQL Server instance error logs and the Windows event logs.To view the logs, right-click SQL Server Logs under the Management tree and select View  SQL Server and Windows Log to open “Log File Viewer” screenFrom this screen, user can check and uncheck log files that you want to bring into the view or can consolidate logs from SQL Server, Agent, Database Mail, and the Windows Event Files.Troubleshooting SQL ServerError log:Troubleshooting SQL ServerActivity 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 ServerActivity monitoring:In the view:The top section shows 4 graphs:Processor timeWaiting tasksDatabase i/oBatch requests/secThere are 4 lists under the graphs:ProcessesResource WaitsData File I/ORecent Expensive Queries.Troubleshooting SQL ServerActivity monitoring:Troubleshooting SQL ServerProcess monitoring:User can also monitor the activity of your server via T-SQL.sp_who: returns who is connecting to your instance,sp_who2: gives you much more information about each 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’ sys.dm_exec_connections: gives more information to help to troubleshoot the Database Engine.Troubleshooting SQL ServerProcess monitoring:DBCC INPUTBUFFER: is a great DBCC command that 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 diagnoseEx: DBCC INPUTBUFFER (55)Performance TuningThe 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 TuningMonitoring 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 TuningMonitoring lets administrators identify performance trends to determine if changes are necessary. Performance TuningTo 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 TuningTo 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 TuningThe 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 TuningSQL Server provides Database Engine Tuning Advisor - a tool that analyzes the performance effects of workloads (a set of Transact-SQL statements that executes against databases you want to tune) on one or more databases.