Tracing and Profiling SQL Server

Chia sẻ: Thien Phuc | Ngày: | Loại File: PPT | Số trang:36

Thêm vào BST

Báo xấu

78
lượt xem 14
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Query tuning, optimization and troubleshooting are all possible by the ability to view what's going on inside SQL Server

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Tracing and Profiling SQL Server

ISQL Users Group May 2009 Tracing and Profiling SQL Server Yaniv.Etrogi@gmail.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi 1
Over the next 1 hour: SQL Trace Architecture and Terminology  Security and Permissions  Profiler  Server side Tracing  Saving and Replaying Traces  Querying ServerSide Trace Metadata  2
Introduction  Query tuning, optimization and troubleshooting are all possible by the ability to view what's going on inside SQL Server  SQL Trace provides a realtime view into what’s going on inside the database engine and at a very granular level 3
SQL Trace Architecture and Terminology 4
 SQL Trace is an SQL Server database engine technology  SQL Server Profiler is a .NET application that uses system stored procedures exposing the functionality of SQL Trace Microsoft.SqlServer.Management.Trace http://msdn.microsoft.com/enus/library/microsoft.sqlserver.man 5
When tracing we monitor for specific events  which are generated when various actions occur in the database engine – For example, a user login or the execution of a query are each actions that cause events to fire Each event has an associated collection of  columns that contain the collected data when the event fires  SQL Server uses a model that selectively enables data collection as required – Data is never collected until someone asks for it  There are around 171 events and 65 columns to select from 6
 The main component of the SQL Trace architecture is the trace controller which is a shared resource that manages all traces created by any consumer – There are various event producers in the database engine for example in the query processor, lock manager, and cache manager – Each of these producers is responsible for generating events that relates to certain categories of server activity – All producers are disabled by default and therefore generate no data – When a trace is started for a certain event a global bitmap in the trace controller is updated letting the event producer know that at least one trace is listening, and causing the event to begin firing – Managed along with this bitmap is a secondary list of which traces are monitoring which events –Once an event fires, its data is routed into a global event sink, which queues the event data for distribution to each trace that is actively listening – The trace controller routes the data to each listening trace based on its internal list of traces and watched events – In addition to the trace controller's own lists each individual trace also keeps track of which events it is monitoring along with which columns are actually being used as well as what filters are in place – The event data returned by the trace controller to each trace is filtered and the data columns are trimmed down as necessary before the data is routed to an I/O provider 7
 Trace I/O providers send the data to the final destination  The available output formats for trace data are either a file on the database server (or a network share) or a rowset to a client  Both providers use internal buffers to ensure that if the data is not consumed quickly enough (written to disk or read from the rowset) it will be queued 8
 The file provider is designed with a guarantee that no event data will be lost  If an I/O slowdown occurs so disk writes are not occurring quickly enough the internal buffers begin to fill  To monitor for these waits watch the SQLTRACE_LOCK and IO_COMPLETION wait types 9
 The rowset provider is not designed for data loss guarantee  If data is not being consumed quickly enough and its internal buffers fill it waits up to 20 seconds before it begins dropping events in order to free buffers for the sake of getting things moving  SQL Server Profiler will then send an information message informing that some events had been lost and had not been captured  You can find out if you're headed in that direction by monitoring SQL Server's TRACEWRITE wait type which is incremented as threads are waiting for buffers to free up 10 10
A background trace management thread is  started whenever at least one trace is active on the server – SELECT * FROM sysprocesses WHERE cmd = N'TRACE QUEUE TASK' ; The background thread is responsible for:  – Flushing file provider buffers (done every 4 seconds) – Can be seen as a wait_type of SQLTRACE_BUFFER_FLUSH – Closing rowsetbased traces that are considered to be expired (dropping events for more than 10 minutes) There is no provider that writes trace data  directly to a table 11 11
Security and Permissions 12 12
 SQL Server 2005 introduces a new permission called ALTER TRACE. This is a serverlevel permission granted to a login principal and allows access to start, stop, or modify a trace (in addition to being able to generate userdefined events)  GRANT ALTER TRACE TO ;  SQL Trace has a couple of builtin security features to keep passwords secured –SQL Trace will automatically omit data if an event contains a call to a passwordrelated stored procedure or statement. For example, a call to CREATE LOGIN including the WITH PASSWORD option will be blanked out by SQL Trace. –SQL Trace will not return statement text or query plans generated within an encrypted stored procedure, userdefined function, or view 13 13
