sql server 2008 profiling and monitoring tools n.
Skip this Video
Download Presentation
SQL Server 2008 – Profiling and Monitoring Tools

Loading in 2 Seconds...

play fullscreen
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

Download Now 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