SQLServer数据库维护优化的提⽰和技巧
Databa maintenance is very important, a critical part of our databa administrators’ daily tasks. However, this aspect is frequently underestimated which could lead to performance problems and respectively angry, unhappy customers. In this article, we will take a look at the different maintenance operations we have in SQL Server and how we can optimize them and take the maximum out of each.
数据库维护⾮常重要，这是我们数据库管理员⽇常任务的关键部分。 但是，这⽅⾯经常被低估，这可能会导致性能问题以及分别使⽣⽓，不满意的客户。 在本⽂中，我们将研究SQL Server中进⾏的各种维护操作，以及如何优化它们并从中获得最⼤收益。
索引重组操作 ( Index Reorganize operation )
We are taking off with the indexes in our databas. I am not going to convince you how important is to keep our indexes in a good shape, so we are directly jumping into how we can maintain them.
我们正在使⽤数据库中的索引。 我不会说服您保持索引良好状态的重要性，因此我们将直接跳⼊如何维护索引的过程。
One of the possibilities that we have in SQL Server to keep our indexes defragmented, is the index reo
rganize operation. This operation is always online, us minimal system resources, honors the fill factor that has been ud during the creation of the index (common misconception is that reorganize operation does not take into account fill factor at all) and if you kill it due to any reason, the work that has been done would still persist. So far so good! However there are veral major drawbacks:
在SQL Server中使索引进⾏碎⽚整理的⼀种可能性是索引重组操作。 此操作始终在线，使⽤最少的系统资源，尊重在创建索引期间使⽤的填充因⼦（常见的误解是，重组操作根本不考虑填充因⼦），并且如果您由于任何原因⽽将其杀死因此，已经完成的⼯作仍将继续。 到⽬前为⽌，⼀切都很好！ 但是，有⼏个主要缺点：
Index statistics are not being updated
索引统计信息未更新
Not efficient when you have a large fragmentation as it is only reorganizing the leaf-level pages
当您的碎⽚很⼤时，效率不⾼，因为它只是重新组织叶级页⾯
Cannot change the initial fill factor ud during index creation
⽆法更改索引创建期间使⽤的初始填充因⼦
Let’s test this with one simple scenario:
让我们⽤⼀个简单的场景进⾏测试：
1. Create a test databa to play with one simple table and inrt some data with deliberately skipping some values to
produce page splits and larger fragmentation:
创建⼀个测试数据库来处理⼀个简单的表，并故意跳过⼀些值以插⼊⼀些数据以产⽣页⾯拆分和更⼤的碎⽚：
USE master
GO
IF EXISTS (SELECT name FROM sys.databas WHERE name = N'IndexMaintenance')
DROP DATABASE [IndexMaintenance]
GO
CREATE DATABASE IndexMaintenance;
GO
USE IndexMaintenance;
GO
CREATE TABLE IndexTable (c1 INT, c2 CHAR (4000));
CREATE CLUSTERED INDEX IndexTable_CL ON IndexTable (c1);
GO
DECLARE @a INT
SET @a = 1
WHILE (@a<80)
BEGIN
IF (@a=5 or @a=15 or @a=22 or @a=29 or @a=34 or @a=38 or @a=45) PRINT 'Nothing to inrt'化淡妆的步骤
mac硬盘格式
ELSE INSERT INTO IndexTable VALUES (@a, 'a')
SET @a=@a + 1
END
INSERT INTO IndexTable VALUES (5, 'a');
GO
INSERT INTO IndexTable VALUES (15, 'a');
GO
INSERT INTO IndexTable VALUES (22, 'a');
GO
INSERT INTO IndexTable VALUES (29, 'a');
GO
INSERT INTO IndexTable VALUES (34, 'a');
GO
INSERT INTO IndexTable VALUES (38, 'a');
GO
INSERT INTO IndexTable VALUES (45, 'a');
GO
2. Check the index details – we are interested mainly in the fragmentation, page fullness and last statistics update for
捉迷藏的英文
this index:
检查索引详细信息–我们主要对该索引的碎⽚，页⾯填充和最新统计信息感兴趣：
寿县古城简介
USE IndexMaintenance
SELECT a.index_id, name, avg_fragmentation_in_percent, a.page_d_count,a.index_type_desc,a.avg_page_space_ud_in_percent,STATS_ as stats_updated FROM sys.dm_db_index_physical_stats
(DB_ID(N'IndexMaintenance'), OBJECT_ID(N'dbo.IndexTable'), 1, NULL, 'DETAILED') AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
3. Our index has above 30 % fragmentation, so we need to do something. Try with Index Reorganize first:
我们的索引有30％以上的碎⽚，因此我们需要做⼀些事情。 请先尝试使⽤“索引重组”：
USE IndexMaintenance
口字旁一个羊
ALTER INDEX IndexTable_CL ON dbo.IndexTable REORGANIZE
4. U the query from point 2 to e what happened with the index:
使⽤第2点的查询来查看索引发⽣了什么：
Fragmentation is lower than the initial 34 %, but there is still some left so our operation did not finish the job
completely. If you take a look at the page fullness, SQL Server tried to honor the fill factor, which is 100 % (or 0) in our ca:
碎⽚率低于最初的34％，但仍有⼀些碎⽚，因此我们的操作未能完全完成⼯作。 如果您看⼀下页⾯的填充度，SQL Server会尝试使⽤填充因⼦，在我们的例⼦中，填充因⼦为100％（或0）：
Last but not least, statistics associated with this index, have not been touched.
最后但并⾮最不重要的⼀点是，尚未触及与此索引相关的统计信息。
5. Repeat steps 1 and 2 to recreate our tup and check index details. This time, u Index Rebuild operation:
重复步骤1和2以重新创建我们的设置并检查索引详细信息。 这次，使⽤索引重建操作：
USE IndexMaintenance
ALTER INDEX IndexTable_CL ON dbo.IndexTable REBUILD
6. Check the index details again after the rebuild with the script from point 2:
使⽤第2点的脚本重建后，再次检查索引详细信息：
Fragmentation is completely gone, fill factor was honored again and this time, index statistics have been updated!
碎⽚完全消失了，填充因⼦再次得到了认可，这⼀次，索引统计信息已更新！
7. Index Reorganize is a uful operation when your fragmentation is below 20-30 %, you do not need to change the
original fill factor of your index and you are planning on doing index statistics update at a later point in time.
当您的碎⽚率低于20％⾄30％，您不需要更改索引的原始填充因⼦，并且计划在以后的某个时间进⾏索引统计信息更新时，索引重组是⼀项有⽤的操作。
索引重建操作 ( Index Rebuild operation )
毒案We already covered that index rebuild is the approach you should u when the fragmentation is high. When you u this method, there are two options:
我们已经介绍了索引重建是在碎⽚过多时应该使⽤的⽅法。 使⽤此⽅法时，有两个选项：
Offline
离线
Online
线上
Offline index rebuild means really offline! While an index is being created, dropped or rebuilt offline, the table cannot be accesd and this is valid for non-clustered indexes as well:
隐蔽的近义词离线索引重建意味着真正的离线！ 在创建索引，离线删除或重建索引时，⽆法访问该表，这对于⾮聚集索引也有效：
1. and restore the databa to your SQL Server获取“ AdventureWorks2014”数据库并将数据库还原到您SQL Server
2. Then run this script created by Jonathan Kehayias to enlarge veral tables:
然后运⾏Jonathan Kehayias创建的此脚本来放⼤⼏个表：
USE [AdventureWorks2014]
GO
IF OBJECT_ID('Sales.SalesOrderHeaderEnlarged') IS NOT NULL
DROP TABLE Sales.SalesOrderHeaderEnlarged;
GO
CREATE TABLE Sales.SalesOrderHeaderEnlarged
(
SalesOrderID int NOT NULL IDENTITY (1, 1) NOT FOR REPLICATION,
RevisionNumber tinyint NOT NULL,
OrderDate datetime NOT NULL,
DueDate datetime NOT NULL,
ShipDate datetime NULL,
Status tinyint NOT NULL,
OnlineOrderFlag dbo.Flag NOT NULL,
SalesOrderNumber AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID],0),N'*** ERROR ***')),
PurchaOrderNumber dbo.OrderNumber NULL,
AccountNumber dbo.AccountNumber NULL,
CustomerID int NOT NULL,
SalesPersonID int NULL,
TerritoryID int NULL,
BillToAddressID int NOT NULL,
ShipToAddressID int NOT NULL,
ShipMethodID int NOT NULL,
CreditCardID int NULL,
CreditCardApprovalCode varchar(15) NULL,
CurrencyRateID int NULL,
SubTotal money NOT NULL,
TaxAmt money NOT NULL,
Freight money NOT NULL,
TotalDue AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))),
Comment nvarchar(128) NULL,
rowguid uniqueidentifier NOT NULL ROWGUIDCOL,
ModifiedDate datetime NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT Sales.SalesOrderHeaderEnlarged ON
GO
INSERT INTO Sales.SalesOrderHeaderEnlarged (SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag, PurchaOrde SELECT SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag, PurchaOrderNumber, AccountNumber, CustomerID, FROM Sales.SalesOrderHeader WITH (HOLDLOCK TABLOCKX)
GO
SET IDENTITY_INSERT Sales.SalesOrderHeaderEnlarged OFF
GO
ALTER TABLE Sales.SalesOrderHeaderEnlarged ADD CONSTRAINT
PK_SalesOrderHeaderEnlarged_SalesOrderID PRIMARY KEY CLUSTERED
(
SalesOrderID
)
WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderHeaderEnlarged_rowguid ON Sales.SalesOrderHeaderEnlarged
(
rowguid
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderHeaderEnlarged_SalesOrderNumber ON Sales.SalesOrderHeaderEnlarged
(
SalesOrderNumber
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderEnlarged_CustomerID ON Sales.SalesOrderHeaderEnlarged
(
CustomerID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderEnlarged_SalesPersonID ON Sales.SalesOrderHeaderEnlarged
(
SalesPersonID
)
WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
IF OBJECT_ID('Sales.SalesOrderDetailEnlarged') IS NOT NULL
DROP TABLE Sales.SalesOrderDetailEnlarged;
GO
CREATE TABLE Sales.SalesOrderDetailEnlarged
(
SalesOrderID int NOT NULL,
SalesOrderDetailID int NOT NULL IDENTITY (1, 1),
CarrierTrackingNumber nvarchar(25) NULL,
双十协定
OrderQty smallint NOT NULL,
ProductID int NOT NULL,
SpecialOfferID int NOT NULL,
UnitPrice money NOT NULL,
UnitPriceDiscount money NOT NULL,
LineTotal AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
rowguid uniqueidentifier NOT NULL ROWGUIDCOL,
ModifiedDate datetime NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT Sales.SalesOrderDetailEnlarged ON
GO
INSERT INTO Sales.SalesOrderDetailEnlarged (SalesOrderID, SalesOrderDetailID, CarrierTracking
Number, OrderQty, ProductID, SpecialOfferID, UnitPrice SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedD FROM Sales.SalesOrderDetail WITH (HOLDLOCK TABLOCKX)
GO
