Rose-Hulman CSSE 333 - Analyzing and Optimizing Queries

Unformatted text preview:

CSSE333 Introduction to Databases – Lab Assignment Analyzing and Optimizing Queries Objective After completing this lab, you will be able to: • Analyze the performance of queries using text-based and graphical analysis tools. • Perform some common optimizations on SELECT statements. Required Materials - SQL Server Management Studio - Your copy of the Northwind database Related Reading • http://www.sql-server-performance.com/ This site, although geared towards Microsoft SQL Server optimization, also contains helpful pointers for optimizing queries on relational databases in general. • http://www.bcarter.com/optimsql.htm Additional information on SQL query optimization. Coverage of SQL query optimization is not as broad as what is provided by the above site, but it is much more focused and accessible. Formatting Details Tasks in this lab are written on a green background and are enclosed by a dashed border. This is an example of text describing a task. Tasks in this lab with a deliverable item are written on a yellow background and are enclosed by a solid border. This is an example of text describing a task with a deliverable. Turn-in Instructions The only turn-in is the ANGEL survey title “Lab Questions”. Assignment Details (1) Query Analysis Tools SQL Server provides a comprehensive suite of tools to facilitate query analysis and optimization. We will take a brief look at a few of them in this part of the lab. The tools covered in this section are the execution plan viewer and retrieval mechanisms for query statistics. A) The Execution Plan The query optimizer is a component of a relational database system that both optimizes a standardized1 version of a user’s SQL query and creates an execution 1 The process of query standardization occurs after parsing. The major action done in query standardization is elimination of redundant clauses.CSSE333 Introduction to Databases – Lab Assignment plan to be executed by the database access routines. It optimizes the query on the basis of CPU and I/O cost, which it measures in seconds. SQL Server includes two tools to view the execution plan devised by the query optimizer. B) The Execution Plan Viewer We will first discuss the graphical execution plan viewer in SQL Server Management Studio, and then briefly cover the text-based analysis tool provided by SQL Server In SQL Server Management Studio, click Query > Include Actual Execution Plan while you are in Query Editor. This action will cause the SQL Server Management Studio to request the execution plan for a query from SQL Server and present a graphical representation of the execution plan. After ensuring that you are using your copy of the Northwind database, input and execute the following query: SELECT o.OrderID, c.CustomerID, c.ContactName, p.ProductName, (od.UnitPrice * od.Quantity * (1 - od.Discount)) AS Subtotal FROM [Order Details] AS od, Orders AS o, Customers AS c, Products AS p WHERE od.OrderID = o.OrderID AND c.CustomerID = o.CustomerID AND p.ProductID = od.ProductID ORDER BY Subtotal DESC This query retrieves information on the products ordered by customers, as well as each order’s subtotal. It is ordered by Subtotal in descending order to highlight the customers with the largest subtotals. (Modulo changes you have made to your copy of Northwind in previous labs, this query should return 2155 rows.) When the query completes, SQL Server Management Studio will have a third tab in its results pane labeled “Execution Plan”. This is like the estimated execution plan from the Indexes lab, but shows what query steps were actually used. The execution plan should resemble the screen below.CSSE333 Introduction to Databases – Lab Assignment Your screen will (hopefully) be more legible than this example. You can zoom the Execution Plan display by right-clicking on the display and selecting one of the Zoom options. The execution plan is designed to be read from right-to-left. Inputs into each node are indicated by arrows, and each node (except left- and right-most nodes) operate on their inputs in some fashion. Hovering the mouse pointer over an item will cause a tooltip resembling the image below to appear.CSSE333 Introduction to Databases – Lab Assignment The tooltip provides information such as the operation being executed, the number of rows being operated on, I/O, CPU, and aggregate costs, and the actual SQL code used to invoke the operation. C) Viewing the Execution Plan through SQL Server It is also possible to retrieve details on the execution plan directly from SQL Server. This section briefly describes how to do so. Modify the query you used in part (B) by placing the SQL commands SET SHOWPLAN_ALL ON GO at the top of the query. Disable the graphical execution plan, and then re-run the query. The SHOWPLAN_ALL flag causes SQL Server to return a table with the same information present in the graphical execution plan viewer. To disable the plan, you must issue the commands SET SHOWPLAN_ALL OFF GO Set the SHOWPLAN_ALL flag to off by issuing the commands SET SHOWPLAN _ALL OFF GO D) Other Analysis Tools In addition to a query’s execution plan, SQL Server provides aggregate statistics on a query’s performance. To see these statistics, you need to set the STATISTICS flag to ON. Ensure that SHOWPLAN_ALL and the graphical execution plan are disabled, and then set the STATISTICS flags to ON by issuing the commands SET STATISTICS IO ON SET STATISTICS TIME ON GO Execute this query, and then re-run the query in part (B). Look at the “Messages” tab to see the statistics. The STATISTICS IO and STATISTICS TIME flags cause SQL Server to output aggregate statistics on I/O (disk) usage and processing time respectively in the “Messages” tab.CSSE333 Introduction to Databases – Lab Assignment (2) Indexes and Other Table Optimizations Indexes are a major part of query optimization. Used correctly, they can drastically reduce I/O load on relational databases, especially on aggregate operations such as COUNT, MAX, MIN, and so forth. In some cases, data for queries can be retrieved directly from a table index, thus eliminating any need to scan through the table to retrieve data. When the entire query can be satisfied by the data in an index, the index is said to cover the query. However, indexes are not a cure-all for SQL query optimization problems. Indexing


View Full Document

Rose-Hulman CSSE 333 - Analyzing and Optimizing Queries

Download Analyzing and Optimizing Queries
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Analyzing and Optimizing Queries and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Analyzing and Optimizing Queries 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?