Optimizing Queries and Tuning SQL Server For this Lab you will use the default instance of...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Optimizing Queries and Tuning SQL Server For this Lab you will use the default instance of SQL Server that you installed during Lab 1. Partial answers or answers that are partially correct will result in minimal partial credit. 1. Run the SQL Script Included with this Lab specification (the Microsoft Recursive CTE example) and show me the Estimated Execution Plan, Actual Execution Plan, and Client Statistics for several runs. I will need a screenshot of each (3 screenshots, make sure the statistics show several runs). Please record your screenshots in a Word Document for upload. 2. Create a simulated Workload file by running the SQL Profiler on you instance of SQL Server while executing the included script file twenty times (20). Include your simulated Workload file in your upload. 3. Run the Database Engine Tuning Advisor on the AdventueWorks database using the Workload file generated in Step 2. I will need a screenshot of the Database Engine Tuning Advisor recommendation screen. Please record your screenshot in the Word Document created in Step 1 for upload. 4. If there were tunings suggested in Step 3, implement it if the improvement is above 5%. Save the requested tunings to an SQL script for upload, execute the script, and then rerun the Database Engine Tuning Advisor. I will need a screenshot of the Database Engine Tuning Advisor recommendation screen. Please record your screenshot in the Word Document created in Step 1 for upload. Also make sure to keep the script with the requested tunings. I will require this to be uploaded as well. 5. If there were tunings suggested in Step 3, clear your client statistics, and rerun the query from Step 1 a couple of times looking at the Client Statistics and the Actual Execution Plan. Please take a screenshot of the Client Statistics Comparison and the Actual Execution Plan. Include these in the Word Document from Step 1 for upload. 6. Answer the following questions (if there were tunings indicated with over 5% improvement). Include these in the Word Document from Step 1 for upload. a. Why do you think the Database Engine Tuning Advisor indicated that you should implement the requested tuning(s)? b. Did the tunings you implemented (if you did) make a difference? Deliverables 1. The Microsoft Document file created in Step 1 and added to throughout the Lab. It should have multiple screenshots as well as the answers to the Item 6 questions. 2. Simulated Workload File from Step 2. 3. The SQL Script that has the tunings suggested by the Database Engine Tuning Advisor from Step 4. Rubric 1. Correctly running the SQL Server Profiler to create Workload file - 30 points 2. Correctly running the Database Engine Tuning Advisor - 30 points 3. Correctly implementing the Tuning Advise from the Tuning Advisor - 30 points 4. Correctly answering questions 6a and 6b - 10 points Optimizing Queries and Tuning SQL Server For this Lab you will use the default instance of SQL Server that you installed during Lab 1. Partial answers or answers that are partially correct will result in minimal partial credit. 1. Run the SQL Script Included with this Lab specification (the Microsoft Recursive CTE example) and show me the Estimated Execution Plan, Actual Execution Plan, and Client Statistics for several runs. I will need a screenshot of each (3 screenshots, make sure the statistics show several runs). Please record your screenshots in a Word Document for upload. 2. Create a simulated Workload file by running the SQL Profiler on you instance of SQL Server while executing the included script file twenty times (20). Include your simulated Workload file in your upload. 3. Run the Database Engine Tuning Advisor on the AdventueWorks database using the Workload file generated in Step 2. I will need a screenshot of the Database Engine Tuning Advisor recommendation screen. Please record your screenshot in the Word Document created in Step 1 for upload. 4. If there were tunings suggested in Step 3, implement it if the improvement is above 5%. Save the requested tunings to an SQL script for upload, execute the script, and then rerun the Database Engine Tuning Advisor. I will need a screenshot of the Database Engine Tuning Advisor recommendation screen. Please record your screenshot in the Word Document created in Step 1 for upload. Also make sure to keep the script with the requested tunings. I will require this to be uploaded as well. 5. If there were tunings suggested in Step 3, clear your client statistics, and rerun the query from Step 1 a couple of times looking at the Client Statistics and the Actual Execution Plan. Please take a screenshot of the Client Statistics Comparison and the Actual Execution Plan. Include these in the Word Document from Step 1 for upload. 6. Answer the following questions (if there were tunings indicated with over 5% improvement). Include these in the Word Document from Step 1 for upload. a. Why do you think the Database Engine Tuning Advisor indicated that you should implement the requested tuning(s)? b. Did the tunings you implemented (if you did) make a difference? Deliverables 1. The Microsoft Document file created in Step 1 and added to throughout the Lab. It should have multiple screenshots as well as the answers to the Item 6 questions. 2. Simulated Workload File from Step 2. 3. The SQL Script that has the tunings suggested by the Database Engine Tuning Advisor from Step 4. Rubric 1. Correctly running the SQL Server Profiler to create Workload file - 30 points 2. Correctly running the Database Engine Tuning Advisor - 30 points 3. Correctly implementing the Tuning Advise from the Tuning Advisor - 30 points 4. Correctly answering questions 6a and 6b - 10 points
Expert Answer:
Related Book For
Concepts of Database Management
ISBN: 978-1285427102
8th edition
Authors: Philip J. Pratt, Mary Z. Last
Posted Date:
Students also viewed these databases questions
-
Design a Java class that represents a cache with a fixed size. It should support operations like add, retrieve, and remove, and it should evict the least recently used item when it reaches capacity.
-
What is the difference between MouseListener and MouseAdapter? [3 marks] (b) Via suitable HTML, the compiled version of the following Java code is presented to the appletviewer application: import...
-
In Exercises 8194, begin by graphing the absolute value function, f(x) = |x| . Then use transformations of this graph to graph the given function. h(x) = x +31-2
-
The efficiency ratio for a steel specimen immersed in a phosphating tank is the weight of the phosphate coating divided by the metal loss (both in mg/ft2). The article "Statistical Process Control of...
-
iCover produces bags for carrying laptop computers. iCover sells 1,000,000 units each year at a price of $20 per unit and a contribution margin of 40%. To respond to customer complaints, iCover's...
-
How would you advise Microcom in developing a strategy to implement relationship marketing and selling?
-
1. Calculate the Altman Z-score for Ford and Toyota. What inferences do you draw from these values? What inferences do you draw from comparing the values across the two companies? (You may assume...
-
Explain the "elements of value" concept and note how firms use this concept to increase revenue. Provide a detail answer.
-
In November 2015, John Wells, a customer service representative of Bayfield Mud Company, was summoned to the Houston warehouse of Wet-Land Drilling, Inc., to inspect three boxcars of mud treating...
-
The Farm House restaurant makes their own bread to serve in their restaurant. They have been approached by a local bakery offering to make the bread for them. The restaurant has two choices: 1)...
-
A model rocket is fired from the roof of a 50 ft. tall building as shown in the figure below. The height of the rocket is given by: y(t) 150t 16t2+ 50ft Find the following. a) The initial velocity...
-
The consulting firm Blossom Financial employs 43 full-time staff. The estimated compensation per employee is $109,190 for 1,790 hours. It charges all direct labour costs to clients. It includes any...
-
Larry, lan, Sarah, and Sergio have all been invited to a dinner party. They arrive randomly and each person arrives at a different time. a. In how many ways can they arrive? b. In how many ways can...
-
Dunphy Company issued $32,000 of 7.0%, 10-year bonds at par value on January 1. Interest is paid semiannually each June 30 and December 31. QS 14-3 (Algo) Financial statement Impact of bond...
-
BSF Co., which produces and sells skiing equipment, is financed as follows: Bonds payable, 10% (issued at face amount) $550,000 Preferred 1% stock, $10 par 550,000 Common stock, $25 par 550,000...
-
Shareholders may recognize gains in a corporation reorganization. Explain how the gains are treated for tax purposes. Do you think that companies ever avoid the tax-free treatment intentionally Why...
-
Q:1 Take any product or service offered in Pakistan and apply all determinents of customer Perceived value ?
-
You need to delete the Order Line table from the TAL Distributors database. Will the following command work? Why or why not?
-
How can splitting a table improve performance?
-
What is generalization?
-
A drainage ditch is to be built to carry runoff from a subdivision. The maximum design capacity is to be $1 \mathrm{million} \mathrm{gph}(\mathrm{gal} / \mathrm{h})$ and it is to be concrete lined....
-
An open drainage canal with a rectangular cross section is $3 \mathrm{~m}$ wide and $1.5 \mathrm{~m}$ deep. If the canal slopes $950 \mathrm{~mm}$ in $1 \mathrm{~km}$ of length, what is the maximum...
-
An open drainage canal is to be constructed to carry water at a maximum rate of $10^{6} \mathrm{gpm}$. The canal is concrete lined and has a rectangular cross section, with a width that is twice its...
Study smarter with the SolutionInn App