For this Critical Thinking Assignment, you will complete the hands-on activity,
ID: 3723151 • Letter: F
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 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. Using the Windows Performance Monitor utility, add a set of counters to the real-time performance monitor display that you consider most relevant to the performance of your SQL Server instance. Document this step by taking a screen shot of the real-time display. 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. Identify the most costly plan steps. Are any of the steps that you identified in Step 2(a) a cause for concern? If so, explain. Describe how the performance of the query could be improved, or explain why the query is already optimal. 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. 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. Launch the Database Engine Tuning Advisor from Query Editor to analyze your query. Did the analysis generate any recommendations? Explain why or why not. Deliverable After you have completed these steps, submit one MS Word document that contains the screenshots and a written report that describes the performance and monitoring activities you have performed. Insert all of the screenshots into the document, label each of them clearly, and include the 1-page minimum report below the screenshots.
Explanation / Answer
SQL Server query execution plans are very useful in query performance analysis and troubleshooting. There are two types of query execution plans in SQL Server: actual and estimated. They show how a query was executed and how it will be executed
Query Optimizer is a SQL Server component that creates query execution plans based on the database objects used, indexes, joins, number of output columns, etc. The plans are represented graphically where each operator is represented with an icon
In this article, we will show a series of examples for basic T-SQL queries, explain the SQL Server query execution plan, and its components for each example. We will show why indexing is important and how it affects the query execution plan structure and cost
Clustered and nonclustered indexes
Indexes are used to enable faster access to table and view records. When executing a SELECT statement against a table without indexes, SQL Server has to read all records, one by one, to find the records requested by the statement. Thanks to indexes, SQL Server can easily find the rows associated to the key values, therefore doesn’t have to read every table row and the data obtaining process is more efficient
Indexes can be built on one or more table or view columns. A nonclustered index contains a pointer to the data row that contains the key value. A clustered index stores and sorts the data based on the key values. Searching for a specific value in a clustered table (a table with a clustered key) is easy like searching for a name in an alphabetically ordered address book. When there is no clustered index on the table, the data is unordered and searching for a specific value requires more time and resources
One of the disadvantages of using indexes is that the table indexes are automatically updated whenever table data is changed (inserted, deleted, updated), thus increasing performance costs. Another cost is that more disk space is used
A table can have only one clustered index and multiple nonclustered ones. The logical explanation for this is that a clustered index sorts the data by the index value, and the data in the table can be sorted only in one order
SELECT * on a table with a clustered index
In the first example, we’ll use the Person.Address table in the AdventureWorks database. Note that the table has a clustered index created on the AddressID column and a nonclustered key on the StateProvinceID column
CREATE TABLE [Person].[Address](
[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AddressLine1] [nvarchar](60) NOT NULL,
[AddressLine2] [nvarchar](60) NULL,
[City] [nvarchar](30) NOT NULL,
[StateProvinceID] [int] NOT NULL,
[PostalCode] [nvarchar](15) NOT NULL,
[SpatialLocation] [geography] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_Address_StateProvinceID] ON [Person].[Address]
(
[StateProvinceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE [Person].[Address](
[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AddressLine1] [nvarchar](60) NOT NULL,
[AddressLine2] [nvarchar](60) NULL,
[City] [nvarchar](30) NOT NULL,
[StateProvinceID] [int] NOT NULL,
[PostalCode] [nvarchar](15) NOT NULL,
[SpatialLocation] [geography] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_Address_StateProvinceID] ON [Person].[Address]
(
[StateProvinceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO