Anatomy of SQL Server database optimization

First, SQL Profiler

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

Event key Fields Eventsequence, EventClass, SPID, DatabaseName, Error, StartTime, TextData, HostName, ClientProcessID, ApplicationName, CPU, Reads, writes, Duration, rowcounts

1. Track Slow SQL

　　　　

2. Tracking SQL Execution errors

　　　　

　　　　

3. Find SQL in Debug

Use special characters as filter criteria

　　　　

　　4. T-SQL query trace table

A, set the time period of the crawl

b, keep it as a file and then query the file with the following statement

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

7. Clustered index

8. General Index

Third, index optimization 1, high selectivity

The field with the highest uniqueness is put first

2. Overlay Index

The Select, where, and by fields are all in the index or include so that they go to the index

3, control index number, narrow index

This graph index is not very useful

4. Improve SQL statements

A, SQL as simple as possible

b, parameterized sarg definition column name operator < 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

Iv. Plan of Implementation

-- To display statistical information SET STATISTICS  on SET STATISTICS  on SELECT *  from dbo. Freezeusermoney

Open execution focus look at the next few places

Anatomy of SQL Server database optimization