1 / 32

How to Collect and Analyze Performance Data in Microsoft SQL Server William P. Carroll SQL Server Support Engineer Mi

2. The Funnel Technique. Capture more information at the beginning then narrow down your attack.It is an iterative process.Don't jump to quick conclusions.Ask a lot of questions of end users, developers, managers, and others.Most critical time of the performance issue.Slow compared to what?Be proactive..

elina
Download Presentation

How to Collect and Analyze Performance Data in Microsoft SQL Server William P. Carroll SQL Server Support Engineer Mi

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. How to Collect and Analyze Performance Data in Microsoft SQL Server William P. Carroll SQL Server Support Engineer Microsoft Corporation

    2. 2

    3. 3 Sources of SQL Server Performance Problems Application Database design (Index and SQL statements) SQL Server bugs Hardware Network

    4. 4 Tools Used to Collect Data for Performance Issues Sqldiag.exe Blocker script Profiler System monitor System and application event logs

    5. 5 How Long to Collect Data Ideally you want to capture the data from good performance to bad performance, back to good performance. Normally dictated by the problem definition. Can be a lot of output data to analyze.

    6. 6 Sqldiag.exe Run from command prompt at the server. Output is written to Sqldiag.txt file in the log folder. sqldiag.exe /? (-i Instance switch) Is a snapshot at the time it is executed. Will overwrite itself.

    7. 7 Sqldiag.exe (2) Sqldiag.exe is a utility located in the \Binn folder that captures: Last six SQL Server error logs Registry information Library version information Last 100 queries SQL configuration information Current user information

    8. 8 Sqldiag.exe (3) Sqldiag.exe is a utility located in the \Binn folder that captures: Lock information Database information Product information Extended procedure information Input buffer information Deadlock information Machine information

    9. 9 Analyzing Sqldiag.txt Check service pack version. Review SQL Server error logs for messages. Sysprocesses, sp_lock, sp_configure, input buffer.

    10. 10 Blocker Script Blocker script Q271509, “INF: How to Monitor SQL Server 2000 Blocking” Q251004, “INF: How to Monitor SQL Server 7.0 Blocking” Q224453, “INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problem”

    11. 11 Blocker Script (2) Steps to set up blocker script: Create the blocking script as a stored procedure. Create a script to execute the stored procedure in a loop. DBCC TRACEON (3604) go WHILE 1=1 BEGIN EXEC sp_blocker_pss80 -- Or for fast mode -- EXEC sp_blocker_pss80 1 WAITFOR DELAY '00:00:015' END go

    12. 12 Blocker Script (3) Steps to set up blocker script (continued): Execute the blocking script Osql –E –Sservername –icheckblk.sql -ocheckblk.out –w2000 (Note that the above text is one line; it has been wrapped for readability.) Stop the blocking script CTRL+C

    13. 13 Analyzing Blocker Script Output Start time Sysprocesses SPID, Ecid, Status, Open_tran, Lastwaittype, Waittype (Q244455),Waittime,Waitresource, Cmd, Cpu, Physical_io, Memusage, Dbid, Last_batch, Login_time, Loginame, Uid, Hostname, Program_name, Nt_domain, Nt_username, Net_address, Net_library

    14. 14 Analyzing Blocker Script Output (2) Spid at the head of blocking chain Syslockinfo (sp_lock) SPID, Ecid, Dbid, Objid, Indid, Type, Resource, Mode, Status DBCC INPUTBUFFER DBCC PSS Locking is normal behavior; look for trends

    15. 15 Profiler Graphical tool to monitor and capture SQL Server events Q224587, “INF: Troubleshooting Application Performance with SQL Server”

    16. 16 Profiler (2)

    17. 17 Profiler (3)

    18. 18 Profiler (4)

    19. 19 Profiler (5)

    20. 20 Profiler (6)

    21. 21 Analyzing Profiler Output Get an overview of events names and number of occurrences. Save profiler trace as a table. Look for long duration queries (> 2 sec.). Look for attention and exception events. Look for hash, sort, and execution warnings.

    22. 22 Analyzing Profiler Output (2) Look for recompiles and auto update statistics. Look for sp_cursoropen and CursorImplicitConversions. Look for deadlocks and lock timeouts. Missing column statistics.

    23. 23 System Monitor Capture to a log file. Reload log file into system monitor. Objects to capture: Memory, processor, process, system, object, thread, page file, physical disk, logical disk, all SQL Server objects.

    24. 24 Analyzing System Monitor Output Memory, disk I/O, processor, network I/O Look for abnormally high or low counters compared to benchmark. If one counter indicates a problem area, look for supporting counters and correlate with other tools to test your hypothesis.

    25. 25 Capture Sqldiag.txt, Blocker Script, Profiler Trace, and System Monitor Together MSDN article: “Index Tuning Wizard for Microsoft SQL Server 2000” MSDN article: “Microsoft SQL Server 7.0 Performance Tuning Guide” Q224587, “INF: Troubleshooting Application Performance with SQL Server” Q271509, “INF: How to Monitor SQL Server 2000 Blocking” Q251004, “INF: How to Monitor SQL Server 7.0 Blocking”

    26. 26 Capture Sqldiag.txt, Blocker Script, Profiler Trace, and System Monitor Together (2) Q224453, “INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problem” Q243589, “INF: Troubleshooting Slow-Running Queries on SQL Server 7.0 or Later” Q243588, “INF: Troubleshooting Performance of Ad-Hoc Queries” Q243586, “INF: Troubleshooting Stored Procedure Recompilation” Q244455, “INF: Definition of Sysprocesses Waittype and Lastwaittype Fields for SQL Server 7.0”

    27. 27 Query Timeout and Long Running Query Blocker script (sysprocesses, sp_who) Profiler trace Use Query Analyzer and “set statistics time, io, profile on” to analyzer the query. Run query through the Index Tuning Wizard.

    28. 28 Deadlock Profiler Startup parameters Locks:Deadlock and Locks:Deadlock Chain -T1204, -T3605 Application rewrite. Add error handling to resubmit the transaction. Add index or query hint

    29. 29 Stored Procedure Recompilation Causes of recompilation: With recompile clause in stored procedure Sp_recompile for table referenced in stored procedure Restoring the database Sufficient server activity causes plan to be aged out of cache Sufficient percentage of data changes in a table referenced by the stored procedure Interleaving DDL and DML statements Stored procedure performs certain operations on temporary tables Fully qualify stored procedure names (best practice)

    30. 30 Stored Procedure Recompilation (2) Profiler trace (SP:Recompile, SP:StmtStarting, SP:StmtComplete). Move DDL statements to the beginning of a stored procedure. Data modifications (Q195565, “How SQL Server 7.0 Autostats Work”). Certain use of temporary tables.

    31. 31 How to Collect and Analyze Performance Data in Microsoft SQL Server Questions

More Related