Microsoft SQL Server 2008 Administration for Oracle DBAs in Visual Studio .NET

Drawer QR Code in Visual Studio .NET Microsoft SQL Server 2008 Administration for Oracle DBAs

Whether the execution plan was cached or not, during execution the Database Engine steps through the logical operations defined in the plan, carrying out each one

Where queries contain SELECT statements, execution is followed by a fetch phase, which actually returns the results of the query to the user or application Any formatting required for values such as dates and times and certain numeric values is carried out during this phase

SQL Server execution plans are a representation of the data access paths chosen by the query optimizer and show the sequence of data access operations and the cost associated with them in various formats Being able to see the choices made by the optimizer is vital to administrators looking to understand where performance issues are occurring or where performance can be further improved You can generate execution plans for the queries you execute using the following syntax:

SET SHOWPLAN_TEXT {ON|OFF} Produces a textual representation of the execution plan as a set of rows that forms a hierarchical tree representing the steps taken by the SQL Server query processor as it executes each statement The format is similar to Oracle s EXPLAIN PLAN Produces a more verbose version of the preceding syntax, designed for applications able to handle this output not for readability Produces an XML document describing the execution plan, which can be used by the Management Studio and other applications to provide a graphical representation of the plan See the following example

SET SHOWPLAN_ALL {ON|OFF} SET SHOWPLAN_XML {ON|OFF}

Figure 6-1

GO SELECT TOP 5spName, stTaxRate FROM SalesSalesTaxRate st JOIN PersonStateProvince sp ON stStateProvinceID = spStateProvinceID WHERE spCountryRegionCode = 'US' ORDER BY stTaxRate desc

Executing the preceding example produces the results shown in Figure 6-1 Viewing the output at this level of detail, the results using SHOWPLAN_ALL will be very similar to those shown in Figure 6-1

Perhaps the most useful Showplan format for administrators (and the one that is recommended for use going forward in SQL Server) is the XML Showplan The XML created by this Showplan can be interpreted by SQL Server Management Studio so that the execution plan is displayed using icons rather than the tabular representation produced by the SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT statements This graphical approach is very useful for understanding the performance characteristics of a query XML Showplans can be created using the syntax shown in the preceding example or by using the SQL Server Management Studio features highlighted in Figure 6-2

Figure 6-2

In Figure 6-2, the toolbar button marked 1 is Display Estimated Execution Plan and the button marked 2 is Display Actual Execution Plan Display Estimated Execution Plan works in the same fashion as the SET SHOWPLAN_ statements in that it causes an execution plan to be returned without executing the query If Display Actual Execution Plan is clicked, the query is executed prior to the execution plan being returned Whether the Showplan is created using SET SHOWPLAN_XML or by selecting either of the Management Studio toolbar options, the output generated for a sample query is shown in Figure 6-3 The individual data access operations are displayed as icons, with the operational sequence represented as a flow and each icon accompanied by summary information detailing The type of operation The object name, where applicable The cost of the operation, relative to the other operations within the batch If a batch contains multiple queries, the execution plan is returned in multiple rows (the example in Figure 6-3 contains one query and shows one such row) In this case, the relative overall cost for each query within the batch is displayed Each icon in an XML Showplan can be made to reveal much more information with regard to the operation than initially displayed in the execution plan window To do this, right-click the icon and select Properties; this opens the Properties dialog box for the operation, an example of which is shown in Figure 6-4

Figure 6-3