Sql server 2008 profiling and monitoring tools
1 / 12

SQL Server 2008 Profiling and Monitoring Tools - PowerPoint PPT Presentation

  • Uploaded on

SQL Server 2008 – Profiling and Monitoring Tools . Learningcomputer.com. Reasons to Monitor . Troubleshoot problems, failed maintenance jobs and isolate bottlenecks Improve SQL Server performance and response time of your queries and reports Establish baseline performance for load testing

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'SQL Server 2008 Profiling and Monitoring Tools' - woody

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Reasons to monitor
Reasons to Monitor

  • Troubleshoot problems, failed maintenance jobs and isolate bottlenecks

  • Improve SQL Server performance and response time of your queries and reports

  • Establish baseline performance for load testing

  • Proactively look for problem areas if you are the DBA or responsible for the server

Monitoring tools in sql server
Monitoring Tools in SQL Server

  • Windows

    • Task Manager

    • Event Viewer

    • Performance Monitor

  • SQL Server

    • Canned reports (server or database)

    • Activity Monitor (SQL Server and Job)

    • Logs (SQL Server and SQL Server Agent)

    • TSQL And DMV (Dynamic Management Views)

    • SQL Server Profiler (Favorite)

Windows tools
Windows Tools

  • Task Manager

    • Quickest way to find information on CPU and Memory

    • Performance tab gives a good visual of resources

    • Processes and Application tabs highlight all the running programs

  • Event Viewer

    • Can be found under Administrative Tools (Control Panel)

    • Application tab gives you information on SQL Server Errors

    • Security tab has information on Failure Audits

  • Demo

Performance monitor
Performance Monitor

  • Performance Monitor lets you get graphical information on system counters

  • You can get real time data or from log files

  • In Vista, Resource Overview gives you real time data on CPU, Disk, Network

  • Charts can be Line, Histogram or Report

  • You can add multiple physical counters to gather tons of information, some of these are specific to SQL Server

  • We will take a look at handful of important counters next

Performance counters continued
Performance Counters – Continued

  • CPU

    • Processor:% Processor Time – Percent of CPU being utilized < 50%


    • Available MBytes - Memory available to new processes, More is better

    • Pages/sec - Tells you how many times Virtual memory is being accessed

  • DISK

    • % Disk Time – Similar to Processor Time > 90% is bad

    • Avg.Disk Queue Length – Shows # of I/O operations waiting >2 is bad

  • SQL Server

    • SQLServer:Access Methods - Full Scans / sec >1 or 2 is bad

    • SQL Server:Buffer Manager - Buffer cache hit ratio (Using cache) > 90

    • SQL Server:Locks - Average Wait Time, should be low

Canned report
Canned Report

  • Server Level

    • Server Dashboard

    • Activity - All Sessions

    • Performance – Top queries by average CPU

  • Database Level

    • Disk usage by Top tables

    • All Transactions

  • Demo

Activity monitor
Activity Monitor

  • Where is it???? Took me a little bit to find this one

  • RMB on Server, select Activity Monitor

  • New look is broken down by Resource Area + Filtering

  • Overview section: Graphs and Charts on system resources

  • Processes Section: Information on SPIDs. If you right click, you can kill the process or launch Profiler directly

  • Resource Waits: Wait info on CPU, I/O, Memory

  • Data File I/O

  • Recent Expensive Queries – favorite section

Error logs sql server and ss agent
Error Logs(SQL Server and SS Agent)

  • General information on SQL Server activity

  • Both logs share the same GUI

  • You can filter by user, computer, date, source and message

  • One current and a number of Archive Logs

  • Archived every time the service is restarted

  • File Location for logs is

    • C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\

  • Demo

Tsql and dmv s

  • TSQL

    • Sp_who2 and Sp_lock


  • DMV

    • DMV’s are dynamic management views return server state that can be used to monitor the health of a server instance

    • select * from sys.dm_os_sys_info has system level info

    • select * from sys.dm_os_performance_counters will let you get information on the performance counters

    • More information can be found at http://msdn.microsoft.com/en-us/library/ms176083.aspx

  • Demo


  • SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine

  • You can capture and save data about each event to a file or table to analyze later

  • You can add Events class which is a type of event that can be traced e.g. Showplan XML

  • You can add a data column which is an attribute of an event classes captured in the trace e.g. Duration in milliseconds

  • Save the trace to a file or table

  • You can use Out of the box templates or customize them

Profiler continued
Profiler – Continued

  • Demo Scenario

  • I have created a user template called Kash_template that I will use for the demo

  • We are looking for slow queries (duration > 100) in AdventureWorks2008 database

  • I have setup two sessions to mimic activity:

    • One inserts data into SALES.CUSTOMER_ALL table and then updates it using a loop

    • The other one just browses data from SALES.CUSTOMER_ALL table using a loop

    • Both use the WAITFOR delay option