SQL Server Database optimization Combat (ii)

Objective:

This chapter focuses on SQL Server Profiler (Profiler), which analyzes the effect of a statement's operation through a sniffer.

--sql Server Profiler

['PR?? Fa?l? (R)]

Profiler

SQL Profiler is a graphical interface and a set of system stored procedures that function as follows:

- Graphical monitoring SQL Server inquiries;

- collect query information in the background;

- analyze performance;

- diagnosing a problem like a deadlock;

- Debug T-SQL statement;

- Analog Replay SQL Server activities;

- You can also use SQL Profiler capture in SQL Server the activity that is performed on the instance. Such an activity is called Profiler tracking.

1. Profiler Tracking

2. Events

an event performance SQL Server the various activities implemented in the

These activities can be simply categorized as event classes:

-- Cursor Events

-- Lock Event

-- Stored Procedure Events

--t-sql Events

-- Resource Pressure

Resource pressure mainly includes the following content:

1 , SQL What kind of activities are involved? CPU use?

2 , how much memory is used?

3 , how much involved i/0 Operation ?

4 , SQL How long did the activity take?

5 , how often does a particular query execute?

6 , what kinds of errors and warnings do queries face?

Http://msdn.microsoft.com/zh-cn/library/ms175927%28v=sql.100%29.aspx

3 , event columns

4 , column Filters

5 , tracking Templates

6 , tracking data

Actual combat:

1 , tracking during the busiest hours of the customer ( 1-2 hours, depending on the situation, some customers have a very large amount of data and may only track - minutes or so, so you can track it multiple times to achieve your goal. )

2 , and the tracked data is saved as a tracking table. (About The amount of data, More than four of the customers in the store can reach this value in less than five minutes)

3 , -- before - the most consumption of CPU the single statement

Select Top Sql_test (nolock) where textdata is not NULL ORDER by CPU DESC

( sql_test is the name of the tracking table you just saved)

4 , --20 a cumulative maximum consumption CPU the statement

Select top substring (textdata,1,200), COUNT (*),

SUM (CPU), sum (CPU)/COUNT (*)

From Sql_test (NOLOCK)

Where TextData is not null

GROUP BY substring (textdata,1,200)

Order by SUM (CPU) desc

5 , Analysis CPU

in general, a single execution CPU Cost value

(Unit Ms , 1000ms=1 seconds):

+ The following are relatively few.

1000-1w is more.

5W the above must be rectified immediately. This is a single value that consumes more resources.

6 , Analysis Duration

[dju?re?? n]

in general, a single execution Duration Cost value

(Unit Ms , 1000ms=1 seconds ;

can be set to microseconds, 1 seconds =1000000 microseconds):

+ The following is a short processing time.

1000-1w is longer processing time.

5W The above is blocking and must be viewed for reasons.

---------------------------Gorgeous split-line-----------------------------------

End of chapter

SQL Server Database optimization Combat (ii)