SQL Performance Detection Tool: SQL Server Profiler and Optimization tool: Database Engine Tuning Advisor

Original: SQL Performance Detection Tool: SQL Server Profiler and Optimization tool: Database Engine Tuning Advisor

I. Summary of TOOLS? database application system performance is low, it needs to be optimized,?? If you do not know where the problem is, you can use the Performance Detection Tool SQL Server Profiler.? If you know where the problem is, but don't know how to solve it, You can use Database Engine Tuning Advisor Tuning Advisor II, SQL Server Profiler features: detects all operations in the database, logs the monitored content to a database or a file. FILE--New trace--Displays the Tracking Properties window


The first select% is a screening 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% ... The
takes the row that excludes the value, and then determines, runs. Then run a select in the database. You'll find him detected.

Each column to the right, starting with EventClass, I'll tell you what it is.
???????????????????????????, application name, operating system user, database user, CPU occupancy, read database count, write database times, execute script, application process number, start time, end time, etc.
?????????????????????, you put the mouse on it. Take a good look at it and pick up the event according to your own needs.
??? and then file-->> as, you can save these monitored data as a file, or a data table.

1. Find the longest-lasting query?? In general, the query statement with the longest query time is the reason for the most performance impact. 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 Tracefiledatatablewhere eventclass=12--equals 12 for batchcompleted events and cpu< (0.4* Duration)--if the CPU occupies less than 40% of the time it takes to execute the SQL statement, it means that the statement waits too long

2. What are the most resource-intensive queries?? 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).

Third, Database Engine Tuning Advisor Tuning Advisor?? The Database Engine Tuning Advisor first accepts SQL Server Profiler-detected SQL, views, stored procedures, data structures, and so on, and then analyzes it himself, giving better index, statistics, partitioning, and so on.

After you open the file you saved in the previous tool, you select the file in the workload here, the table selects the table, the database to be analyzed and the database is selected, that is, the following database selection for workload analysis, with the following database and table to optimize

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

SQL Performance Detection Tool: SQL Server Profiler and Optimization tool: Database Engine Tuning Advisor