SQL Server performance detection and optimization tools use detailed

Reprint Link: http://www.cnblogs.com/knowledgesea/p/3683505.html

Overview of Tools

If you have a database application system, there are a large number of tables, views, indexes, triggers, functions, stored procedures, SQL statements, and so on, and the performance of the poor, and the bitter you have to optimize it, then what should you do? Brother teaches you, first you need to know where the problem is? If you want to know where the problem is and find him, we can use the performance detection tool described in this article--sql Server Profiler (in SQL Setup file-performance tool--sql Server Profiler)

If you know where the problem appears, if you are a peerless master, of course, can be straight in the key, write a paragraph code to deal with, but if you do not, you do not, then also does not matter, you can use the strength of the elder brother to solve the problem. The secret to your kung fu is---Database Engine Tuning Advisor (in SQL Setup file-performance tool-Database Engine Tuning Advisor)

SQL Server Profiler Features

This tool is more Conan than Conan, because he can detect every move in the database, even if you do not touch him, he is watching you, he is very cheap. He not only monitor, but also monitor the detailed, more detailed a will say, also the monitoring of the contents of the database or file, to your daughter-in-law to tell you how bad the performance of the database, but he will also put a good record for you, good and bad this course needs you to analyze, in fact, he is also a very 2 Conan.

Database Engine Tuning Advisor features

This martial arts is superior martial Arts. Like Zhang Mowgli's big move, first accept SQL Server Profiler detected SQL, view, stored procedures, data structure and so on, and then he analyzed himself, and then in the bosom relay two laps, feel his turn almost, to throw a power more dazzling, better index, statistics, partitioning, and so on advice information. You can't bear it, happly to death. Listen to the following brother to you first talk about our very 2 Conan.

Use of SQL Server Profiler

Open the System main Menu--sqlserver several---performance tools--->>sql server Profiler; you got it? Brother Wait for you, give you a picture of open him, let you see.

Then the file--new trace--Displays the Tracking Properties window

First of all, the select% is a screening and monitoring textdata. That percent is a wildcard, and what he means is to filter the statements of the select openings. Of course you can define it yourself, like update%,delete% ...

Take the line that excludes the value, and then OK, run. Then run a select in the database. You'll find him detected.

Each column to the right, starting from EventClass, I'll tell you what it is.

Event classification, request statement, application name, operating system user, database user, CPU occupancy, read database times, write database session, execute script time, application process number, start time, end time, etc.

Event selection, you put the mouse up, he has the Chinese comments below. Take a good look at it and pick up the event according to your own needs.

Then the file-->> saved as, you can save these monitored data as a file, or a data table.

Analysis:

1. Find the longest-lasting query

In general, a query statement that has the longest query time is the one that most affects performance. It not only occupies a large amount of time in the database engine, but also wastes system resources and affects the interaction speed of the database application system. When the data is optimized with the application system, we first find him, optimize it, and when the tracking is created, tick the tsql-sql:batchcompleted. With stored procedures-rpc:completed. This makes it possible to find the maximum time to query and then analyze and optimize it.

Select Textdata,duration,cpu from < tracked table >where eventclass=12-equals 12 for BatchCompleted event and cpu< (0.4*duration)  --If the CPU time is less than 40% of the time the SQL statement was executed, it indicates that the statement waits too long

2. Queries that most Occupy system resources

Is the amount of CPU time, and the number of read/write Io. Recommended events include Connect, Disconnect, ExistingConnection, sql:batchcompleted, rpc:completed, and columns that contain WRITES,READS,CPU.

3. Detect Deadlocks

In a database with a large number of accesses and concurrency, if the design is slightly unreasonable, it is possible to create deadlocks that can have an impact on system performance. Events include: Rpc:starting, Sql:batchstarting, Lock:deadlock (Deadlock event), lock:deadlockchaining (sequence of events that are deadlocked).

Using Database Engine Tuning Advisor analysis to troubleshoot database performance

Open the System main Menu--sqlserver several---performance tools--->> Database Engine Tuning Advisor, the interface is as follows

After you open the file that you saved in the previous tool, you select the file in the workload here and the table is selected. Don't rush after the election.

The database to be analyzed with the table selected, that is, the following for the workload analysis of the database selection, with the following to optimize the database and table, slowly buckle, put him to choose the right.

Then choose the optimization option you want

As needed, the advanced options are usually the default. Are you sure..

Then click on the upper left corner to have a start analysis.

Analysis complete

Speaking of this,

I feel this martial arts unique knowledge you already have 70% skill, later on your own good fortune. Brother Entrust you more use, diligent use, brain, don't force, drink more than six walnut brain white gold, open cover do not use teeth bite.

Elder brother Go, Life also what Huan, die also why.

SQL Server performance detection and Optimization tool usage details (RPM)