- As you might expect, you can also use sqlcmd variables in conjunction with T-SQL to create flexible query scripts where the query variables can be substituted in at run time. - SQL Profiler. - Starting SQL Profiler Starting, Pausing, and Stopping a Trace Replaying a Trace Showplan Events. - S QL Profiler is a graphical user interface tool for the SQL Trace facility, which allows you to monitor an instance of SQL Server Database Engine or Analysis Services. - Using SQL Profiler, you can interactively capture database activity and optionally save the data about the database events to a file or table. - The SQL Server 2000 Profiler was limited to tracing only relational database calls. - With SQL Server 2005 Profiler, you can save the trace file in XML format, as well as to the standard save formats of ANSI, Unicode, and OEM. - Traced ShowPlan results can also be saved as XML and then loaded into SQL Server Management Studio for analysis.. - You use SQL Profiler to monitor the events you are interested in watching. - Once you identify the reasons you want to monitor the activity of the SQL Server instance, you can filter events so that only a pertinent subset of the event data is collected.. - These are some typical reasons for using the SQL Profiler:. - 䉴 Monitor the performance of an instance of the Database Engine or Analysis Server.. - 䉴 Audit and review activity that occurred on an instance of SQL Server.. - Starting SQL Profiler. - Unlike in previous versions of Profiler where you needed to be a System Administrator to run Profiler, SQL Server 2005 Profiler allows the same user permissions as the Transact-SQL stored procedures that are used to create traces. - You can start SQL Profiler in several ways. - One way is from the Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler menu option.. - Another is from the SQL Server Management Studio menu, where you select Tools | SQL Server Profiler. - You can also start SQL Profiler from the Database Engine Tuning Advisor’s Tools | SQL Server Profiler menu option.. - The first thing to do once SQL Profiler is started is to select File | New Trace from the main menu. - A Connect To Server dialog will be displayed where you can specify the SQL Server instance you want to connect to. - In the Server Type drop-down of the connection dialog, you can choose to connect to a Database Engine server or an Analysis Services server. - Once the server type selection is made and the connection to the SQL Server instance is complete, a Trace properties dialog like the one shown in Figure A-1 will be displayed.. - As you can see in Figure A-1, the trace properties dialog has two tabs: General and Events Selection. - 䉴 Name your trace in the Trace name text box.. - 䉴 Save your trace to a fi le. - The trace data is captured to a .trc fi le.. - 䉴 Save your trace to a table. - The trace data is captured and saved to a database table.. - You can set the date and time for the trace to end and close itself.. - Figure A-1 Profiler Trace Properties – General. - TSQL Captures TSQL statements submitted to SQL Server by clients and the time issued.. - Classes: Audit Login, Audit Logout, ExistingConnection, RPC:Starting, SQL:BatchStarting TSQL_Duration Captures TSQL statements submitted to SQL Server by clients and their execution time. - TSQL_Grouped Captures TSQL statements submitted to SQL Server and the time they were issued, grouped by the user or client that submitted the statement.. - Classes: Audit Login, Audit Logout, ExistingConnection, RPC:Starting, SQL:BatchStarting TSQL_Replay Captures information about TSQL statements required if the trace is to be replayed.. - The second tab of the Trace Properties dialog, the Events Selection, is shown in Figure A-2. - Here you can select or deselect any of the event classes to monitor during your trace. - The Organize Columns button displays a dialog that lets you change the order of the columns involved in the trace or group the columns, for example, by EventClass or StartTime.. - Once the trace properties have been set, click the Run button to start the trace.. - Figure A-3 shows the SQL Profiler running a trace. - As you can see in the figure, when a trace is started, a window is opened in the Profiler utility. - The top portion of the window shows the EventClass that is being monitored and the TextData for the event, along with the columns related to the trace template set in the trace properties.. - In this example, the TSQL_SPs template was used for the trace, so the columns displayed in the window are: DatabaseName, DatabaseID, ObjectID, ObjectName, ServerName, BinaryData, SPID, and the Start Time of the event. - The bottom portion of the window shows the TSQL that the event is executing. - Clicking each line item listed in the top portion of the display will show its corresponding statement in the bottom portion of the display.. - Figure A-2 Profiler Trace Properties - Events Selection. - Once you have defined a trace by using SQL Server Profiler, you can start, pause, or stop capturing data by using the user interface menu options. - When you start a trace for the SQL Server Database Engine or Analysis Services, a queue is created and used as a temporary hold for captured events. - Using SQL Profiler to access a trace opens a window in the interface, and the data is captured immediately. - Only the name of the trace can be modified while the trace is running.. - When you pause a trace, data is not captured until the trace is restarted. - When the trace is restarted, data capture continues from that time on without the loss of previously captured data. - You can change the name, events, columns, and filters of a trace while it is paused, but the destination of the trace and the server connection cannot be changed.. - Figure A-3 SQL Profiler trace. - After stopping a trace, you can save the collected information to a table or file. - The trace properties are saved when a trace is stopped, and you can change the name, events, columns, and filters.. - SQL Profiler contains the ability to save a trace and replay it at a later time. - SQL Profiler features a multithreaded playback engine that can simulate user connections and SQL Server Authentication. - When a problem has been identified and corrected, you can run a trace against the corrected situation and also replay a trace from the problematic situation and compare the results.. - The SQL Profiler Replay menu option allows trace debugging using the Toggle Breakpoint option and the Run To Cursor option. - Showplan Events. - SQL Profiler allows you to gather and display query plan information in your trace.. - You can add Showplan event classes to your trace and even save these Showplan events to an XML file. - You can extract Showplan events from a trace by selecting File | Export | Extract SQL Server Events | Extract Showplan Events from the main Profiler menu. - This will display a Save File dialog box for you to save the extracted Showplan events to either a single .SQLPlan file or separate .SQLPlan files for each event. - The file(s) can then be opened in SQL Server Management Studio for analysis.. - You can also set your trace properties at configuration time to extract Showplan events. - Click the Events Selection tab of the Trace Properties dialog and scroll to the Performance events as shown in Figure A-4. - The Showplan events you can add to your trace are listed in Table A-2.. - Showplan All Shows the query plan with all compilation details of the executed TSQL statement.. - Showplan All for Query Compile Shows when SQL Server compiles a SQL statement. - Returns a subset of the information available in Showplan XML for Query Compile.. - Showplan Statistics Profile Shows the query plan, including run-time details of executing SQL statements and the number of rows passed through the operations.. - Showplan Text Shows (as binary) the query plan for the executing TSQL statement.. - Showplan XML For Query Compile Shows the query plan when it is compiled.. - Showplan XML Statistics Profile Shows the query plan, including run-time details of executing SQL statements and the number of rows passed through the operations in XML format.. - Table A-2 Showplan Events. - Figure A-4 Trace Properties Performance Events
Xem thử không khả dụng, vui lòng xem tại trang nguồn hoặc xem
Tóm tắt