SQL Server database optimization analysis (graphic analysis) _mssql

The following are shown in the Illustrated form:

First, SQL Profiler

Event class Stored procedures\rpc:completed tsql\sql:batchcompleted

Event-critical Fields Eventsequence, EventClass, SPID, DatabaseName, Error, StartTime, TextData, HostName, ClientProcessID, ApplicationName, CPU, reads, writes, Duration, rowcounts

1, tracking slow SQL

2. Tracking SQL Execution Errors

3, debugging found in the SQL with special characters as a filter criteria　　

4. T-SQL query trace table

A, set crawl time segment

b, keep the file and query the file with the following statement

Copy Code code 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.traceFROM:: fn_ Trace_gettable (' C:\trace\DB50 20150623.trc ', default)

Second, table storage structure

1, page structure

2 District Structure

3 File Storage

4, base table

5. Heap Table

6. Clustered index

7. General Index

Third, index optimization

1, high selectivity high uniqueness of the field put the front

2. Overwrite index Select, where, and by-by fields are in the index or include this will go to the index

3, the control index quantity, narrow index This graph index function is not big

4. Improve SQL statements

A, SQL as simple as possible

b, parameterized sarg definition column name operator < constant or variable > name= ' John ' and Price >5000

C, Srag name like '% ' name= ' John ' and price >5000 not,!=, <>,!<,!>, not EXISTS, not in, not-like four, execution plan

--Display statistics

Copy Code code as follows:

Set STATISTICS IO on set STATISTICS time on the SELECT * FROM dbo. Freezeusermoney

Open the executive focus and look at a few places below.

The above is for optimizing SQL Server database All content, hope everybody can like.