Optimizing Queries and Tuning SQL Server For this Lab you will use the default instance of...

 

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