Microsoft SQL Server 2005 Developer’s Guide- P52

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:10

Thêm vào BST

Báo xấu

46
lượt xem 2
download

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

Microsoft SQL Server 2005 Developer’s Guide- P52:SQL Server 2005 is a feature-rich release that provides a host of new tools and technologies for the database developer. This book is written to help database developers and DBAs become productive immediately with the new features and capabilities found in SQL Server 2005.

Chủ đề:

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

Lưu

Nội dung Text: Microsoft SQL Server 2005 Developer’s Guide- P52

Chapter 13: Using sqlcmd 489 Using sqlcmd Variables and T-SQL Statements 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. The following listing gives you an idea of how to combine sqlcmd variables and T-SQL statements: :setvar c1 DepartmentID :setvar c2 name :setvar c3 groupname :setvar t1 humanresources.department use adventureworks select $(c1), $(c2) , $(c2) from $(t1) The results of running this script from within Query Editor using the sqlcmd mode are shown in Figure 13-3. Figure 13-3 Combining sqlcmd variables and T-SQL statements
490 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Summary SqlCmd replaces the old isql nd osql utilities and at the same time brings with it several new features that enable you to create more powerful and flexible scripts. In this chapter you saw how to use the new SqlCmd utility both interactively and in batch. You also learned about its command line parameters, how to include scripts for added functionality, and how to use variables in your SqlCmd scripts.
APPENDIX SQL Profiler IN THIS APPENDIX Starting SQL Profiler Starting, Pausing, and Stopping a Trace Replaying a Trace Showplan Events 491 Copyright © 2006 by The McGraw-Hill Companies. Click here for terms of use.
492 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e 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 saved data can then be replayed and analyzed at a later date. 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. Analyze and streamline the performance of slowly executing queries. Perform query analysis by saving Showplan results. Identify the cause of a deadlock. Debug TSQL statements and stored procedures. Perform stress and benchmark testing by replaying traces. Replay traces of one or more users. Audit and review activity that occurred on an instance of SQL Server. Aggregate trace results to allow similar event classes to be grouped and analyzed. 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. To run SQL Profiler, users need to have the ALTER TRACE permission granted to them. 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.
Appendix: SQL Profiler 493 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. Options under the General tab allow you to Name your trace in the Trace name text box. Select a template to use. This drop-down is populated with the predeﬁned templates and any user-deﬁned templates created for the current trace provider type. The predeﬁned templates are shown in Table A-1. Save your trace to a ﬁle. The trace data is captured to a .trc ﬁle. Save your trace to a table. The trace data is captured and saved to a database table. Enable a trace stop time. You can set the date and time for the trace to end and close itself. Figure A-1 Profiler Trace Properties – General
494 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Template Name Template Purpose and Event Classes Standard (default) Captures stored procedures and TSQL batches that are run. Use: Monitor general database server activity. Classes: Audit Login, Audit Logout, ExistingConnection, RPC:Completed, SQL:BatchCompleted, SQL:BatchStarting SP_Counts Captures stored procedure execution behavior over time. Classes: SP:Starting TSQL Captures TSQL statements submitted to SQL Server by clients and the time issued. Use: Debug client applications. 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 (in milliseconds). Groups them by duration. Use: Identify slow queries. Classes: RPC:Completed, SQL:BatchCompleted 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. Use: Investigate queries from a particular client or user. 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. Use: Performance tuning, benchmark testing. Classes: CursorClose, CursorExecute, CursorOpen, CursorPrepare, CursorUnprepare, Audit Login, Audit Logout, Existing Connection, RPC Output Parameter, RPC:Completed, RPC:Starting, Exec Prepared SQL, Prepare SQL, SQL:BatchCompleted, SQL:BatchStarting TSQL_SPs Captures information about executing stored procedures. Use: Analyze the component steps of stored procedures. Classes: Audit Login, Audit Logout, ExistingConnection, RPC:Starting, SP:Completed, SP:Starting, SP:StmtStarting, SQL:BatchStarting Tuning Captures information about stored procedures and TSQL batch execution. Use: Produce trace output for Database Engine Tuning Advisor to use as workload to tune databases. Classes: RPC:Completed, SP:StmtCompleted, SQL:BatchCompleted Table A-1 Predefined Templates
Appendix: SQL Profiler 495 Figure A-2 Profiler Trace Properties - Events Selection 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 Column Filters button starts an Edit dialog allowing you to set criteria for column filtering. 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.
496 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Figure A-3 SQL Profiler trace Starting, Pausing, and Stopping a Trace 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. The options are found under File | Run Trace, File | Pause Trace, and File | Stop Trace. 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.
Appendix: SQL Profiler 497 When you stop a trace, data ceases to be captured. After a trace is stopped, previously captured data will be lost when it is restarted, unless the data has been captured to a trace file or trace table. 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. Replaying a Trace SQL Profiler contains the ability to save a trace and replay it at a later time. This replay ability allows you to reproduce activity captured in a trace. SQL Profiler features a multithreaded playback engine that can simulate user connections and SQL Server Authentication. Replay is especially helpful in troubleshooting an application or process. 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. These options make it easier for you to break up the replay of a trace into shorter, more manageable segments for analysis. 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. If you select the Showplan XML, Showplan XML For Query Compile, or Showplan XML Statistics Profile event, a third tab called Events Extraction Settings will be
498 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Figure A-4 Trace Properties Performance Events Showplan Event Description Performance Statistics Shows when a compiled Showplan is cached, recompiled, and dropped from the plan cache. 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 Text (Unencoded) Shows (as plain text) the query plan for the executing TSQL statement. Showplan XML Shows an optimized query plan, including data collected during query optimization. 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