1 / 12

SQL Server 2008 – Profiling and Monitoring Tools

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

woody
Download Presentation

SQL Server 2008 – Profiling and Monitoring Tools

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2008 – Profiling and Monitoring Tools Learningcomputer.com

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

  3. 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)

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

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

  6. Performance Counters – Continued • CPU • Processor:% Processor Time – Percent of CPU being utilized < 50% • MEMORY • 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

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

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

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

  10. TSQL And DMV’s • TSQL • Sp_who2 and Sp_lock • DBCC_INPUTBUFFER • 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

  11. Profiler • 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

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

More Related