SQL Server Tuning Basics

When it is determined that application performance problems can be attributed to one or several time-consuming statements, tuning these statements is the responsibility of the database administrator or database application developer. Statement tuning is one of the essential skills for dealing with databases.

When you face a "problem" statement, how should you analyze its problem, and finally achieve the purpose of the optimization statement? The first thing to think about is, where is the "problem" statement "question"? In other words, what is the goal you want to optimize. The common requirements are:

1) statement requires access to a large number of data pages, resulting in internal pressure, disk busy and so on.

For this kind of problem, the concern is why does the statement want to access so many data pages? Is the result set of the statement itself is larger, or SQL Server does not have a way to effectively seek, but like the cannon hit the flies from a large amount of raw data to find the results needed to return, or because the data page has a lot of fragments, causing SQL Server to read a lot of pages, But the amount of data in each page is not much. These are all factors to be considered.

2) If the memory is not under pressure (the page that the statement accesses is cached in memory beforehand), the statement runs for a long time.

The run time of a statement is typically mainly spent on these 3 steps: Statement compilation, statement execution, and result set return. The speed returned by the result set is not much related to SQL Server itself, so it is generally not considered when the statement is tuned. When the statement is tuned, you need to figure out how much time has been spent compiling and executing, what time has been optimized, and how to optimize it.

3) A single statement execution time is acceptable, but The amount of bitter CPU usage is large, and multiple statements executing concurrently can cause SQL SERVER CPU to be high.

Some statements can be executed in two seconds and are acceptable to the user. But it may also be in a two-second, or even longer, CPU. If there are more than 10 users running the same statement at the same time, SQL SERVER will be full load. The CPU time of the statement is also divided into the compile and execute phases. The optimizer has to figure out how much CPU resources are used in each of these two phases, and then see if there is any possibility of optimization to reduce CPU usage.

4) statement execution alone does not see a big problem, but concurrent execution is prone to blocking and deadlocks.

This is also an important task of sentence tuning. Many statements execute quickly, and SQL Server can withstand the use of resources, but it is easy to cause blocking and deadlocks. This behavior is often due to the fact that the concurrency of the application on a table or index is particularly high, and the number of locks requested by the problem statement is relatively large. Of course, you can sometimes use query Hint to force SQL Server to use a lock with a smaller granularity. But this is often not the best solution, and it may not solve the problem. The best way to do this is to instruct it to request as few and as small a lock as possible by adjusting the way the statement is run. Here also to do sentence tuning.

When doing these tuning, the first thing to do is to estimate the target statement and see how much space it optimizes. Some of the statements themselves are relatively simple and can be quickly improved by adjusting the index, which is well worth the tuning. Some statements are very complex, or the result set returned is large, and by adjusting the settings here in SQL Server, there is often little room to improve performance. This time to consider whether the statement itself can be implemented in a different way. A lot of time to change the statement, a large statement split into several small sentences, or remove some unnecessary logic, will achieve a multiplier effect

Before talking about how to do the exact method of sentence tuning, you must first introduce the most necessary background knowledge. Do not understand these knowledge, do sentence tuning can only rely on guessing. The required background knowledge mainly includes understanding indexes and statistics, understanding what is statistical and recompiling, and being able to read the execution plan of the statement in a basic sense. Here is an example of the MS sample database Adventurewordks.

--Test case use adventureworks2008goif object_id (' salesorderheader_test ') was not NULL    DROP TABLE dbo. Salesorderheader_testgoif object_id (' dbo. Salesorderdetail_test ') is not a NULL    DROP TABLE dbo. salesorderdetail_testgo--(31465 rows affected) SELECT * into dbo. Salesorderheader_testfrom sales.salesorderheader--(121317 rows affected) SELECT * into dbo. Salesorderdetail_testfrom sales.salesorderdetail--Create a clustered index create CLUSTERED index SALESORDERHEADER_TEST_CL on dbo. Salesorderheader_test (SalesOrderID)--Build a nonclustered index create nonclustered index  SALESORDERDETAIL_TEST_NCL on dbo. Salesorderdetail_test (SalesOrderID) go

The salesorderheader_test contains the header information for each order, including the order creation date, customer number, contract number, salesperson number, etc., with a separate order number for each order. On the Order Number field, there is a clustered index.
The details of the order are stored in the salesorderdetail_test. A single order can sell multiple products to the same customer, so salesorderheader_test and Salesorderdetail_test are a one-to-many relationship. Each detail includes the order number it belongs to, its own unique number (Salesorderdetailid) in the form, the product number, the unit price, and the number of sales. In this case, only a nonclustered index is established on the salesorderdetailid.
According to the original data in AdventureWorks, header_test inside has more than 30,000 orders information, detail has more than 120,000 orders detailed records, basically an order has 3-5 detailed records. This is a normal distribution.

Below the header_test added 9 order records, their number is from 75124 to 75132 this is 9 special orders, each with more than 120,000 detailed records. That is to say, 90% of the data in Deatil_test belongs to these 9 orders.

DECLARE @i int Set @i = 1while @i < begin INSERT into [AdventureWorks2008]. [dbo].           [Salesorderheader_test] ([RevisionNumber], [OrderDate], [DueDate], [shipdate], [Status], [Online Orderflag], [SalesOrderNumber], [Purchaseordernumber], [AccountNumber], [customeri           D], [SalesPersonID], [TerritoryID], [Billtoaddressid], [Shiptoaddressid] , [Shipmethodid], [Creditcardid], [Creditcardapprovalcode], [Currencyrateid], [Sub           Total], [Taxamt], [Freight], [TotalDue], [Comment], [rowguid]           , [ModifiedDate]) SELECT [RevisionNumber], [OrderDate], [DueDate], [shipdate] , [Status], [Onlineorderflag], [SalesOrderNumber], [Purchaseordernumber], [account Number], [CustoMerid], [SalesPersonID], [TerritoryID], [Billtoaddressid], [Shiptoaddressid] , [Shipmethodid], [Creditcardid], [Creditcardapprovalcode], [Currencyrateid],           [SubTotal], [Taxamt], [Freight], [TotalDue], [Comment], [rowguid] , [ModifiedDate] from [salesorderheader_test] WHERE SalesOrderID = 75123INSERT into [AdventureWorks2008]. [dbo].           [Salesorderdetail_test] ([SalesOrderID], [Carriertrackingnumber], [OrderQty], [ProductID], [Specialofferid ], [UnitPrice], [Unitpricediscount], [LineTotal], [rowguid], [Modifiedda TE]) SELECT 75123 + @i, [Carriertrackingnumber], [OrderQty], [ProductID], [specia Lofferid], [UnitPrice], [Unitpricediscount], [LineTotal], [rowguid],GETDATE () from Sales.salesorderdetailset @i = @i + 1 END GO 

original text to [email protected]_CC reprint study

SQL Server Tuning Basics