Lab Answer Key: Module 8: Tracing SQL Server Activity

Sep 26, 2016 - query over . the workload. Select the. Recommended. Statement frequently used. the and then right-click. Minimize. Management. Studio. using.
Télécharger le PDF
3MB taille 14 téléchargements 321 vues
commentaire
Report
Lab Answer Key: Module 8: Tracing SQL Server Activity

Lab Answer

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Activity

Lab: Tracing Exercise

Task 1: Prepare

1.

Ensure running,

are both ADVENTUREWORKS\Student

with the 2.

In the D:

click Run as

administrator 3.

Click Yes

file, and then

wait for

Task 2: Create

1.

On the

2.

In SQL

the

MIA-SQL 3.

4.

1 of 9

In the Trace

o

Trace

o

Use

o

Save

In the Trace

properties:

events and

26/09/2016 16:03

Lab Answer Key: Module 8: Tracing SQL Server Activity

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

columns 5.

Select Show Show all

Then clear selected are

shown. 6.

Select Show

StmtCompleted

event. 7.

Click the box, expand

Filter dialog columns so

that only

Task 3: Capture

1.

In the Trace

2.

In the D:

Run with

PowerShell

for

approximately 3.

While the

observe the

activity. 4.

When the

click Stop

Trace. 5.

In the trace,

the

Transact-SQL

Result: After Profiler.

Exercise

Server

Recommendations

Task 1: Create

2 of 9

26/09/2016 16:03

Lab Answer Key: Module 8: Tracing SQL Server Activity

1.

In SQL

2.

When the

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Advisor. database

engine 3.

In the Database

Tune

InternetSales 4.

Under Workload \Starter\InternetSales

\Labfiles\Lab08 trace from

SQL Server 5.

In the Database

6.

In the Select its tables

InternetSales. note that all of checkbox for the

CurrencyRate 7.

On the Advanced

recommendations. Then click possible, and

click OK

Task 2: Generate

1.

In the Database

2.

When the

. index

recommendations 3.

On the as DTA

recommendations script click OK

when notified

Task 3: Validate

1.

In the Database session,

3 of 9

InternetSales frequency

26/09/2016 16:03

Lab Answer Key: Module 8: Tracing SQL Server Activity

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

report. 2.

View the

3.

In the Select

4.

View the Statement

5.

Select the

workload.

Recommended frequently used. and then

right-click 6.

Minimize Studio.

Management using

Windows 7.

In SQL

statement you

copied 8.

In the Available menu, click

the Query breakdown of the tasks

that the 9.

Note that

index that would

improve

the left side

of the query

is displayed

in a tooltip. 10. In SQL saved from

script you \Labfiles\Lab08\Starter folder.

Then click 11.

Close the

estimated

execution 12. On the query processor over the

note that the the mouse the Estimated

Subtree

4 of 9

26/09/2016 16:03

Lab Answer Key: Module 8: Tracing SQL Server Activity

Result: After Tuning Advisor,

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Engine

Exercise

Task 1: Export

1.

In SQL menu, point

on the File SQL Server

2005 2.

Save the

\Labfiles\Lab08

\Starter

Task 2: Run

1.

In SQL D:\Labfiles\Lab08\Starter

script file in the Profiler in the

previous 2.

View the replace

sp_trace_create, \Labfiles\Lab08\Starter\InternetSales.

3.

Click Execute

4.

In the D:

Run with

PowerShell

for

approximately 5.

While the click New TraceID

Studio and replacing not execute

the code DECLARE

5 of 9

26/09/2016 16:03

Lab Answer Key: Module 8: Tracing SQL Server Activity

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

EXEC EXEC GO

6.

When the finished),

workload has trace.

Task 3: View

1.

In SQL following

code, add the and duration

for each SELECT FROM \InternetSales. WHERE

2.

Select the

3.

Close SQL

view the results. Database Engine

Tuning

Result: After

6 of 9

26/09/2016 16:03

Lab Answer Key: Module 8: Tracing SQL Server Activity

7 of 9

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

26/09/2016 16:03

Lab Answer Key: Module 8: Tracing SQL Server Activity

8 of 9

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

26/09/2016 16:03

Lab Answer Key: Module 8: Tracing SQL Server Activity

9 of 9

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

26/09/2016 16:03