Profiler 14 14
 The General tab allows you to control how the trace will be processed by the consumer  The default setting is to use the rowset provider displaying the events in real time in the SQL Server Profiler tool window  Other available options are –Save the events to a table –Save the events to a file (on either the server or the client)  When saving to a table Profiler uses the rowset provider and routes the data back into a table  When saving to a server side file Profiler actually starts two equivalent traces—one using the rowset provider and the other using the file provider  Saving to a clientside file does not use the file provider at all. Rather, the data is routed to the Profiler tool via the rowset provider and then saved from there to a file. –This is more efficient than using Profiler to write to a serverside file –Remember that doing so you do not take benefit of the lossless data guarantee offered by the file provider 15 15
The Events Selection tab allows you to select events that you'd like to  trace along with their associated data columns  In order to narrow your scope and help ensure that tracing does not cause performance issues SQL Trace offers the ability to filter the events based on various criteria In SQL Server Profiler filtration is exposed via the “Column Filters”  tab  Remember to check the “Show All Columns” checkbox in order to see the complete list of columns  Once you click the Run button data will begin streaming immediately and be displayed at the Profiler window (this is because Profiler uses the rowset provider) –If you find that data is coming in too quickly for you to be able to read it you may disable scrolling using the Auto Scroll icon on the SQL Server Profiler toolbar –If you are on a slow remote connection and your trace is not well filtered you may be in a situation that the icons in the Profiler tool bar are seen as if they were inactive thus disallowing you to stop the trace. The only way out here is to stop the trace using sp_trace_setstatus 16 16
Events that do not produce data for a specific  column cannot be used to filter on that column –For example, the SQL:BatchStarting event does not produce duration data and therefore a filter defined on that column for this event will not filter anything –Note that the SQL:BatchStarting event will still return even though it lacks the Duration output column. To modify this behavior, check the “Exclude Rows That Do Not Contain Values” checkbox in the “Edit Filter” dialog box for the column you'd like to change the setting for 17 17
Saving and Replaying Traces 18 18
 Profiler ships with eight predefined templates –A template is a collection of event and column selections, filters, and other settings that you can save to create a reusable trace definitions  TSQL_Replay template selects a variety of columns for 15 events that are all required for Profiler to be able to play back (Replay) a collected trace at a later time. –Collecting data using this template allows you to reproduce a problem experienced on a production system by replaying events collected on the production system at a test environment –Very useful for support departments  Collected trace data has to be saved and then reopened before a replay can begin  SQL Server Profiler offers the following options for saving trace data available from the File menu: –The Trace File option is used to save the data to a file formatted using a proprietary binary format. This is generally the fastest way to save the data, and also the smallest in terms of bytes on disk –The Trace Table option is used to save the data to a new or previously created table in a database. This option is useful if you need to manipulate or report on the data using TSQL –The Trace XML File option saves the data to a text file formatted as XML –The Trace XML File For Replay option also saves the data to an XML text file, but only those events and columns needed for replay functionality are saved 19 19
 Once the data has been saved to a file or table the original trace window can be closed and the file or table reopened via SQL Server Profiler's File menu  A trace reopened in this way will have a Replay menu on the Profiler tool bar allowing you to start replaying the trace, stop the replay, or set a breakpoint  During the course of the replay, the same events used to produce the trace being replayed will be traced from the server on which you replay –The ”Save To File” and “Save To Table” options are used for a clientside save. No serverside option exists for saving playback results  The trace will be replayed on multiple threads (2005 only), corresponding to at most the “Number Of Replay Threads” specified –The “Replay Events In The Order They Were Traced” option ensures that all events will be played back in exactly the order in which they occurred as based upon the EventSequence column. Multiple threads will still be used to simulate multiple spids. –The “Replay Events Using Multiple Threads” option allows SQL Server Profiler to reorder the order in which each spid starts to execute events, in order to enhance playback performance. However, the order of events will remain consistent with the EventSequence within a given spid 20 20