My view of SQL performance optimization, a welcome comment on my article is proposed!

What are the reasons that affect your query speed?

• Speed is not power, unstable.
• There is not enough memory on the server, or there is not enough memory allocated for SQL.
• SQL statement Design is unreasonable
• There is no corresponding index, the index is unreasonable
• Table data too large no effective partition design
• Database design too 2, there is a lot of data redundancy

So how do we find the reason for the slowness?

• First you need to know whether it is related to SQL statements, to ensure that the machine is not turned on, the server hardware configuration is poor, no net you say P AH
• SQL Server Profiler, analysis of SQL slow related statements, is the execution time is too long, the use of system resources, too many CPU tools
• And then here's what this article is about, SQL optimization methods and techniques, avoiding some unreasonable SQL statements, taking the advantage of SQL
• Then judge whether to use, reasonable statistics.
• A reasonable index is used in the confirmation table.
• Too many data tables, to partition, to narrow the search scope

Parse the time of the SQL statement execution

1. Log the query time and CPU consumption time

Set Statistics  on Select *  from Detitalindex Set Statistics off

2. Record I/O dominance

Set Statistics  on Select *  from dbo. Productsetstatisticsoff

Scan count: Number of indexes or table scans

Logical reads: number of pages read in the data cache

Physical reads: Number of pages read from disk

Read-ahead: Number of pages that are cached from disk during query

LOB logical reads: The number of pages of image,text,ntext or large data read from the data cache

LOB physical reads: The number of pages that are read from disk, Image,text,ntext, or large data

LOB pre-read: The number of pages of image,text,ntext or large data that is placed from the disk during a query

If the number of physical and pre-read times is high, the index can be used for optimization.

If you do not want to use SQL commands to view the content, you can right-click to find the query option in the new SQL Server query

Ching SET STATISTICS time and io OK.

3. View the Execution plan

The mouse over this icon will appear the detailed steps of the query, and see which SQL cost you how much, if which table is too large, to prove that the table index design is incorrect, or performance needs to be improved.

Select is a steel knife, grinding good seconds to kill the Army generals! Grinding is not good is suicide.

1. Make sure not to appear *, use the column instead of *

2. Try to avoid unnecessary columns when using the Where query

3. Use top or distinct to reduce redundant or repetitive

(as the saying goes, what is boring is a double-edged sword)

1.distinct

Distinct is used in the case of querying a field or a few fields, which avoids duplication of data and results in optimization of the query.

However, a large number of query fields are used, which can greatly reduce the query efficiency.

This test result represents the use of Distint, where the database uses CPU to filter the data. So use less distinct.

2. Determine if the table has data

Select Count (*fromselectTop(1 from Indentdetails

It's clear that the following outright

3. Connection Query optimization

Select *  from (Select*from leftjoinson=  Grade.gradeid  Select*from leftjoins on S.gradeid= G.gradeid)

Reducing table joins can improve query performance, but this gap is widened by the size of the data.

Modify Delete SQL Performance optimizations

If the operations are clustered, the CPU utilization is too high to affect other users ' access to the database.

And if the individual iterations are working, then the efficiency is too low.

So we use a compromise approach. Block operations

Delete orderwhere ID<Deletewhere ID>=  +  and ID<Delete orderwhere ID>=2000   and ID< .....