SQL Server database optimization analysis (graphic analysis), SQL Server database

SQL Server database optimization analysis (graphic analysis), SQL Server database

The following illustration is illustrated as follows:

I. SQL Profiler

Event class Stored Procedures \ RPC: Completed TSQL \ SQL: BatchCompleted

EventSequence, EventClass, SPID, DatabaseName, Error, StartTime, TextData, HostName, ClientProcessID, ApplicationName, CPU, Reads, Writes, Duration, RowCounts

1. Track slow SQL statements 

2. Tracking SQL Execution errors

3. Find the SQL statement in the debugging and use special characters as the filter conditions.　　

4. T-SQL query trace table

A. Set the capture time segment

B. Keep it as a file and use the following statement to query the file

Copy codeThe Code is as follows:
SELECT EventSequence, SPID, RequestID, DatabaseId, DatabaseName, LoginName, StartTime, EndTime, TextData, Error, Duration/1000 AS Duration, Reads, CPU, Writes, RowCounts, HostName, ClientProcessID, applicationName INTO bak. dbo. trace ROM: fn_trace_gettable ('C: \ trace \ DB50 20150623. trc', default)

Ii. Table Storage Structure

1. Page Structure

Zone 2 Structure

3. File Storage

4. base table

5. Heap table

6. Clustered Index

 

7. Common Indexes

 

Iii. INDEX OPTIMIZATION

1. Put the fields with high selectivity and uniqueness at the beginning

2. overwrite the Select, Where, and Orderby fields in the index or INCLUDE fields.

3. Control the number of indexes. Narrow indexes have little effect on this graph index.

4. Improve SQL statements

A. SQL should be as simple as possible

B. Column Name operator defined by parameterized SARG <constant or variable> Name = 'zhang san' and price> 5000

C. Non-SRAG name like '% Zhang' Name = 'zhang san' and price> 5000 NOT ,! =, <> ,! <,!> , Not exists, not in, NOT LIKE4. Execution Plan

-- Display statistics

Copy codeThe Code is as follows:
Set statistics io on set statistics time on select * FROM dbo. FreezeUserMoney

The focus of enabling the execution is as follows:

The above is all about optimizing the SQL Server database. I hope you will like it.