首页 > 代码库 > 调优过程
调优过程
性能调优 1 百胜表规范 USE ERP_DW GO /* 功能说明： 创建【区域】维度表 修改说明： Create by LY on 2011-09-07 */ IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE id = OBJECT_ID(‘Dim_Area‘) AND type = ‘U‘) BEGIN DROP TABLE Dim_Area END GO CREATE TABLE [dbo].[Dim_Area] ( AreaCode VARCHAR(20) NOT NULL, AreaName VARCHAR(50) NULL, CONSTRAINT PK_DIM_AREA PRIMARY key (AreaCode) ); GO /* 功能说明： 获取区域表有效的信息 修改说明： Create by LY on 2011-09-07 */ IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE id = OBJECT_ID(‘VW_Dim_Area‘) AND type = ‘V‘) BEGIN DROP VIEW VW_Dim_Area END GO CREATE VIEW VW_Dim_Area AS SELECT AreaCode FROM Dim_Area LEFT JOIN SYSOBJECTS ON 1=1 LEFT JOIN SYSCOLUMNS ON 1=1 WHERE 1=1 GROUP BY AreaCode GO /* 功能说明：抽取业务库的数据到数据仓库 修改说明： Create by LY on 2011-09-07 Modify by LY on 2011-09-07 增加变量的注释 */ IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE id = OBJECT_ID(‘P_GetData_Load_Dim_Area‘) AND OBJECTPROPERTY(ID, N‘IsProcedure‘) = 1) BEGIN DROP PROCEDURE [dbo].[P_GetData_Load_Dim_Area] END GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[P_GetData_Load_Dim_Area] @P_sourceDB_name NVARCHAR(50) ----源数据库名称，数据从哪抽取 AS BEGIN BEGIN TRAN ---开始事务 DECLARE @TrunSql VARCHAR(50); ----清空数据，不记录日志 DECLARE @InsertSql VARCHAR(MAX); ----插入数据 SET @TrunSql=‘ TRUNCATE Table Dim_Area ‘; EXEC (@TrunSql) IF @@error<>0 BEGIN ROLLBACK TRAN RETURN -1 END SET @InsertSql=‘ INSERT INTO Dim_Area SELECT QUYU.QYDM as AreaCode, QUYU.QYMC as AreaName FROM ERP_Business..QUYU ‘; EXEC(@InsertSql) IF @@ERROR<>0 BEGIN ROLLBACK TRAN RETURN -1 END COMMIT TRAN END; GO /* 功能说明：抽取业务库的数据到数据仓库 修改说明： Create by LY on 2011-09-07 Modify by LY on 2011-09-07 增加变量的注释 */ IF OBJECT_ID(‘[dbo].[P_GetData_Load_Dim_Area]‘,‘P‘) IS NOT NULL DROP PROC [dbo].[P_GetData_Load_Dim_Area] GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[P_GetData_Load_Dim_Area] @P_sourceDB_name NVARCHAR(50) ----源数据库名称，数据从哪抽取 AS BEGIN BEGIN TRAN ---开始事务 DECLARE @TrunSql VARCHAR(50); ----清空数据，不记录日志 DECLARE @InsertSql VARCHAR(MAX); ----插入数据 SET @TrunSql=‘ TRUNCATE Table Dim_Area ‘; EXEC (@TrunSql) IF @@error<>0 BEGIN ROLLBACK TRAN RETURN -1 END SET @InsertSql=‘ INSERT INTO Dim_Area SELECT QUYU.QYDM as AreaCode, QUYU.QYMC as AreaName FROM ERP_Business..QUYU ‘; EXEC(@InsertSql) IF @@ERROR<>0 BEGIN ROLLBACK TRAN RETURN -1 END COMMIT TRAN END; 2 表分区 SQL Server引入的表分区技术，让用户能够把数据分散存放到不同的物理磁盘中，提高这些磁盘的并行处理性能以优化查询性能 2.1 表分区的介绍 ? 表分区的作用，优点，步骤 (1)分区表的作用： 在大量业务数据处理的项目中，可以考虑使用分区表来提高应用系统的性能并方便数据管理，本文详细介绍了分区表的使 用。在大型的企业应用或企业级的数据库应用中，要处理的数据量通常可以达到几十到几百GB，有的甚至可以到TB级。虽然存储介质和数据处理技术的发展也很快，但是仍然不能满足用户的需求，为了使用户的大量的数据在读写操作和查询中速度更快，Oracle提供了对表和索引进行分区的技术，以改善大型应用系统的性能。 (2)使用分区的优点： 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用； 维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可； 均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能； 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。 (3).分区表的步骤： 1.创建分区函数 2.创建映射到分区函数的分区方案 3.创建使用该分区方案的分区表 2.2 表分区的SQL步骤 ? 创建数据库的时候就创建分区的方案： use Master go /* 功能说明： 关闭数据库连接 修改说明： Create by LY on 2011-09-09 */ if object_id(‘Proc_KillDataBase‘,‘P‘) is not null drop proc Proc_KillDataBase go create proc Proc_KillDataBase @DataBaseName nvarchar(100) as begin DECLARE @i INT SELECT @i=1 DECLARE @sSPID VARCHAR(100) DECLARE KILL_CUR SCROLL CURSOR FOR SELECT SPID FROM sysprocesses WHERE DBID=DB_ID(@DataBaseName) OPEN KILL_CUR IF @@CURSOR_ROWS = 0 GOTO END_KILL_CUR FETCH FIRST FROM KILL_CUR INTO @sSPID EXEC( ‘KILL ‘+@sSPID) WHILE @i < @@CURSOR_ROWS BEGIN FETCH NEXT FROM KILL_CUR INTO @sSPID EXEC( ‘KILL ‘+@sSPID) SELECT @i=@i+1 END END_KILL_CUR: CLOSE KILL_CUR DEALLOCATE KILL_CUR end go EXEC Proc_KillDataBase ‘PT_Sales‘ GO /* 步骤一: 功能说明： 创建Sales数据库，创建多个文件组，用于数据分区 修改说明： Create by LY on 2011-09-09 */ IF EXISTS (SELECT 1 FROM sys.databases WHERE name = ‘PT_Sales‘) BEGIN DROP DATABASE PT_Sales END GO CREATE DATABASE PT_Sales ON PRIMARY -----------一个主文件，三个文件组，一个日志文件 ( NAME = N‘Sales‘, FILENAME = N‘D:\DB\Sales.mdf‘, SIZE = 3MB, MAXSIZE = 100MB, FILEGROWTH = 10% ), FILEGROUP FG1 ( NAME = N‘File1‘, FILENAME = N‘D:\DB\PT\File1.ndf‘, SIZE = 1MB, MAXSIZE = 100MB, FILEGROWTH = 10% ), FILEGROUP FG2 ( NAME = N‘File2‘, FILENAME = N‘D:\DB\PT\File2.ndf‘, SIZE = 1MB, MAXSIZE = 100MB, FILEGROWTH = 10% ), FILEGROUP FG3 ( NAME = N‘File3‘, FILENAME = N‘D:\DB\PT\File3.ndf‘, SIZE = 1MB, MAXSIZE = 100MB, FILEGROWTH = 10% ) LOG ON ( NAME = N‘Sales_Log‘, FILENAME = N‘D:\DB\Sales_Log.ldf‘, SIZE = 1MB, MAXSIZE = 100MB, FILEGROWTH = 10% ) GO /* 步骤二: 功能说明： 建立分区函数，这里我们建立三个分区。how（如何对数据进行分区） 修改说明： Create by LY on 2011-09-09 */ USE PT_Sales GO CREATE PARTITION FUNCTION PT_OrderDate (datetime) AS RANGE RIGHT FOR VALUES (‘2003/01/01‘, ‘2004/01/01‘) --n不能超过999，创建的分区数等于n + 1 GO /* 步骤三: 功能说明: 创建分区方案，关联到分区函数。where（在哪里对数据进行分区） 修改说明： Create by LY on 2011-09-09 */ USE PT_Sales GO CREATE PARTITION SCHEME PS_OrderDate AS PARTITION PT_OrderDate TO (FG1, FG2, FG3) GO /* 步骤四: 功能说明: 创建分区表。创建表并将其绑定到分区方案。这里我们建立个表，表的结构一样。 其中OrdersHistory表用于保存归档数据。 */ USE PT_Sales GO IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE id = OBJECT_ID(‘Orders‘) AND type = ‘U‘) BEGIN DROP TABLE Orders END GO /*订单分区表*/ CREATE TABLE dbo.Orders ( OrderID int identity(10000,1), OrderDate datetime NOT NULL, CustomerID int NOT NULL, CONSTRAINT PK_Orders PRIMARY KEY (OrderID, OrderDate) ) ON PS_OrderDate (OrderDate) GO /*归档表【历史表】*/ IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE id = OBJECT_ID(‘OrdersHistory‘) AND type = ‘U‘) BEGIN DROP TABLE OrdersHistory END GO CREATE TABLE dbo.OrdersHistory ( OrderID int identity(10000,1), OrderDate datetime NOT NULL, CustomerID int NOT NULL, CONSTRAINT PK_OrdersHistory PRIMARY KEY (OrderID, OrderDate) ) ON PS_OrderDate (OrderDate) GO USE PT_Sales GO INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2002/6/25‘, 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2002/8/13‘, 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2002/8/25‘, 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2002/9/23‘, 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2003/6/25‘, 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2003/8/13‘, 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2003/8/25‘, 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2003/9/23‘, 1000) GO SELECT * FROM dbo.Orders SELECT * FROM dbo.OrdersHistory /* 查看某一个分区 这里我们要用到$PARTITION 函数，这个函数可以帮助我们查询某个分区的数据， 还可以检索某个值所隶属的分区号。$PARTITION 函数的进一步细节可以查看MSDN 查询已分区表Order的第一个分区，代码如下： */ SELECT * FROM dbo.Orders WHERE $PARTITION.PT_OrderDate(OrderDate) = 2 /* 我们还可以查询某个分区有多少行数据，代码如下： */ SELECT $PARTITION.PT_OrderDate(OrderDate) AS Partition, COUNT(*) AS [COUNT] FROM dbo.Orders GROUP BY $PARTITION.PT_OrderDate(OrderDate) ORDER BY Partition ; /* 我们还可以通过$PARTITION 函数获得一组分区标示列值的分区号， 例如获得属于哪个分区，代码如下： 很明显，年隶属于第个分区，因为我们建立分区函数时用了RANGE RIGHT，所以返回。 你也可以把年换成，，，等等测试。你会发现，年属于第个分区， 2004年以后的都属于第个分区。 */ SELECT PT_Sales.$PARTITION.PT_OrderDate(‘2003‘) /* 归档数据 假如现在是年年初，那么我们就可以把年所有的交易记录 归档到历史订单表HistoryOrder中。代码如下： */ USE PT_Sales GO ALTER TABLE dbo.Orders SWITCH PARTITION 1 TO dbo.OrdersHistory PARTITION 1 GO SELECT * FROM dbo.Orders SELECT * FROM dbo.OrdersHistory /* 便会发现，Orders 表只剩年的数据，而OrdersHistory表中包含了年的数据。 当然如果到了年年初，我们也可以归档年的所有交易数据。代码如下： */ USE PT_Sales GO ALTER TABLE dbo.Orders SWITCH PARTITION 2 TO dbo.OrdersHistory PARTITION 2 GO /* 添加分区: ALTER PARTITION SCHEME ps_OrderDate NEXT USED FG2 用来指定新分区的数据存储在那个文件。这里NEXT USED FG2 代表我们将新分区的数据保存在FG2文件组中，当然我们也可以在原有数据库上新建一个文件组，把新分区的数据保存在新文件组当中，这里我们直接用FG2文件组。 ALTER PARTITION FUNCTION pf_OrderDate() SPLIT RANGE (‘2005/01/01‘) 代表我们创建一个新分区，而这里SPLIT RANGE (‘2005/01/01‘)正是创建新分区的关键语法。 执行完上面的代码之后，我们就有了个分区，此时的区间如下： */ USE PT_Sales GO ALTER PARTITION SCHEME PS_OrderDate NEXT USED FG2 ALTER PARTITION FUNCTION PT_OrderDate() SPLIT RANGE (‘2005/01/01‘) GO /* 删除分区: 删除分区又称为合并分区，假如我们想合并年的分区和年的分区到一个分区，我们可以用如下的代码： */ USE PT_Sales GO ALTER PARTITION FUNCTION PT_OrderDate() MERGE RANGE (‘2003/01/01‘) GO /* 你会发现返回的结果是。而原来返回的是，原因是年以前数据所在的那个分区合并到了年这个分区中了。 假如此时我们执行如下代码： */ SELECT PT_Sales.$PARTITION.PT_OrderDate(‘2003‘) /* 结果一行数据都没返回，事实就这样，因为OrdersHistory 表中只存储了 2002和年的历史数据，在没有合并分区之前，执行上面的代码肯定会 查询出年的数据，但是合并了分区之后，上面代码实际查询的是第二个分区中年的数据。 */ SELECT * FROM dbo.OrdersHistory WHERE $PARTITION.PT_OrderDate(OrderDate) = 2 /* 便会查询出行数据，包括年和年的数据，因为合并分区 后年和年的数据都成了第个分区的数据了。 */ SELECT * FROM dbo.OrdersHistory WHERE $PARTITION.PT_OrderDate(OrderDate) = 1 /* 查看源数据 */ select * from sys.partition_functions select * from sys.partition_range_values select * from sys.partition_schemes 2.3 将普通表转换成分区表 ? 将普通表转换为分区表的做法 /* 功能说明:将普通表转换成分区表. 介绍: 在以上代码中，我们可以看出，这个表拥有一般普通表的特性——有主键，同时这个主键还是聚集索引。 前面说过，分区表是以某个字段为分区条件，所以，除了这个字段以外的其他字段，是不能创建聚集 索引的。因此，要想将普通表转换成分区表，就必须要先删除聚集索引，然后再创建一个新的聚集索 引，在该聚集索引中使用分区方案。 可惜的是，在SQL Server中，如果一个字段既是主键又是聚集索引时，并不能仅仅删除聚集索引。因此， 我们只能将整个主键删除，然后重新创建一个主键，只是在创建主键时，不将其设为聚集索引，如以下 代码所示： */ /* 功能说明:创建文件组 */ USE ERP_DW GO ALTER DATABASE ERP_DW ADD FILEGROUP [FG_ERP_DW_01] ALTER DATABASE ERP_DW ADD FILEGROUP [FG_ERP_DW_02] ALTER DATABASE ERP_DW ADD FILEGROUP [FG_ERP_DW_03] GO /* */ /* 功能说明:创建文件 */ ALTER DATABASE ERP_DW ADD FILE ( NAME = N‘FG_ERP_DW_01_data‘, FILENAME = N‘D:\DB\PT\FG_ERP_DW_01_data.ndf‘, SIZE = 30MB, FILEGROWTH = 10% ) TO FILEGROUP [FG_ERP_DW_01]; ALTER DATABASE ERP_DW ADD FILE ( NAME=N‘FG_ERP_DW_02_date‘, FILENAME=N‘D:\DB\PT\FG_ERP_DW_02_data.ndf‘, SIZE=30MB, FILEGROWTH=10% ) TO FILEGROUP [FG_ERP_DW_02]; ALTER DATABASE ERP_DW ADD FILE ( NAME=N‘FG_ERP_DW_03_date‘, FILENAME=N‘D:\DB\PT\FG_ERP_DW_03_data.ndf‘, SIZE=30MB, FILEGROWTH=10% ) TO FILEGROUP [FG_ERP_DW_03]; GO /* 功能说明:创建分区函数 */ CREATE PARTITION FUNCTION PT_Fun_Fact_SaleCar_BusinessDate(DATETIME) AS RANGE LEFT FOR VALUES(‘2010-1-1‘,‘2011-1-1‘) GO /* 功能说明:创建分区方案 */ CREATE PARTITION SCHEME PT_Sch_Fact_SaleCar_BusinessDate AS PARTITION PT_Fun_Fact_SaleCar_BusinessDate TO([FG_ERP_DW_01],[FG_ERP_DW_02],[FG_ERP_DW_03]) GO /*-------------------创建分区表------------------------ 功能说明:将普通表转换成分区表 首先:删掉主键,创建主键，但不设为聚集索引 -------------------------------------------------------*/ ALTER TABLE Fact_SaleCar DROP constraint PK_Fact_SaleCar ALTER TABLE Fact_SaleCar ADD CONSTRAINT PK_Fact_SaleCar PRIMARY KEY NONCLUSTERED ( SaleCode ASC ) ON [PRIMARY] GO /* 功能说明: 在重新非聚集主键之后，就可以为表创建一个新的聚集索引， 并且在这个聚集索引中使用分区方案，如以下代码所示： 其次:创建一个新的聚集索引，在该聚集索引中使用分区方案 */ CREATE CLUSTERED INDEX CT_Fact_SaleCar ON Fact_SaleCar(BusinessDate) ON PT_Sch_Fact_SaleCar_BusinessDate(BusinessDate) GO /* 功能说明:写查询，根据分区来查效果果然快多了。好处。。 */ SELECT * FROM Fact_SaleCar -- WHERE YEAR(BusinessDate)=2010 --$PARTITION.PT_Fun_Fact_SaleCar_BusinessDate(BusinessDate) = 3 2.4 一个完整的表分区案例 ? 与普通表比对查询效率 USE CubeDemo GO /* 功能说明： 创建表分区测试表 修改说明： Create by LY on 2011-09-11 */ IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE id = OBJECT_ID(‘Fact_SaleCar‘) AND type = ‘U‘) BEGIN DROP TABLE Fact_SaleCar END GO CREATE TABLE [dbo].Fact_SaleCar ( SaleCarId VARCHAR(20) NOT NULL, SaleName VARCHAR(50) NULL, CheckOutDate DATETIME NULL, Attribute1 VARCHAR(50) NULL, Attribute2 VARCHAR(50) NULL, Attribute3 VARCHAR(50) NULL, Attribute4 VARCHAR(50) NULL, Attribute5 VARCHAR(50) NULL, Attribute6 VARCHAR(50) NULL, Attribute7 VARCHAR(50) NULL, Attribute8 VARCHAR(50) NULL, Attribute9 VARCHAR(50) NULL, Attribute10 VARCHAR(50) NULL, Attribute11 VARCHAR(50) NULL, Attribute12 VARCHAR(50) NULL, CONSTRAINT PK_Fact_SaleCar PRIMARY key (SaleCarId) ); GO /* 功能说明： 用循环加入测试数据 修改说明： Create by LY on 2011-09-11 */ BEGIN BEGIN TRAN ---开始事务 DECLARE @NUM INT; SET @NUM=1; /*-------2009年的时间导入【万条】---- */ WHILE @NUM <= 800000 BEGIN INSERT INTO dbo.Fact_SaleCar SELECT RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+RTRIM(@NUM),‘商店‘+RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+RTRIM(@NUM),DATEADD(YEAR,-2,GETDATE()), RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+‘01‘,RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+‘02‘, RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+‘03‘,RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+‘04‘, RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+‘05‘,RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+‘06‘, RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+‘07‘,RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+‘08‘, RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+‘09‘,RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+‘10‘, RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+‘11‘,RTRIM(YEAR(DATEADD(YEAR,-2,GETDATE())))+‘12‘; SET @NUM=@NUM+1; IF @@error<>0 BEGIN ROLLBACK TRAN RETURN; END END; SET @NUM=1 /*-------2010年的时间导入 【万】---- */ WHILE @NUM <= 500000 BEGIN INSERT INTO dbo.Fact_SaleCar SELECT RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+RTRIM(@NUM),‘商店‘+RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+RTRIM(@NUM),DATEADD(YEAR,-1,GETDATE()), RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+‘01‘,RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+‘02‘, RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+‘03‘,RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+‘04‘, RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+‘05‘,RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+‘06‘, RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+‘07‘,RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+‘08‘, RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+‘09‘,RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+‘10‘, RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+‘11‘,RTRIM(YEAR(DATEADD(YEAR,-1,GETDATE())))+‘12‘; SET @NUM=@NUM+1; IF @@error<>0 BEGIN ROLLBACK TRAN RETURN; END END; SET @NUM=1 /*-------2011年的时间导入 【万】---- */ WHILE @NUM <= 1000000 BEGIN INSERT INTO dbo.Fact_SaleCar SELECT RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+RTRIM(@NUM),‘商店‘+RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+RTRIM(@NUM),DATEADD(YEAR,0,GETDATE()), RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+‘01‘,RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+‘02‘, RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+‘03‘,RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+‘04‘, RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+‘05‘,RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+‘06‘, RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+‘07‘,RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+‘08‘, RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+‘09‘,RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+‘10‘, RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+‘11‘,RTRIM(YEAR(DATEADD(YEAR,0,GETDATE())))+‘12‘; SET @NUM=@NUM+1; IF @@error<>0 BEGIN ROLLBACK TRAN RETURN; END END; COMMIT TRAN END; --SELECT * FROM Fact_SaleCar /* 功能说明：创建分区表 */ USE PFCube GO /* 功能说明:将普通表转换成分区表. 介绍: 在以上代码中，我们可以看出，这个表拥有一般普通表的特性——有主键，同时这个主键还是聚集索引。 前面说过，分区表是以某个字段为分区条件，所以，除了这个字段以外的其他字段，是不能创建聚集 索引的。因此，要想将普通表转换成分区表，就必须要先删除聚集索引，然后再创建一个新的聚集索 引，在该聚集索引中使用分区方案。 可惜的是，在SQL Server中，如果一个字段既是主键又是聚集索引时，并不能仅仅删除聚集索引。因此， 我们只能将整个主键删除，然后重新创建一个主键，只是在创建主键时，不将其设为聚集索引，如以下 代码所示： */ /* 功能说明:创建文件组 */ ALTER DATABASE PFCube ADD FILEGROUP [FG_PFCube_01] ALTER DATABASE PFCube ADD FILEGROUP [FG_PFCube_02] ALTER DATABASE PFCube ADD FILEGROUP [FG_PFCube_03] GO /* */ /* 功能说明:创建文件 */ ALTER DATABASE PFCube ADD FILE ( NAME = N‘FG_PFCube_01_data‘, FILENAME = N‘D:\DB\PT\FG_PFCube_01_data.ndf‘, SIZE = 30MB, FILEGROWTH = 10% ) TO FILEGROUP [FG_PFCube_01]; ALTER DATABASE PFCube ADD FILE ( NAME=N‘FG_PFCube_02_date‘, FILENAME=N‘E:\DB\PT\FG_PFCube_02_data.ndf‘, SIZE=30MB, FILEGROWTH=10% ) TO FILEGROUP [FG_PFCube_02]; ALTER DATABASE PFCube ADD FILE ( NAME=N‘FG_PFCube_03_date‘, FILENAME=N‘E:\DB\PT\FG_PFCube_03_data.ndf‘, SIZE=30MB, FILEGROWTH=10% ) TO FILEGROUP [FG_PFCube_03]; GO /* 功能说明:创建分区函数 */ CREATE PARTITION FUNCTION PT_Fun_Fact_SaleCar_CheckOutDate(DATETIME) AS RANGE LEFT FOR VALUES(‘2010-1-1‘,‘2011-1-1‘) GO /* 功能说明:创建分区方案 */ CREATE PARTITION SCHEME PT_Sch_Fact_SaleCar_CheckOutDate AS PARTITION PT_Fun_Fact_SaleCar_CheckOutDate TO([FG_PFCube_01],[FG_PFCube_02],[FG_PFCube_03]) GO /*-------------------创建分区表------------------------ 功能说明:将普通表转换成分区表 首先:删掉主键,创建主键，但不设为聚集索引 -------------------------------------------------------*/ ALTER TABLE Fact_SaleCar DROP constraint PK_Fact_SaleCar ALTER TABLE Fact_SaleCar ADD CONSTRAINT PK_Fact_SaleCar PRIMARY KEY NONCLUSTERED ( SaleCarId ASC ) ON [PRIMARY] GO /* 功能说明: 在重新非聚集主键之后，就可以为表创建一个新的聚集索引， 并且在这个聚集索引中使用分区方案，如以下代码所示： 其次:创建一个新的聚集索引，在该聚集索引中使用分区方案 */ CREATE CLUSTERED INDEX CT_Fact_SaleCar ON Fact_SaleCar(CheckOutDate) ON PT_Sch_Fact_SaleCar_CheckOutDate(CheckOutDate) GO /* 功能说明:写查询，根据分区来查效果果然快多了。好处。。 */ SELECT * FROM Fact_SaleCar WHERE YEAR(CheckOutDate)=2010 --$PARTITION.PT_Fun_Fact_SaleCar_CheckOutDate(CheckOutDate) = 2 3 表索引 数据库中的索引是某个表中一列或多列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 3.1 索引的定义及分类 /* 语法： CREATE [索引类型] INDEX 索引名称 ON 表名(列名) WITH FILLFACTOR = 填充因子值~100 GO */ /*实例*/ USE CubeDemo GO IF EXISTS (SELECT * FROM SYSINDEXES WHERE NAME=‘IX_TEST_TNAME‘)--检测是否已经存在IX_TEST_TNAME索引 DROP INDEX TEST.IX_TEST_TNAME--如果存在则删除 --创建索引 CREATE NONCLUSTERED INDEX IX_TEST_TNAME --创建一个非聚集索引 ON TEST(TNAME) --为TEST表的TNAME字段创建索引 WITH FILLFACTOR = 30 --填充因子为% GO SELECT * FROM Employee(INDEX = IX_TEST_TNAME) WHERE Name = ‘A‘ --指定按‘IX_TEST_TNAME’索引查询 /* 总结： 1.什么是索引：数据库中的索引是某个表中一列或多列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 2.分类： 唯一索引(UNIQUE)：不允许两行具有相同的索引值（创建了唯一约束，系统将自动创建唯一索引） 主键索引：主键索引要求主键中的每个值是唯一的，（创建主键自动创建主键索引） 聚集索引(CLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，表中只能包含一个聚集索引，主键列默认为聚集索引 非聚集索引(NONCLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序不匹配，表中可以有个非聚集索引 3.创建索引的标准：用语频繁搜索的列；用语对数据进行排序的列 注意：如果表中仅有几行，或列中只包含几个不同的值，不推荐创建索引，因为SQL Server 在小型表中用索引搜索数据所花的时间比逐行搜索更长。 */ 3.2 表索引案例说明 /* 功能说明:使用T-SQL语句创建索引 语法如下: CREATE [索引类型] INDEX 索引名称 ON 表名(列名) WITH FILLFACTOR = 填充因子值~100 GO */ /*----------------索引的基本介绍 二、建立索引 建立索引时，一般先创建簇索引，再创建非簇索引。 索引分为三类（）unique（）clustered（）nonclustered 实例： (1)create unique index my_index on titles(title_id) (2)CREATE CLUSTERED INDEX BigTable_Index ON BigTable(PK, customerid, companyname) (3)create index my_index2 on authors(au_lname,au_fname)等同于create nonclustered index my_index2 on authors(au_lname,au_fname) 簇索引clustered，索引的逻辑结构与行的物理顺序相同，只能有一个簇索引，速度很快。 非簇索引nonclustered 索引的逻辑结构与行的物理顺序不相同,可以建立多个非簇索引。 复合索引，指定了多个列名就是复合索引，最大组合个列。并且所组合的所有列的存储空间不超过个字节。 三、删除索引 drop index authors.my_index2; */ USE IndexCube GO IF EXISTS (SELECT * FROM SYSINDEXES WHERE NAME=‘IX_Fact_SaleCar_SaleNAME‘ )--检测是否已经存在IX_Fact_SaleCar_SaleNAME索引 BEGIN DROP INDEX Fact_SaleCar.IX_Fact_SaleCar_SaleNAME--如果存在则删除 END GO /* 功能说明: 没有设置索引前，看他的IO情况 */ SET STATISTICS IO ON; SELECT * FROM Fact_SaleCar WHERE SaleName LIKE ‘%商店%‘ GO /* 功能说明:创建一个非聚集索引 */ --DROP INDEX Fact_SaleCar.PK_Fact_SaleCar CREATE NONCLUSTERED INDEX IX_Fact_SaleCar_SaleNAME --创建一个非聚集索引 ON Fact_SaleCar(SaleName) --为Fact_SaleCar表的SaleName字段创建索引 WITH FILLFACTOR = 30 --填充因子为% GO SET STATISTICS IO ON; SELECT * FROM Fact_SaleCar WHERE SaleName LIKE ‘%商店%‘ GO 3.3 索引的使用 SqSQL什么条件会使用索引？ 当字段上建有索引时，通常以下情况会使用索引： INDEX_COLUMN = ? (或者>、>=、<、<=) INDEX_COLUMN between ? and ? INDEX_COLUMN in (?,?,...,?) INDEX_COLUMN like ?||‘%‘（后导模糊查询） T1. INDEX_COLUMN=T2. COLUMN1（两个表通过索引字段关联） SQL什么条件不会使用索引？ 不等于操作不能使用索引 经过普通运算或函数运算后的索引字段不能使用索引，但是经过函数运算字段的字段要使用可以使用函数索引 使用多个字段的组合索引，如果查询条件中第一个字段不能使用索引，那整个查询也不能使用索引 含前导模糊查询的Like语法不能使用索引 B-TREE索引里不保存字段为NULL值记录，因此IS NULL不能使用索引。 Oracle在做数值比较时需要将两边的数据转换成同一种数据类型，如果两边数据类型不同时会对字段值隐式转换，相当于加了一层函数处理，所以不能使用索引。 给索引查询的值应是已知数据，不能是未知字段值。 索引使用经验总结如下: 1. 尽量避免非操作符的使用 在索引列上使用NOT、<>等非操作符，DBMS是不使用索引的，可以将查询语句转换为可以使用索引的查询。 2. 避免对查询的列的操作 任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。如: select * from record where substr(cardno,1,4) = ’5378’ select * from record where amount/30 < 1000 由于where子句中对列的任何操作结果都是在SQL运行时逐行计算得到的，因此它不得不进行表扫描，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表扫描，因此将SQL重写如下: select * from record where cardno like ’5378%’ select * from record where amount < 1000*30 3. 避免不必要的类型转换 需要注意的是，尽最避免潜在的数据类型转换。如将字符型数据与数值型数据比较，会自动将字符进行转换，从而导致全表扫描。 4. 增加查询的范围限制 增加查询的范网限制，避免全范闱的搜索。 5. 尽量去掉IN、OR 含有IN、OR的Where子句常会使用全表扫描，使索引失效：如果不产生大景重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。 6. 尽量去掉<> 尽景去掉<>，避免全表扫描，如果数据是枚举值，且取值范闱同定，则修改为OR或者in 7. 去掉Where子句中的IS NULL和IS NOT NULL Where字句中的IS NULL和IS NOT NULL将不会使用索引而是进行全表搜索，因此需要通过改变查洵方式，分情况讨论等办法，去掉Where子句巾的IS NULL和IS NOT NULL。 3.4 索引的创建 数据库索引的原理非常简单，但在复杂的表中真正能正确使用索引的人很少，即使是专业的DBA也不一定能完全做到最优。 索引会大大增加表记录的DML(INSERT,UPDATE,DELETE)开销，正确的索引可以让性能提升100，1000倍以上，不合理的索引也可能会让性能下降100倍，因此在一个表中创建什么样的索引需要平衡各种业务需求。 索引有哪些种类？ 常见的索引有B-TREE索引、位图索引、全文索引，位图索引一般用于数据仓库应用，全文索引由于使用较少，这里不深入介绍。B-TREE索引包括很多扩展类型，如组合索引、反向索引、函数索引等等，以下是B-TREE索引的简单介绍： B-TREE索引也称为平衡树索引(Balance Tree)，它是一种按字段排好序的树形目录结构，主要用于提升查询性能和唯一约束支持。B-TREE索引的内容包括根节点、分支节点、叶子节点。 叶子节点内容：索引字段内容+表记录ROWID 根节点，分支节点内容：当一个数据块中不能放下所有索引字段数据时，就会形成树形的根节点或分支节点，根节点与分支节点保存了索引树的顺序及各层级间的引用关系。 。 在什么字段上建索引？ 建立必要的索引 总纲只有一句话：建立必要的索引，这就是后面内容基础。这点看似容易实际却很难。难就难在如何判断哪些索引足必要的，哪些又是不必要的。判断的最终标准是看这些索引是否对我们的数据库性能有所帮助。具体到方法上，就必须熟悉数据库应用程序巾的所有SQL语句，从中统计出常用的能对性能有影响的部分SQL，分析、归纳出作为Where条件子句的字段及其组合方式：在这一基础上可以初步判断出哪些表的哪些宁段应该建立索引。其次，必须熟悉应用程序。必须了解哪些表足数据操作频繁的表：哪些表经常与其他表进行连接；哪些表的数据可能很大；对于数据是大的表，其巾各个字段的数据分布情况如何；等等。对于满足以上条件的这些表，必须重点关注，因为在这些表上的索引，将对SQL语句的性能产生举足轻重的影响。建立索引常用的规则如下： 1、表的主键、外键必须有索引： 2、数据最超过300的表应该有索引： 3、经常与其他表进行连接的表，在连接字段上应该建立索引； 4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； 5、索引应该建在选择性高的字段上；通过字段条件可筛选的记录集很小 6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引： 7、复合索引的建立需要进行仔细分析：尽最考虑用单字段索引代替： 8、频繁进行数据操作的表，不要建立太多的索引 以下是一些字段是否需要建B-TREE索引的经验分类： 1. 需要建索引的字段 主键、外键和有对象或身份表示意义的字段，如CODE,USERNAME 2. 索引慎用字段,需要进行数据分布及使用场景详细评估 日期、年月、状态标志、类型、区域(如COUNTRY,PROVINCE,CITY)、操作人员(如CREATOR,AUDITOR)、数值(如SCORE)、长字符(如ADDRESS) 3. 不适合建索引的字段 描述备注(如: MEMO)、大字段(如：FILE_CONTENT) 索引对DML(INSERT,UPDATE,DELETE)附加的开销 这个没有固定的比例，与每个表记录的大小及索引字段大小密切相关，以下是一个普通表测试数据，仅供参考： 索引对于Insert性能降低56% 索引对于Update性能降低47% 索引对于Delete性能降低29% 因此对于写IO压力比较大的系统，表的索引需要仔细评估必要性，另外索引也会占用一定的存储空间。 4 表执行计划 执行计划可以辅助我们写出高效率的T-SQL代码，同时也可以找出现有T-SQL代码的问题，还可以监控数据库 4.1 表执行开启介绍 SET STATISTICS IO {ON| OFF} /*Transact-SQL 语句生成的磁盘活动量的信息*/ SET SHOWPLAN_ALL ON {ON| OFF} GO;/*返回有关语句执行情况的详细信息，并估计语句对资源的需求*/ SET STATISTICS TIME {ON| OFF} /*显示分析、编译和执行各语句所需的毫秒数*/ 4.2 表执行计划 /*-----------------------------表执行计划------------------------- 如果在执行计划中看到如下所示的任何一项,从性能方面来说,下面所示的每一项都是不理想的。 Index or table scans(索引或者表扫描)：可能意味着需要更好的或者额外的索引。 Bookmark Lookups(书签查找)：考虑修改当前的聚集索引，使用复盖索引，限制SELECT语句中的字段数量。 Filter(过滤)：在WHERE从句中移除用到的任何函数，不要在SQL语句中包含视图，可能需要额外的索引。 Sort(排序)：数据是否真的需要排序？可否使用索引来避免排序？在客户端排序是否会更加有效率？ 以上事項避免得越多,查询性能就会越快. */ use Northwind go SET SHOWPLAN_TEXT ON go select ProductID,sum(Quantity)Quantity from [Order Details] group by ProductID order by ProductID go SET SHOWPLAN_TEXT OFF use Northwind go ----建一个聚集索引 CREATE CLUSTERED INDEX INDEX_ProductID on [Order Details](ProductID) go SET SHOWPLAN_TEXT ON go select ProductID,sum(Quantity)Quantity from [Order Details] group by ProductID order by ProductID go SET SHOWPLAN_TEXT OFF /* 注意:如果有在存储过程中或者其它T-SQL批处理代码中用到了临时表，就不能在查询分析器或Management Studio使用”显示预估的执行计划”选项来评估查询。必须实际运行这个存储过程或者批处理代码。这是因 为使用”显示预估的执行计划”选项来运行一个查询时，它并没有实际被运行，临时表也没有创建。由于临 时表没有被创建，参考到临时表的代码就会失败，导致预估的执行计划不能成创建成功。从另一方面来说， 如果使用的是表变量而不是临时表，则可以使用”显示预估的执行计划”选项. */ use Northwind go --SET STATISTICS TIME ON --GO select a.* from [orders] a,[Order Details] b where a.OrderID=b.OrderID /* 查看执行计划时记住如下几点： (1) 非常复杂的执行计划会被分成多个部分，它们分别列出在屏幕上。每个部分分别代表查询优化器为了得到最终结果而必须执行的单个处理或步骤。执行计划的每个步骤经常会被拆分成一个个更小的子步骤。不幸的是，它们是从右至左显示在屏幕上的。这意味着你必须滚动到图形执行计划的最右边去查看每个步骤是从哪儿开始的 (2) 每个步骤与子步骤间通过箭头连接，藉此显示查询执行的路径。 (3) 最后，查询的所有部分在屏幕顶部的左边汇总到一起,如果将鼠标移动到连接步骤或子步骤的箭头上，就可以看到一个弹出式窗口，上面显示有多少笔记录从一个步骤或子步骤移动到另一个步骤或子步骤(如圖3) 如果将鼠标移动到任何执行计划任何步骤或者子步骤的上面，就会显示一个弹出式窗口，上面显示该步骤或子步骤的更加详细的信息如圖彈出窗口 */ use Northwind go --刪除聚集索引 DROP INDEX [Order Details].INDEX_ProductID --CREATE CLUSTERED INDEX INDEX_ProductID ON [Order Details](ProductID) SET STATISTICS IO ON select * from [Order Details] where ProductID=42 SET STATISTICS IO OFF use Northwind go --刪除聚集索引 --DROP INDEX [Order Details].INDEX_ProductID --建立聚集索引 CREATE CLUSTERED INDEX INDEX_ProductID ON [Order Details](ProductID) go SET STATISTICS IO ON select * from [Order Details] where ProductID=42 SET STATISTICS IO OFF 4.3 表执行计划的应用案例及分析过程 /* 使用说明: SET STATISTICS IO {ON| OFF} /*Transact-SQL 语句生成的磁盘活动量的信息*/ SET SHOWPLAN_ALL ON {ON| OFF} /*返回有关语句执行情况的详细信息，并估计语句对资源的需求*/ SET STATISTICS TIME {ON| OFF} /*显示分析、编译和执行各语句所需的毫秒数*/ 使用T-SQL语句创建索引的语法: CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name ON table_name (column_name) [WITH FILLFACTOR=x] UNIQUE表示唯一索引，可选CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引， 可选FILLFACTOR表示填充因子，指定一个到之间的值，该值指示索引页填满的空间所占的百分比 */ /* 功能说明:创建测试数据，测试上面的优化功能 */ CREATE TABLE employee ( emp_username varchar (20), emp_register DATETIME ) GO --插入测试数据 DECLARE @startid INT DECLARE @endid INT SELECT @startid= 101,@endid = 1000 WHILE @startid <=@endid BEGIN INSERT INTO employee ( emp_username, emp_register ) VALUES ( /* emp_username - varchar (20) */ ‘刘‘+CAST(@startid AS NVARCHAR(20)), /* emp_register - DATETIME */ GETDATE() ) SELECT @startid =@startid +1; END GO -- 查询employee的执行计划和io 信息 SET STATISTICS IO ON SELECT * FROM employee WHERE emp_username = ‘刘‘ /* 查看消息输出的IO 信息 表‘employee‘。(1)1扫描计数，(2)逻辑读取次，(3)物理读取次，(4)预读次，lob 逻辑读取次， lob 物理读取次，lob 预读次。 输出的信息和上面的图片讲解的是对应的 1. 执行的扫描次数。 2. 从磁盘读取的页数。 3. 为进行查询而放入缓存的页数。 4. 预读 T_SQL transaction 语句有很多种的写法， 但是决定那条语句是最优的是根据(logical reads) 逻辑读取来判断。 */ /* 添加聚集索引查询逻辑读取是否会变少 */ CREATE CLUSTERED INDEX Idx_emp_username ON employee (emp_username); DROP INDEX employee.Idx_emp_username /*c查询*/ SET STATISTICS IO ON SELECT * FROM employee WHERE emp_username = ‘刘‘ /* 查看消息输出的IO 信息 表‘employee‘。扫描计数，逻辑读取次，物理读取次，预读次，lob 逻辑读取次，lob 物理读取次，lob 预读次。 Q 这次逻辑读取是次为什么呢？ A.难道查询比表扫描还要慢，答案是对的，数据量小的时候，聚集索引的优势体现不出来。 Q 为什么是次逻辑读取 A 现在查询的时候如聚集索引图，先查询索引页，查找到对应的键值后，扫描数据页，如果有包含索引， 直接在索引页就可以提取到需要的数据。 */ /*------------------------表索引---------------------------------- 上面说了小数据量的时候聚集索引体现不出效果，下面我们继续填充数据测试。 填充测试数据到 表扫描 消息： 表‘employee‘。扫描计数，逻辑读取次，物理读取次，预读次， lob 逻辑读取次，lob 物理读取次，lob 预读次。 聚集索引扫描 消息： 表‘employee‘。扫描计数，逻辑读取次，物理读取次，预读次， lob 逻辑读取次，lob 物理读取次，lob 预读次。 这个时候聚集索引的优势就先显示出来了O(∩_∩)O */ /*-------------------表优化----------------------------------- 下面在来讲讲transaction sql 语句，大家在网上看到的一些人说In like left 不使用索引，我们动手来测试下看他们说的对不对？ */ SET STATISTICS IO ON SELECT * FROM employee WHERE employee.emp_username in (‘刘‘) /* 删除employee表的索引 DROP INDEX employee.Idx_emp_username 表‘employee‘。扫描计数，逻辑读取次，物理读取次，预读次，lob 逻辑读取次，lob 物理读取次，lob 预读次。 打开IO信息 */ --添加Idx_emp_username聚集索引 CREATE CLUSTERED INDEX Idx_emp_username ON employee (emp_username); SET STATISTICS IO ON SELECT * FROM employee WHERE employee.emp_username in (‘刘‘); /* 消息: 表‘employee‘。扫描计数，逻辑读取次，物理读取次，预读次，lob 逻辑读取次，lob 物理读取次，lob 预读次。 使用索引后逻辑读取次，没有使用索引是次，IN 很好的使用了索引！ */ --下面我们来测试下LIKE 是否很好的使用索引 --删除索引 DROP INDEX employee.Idx_emp_username --打开IO 信息 SET STATISTICS IO ON --执行查询 SELECT * FROM employee WHERE employee.emp_username like (‘刘%‘) --添加索引 CREATE CLUSTERED INDEX Idx_emp_username ON employee (emp_username); SET STATISTICS IO ON SELECT * FROM employee WHERE employee.emp_username like ( ‘刘%‘) 4.4 执行计划图标的解释【执行计划学习经典(没有索引)】 1、建立示例数据库（TestDB）： USE [master] GO IF EXISTS (SELECT name FROM sys.databases WHERE name = N‘TestDB‘) DROP DATABASE [TestDB] CREATE DATABASE [TestDB] 2、建立一张新表（Nums,该方法来源SQL SERVER 2005技术内幕:查询）： USE [TestDB] GO IF OBJECT_ID(‘dbo.Nums‘) IS NOT NULL DROP TABLE dbo.Nums; GO CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY); DECLARE @max AS INT, @rc AS INT; SET @max = 1000000; SET @rc = 1; INSERT INTO Nums VALUES(1); WHILE @rc * 2 <= @max BEGIN INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums; SET @rc = @rc * 2; END INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max; GO 3、我们删除聚集索引 USE [TestDB] GO IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N‘[dbo].[Nums]‘) AND name = N‘PK_Nums‘) ALTER TABLE [dbo].[Nums] DROP CONSTRAINT [PK_Nums] 4、对无索引表执行T-SQL语句： 4.1、语句：SELECT n FROM [TestDB].[dbo].[Nums] 选择包括实际的执行计划，我们将看到： SELECT：所使用的是SQL Server 使用的 Transact-SQL 语言元素（Result）图标，Result 运算符是查询计划结束时返回的数据。它通常是显示计划的根元素。Result 是一个语言元素。 表扫描（Table Scan）：该运算符从 参数（Argument） 列中指定的表中检索所有行。如果 Argument 列中出现 WHERE:() 谓词，则仅返回满足该谓词的行。它既是一个逻辑运算符，也是一个物理运算符。 箭头：代表数据流向，高度代表数据的大小。 4.2、语句：INSERT INTO [TestDB].[dbo].[Nums] ([n]) VALUES (1000001) 选择包括实际的执行计划，我们将看到： INSERT：所使用的是通用图标，生成图形显示计划的逻辑找不到迭代器的合适图标时，将显示通用图标。有下列三种通用图标：蓝色（用于迭代器）、橙色（用于游标）和绿色（用于 Transact-SQL 语言构造）。 表插入（Table Insert）：Table Insert 运算符将输入的行插入到 Argument 列指定的表中。Argument 列中还包含一个 SET:() 谓词，指示为各个列设置的值。如果 Table Insert 没有子级插入值，则插入的行是从插入运算符自身获取的。Table Insert 是一个物理运算符。 4.3、语句：UPDATE [TestDB].[dbo].[Nums] SET n = 1000002 WHERE n = 1000001 选择包括实际的执行计划，我们将看到： UPDATE：同INSERT。 表更新（Table Update）：Table Update 物理运算符用于更新在 Argument 列指定的表中的输入行。SET:() 谓词用于确定每个已更新列的值。可以在 SET 子句中、此运算符内的其他位置以及此查询内的其他位置引用这些值。 前几行（Top）：Top 运算符扫描输入，但仅基于排序顺序返回最前面的指定行数或行百分比。Argument 列可以包含要检查重复值的列的列表。在更新计划中，Top 运算符用于强制实施行计数限制。Top 既是一个逻辑运算符，也是一个物理运算符。 表扫描（Table Scan）：同上，待条件的表扫描。 4.4、语句：DELETE FROM [TestDB].[dbo].[Nums] WHERE n = 1000002 选择包括实际的执行计划，我们将看到： DELECTE：同INSERT。 表删除（Table Delete）：Table Delete 物理运算符用于从参数列内所指定的表中删除行。 4.5 执行计划图标的解释【执行计划学习经典(有索引)】 5、对拥有聚集索引表执行T-SQL语句： 执行下面脚本： USE [TestDB] GO ALTER TABLE dbo.Nums ADD CONSTRAINT PK_Nums PRIMARY KEY CLUSTERED ( n ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO 5.1、语句：SELECT n FROM [TestDB].[dbo].[Nums] 选择包括实际的执行计划，我们将看到： 聚集索引扫描（Clustered Index Scan）：Clustered Index Scan 运算符扫描在 Argument 列中指定的聚集索引。存在可选的 WHERE:() 谓词时，则只返回满足该谓词的那些行。如果 Argument 列包含 ORDERED 子句，则表示查询处理器已请求按聚集索引排列行的顺序返回行输出。如果没有 ORDERED 子句，则存储引擎将以最佳方式扫描索引，而不需要生成排序输出。Clustered Index Scan 既是一个逻辑运算符，也是一个物理运算符。 5.2、语句：INSERT INTO [TestDB].[dbo].[Nums] ([n]) VALUES (1000001) 选择包括实际的执行计划，我们将看到： 聚集索引插入（Clustered Index Insert）：Clustered Index Insert 运算符将行从其输入插入到 Argument 列中指定的聚集索引中。Argument 列中还包含一个 SET:() 谓词，指示为各个列设置的值。如果 Clustered Index Insert 没有子级插入值，则插入的行是从插入运算符自身获取的。Clustered Index Insert 是一个物理运算符。 5.3、语句：UPDATE [TestDB].[dbo].[Nums] SET n = 1000002 WHERE n = 1000001 选择包括实际的执行计划，我们将看到： 聚集索引更新（Clustered Index Update）：Clustered Index Update 运算符用于更新 Argument 列中指定的聚集索引中的输入行。如果出现 WHERE:() 谓词，则只更新那些满足该谓词的行。如果出现 SET:() 谓词，则每个已更新的列都将设置为该值。如果出现 DEFINE:() 谓词，则将列出该运算符定义的值。可以在 SET 子句中、该运算符内的其他位置和该查询内的其他位置引用这些值。Clustered Index Update 既是一个逻辑运算符，也是一个物理运算符。 5.4、语句：DELETE FROM [TestDB].[dbo].[Nums] WHERE n = 1000002 选择包括实际的执行计划，我们将看到： 聚集索引删除（Clustered Index Delete）：Clustered Index Delete 运算符从 Argument 列中指定的聚集索引中删除行。如果 Argument 列中出现 WHERE:() 谓词，则只删除满足该谓词的行。Clustered Index Delete 是一个物理运算符。 5.4、语句：SELECT n FROM [TestDB].[dbo].[Nums] WHERE n = 1000000 选择包括实际的执行计划，我们将看到： 聚集索引查找（Clustered Index Seek）：Clustered Index Seek 运算符可以利用索引的查找功能从聚集索引中检索行。Argument 列包含正在使用的聚集索引的名称和 SEEK:() 谓词。存储引擎仅使用索引来处理满足 SEEK:() 谓词的行。它还包括 WHERE:() 谓词，其中存储引擎对满足 SEEK:() 谓词的所有行进行计算，但此操作是可选的，并且不是使用索引来完成的。如果 Argument 列包含 ORDERED 子句，则表示查询处理器已决定必须按聚集索引排序行的顺序返回行。如果没有 ORDERED 子句，存储引擎将以最佳方式搜索索引，而不对输出进行必要的排序。若允许输出保持顺序，则效率可能比生成非排序输出的效率低。出现关键字 LOOKUP 时，将执行书签查找。Clustered Index Seek 既是一个逻辑运算符，也是一个物理运算符。 5 SQL性能调优 系统运行正确性的前提下，使之运行地更快，完成特定功能所需的时间更短。 5.1 SqlServer性能调优之Profile使用【RMLutils生成报表图形看效果】 通常业务系统遇到性能问题时，需要查找原因，Sqlserver为我们提供了RML工具分析此问题 分析方法如下： 1. 打开profile跟踪 选择 Audit Login,Audit Logout,RPC:Completed,RPC:Starting,SQL:BatchCompleted,SQL:BatchStarting事件 选择DataBaseID 和DataBaseName 列开始跟踪，一般跟踪两个小时作业左右的日志文件，可以图形界面也可以用job定时调用(之前介绍过) 2.得到.trc文件之后，安装RMLSetup_X86.msi,并在本地安装，本机安装路径如下：C:\Program Files\Microsoft Corporation\RMLUtils 3.步骤1跟踪的trc文件在本机的C:\1.trc，输入cmd，进入rml安装路径，ReadTrace.exe –I"C:\1.trace.trc" –o"c:\temp\breakout" 4.分析完成之后可以看到报表界面 这样影响性能的语句基本找出来了，接下来的就是优化部分了。 补充： 1.trace文件也可以运用于数据库引擎顾问优化 2.RML分析时是在本地生成了一个数据库，默认是PerfAnalysis，并将trace文件的数据标准化之后导入数据库，为报表展示提供数据 也可以本机连接至PerfAnalysis库，运用存储过程进行分析： CREATE PROCEDURE usp_GetAccessPattern @duration_filter INT = -1 --传入的参数，可以按照语句执行的时间过滤统计 AS BEGIN --DECLARE @duration_filter int=1 /*首先得到全部语句的性能数据的总和*/ DECLARE @sum_total FLOAT, @sum_cpu FLOAT, @sum_reads FLOAT, @sum_duration FLOAT, @sum_writes FLOAT SELECT @sum_total = COUNT(*) * 0.01,--这是所有语句的总数。 @sum_cpu = SUM(cpu) * 0.01, --这是所有语句耗费的CPU时间 @sum_reads = SUM(reads) * 0.01, --这是所有语句耗费的Reads数目，K为单位。 @sum_writes = SUM(writes) * 0.01,--这是所有语句耗费的Writes数目，K为单位。 @sum_duration = SUM(duration) * 0.01--这是所有语句的执行时间总和。 FROM ReadTrace.tblBatches --这是Read80Trace产生的表，包括了Trace文件中所有的语句。 WHERE duration >= @duration_filter --是否按照执行时间过滤 /*然后进行Group by，得到某类语句占用的比例*/ SELECT LTRIM(STR(COUNT(*))) exec_stats, ‘‘ + STR(COUNT(*) / @sum_total, 4, 1) + ‘%‘ ExecRatio, LTRIM(STR(SUM(cpu))) + ‘ : ‘ + +LTRIM(STR(AVG(cpu))) cpu_stats, ‘‘ + STR(SUM(cpu) / @sum_cpu, 4, 1) + ‘%‘ CpuRatio, LTRIM(STR(SUM(reads))) + ‘ : ‘ + LTRIM(STR(AVG(reads))) reads_stats, ‘‘ + STR(SUM(reads) / @sum_reads, 4, 1) + ‘%‘ ReadsRatio, --ltrim(str(sum(writes) ))+‘ : ‘+ltrim(str(avg(writes) )) --writes_stats,‘‘+str(sum(writes)/@sum_writes,4,1) +‘%)‘, LTRIM(STR(SUM(duration))) + ‘ : ‘ + LTRIM(STR(AVG(duration))) duration_stats, ‘‘ + STR(SUM(duration) / @sum_duration, 4, 1) + ‘%‘ DurRatio, textdata, COUNT(*) / @sum_total tp, SUM(cpu) / @sum_cpu cp, SUM(reads) / @sum_reads rp, SUM(duration) / @sum_duration dp INTO #queries_staticstics FROM /* tblUniqueBatches表中存放了所有标准化的语句。*/ ( SELECT reads, cpu, duration, writes, CONVERT(VARCHAR(2000), NormText) textdata FROM ReadTrace.tblBatches INNER JOIN ReadTrace.tblUniqueBatches ON tblBatches.HashId = tblUniqueBatches.hashid WHERE duration > @duration_filter ) B GROUP BY textdata --这个group by很重要，它对语句进行归类统计。 --print ‘Top 10 order by cpu+reads+duration‘ --select top 10 * from #queries_staticstics order by cp+rp+dp desc PRINT ‘Top 10 order by cpu‘ SELECT TOP 10 * FROM #queries_staticstics ORDER BY cp DESC --print ‘Top 10 order by reads‘ --select top 10 * from #queries_staticstics order by rp desc --print ‘Top 10 order by duration‘ --select top 10 * from #queries_staticstics order by dp desc --print ‘Top 10 order by batches‘ --select top 10 * from #queries_staticstics order by tp desc SELECT TOP 10 * FROM #queries_staticstics ORDER BY cp DESC END /************************************************************* 5.2 SqlServer性能调优之SqlServerProfile结合数据库引擎优化顾问的一起使用 步骤一: 【打开SQLSERVERProfile】 步骤二:【新建跟踪】 步骤三:【连接到你要优化查询的数据库】 步骤四:【打开跟踪属性，在常规里面把跟踪名称命名好，在事件选择里面，默认】 步骤五:【点开跟踪属性界面后，点清空跟踪窗口】 步骤六:【在SSMS里面执行SQL语句】 步骤七:【保存跟踪文件】 步骤八: 【在工具里面选择数据库引擎优化顾问 或者在开始任务，=>程序里面选择】 步骤九:【连接到数据库引擎服务里面】 步骤十：【连接到数据库引擎服务里面后，界面如下，然后点新建会话】 步骤十一:【为该会话起个名称，然后选择工作负荷文件(我们之前保存的.trc监测文件)】 步骤十二:【选择.trc文件中sql对应的数据库，并对应的表】 步骤十三:【选择好后，点开始分析按钮，系统会自动把最优的方案给你.】 步骤十四:【系统会生成建议的模板，让你选择】 步骤十五:【系统会提示你建议STATISTICS】 步骤十四:【系统会提示你建议 非聚集索引，如下图，等等】 5.3 SqlServer性能调优之SQL语句结合数据库引擎优化顾问的一起使用【经典】 步骤同SqlServerProfile一样，不一样的就是，在选择文件的时候要选择.sql文件，如下图 【其他的都同SqlServerProfile】 5.4 SQL Server 性能优化之RML Utilities RML（Replay Markup Language）是MS SQL Server产品支持服务团队内部开发使用的一个Trace分析工具，最新的版本支持SQL Server 2005和SQL Server 2008。 功能： １、分析最占资源的应用和查询。 ２、分析跟踪期间的查询计划变更的情况。 ３、分析哪些查询比起以前来说变慢了。 工具地址：http://blogs.msdn.com/psssql/archive/2008/11/12/cumulative-update-1-to-the-rml-utilities-for-microsoft-sql-server-released.aspx。 该文章所有内容均来自于工具的帮助文档。 Quick Start 如果你以前从来没有使用过这个RML Utilities工具，Quick Start能够帮助指导您去使用它们，通过该工具下的示例，我们将完成数据的捕获、分析、生成报告及环境的重现。 The Performance Cycle 做过性能分析的人都知道，性能问题是一个长期的工作，不是通过一次性能优化就能够解决所有的问题，它是一个需要循环的捕获、分析以及解决问题。 生产系统上的环境变量的改变都将影响的SQL Server的性能，所以当我们进行性能处理的时候，需要提前考虑到参数的变化所带来的影响，而且测试环境是在可控制的环境下进行的。下图描述了循环进行性能优化的流程图： 通过Quick Start演练，在详细的RML工具命令下，分解每一步骤来帮助你理解RML工具是如何有效地精确找到性能问题。 Setup Quick Start 假设下列操作已经发生。 1、你已经安装了RML Utilities。 2、你已经创建了文件目录c:\temp，用于文件的存储。 3、你已经在SQL Server 2005或SQL Server 2008上安装了一个实例，并可以通过Windows Authentication和SQL Server管理员权限连接到这个实例。 4、你已经解压了samples.cab, 它包含了演练中所使用的所有脚本。（* Samples.cab放在RML Utility安装目录下。） Quick Start 演练需要使用一个数据库PrecisionPerformance。 1、将RML安装目录添加到PATH环境变量中，打开RML命令提示框。使用Ostress命令执行setup.sql，创建一个名为PrecisionPerformance的数据库。在下面的示例中，你需要为setup.sql文件中指定完整路径，来替代被省略的部分。 ostress -E -S(local) -i......setup.sql -oc:\temp\PPSetup 查看c:\temp\PPSetup\setup.out 与ostress.log文件。 当第一次执行时，这个脚本将返回一个关于删除数据库的错误信息。当然这个在预料之中，是为了证明OStress错误日志输出功能。 [Microsoft][SQL Native Client][SQL Server]无法对数据库 ‘PrecisionPerformance‘ 执行 删除，因为它不存在，或者您没有所需的权限。 现在，您已经将前期的工作准备好了，下一章我们将介绍如何通过SQLDiag采集生产环境的数据及利用跟踪捕获SQL Server执行的语句，执行计划，以及其它的应用数据，并利用ReadTrace工具加载.TRC文件及转化为RML格式文件。 Capture 数据采集需要捕获生产系统上环境及SQL Server执行情况的数据，用于我们进行数据分析。 1、修改SD_Detailed.xml配置文件。(如果你没有执行过SQLDiag.exe工具，那么你必须执行它，用来解压出XML配置文件。) Copy “C:\program Files\Microsoft SQL Server\90\Tools\Binn\SD_Detailed.xml” ”c:\program Files\Microsoft SQL Server\90\Tools\Binn\PPConfig.xml” 将sd_detailed.xml复制一份，并将其命名为PPConfig.xml。 用记事本工具打开PPConfig.xml文件，禁用跟踪事件采集。 定位到：<ProfilerCollector enabled="true"，将它修改为：<ProfilerCollector enabled="false"。 大家是否考虑这样一个问题：我们为什么不直接用SQLDiag进行数据跟踪？ SQLDiag的sd_dtailed.xml文件不能捕获RML Utilities所需要的所有事件，尽管它能采集到部分的跟踪事件。 2、如果我们想使用Replay的话，我们需要一个PrecisionPerformance的克隆数据库，你可以通过SQL Server Management Studio内置功能来实现克隆操作。 根据下面文章中的步骤获取到克隆数据库的脚本。这个脚本也适合SQL Server 2008： How to generate a script of the necessary database metadata to create a statistics-only database in SQLServer 2005: http://support.microsoft.com/kb/914288/en-us 3、如果我们想使用Replay的话，我们还得需要备份当前的数据库，备份操作可以通过示例下的SecureState.sql脚本来获取，如果在你真实的项目上进行操作，请主要安全性，以免数据外泄。（备份操作因该在关闭跟踪捕获后进行，并尽可能消除两者之间(源库与备份库)的差异，否则将影响到replay。） ostress -E -S(local) -iSecureState.sql -oc:\temp\PPSecure 4、启动我们的配置，利用SQLDiag.exe进行数据采集。(跟踪数据的采集将在接下来执行)PPConfig.xml文件将会收集计算机与SQL Server 配置信息，并添加到性能监视计数器上。 sqldiag.exe /Oc:\temp\PPSecure /IPPConfig.xml 注意：请等待直道看见”collection started ”信息后继续运行。 你可以查看c:\temp\PPSecure目录下新增的文件。他们包含SQL Server 错误信息，MSINFO32及其它配置信息。主要文件如下： <<MACHINE NAME>><<INSTANCE NAME>>sp_sqldiag_shutdown.out <<MACHINE>>MSINFO32.TXT MSINFO数据收集需要花费几分钟时间。 注意：通过SQLDiag.exe收集的数据不需要使用RML Utilities的功能。不过捕获当前机器的状态的时刻点，所搜集到的数据非常重要，当我们进行测试时，它能确保replay与测试环境具有相同配置的依据。 5、在当前运行的SQL Server上启动一个SQL Server跟踪来捕获事件。 在SQL Server Management Studio打开一个查询窗体，并加载TraceCaptureDef.sql脚本文件。 注意：这个窗体请保留，直到你完成了Quick Start所有演练。 在这个脚本中，请修改字符串“InsertFileNameHere”为“c:\temp\PrecisionPerformance\sp_trace”。 注意：请不要添加.trc后缀。系统将自动创建一个名为sp_trace.trc的跟踪文件。 执行这个脚本，并保存TraceID，当跟踪数据完成后，用于停止跟踪。 注意：我们所跟踪到数据sp_trace.trc，将被用于RML Utilities进行数据分析。 6、我们需求模拟用户请求，并在当前的数据库上执行，下面的命令是打开两个数据库链接，每个链接执行文件中的查询语句1000次。 ostress -E -S(local) -iWorkload.sql -oc:\temp\PPWorkload -q -n2 -r1000 7、一旦模拟操作结束后，立即停止跟踪与SQLDiag采集。 （1）、使用CTRL + C 停止SQLDiag工具。 （2）、当跟踪被启动时，通过sp_trace_setstatus命令执行T-SQL语句来停止跟踪。 当前，我们已经采集了可供分析的数据，接下来，我将利用RML Utilities的工具进行数据分析，并以图形化的方式进行展现。 Analyze 我们将根据跟踪到的数据进行分析，利用Reporter工具以图表的形式展现出来。 捕获到的TRC数据文件能够被ReadTrace工具所读取，并转化成.RML格式数据，供replay工具使用。 1、执行下面命令，处理加载跟踪采集数据到数据库PPProd中。 ReadTrace -Ic:\temp\PrecisionPerformance\sp_trace.trc -oc:\temp\PPBreakout -S(local) -dPPProd 系统自动分析sp_trace.trc数据，并创建一个PPProd数据库存放分析后的数据，将转化的RML格式数据文件存放到PPBreakout目录下。 2、ReadTrace工具处理完跟踪数据后，将自动启动Reporter工具，方便你对数据进行分析。 你也可以使用SQLNexus加载跟踪采集数据和其它由SQLDiag采集到的其它信息。 SQLNexus提供了附加报表功能，支持可扩展数据采集。 当数据被加载到PPProd数据库后，Performance Overview报表将自动生成。 执行下列操作，理解Reporter所提供的报表数据。 当你执行下列动作，展现新的选项卡时，可以执行不同的操作返回到主选项卡上。 （1）、点击Show/Hide按钮，注意操作后数据的变化。 （2）、点击Application Name,找出占用CPU最多的应用程序。 （3）、点击Parameters，弹出更改时间的窗体。 （4）、点击Unique Batches进入Top Unique Batches详细页面。 （5）、在Unique Batch上点击一个查询，查看具体的详细信息。 正如你所看到的，RML Unities提供了不同方式来分析和测量数据。 想查看更为详细的分析，你可以选择打开由SQLDiag所采集的性能监视文件(.blg)，与计数器有关的信息将在Reporter中显示。 接下来将介绍：Generate Workload (Replay)。 5.5 应用Profiler优化SQL Server数据库系统 概述 当你的SQL Server数据库系统运行缓慢的时候，你或许多多少少知道可以使用SQL Server Profiler（中文叫SQL事件探查器）工具来进行跟踪和分析。是的，Profiler可以用来捕获发送到SQL Server的所有语句以及语句的执行性能相关数据（如语句的read/writes页面数目，CPU的使用量，以及语句的duration等）以供以后分析。但本文并不介绍如何使用Profiler 工具，而是将介绍如何使用read80trace（有关该工具见后面介绍）工具结合自定义的存储过程来提纲挈领地分析Profiler捕获的Trace文件，最终得出令人兴奋的数据分析报表，从而使你可以高屋建瓴地优化SQL Server数据库系统。 本文对那些需要分析SQL Server大型数据库系统性能的读者如DBA等特别有用。在规模较大、应用逻辑复杂的数据库系统中Profiler产生的文件往往非常巨大，比如说在Profiler中仅仅配置捕获基本的语句事件，运行二小时后捕获的Trace文件就可能有GB级的大小。应用本文介绍的方法不但可以大大节省分析Trace的时间和金钱，把你从Trace文件的海量数据中解放出来，更是让你对数据库系统的访问模式了如指掌，从而知道哪一类语句对性能影响最大，哪类语句需要优化等等。 Profiler trace文件性能分析的传统方法以及局限 先说一下什么是数据库系统的访问模式。除了可以使用Trace文件解决如死锁，阻塞，超时等问题外，最常用也是最主要的功能是可以从Trace文件中得到如下三个非常重要的信息： 1. 运行最频繁的语句 2.最影响系统性能的关键语句 3.各类语句群占用的比例以及相关性能统计信息 本文提到的访问模式就是上面三个信息。我们知道，数据库系统的模块是基本固定的，每个模块访问SQL Server的方式也是差不多固定的，具体到某个菜单，某个按钮，都是基本不变的，所以，在足够长的时间内，访问SQL Server的各类语句及其占用的比例也基本上是固定的。换句话说，只要Profiler采样的时间足够长（我一般运行2小时以上），那么从Trace文件中就肯定可以统计出数据库系统的访问模式。每一个数据库系统都有它自己独一无二的访问模式。分析Profiler Trace文件的一个重要目标就是找出数据库系统的访问模式。一旦得到访问模式，你就可以在优化系统的时候做到胸有成竹，心中了然。可惜直到目前为止还没有任何工具可以方便地得到这些信息。 传统的Trace分析方法有两种。一种是使用Profiler工具本身。比如说可以使用Profiler的Filter功能过滤出那些运行时间超过10秒以上的语句，或按照CPU排序找出最耗费CPU的语句等。另一种是把Trace文件导入到数据库中，然后使用T-SQL语句来进行统计分析。这两种方法对较小的Trace文件是有效的。但是，如果Trace文件数目比较多比较大（如4个500MB以上的trace文件），那么这两种方法就有很大的局限性。其局限性之一是因为文件巨大的原因，分析和统计都非常不易，常常使你无法从全局的高度提纲挈领地掌握所有语句的执行性能。你很容易被一些语句迷惑而把精力耗费在上面，而实际上它却不是真正需要关注的关键语句。局限性之二是你发现尽管很多语句模式都非常类似（仅仅是执行时参数不同），却没有一个简单的方法把他们归类到一起进行统计。简而言之，你无法轻而易举地得到数据库系统的访问模式，无法在优化的时候做到高屋建瓴，纲举目张。这就是传统分析方法的局限性。使用下面介绍的Read80trace工具以及自定义的存储过程可以克服这样的局限性。 Read80trace工具介绍以及它的Normalization 功能 Read80Trace工具是一个命令行工具。使用Read80Trace工具可以大大节省分析Trace文件的时间，有事半功倍的效果。Read80Trace的主要工作原理是读取Trace文件，然后对语句进行Normalize (标准化)，导入到数据库，生成性能统计分析的HTML页面。另外，Read80trace可以生成RML文件，然后OSTRESS工具使用RML文件多线程地重放Trace文件中的所有事件。这对于那些想把Profiler捕获的语句在另外一台服务器上重放成为可能。本文不详细介绍Read80trace或OStress工具，有兴趣的读者请自行参阅相关资料，相关软件可以从微软网站下载（注：软件名称为RML，http://www.microsoft.com/downloads/）。 我要利用的是Read80Trace的标准化功能。什么是标准化？就是把那些语句模式类似，但参数不一样的语句全部归类到一起。举例说Trace中有几条语句如下： select * from authors where au_lname = ‘white‘ select * from authors where au_lname = ‘green‘ select * from authors where au_lname = ‘carson‘ 经过标准化后，上面的语句就变成如下的样子： select * from authors where au_lname = {str} select * from authors where au_lname = {str} select * from authors where au_lname = {str} 有了标准化后的语句，统计出数据库系统的访问模式就不再是难事。运行Read80trace 的时候我一般使用如下的命令行： Read80trace –f –dmydb –Imytrace.trc 其中－f开关是不生成RML文件，因为我不需要重放的功能。生成的RML文件比较大，建议读者如果不需要重放的话，也使用－f开关。 －d开关告诉read80trace把trace文件的处理结果存到mydb数据库中。我们后面创建的存储过程正是访问read80trace在mydb中生成的表来进行统计的。－I开关是指定要分析的的trace文件名。Read80trace工具很聪明，如果该目录下有Profiler产生的一系列Trace文件，如mytrace.trc,mytrace1.trc,mytrace2.trc等，那么它会一一顺序读取进行处理。 除了上面介绍的外，Read80trace还有很多其它有趣的开关。比如说使用－i开关使得Read80trace可以从zip或CAB文件中读取trace文件，不用自己解压。所有开关在Read80trace.chm中有详细介绍。我最欣赏的地方是read80trace的性能。分析几个GB大小的trace文件不足一小时就搞定了。我的计算机是一台内存仅为512MB的老机器，有这样的性能我很满意。 你也许会使用read80trace分析压力测试产生的trace文件。我建议还是分析从生产环境中捕获的Trace文件为好。因为很多压力测试工具都不能够真正模拟现实的环境，其得到的trace文件也就不能真实反映实际的情况。甚至有些压力测试工具是循环执行自己写的语句，更不能反映准确的访问模式。建议仅仅把压力测试产生的trace作为参考使用。 使用存储过程分析Normalize后的数据 有了标准化后的语句就可以使用存储过程进行统计分析了。分析的基本思想是把所有模式一样的语句的Reads，CPU和Duration做group by统计，得出访问模式信息： 1. 某类语句的总共执行次数，平均读页面数（reads）/平均CPU时间/平均执行时间等。 2. 该类语句在所有语句的比例，如执行次数比例，reads比例，CPU比例等。 存储过程的定义以及说明如下： /*************************************************************/ Create procedure usp_GetAccessPattern 8000 @duration_filter int=-1 --传入的参数，可以按照语句执行的时间过滤统计 as begin /*首先得到全部语句的性能数据的总和*/ declare @sum_total float,@sum_cpu float,@sum_reads float,@sum_duration float,@sum_writes float select @sum_total=count(*)*0.01,--这是所有语句的总数。 @sum_cpu=sum(cpu)*0.01, --这是所有语句耗费的CPU时间 @sum_reads=sum(reads)*0.01, --这是所有语句耗费的Reads数目，8K为单位。 @sum_writes=sum(writes)*0.01,--这是所有语句耗费的Writes数目，8K为单位。 @sum_duration=sum(duration)*0.01--这是所有语句的执行时间总和。 from tblBatches --这是Read80Trace产生的表，包括了Trace文件中所有的语句。 where duration>=@duration_filter --是否按照执行时间过滤 /*然后进行Group by，得到某类语句占用的比例*/ Select ltrim(str(count(*))) exec_stats,‘‘+ str(count(*)/@sum_total,4,1)+‘%‘ ExecRatio, ltrim(str(sum(cpu)))+‘ : ‘++ltrim(str(avg(cpu))) cpu_stats,‘‘+str(sum(cpu)/@sum_cpu,4,1)+‘%‘ CpuRatio, ltrim(str(sum(reads) ))+‘ : ‘+ltrim(str(avg(reads) )) reads_stats,‘‘+str(sum(reads)/@sum_reads,4,1) +‘%‘ ReadsRatio , --ltrim(str(sum(writes) ))+‘ : ‘+ltrim(str(avg(writes) )) --writes_stats,‘‘+str(sum(writes)/@sum_writes,4,1) +‘%)‘, ltrim(str(sum(duration) ))+‘ : ‘+ltrim(str(avg(duration))) duration_stats,‘‘+str(sum(duration)/@sum_duration,4,1)+‘%‘ DurRatio , textdata,count(*)/@sum_total tp,sum(cpu)/@sum_cpu cp,sum(reads)/@sum_reads rp,sum(duration)/@sum_duration dp into #queries_staticstics from /* tblUniqueBatches表中存放了所有标准化的语句。*/ (select reads,cpu,duration,writes,convert(varchar(2000),NormText)textdata from tblBatches inner join tblUniqueBatches on tblBatches.HashId=tblUniqueBatches.hashid where duration>@duration_filter ) B group by textdata --这个group by很重要，它对语句进行归类统计。 print ‘Top 10 order by cpu+reads+duration‘ select top 10 * from #queries_staticstics order by cp+rp+dp desc print ‘Top 10 order by cpu‘ select top 10 * from #queries_staticstics order by cp desc print ‘Top 10 order by reads‘ select top 10 * from #queries_staticstics order by rp desc print ‘Top 10 order by duration‘ select top 10 * from #queries_staticstics order by dp desc print ‘Top 10 order by batches‘ select top 10 * from #queries_staticstics order by tp desc End /*************************************************************/ 考虑到输出结果横向较长，存储过程中把writes去掉了。这是因为大部分的数据库系统都是Reads为主的。你可以轻易的修改存储过程把write也包括进去。 存储过程并不复杂，很容易理解。可以看到统计的结果放在queries_staticstics表中，然后按照不同的条件排序后输出。举例说： select top 10 * from #queries_staticstics order by cp desc 上面的语句将把queries_staticstics表中的记录按照某类语句占用总CPU量的比例cp（即sum(cpu)/@sum_cpu）进行排序输出。这让你在分析服务器CPU性能问题的时候快速定位哪一类语句最耗CPU资源，从而对症下药。 现在让我们看一个实例的输出： /********************/ Use mydb Exec usp_GetAccessPattern /*你可以输入一个执行时间作为过滤参数，毫秒为单位。如usp_GetAccessPattern 1000*/ /********************/ 输出结果如图 1所示（是部分结果，另外，因为原输出结果横向很长，为方便阅读，把结果从中截断为两部分）： 图 1：输出结果采样一 上面的例子采样于一家大型公司的业务系统。该系统的问题是应用程序运行缓慢，SQL Server 服务器的CPU高居不下（8个CPU都在90%～100%间波动）。我使用PSSDIAG工具采样2小时左右的数据，然后运行read80trace和usp_GetAccessPattern得出上面的结果。报表一目了然。存储过程DBO.x_DEDUP_PROC在两小时内共运行75次，却占用了90.8％的CPU资源，94.6%的Reads，从访问模式的角度，该存储过程正是导致CPU高和系统性能慢的关键语句。一旦优化了该存储过程，系统的性能问题将迎刃而解。你也许有疑问，两小时内共运行75次,不是很频繁啊。其实你看看这条存储过程的平均CPU时间是681961毫秒，大概11分钟左右。也就是说一个CPU两小时内最多可以执行（60*2）/11＝10条左右，该系统总共有8个CPU，即使全部CPU都用来运行该语句，那么最多也就是10*8＝80条左右。上面执行总数是75，说明该存储过程一直在连续不断地运行。 那么该系统运行最频繁的语句是什么呢？我从结果中摘取另外一部分如下（图 2）： 上表可以看出，最频繁运行的语句是 USE xb SET QUOTED_IDENTIFIER,ANSI_NULL_DFLT_ON… 显然这是一条执行环境配置语句，没有参考价值。倒是另外两条占用语句总数8.2%的语句值得关注： SELECT COUNT(*) FROM x_PROCESS_STATS WHERE PROCESS…… SELECT COUNT(*) FROM x_PROCESS_STATS WHERE PROCESS…… 在这个例子中，因为关键语句DBO.x_DEDUP_PROC非常突出，甚至上面的两条语句都可以忽略了。 让我们再多看一个例子（图 3）： 从上面的例子中, 可以得出关键的语句是: SELECT COUNT(*) FROM GTBL7MS SELECT CaseNO FROM PATIENTDATA_sum WHERE MRN = @P1 后续的检查发现相关的表没有有效的索引，加上索引后性能立即整体地提高了不少.。解决了这两个语句，需要使用同样的手段继续分析和优化，直到系统的性能能够接受为止.。注意性能调优是一个长期的过程，你不太可能一两天就可以把所有的问题都解决。也许一开始可以解决80%的问题，但是后面20%的问题却需要另外80%的时间。 返回页首 使用usp_GetAccessPattern的一些技巧 usp_GetAccessPattern的输出报表包含了非常丰富的信息。分析报表的时候需要有大局观。你也可以有目的性地选择你需要的信息。如果是CPU性能瓶颈的系统，那么你需要关注CPU占用比例高的那类语句。如果是磁盘IO出现性能瓶颈那么你需要找到那些Reads占用比例大而且平均reads也很高的语句。需要注意的是有时候运行频繁的语句未必就是你需要关注的关键语句。一个最理想的情况是关键语句正好就是最频繁的语句。有时候即使最频繁语句占用的资源比例不高，但如果还可以优化，那么因为放大效应，微小的优化也会给系统带来可观的好处。 在使用usp_GetAccessPattern的时候多结合@duration_filter参数使用。因为参数以毫秒为单位，建议参数不要小于1000，而应该是1000的倍数 如3000，5000等。该参数常常会给出非常有意思的输出。该输出和不带参数运行的结果会有某些重叠。重叠出现的语句通常正是需要关注的语句。要注意运行最多最密的语句未必有超过1000毫秒的执行时间，所有带参数运行的结果有可能不包括最频繁语句。我常常同时交叉分析四个结果，一个是不带参数运行得到的，另三个分别是使用1000，3000和5000毫秒为参数运行的结果。比较分析这四个结果往往使我对数据库系统的访问模式有非常清晰透彻的理解。 运行存储过程时你也许会碰到int整数溢出的错误。这是因为表tblBatches 中的reads,cpu 和writes字段是int而不是bigint。可以运行如下语句进行修正： alter table tblBatches alter column reads bigint alter table tblBatches alter column cpu bigint alter table tblBatches alter column writes bigint 修正后溢出问题就会解决。 返回页首 蛇足：哪个是HOT 数据库？ 本文到这里就基本上结束了。你已经知道如何使用Read80Trace和usp_GetAccessPattern得到数据库系统的访问模式，以及如何从全局的高度去分析访问模式报表，从而在优化系统的时候做到提纲挈领，胸有成竹。 除此之外，你还可以应用类似的分析思想得到每个数据库的占用资源比例。这对于SQL Server有多个数据库的情况非常有用。从报表中你可以立即知道哪个数据库是最HOT最消耗系统资源的数据库。语句如下： print ‘group by dbid‘ declare @sum_total float,@sum_cpu float,@sum_reads float,@sum_duration float,@sum_writes float select @sum_total=count(*)*0.01,@sum_cpu=sum(cpu)*0.01,@sum_reads=sum(reads)*0.01,@sum_writes=sum(writes)*0.01, @sum_duration=sum(duration)*0.01 from tblBatches select dbid, ltrim(str(count(*))) exec_stats,‘‘+ str(count(*)/@sum_total,4,1)+‘%‘ ExecRatio, ltrim(str(sum(cpu)))+‘ : ‘++ltrim(str(avg(cpu))) cpu_stats,‘‘+str(sum(cpu)/@sum_cpu,4,1)+‘%‘ CpuRatio , ltrim(str(sum(reads) ))+‘ : ‘+ltrim(str(avg(reads) )) reads_stats,‘‘+str(sum(reads)/@sum_reads,4,1) +‘%‘ ReadsRatio , ltrim(str(sum(duration) ))+‘ : ‘+ltrim(str(avg(duration))) duration_stats,‘‘+str(sum(duration)/@sum_duration,4,1)+‘%‘ DurRatio , count(*)/@sum_total tp,sum(cpu)/@sum_cpu cp,sum(reads)/@sum_reads rp,sum(duration)/@sum_duration dp into #queries_staticstics_groupbydb from (select reads,cpu,duration,writes,convert(varchar(2000),NormText)textdata,dbid from tblBatches inner join tblUniqueBatches on tblBatches.HashId=tblUniqueBatches.hashid ) b group by dbid order by sum(reads) desc select dbid,ExecRatio batches,CPURatio CPU,ReadsRatio Reads,DurRatio Duration from #queries_staticstics_groupbydb 下面是一个上面语句结果的一个例子： dbid batches CPU Reads Duration ------ ------- ----- ------- -------- 37 21.1% 18.7% 29.1% 27.1% 33 12.7% 32.4% 19.5% 24.8% 36 5.6% 28.3% 15.6% 26.1% 20 53.9% 2.9% 14.2% 2.1% 22 0.8% 7.2% 13.2% 6.6% 25 1.0% 3.6% 5.4% 3.5% 16 0.0% 1.5% 1.9% 0.7% 35 2.0% 2.7% 1.8% 5.7% 7 0.1% 0.1% 1.1% 0.3% 上面的结果明确地告诉我们ID为37，33和36的数据库是最活跃的数据库。一个有趣的事实是数据库20发出的语句总数比例是53.9%，但是其占用的系统资源比例却不高。 5.6 SQL查询性能调试，用SET STATISTICS IO和SET STATISTICS TIME 一个查询需要的CPU、IO资源越多，查询运行的速度就越慢，因此，描述查询性能调节任务的另一种方式是，应该以一种使用更少的CPU、IO资源的方式重写查询命令，如果能够以这样一种方式完成查询，查询的性能就会有所提高。 如果调节查询性能的目的是让它使用尽可能少的服务器资源，而不是查询运行的时间最短，那么就更容易测试你采取的措施是提高了查询的性能还是降低了查询的性能。尤其是在资源利用不断变化的服务器上更是如此。首先，需要搞清楚在对查询进行调节时，如何测试我们的服务器的资源使用情况。 在开始我们的例子前，先运行下面的这二条命令（不要在正在使用的服务器上执行），这二条命令将清除SQL Server的数据和过程缓冲区，这样能够使我们在每次执行查询时在同一个起点上，否则，每次执行查询得到的结果就不具有可比性了：DBCC DROPCLEANBUFFERS和DBCC FREEPROCCACHE 输入并运行下面的Transact-SQL命令： SET STATISTICS IO ON SET STATISTICS TIME ON 一旦上面的准备工作完成后，运行下面的查询： SELECT * FROM [order details] 显示结果： SQL Server parse and compile time: （SQL Server解析和编译时间：） CPU time = 10 ms, elapsed time = 61 ms. ……（1） SQL Server parse and compile time: （SQL Server解析和编译时间：） CPU time = 0 ms, elapsed time = 0 ms. ……（2） （所影响的行数为 2155 行） ……（3） Table ‘Order Details‘. Scan count 1, logical reads 10, physical reads 1, read-ahead reads 9. （表：Order Details，扫描次数 1，逻辑读 10，物理读 1，提前读取 9） ……（4） SQL Server Execution Times: （SQL Server执行时间：） CPU time = 30 ms, elapsed time = 387 ms. ……（5） 标志（1）表示SQL Server解析“ELECT * FROM [order details]”命令并将解析的结果放到SQL Server的过程缓冲区中供SQL Server使用所需要的CPU运行时间和总的时间。 标志（2）表示SQL Server从过程缓冲区中取出解析结果供执行的时间，大多数情况下这二个值都会是0，因为这个过程执行得相当地快。 标志（5）表示执行这次查询使用了多少CPU运行时间和运行查询使用了多少时间。CPU运行时间是对运行查询所需要的CPU资源的一种相对稳定的测量方法，与CPU的忙闲程度没有关系。但是，每次运行查询时这一数字也会有所不同，只是变化的范围没有总时间变化大。总时间是对查询执行所需要的时间（不计算阻塞或读数据的时间），由于服务器上的负载是在不断变化的，因此这一数据的变化范围有时会相当地大。（由于CPU占用时间是相对稳定的，因此可以使用这一数据作为衡量你的调节措施是提高了查询性能还是降低了查询的性能的一种方法。） 标志（4）是SET STATISTICS IO的效果 Scan Count：在查询中涉及到的表被访问的次数。在我们的例子中，其中的表只被访问了1次，由于查询中不包括连接命令，这一信息并不是十分有用，但如果查询中包含有一个或多个连接，则这一信息是十分有用的。（一 个循环外部的表的Scan Count值为1，但对于一个循环内的表而言，其值为循环的次数。可以想象得到，对于一个循环内的表而言，其Scan Count值越小，它所使用的资源越少，查询的性能也就越高。因此在调节一个带连接的查询的性能时，需要关注Scan Count的值，在进行调节时，注意观察它是增加还是减少了。） Logical Reads: 这是SET STATISTICS IO或SET STATISTICS TIME命令提供的最有用的 数据。我们知道，SQL Server在可以对任何数据进行操作前，必须首先把数据读取到其数据缓冲区中。此外，我们也知道SQL Server何时会从数据缓冲区中读取数据，并把数据读取到大小为8K字节的页中。那么Logical Reads的意义是什么呢？Logical Reads是指SQL Server为得到查询中的结果而必须从数据缓冲区读取的页数。在执行查询时，SQL Server不会读取比实际需求多或少的数据，因此，当在相同的数据集上执行同一个查询，得到的Logical Reads的数字总是相同的。（SQL Server执行查询时的Logical Reads值每一次这个数值是不会变化的。因此，在进行查询性能的调节时，这是一个可以用来衡量你的调节措施是否成功的一个很好的标准。如果 Logical Reads值下降，就表明查询使用的服务器资源减少，查询的性能有所提高。如果Logical Reads值增加，则表示调节措施降低了查询的性能。在其他条件不变的情况下，一个查询使用的逻辑读越少，其效率就越高，查询的速度就越快。） Physical Reads：物 理读，在执行真正的查询操作前，SQL Server必须从磁盘上向数据缓冲区中读取它所需要的数据。在SQL Server开始执行查询前，它要作的第一件事就是检查它所需要的数据是否在数据缓冲区中，如果在，就从中读取，如果不在，SQL Server必须首先将它需要的数据从磁盘上读到数据缓冲区中。我们可以想象得到，SQL Server在执行物理读时比执行逻辑读需要更多的服务器资源。因此，在理想情况下，我们应当尽量避免物理读操作。下面的这一部分听起来让人容易感到糊涂 了。在对查询的性能进行调节时，可以忽略物理读而只专注于逻辑读。你一定会纳闷儿，刚才不是还说物理读比逻辑读需要更多的服务器资源吗？情况确实是这样， SQL Server在执行查询时所需要的物理读次数不可能通过性能调节而减少的。减少物理读的次数是DBA的一项重要工作，但它涉及到整个服务器性能的调节，而 不仅仅是查询性能的调节。在进行查询性能调节时，我们不能控制数据缓冲区的大小或服务器的忙碌程度以及完成查询所需要的数据是在数据缓冲区中还是在磁盘 上，唯一我们能够控制的数据是得到查询结果所需要执行的逻辑读的次数。 因此，在查询性能的调节中，我们可以心安理得地不理会SET STATISTICS IO命令提供的Physical Read的值。（减少物理读次数、加快SQL Server运行速度的一种方式是确保服务器的物理内存足够多。） Read-Ahead Reads： 与Physical Reads一样，这个值在查询性能调节中也没有什么用。Read-Ahead Reads表示SQL Server在执行预读机制时读取的物理页。为了优化其性能，SQL Server在认为它需要数据之前预先读取一部分数据，根据SQL Server对数据需求预测的准确程度，预读的数据页可能有用，也可能没用。 在本例中，Read-Ahead Reads的值为9，Physical Read的值为1，而Logical Reads的值为10，它们之间存在着简单的相加关系。那么我在服务器上执行查询时的过程是怎么样的呢？首先，SQL Server会开始检查完成查询所需要的数据是否在数据缓冲区中，它会很快地发现这些数据不在数据缓冲区中，并启动预读机制将它所需要的10个数据页中的 前9个读取到数据缓冲区。当SQL Server检查是否所需要的全部数据都已经在数据缓冲区时，会发现已经有9个数据页在数据缓冲区中，还有一个不在，它就会立即再次读取磁盘，将所需要的 页读到数据缓冲区。一旦所有的数据都在数据缓冲区后，SQL Server就可以处理查询了。 总结：在对查询的性能进行调节时用一些科学的标准来测量你的调节措施是否有效是十分重要的。问题是，SQL Servers的负载是动态变化的，使用查询总的运行时间来衡量你正在调节性能的查询的性能是提高了还是没有，并不是一个合理的方法。 更好的方法是比较多个数据，例如逻辑读的次数或者查询所使用的CPU时间。因此在对查询的性能进行调节时，需要首先使用SET STATISTICS IO和SET STATISTICS TIME命令向你提供一些必要的数据，以便确定你对查询性能进行调节的措施是否真正地得到了目的。 ====================== 1.测试前用二条命令清除SQL Server的数据和过程缓冲区，以保证测试条件相同： DBCC DROPCLEANBUFFERS和DBCC FREEPROCCACHE 2.SET STATISTICS TIME：看cpu时间 3.SET STATISTICS IO：关注scan count（计数)------查询读取的表数量 logical read( 逻辑读)次数 ====================== PS:经测试确认SqlServer 2005中对表的连接条件会自动进行优化，但为了养成良好的习惯和在其他数据库上开发的性能考虑，需继续保持大表连接字段放在左边的习惯。 5.7 SQL性能优化【不断总结】 1.查询的模糊匹配 尽量避免在一个复杂查询里面使用 LIKE ‘%parm1%‘—— 红色标识位置的百分号会导致相关列的索引无法使用，最好不要用. 解决办法: 其实只需要对该脚本略做改进，查询速度便会提高近百倍。改进方法如下： a、修改前台程序——把查询条件的供应商名称一栏由原来的文本输入改为下拉列表，用户模糊输入供应商名称时，直接在前台就帮忙定位到具体的供应商，这样在调用后台程序时，这列就可以直接用等于来关联了。 b、直接修改后台——根据输入条件，先查出符合条件的供应商，并把相关记录保存在一个临时表里头，然后再用临时表去做复杂关联 2.索引问题 在做性能跟踪分析过程中，经常发现有不少后台程序的性能问题是因为缺少合适索引造成的，有些表甚至一个索引都没有。这种情况往往都是因为在设计表时，没去定义索引，而开发初期，由于表记录很少，索引创建与否，可能对性能没啥影响，开发人员因此也未多加重视。然一旦程序发布到生产环境，随着时间的推移，表记录越来越多，这时缺少索引，对性能的影响便会越来越大了。 这个问题需要数据库设计人员和开发人员共同关注 法则：不要在建立的索引的数据列上进行下列操作: 避免对索引字段进行计算操作 避免在索引字段上使用not，<>，!= 避免在索引列上使用IS NULL和IS NOT NULL 避免在索引列上出现数据类型转换 避免在索引字段上使用函数 避免建立索引的列中使用空值。 3.复杂操作 部分UPDATE、SELECT 语句 写得很复杂（经常嵌套多级子查询）——可以考虑适当拆成几步，先生成一些临时数据表，再进行关联操作 4.update 同一个表的修改在一个过程里出现好几十次，如： update table1 set col1=... where col2=...; update table1 set col1=... where col2=... ...... 象这类脚本其实可以很简单就整合在一个UPDATE语句来完成（前些时候在协助xxx项目做性能问题分析时就发现存在这种情况） 5.在可以使用UNION ALL的语句里，使用了UNION UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话， 务必使用UNION ALL。还有一种情况大家可能会忽略掉，就是虽然要求几个子集的并集需要过滤掉重复记录，但由于脚本的特殊性，不可能存在重复记录，这时便应该使用UNION ALL，如xx模块的某个查询程序就曾经存在这种情况，见，由于语句的特殊性，在这个脚本 中几个子集的记录绝对不可能重复，故可以改用UNION ALL） 6.在WHERE 语句中，尽量避免对索引字段进行计算操作 这个常识相信绝大部分开发人员都应该知道，但仍有不少人这么使用，我想其中一个最主要的原因可能是为了编写方便吧，但如果仅为了编 写简单而损害了性能，那就不可取了 9月份在对XX系统做性能分析时发现，有大量的后台程序存在类似用法，如： ...... where trunc(create_date)=trunc(:date1) 虽然已对create_date 字段建了索引，但由于加了TRUNC，使得索引无法用上。此处正确的写法应该是 where create_date>=trunc(:date1) and create_date<trunc(:date1)+1 或者是 where create_date between trunc(:date1) and trunc(:date1)+1-1/(24*60*60) 注意：因between 的范围是个闭区间（greater than or equal to low value and less than or equal to high value.）， 故严格意义上应该再减去一个趋于0的小数，这里暂且设置成减去1秒（1/(24*60*60)），如果不要求这么精确的话，可以略掉这步 7.对Where 语句的法则 7.1 避免在WHERE子句中使用in，not in，or 或者having。 可以使用 exist 和not exist代替 in和not in。 可以使用表链接代替 exist。 Having可以用where代替，如果无法代替可以分两步处理。 例子 SELECT * FROM ORDERS WHERE CUSTOMER_NAME NOT IN (SELECT CUSTOMER_NAME FROM CUSTOMER) 优化 SELECT * FROM ORDERS WHERE CUSTOMER_NAME not exist (SELECT CUSTOMER_NAME FROM CUSTOMER) 7.2 不要以字符格式声明数字，要以数字格式声明字符值。（日期同样） 否则会使索引无效，产生全表扫描。 例子 使用：SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369; 不要使用：SELECT emp.ename, emp.job FROM emp WHERE emp.empno = ‘7369’ 7.3 WHERE后面的条件顺序影响 Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。 WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如 Select * from zl_yhjbqk where dy_dj = ‘1KV以下‘ and xh_bz=1 Select * from zl_yhjbqk where xh_bz=1 and dy_dj = ‘1KV以下‘ 以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj = ‘1KV以下‘条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。 8.对Select语句的法则 在应用程序、包和过程中限制使用select * from table这种方式。 例子 使用 SELECT empno,ename,category FROM emp WHERE empno = ‘7369‘ 而不要使用 SELECT * FROM emp WHERE empno = ‘7369‘ 9. 排序 避免使用耗费资源的操作 带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行，耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序 10.临时表 慎重使用临时表可以极大的提高系统性能 11.ORDER BY ORDER BY 子句只在两种严格的条件下使用索引. ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序. ORDER BY中所有的列必须定义为非空. 12.SQL书写的影响（共享SQL语句可以提高操作效率） 同一功能同一性能不同写法SQL的影响 如一个SQL在A程序员写的为 Select * from zl_yhjbqk B程序员写的为 Select * from dlyx.zl_yhjbqk（带表所有者的前缀） C程序员写的为 Select * from DLYX.ZLYHJBQK（大写表名） D程序员写的为 Select * from DLYX.ZLYHJBQK（中间多了空格） 以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。 推荐方案：不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，防止相同的Sql语句被多次分析。 5.8 SQL性能优化一个完整的SQL案例 /*----------------------SQL性能优化（不断总结）-------------- 功能说明：索引的总结 */ DECLARE @BeginDate DATETIME, @EndDate DATETIME; SELECT @BeginDate = ‘2010-1-1‘, @EndDate = ‘2010-12-31‘; ----------------此查询根据索引来查得，花费时间【分秒】 400万数据 SELECT * FROM dbo.Fact_SaleCar WHERE CheckOutDate >= @BeginDate AND CheckOutDate <= @EndDate -------------------而这个是指扫描聚集索引特定范围的行 /* ------------------避免不要在建立的索引的数据列上进行下列操作: 此查询在索引字段进行计算操作了，花费时间【分秒】避免对索引字段进行计算操作 400万数据 延伸说明:避免对索引字段进行计算操作 避免在索引字段上使用not，<>，!= 避免在索引列上使用IS NULL和IS NOT NULL 避免在索引列上出现数据类型转换 避免在索引字段上使用函数 避免建立索引的列中使用空值。 */ SELECT * FROM dbo.Fact_SaleCar WHERE YEAR(CheckOutDate) = 2010 -------执行此全部扫描了，索引不起作用了【整体扫描聚集索引】 /*--------------查询的模糊匹配 功能说明: 尽量避免在一个复杂查询里面使用LIKE ‘%parm1%‘ ——红色标识位置的百分号会导致相关列的索引无法使用，最好不要用. */ SELECT * FROM dbo.Fact_SaleCar WHERE SaleName LIKE ‘%商店%‘ /*------------------复杂操作 功能说明:部分UPDATE、SELECT 语句写得很复杂（经常嵌套多级子查询） ——可以考虑适当拆成几步，先生成一些临时数据表，再进行关联操作 ,可使用WITH来替代 */ ;WITH CTE AS ( SELECT * FROM dbo.Fact_SaleCar ) SELECT * FROM CTE /*------------------UPDATE 功能说明:同一个表的修改在一个过程里出现好几十次， 象这类脚本其实可以很简单就整合在一个UPDATE语句来完成 */ UPDATE Fact_SaleCar SET Attribute1 = ‘?‘,Attribute2 = ‘?‘ WHERE SaleCarId = ‘?‘ /*----------------------在可以使用UNION ALL的语句里，使用了UNION 功能说明:UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。 一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL。还有一种情况大 家可能会忽略掉，就是虽然要求几个子集的并集需要过滤掉重复记录，但由于脚本的特 殊性，不可能存在重复记录，这时便应该使用UNION ALL，如xx模块的某个查询程序就曾 经存在这种情况，见，由于语句的特殊性，在这个脚本 中几个子集的记录绝对不可能重复，故可以改用UNION ALL） */ /*-------------------------------在WHERE 语句中，尽量避免对索引字段进行计算操作 功能说明:这个常识相信绝大部分开发人员都应该知道，但仍有不少人这么使用， 我想其中一个最主要的原因可能是为了编写方便吧，但如果仅为了编 写简单而损害了性能，那就不可取了 9月份在对XX系统做性能分析时发现，有大量的后台程序存在类似用法，如： ...... where trunc(create_date)=trunc(:date1) 虽然已对create_date 字段建了索引，但由于加了TRUNC，使得索引无法用上。此处正确的写法应该是 where create_date>=trunc(:date1) and create_date<trunc(:date1)+1 或者是 where create_date between trunc(:date1) and trunc(:date1)+1-1/(24*60*60) 注意：因between 的范围是个闭区间（greater than or equal to low value and less than or equal to high value.）， 故严格意义上应该再减去一个趋于的小数，这里暂且设置成减去秒（/(24*60*60)），如果不要求 这么精确的话，可以略掉这步 */ SELECT * FROM dbo.Fact_SaleCar WHERE CheckOutDate >= @BeginDate AND CheckOutDate <= @EndDate SELECT * FROM dbo.Fact_SaleCar ----------不能使用这种 WHERE YEAR(CheckOutDate) = 2010 /*----------------------对WHERE 语句的法则 功能说明:避免在WHERE子句中使用in，not in，or 或者having。 【可以使用exist 和not exist代替in和not in。 可以使用表链接代替exist。 Having可以用where代替，如果无法代替可以分两步处理。 】 */ --例子【sysobjects是辅助表，】 SELECT * FROM Fact_SaleCar WHERE SaleCarId NOT IN ( SELECT name FROM sysobjects ) --优化 SELECT * FROM Fact_SaleCar WHERE not exists ( SELECT TOP 1 * FROM sysobjects WHERE SaleCarId = sysobjects.NAME ) /* 功能说明:不要以字符格式声明数字，要以数字格式声明字符值。（日期同样） 否则会使索引无效，产生全表扫描。 */ -----例子 --使用： SELECT emp.SaleCarId, emp.SaleName FROM Fact_SaleCar emp WHERE emp.CheckOutDate = GETDATE(); --不要使用： SELECT emp.SaleCarId, emp.SaleName FROM Fact_SaleCar emp WHERE emp.CheckOutDate = ‘2011-9-15‘; /*-------------------WHERE后面的条件顺序影响 功能说明:Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前， 可以过滤掉最大数量记录的条件必须写在Where子句的末尾。WHERE子句后面的条件顺序对 大数据量表的查询会产生直接的影响 */ --SELECT * FROM zl_yhjbqk WHERE dy_dj = ‘1KV以下‘ AND xh_bz=1 --SELECT * FROM zl_yhjbqk WHERE xh_bz=1 AND dy_dj = ‘1KV以下‘ /* 以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全 表扫描，第一条SQL的dy_dj = ‘1KV以下‘条件在记录集内比率为%，而xh_bz=1的比率只为.5%，在进行 第一条SQL的时候%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候.5%条记录都进行dy_dj 及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。 */ /*-------------------对Select语句的法则 功能说明:在应用程序、包和过程中限制使用select * from table这种方式。 例子 使用 SELECT empno,ename,category FROM emp WHERE empno = ‘7369‘ 而不要使用 SELECT * FROM emp WHERE empno = ‘7369‘ */ /* ----------------------排序 功能说明:避免使用耗费资源的操作 带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行，耗费资源 的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序 */ /*----------------------临时表 功能说明:慎重使用临时表可以极大的提高系统性能 */ /*----------------------ORDER BY 功能说明:ORDER BY 子句只在两种严格的条件下使用索引. ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序. ORDER BY中所有的列必须定义为非空. */ /*--------------------SQL书写的影响（共享SQL语句可以提高操作效率） 同一功能同一性能不同写法SQL的影响 如一个SQL在A程序员写的为 Select * from zl_yhjbqk B程序员写的为 Select * from dlyx.zl_yhjbqk（带表所有者的前缀） C程序员写的为 Select * from DLYX.ZLYHJBQK（大写表名） D程序员写的为 Select * from DLYX.ZLYHJBQK（中间多了空格） 以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存， 如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可 以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。 推荐方案：不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，防止相同的Sql语句被多次分析。 6 参考文献 http://terryli.blog.51cto.com/704315/163315 【分区表理论解析(上)：SQL Server 2k5&2k8系列(一)】 http://terryli.blog.51cto.com/704315/163317 【分区表理论解析(上)：SQL Server 2k5&2k8系列(一)】 http://terryli.blog.51cto.com/704315/169601 【实战分区表：SQL Server 2k5&2k8系列(三) 】 http://www.sansky.net/article/2007-05-23-dskprobe.html 【分区表备份工具DSKPROBE使用说明】 http://www.cnblogs.com/liuyong/archive/2010/09/10/1822543.html 【SQL SERVER 表分区 】 http://www.cnblogs.com/rob0121/articles/2077894.html 【SQL Server 表分区注意事项 】 http://blog.csdn.net/wufeng4552/article/details/4716053 【SQL Server 2005中的文件和文件组(ㄧ) .】 http://blog.csdn.net/wufeng4552/article/details/4728248 【SQL Server2005 表分区三步曲 】 http://www.cnblogs.com/gaizai/archive/2011/07/01/2095539.html 【SQL Server 表分区实战系列（文章索引）】 http://www.cnblogs.com/fuhongwei041/archive/2011/06/30/2093899.html 【SQL Server表分区(一):基础知识和实现方式】 http://www.cnblogs.com/beachant/archive/2011/06/24/2089046.html 【手把手教你建立SQL数据库的表分区】 http://www.cnblogs.com/fuhongwei041/archive/2011/06/30/2093899.html 【SQL Server表分区(一):基础知识和实现方式 】 http://www.cnblogs.com/lanzi/archive/2011/03/24/1993612.html 【表分区介绍】 http://blog.csdn.net/smallfools/article/details/4930810 【SQL Server 2005中的分区表（一）：什么是分区表？为什么要用分区表？如何创建分区表？】 http://www.cnblogs.com/gaizai/archive/2011/01/14/1935579.html 【SQL Server 动态生成分区脚本】 http://www.blogjava.net/wangxinsh55/archive/2011/04/20/348634.html 【Sql Server性能优化——Partition（创建分区）】 http://www.cnblogs.com/Henry1225/archive/2011/06/22/2087011.html 【MSSQL08实现对数据库对象更改跟踪】 http://www.cnblogs.com/Henry1225/archive/2011/05/18/2050386.html 【Sqlserver性能调优之RML Utilities】 http://www.cnblogs.com/danling/archive/2010/05/12/1733279.html 【SQL Server 性能优化之RML Utilities：快速入门（Quick Start）（1）】 http://www.cnblogs.com/danling/archive/2010/05/12/1733523.html 【SQL Server 性能优化之RML Utilities：快速入门（Quick Start）（2）】 http://www.cnblogs.com/danling/archive/2010/05/12/1733805.html 【SQL Server 性能优化之RML Utilities：快速入门（Quick Start）（3）】 http://www.cnblogs.com/danling/archive/2010/05/19/1739444.html 【SQL SERVER 图形执行计划中的图标学习（1）】 http://www.cnblogs.com/danling/archive/2010/05/20/1740242.html 【SQL SERVER 图形执行计划中的图标学习（2） 】 http://www.cnblogs.com/wq3if2in/archive/2009/04/02/1428068.html 【SQL索引机制】
调优过程
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。