Question
For this Critical Thinking Assignment, you will complete the hands-on activity, Project 7-1: Monitoring and Analyzing Performance,based on p. 272 of the MCSA guide to
For this Critical Thinking Assignment, you will complete the hands-on activity, Project 7-1: Monitoring and Analyzing Performance,based on p. 272 of the MCSA guide to Microsoft SQL server 2012textbook.
For this hands-on project, you will use the SQL Server named instance SQLSERVERHOA, and the HandsOnOne database and tables you created in previous assignments. The objective of this project is to practice using Windows utilities and SQL Server management tools to monitor performance and diagnose query performance.
2. In SQL Server Management Studio, construct a SQL query that uses a SELECT statement and a JOIN clause to query data from both the Customer and Address tables. Analyze the estimated execution plan.
a. Identify the most costly plan steps.
b. Are any of the steps that you identified in Step 2(a) a cause for concern? If so, explain.
c. Describe how the performance of the query could be improved, or explain why the query is already optimal.
3. Implement any changes identified in Step 2(c) and execute the query using the option Include Actual Execution Plan. Document this step by taking a screen shot of the actual execution plan.
4. Use SQL Server Profiler to capture a trace and execute the query again. Analyze the trace and determine how many milliseconds the query took to execute. Take a screen shot of the trace that shows the execution of your query to document this step.
5. Launch the Database Engine Tuning Advisor from Query Editor to analyze your query. Did the analysis generate any recommendations? Explain why or why not.
Additional Sample Data for Customer Table
CustomerID | CustomerName | CustomerAddressID |
1 | Western Supply Company | 1 |
2 | Nick Harper | 3 |
3 | Alice Harper | 3 |
4 | Abacus Consulting | 4 |
Additional Sample Data for the Address Table
AddressID | Street | City | State | Zip Code |
1 | 2400 Broadway Drive | Missoula | MT | 59802 |
2 | 320 21st Street | Billings | MT | 59101 |
3 | 439 Skyline Blvd | Denver | CO | 80002 |
4 | 56 Park Avenue | New York | NY | 1000 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started