USE
AdventureWorks;
GO
-- 主键只用来保证数据,而聚集索引影响数据的逻辑排序
-- 下面的语句执行计划中并没有看到排序操作,证明数据是按SalesOrderID排过序的,而且是一种双向链表
SELECT TOP ( 10 ) * FROM Sales.SalesOrderHeader ORDER BY SalesOrderID
SELECT TOP ( 10 ) * FROM Sales.SalesOrderHeader ORDER BY SalesOrderID DESC
SELECT TOP ( 10 ) SalesOrderID FROM Sales.SalesOrderHeader ORDER BY SalesOrderID
SELECT TOP ( 10 ) SalesOrderID FROM Sales.SalesOrderHeader
-- ======================================================================
-- 查看表内数据是如何存储的
-- ======================================================================
SELECT object_name (p. object_id ) as tablename,i.name as indexname,
rows,a.type_desc as page_type_desc,
total_pages as pages,first_page
FROM sys.partitions p
JOIN sys.system_internals_allocation_units a
ON p.partition_id = a.container_id
JOIN sys.indexes i
ON p.index_id = i.index_id AND p. object_id = i. object_id
WHERE p. object_id = object_id (N ' Sales.SalesOrderHeader ' )
-- 查看所含的pages字段最多的则为聚集索引叶级,其余的为别的索引页
-- 0xA01400000100得知第一页为5280,此为聚集索引叶的第一页
DBCC TRACEON( 3604 )
GO
DBCC PAGE(AdventureWorks, 1 , 5280 , 3 ) -- 查看第4~7字节的值是按照SalesOrderID从小到大排列的
GO
-- 查看IX_SalesOrderHeader_CustomerID索引的第一页的第一个值是否为1
-- 0x105200000100得到第一页为21008
DBCC PAGE(AdventureWorks, 1 , 21008 , 1 )
GO
-- ======================================================================
-- 行长度的限制8060
-- ======================================================================
USE tempdb;
GO
IF OBJECT_ID (N ' bigrows ' ,N ' U ' ) IS NOT NULL
DROP TABLE dbo.bigrows
GO
CREATE TABLE dbo.bigrows
(
a char ( 3000 ),
b char ( 3000 ),
c char ( 2000 ),
d char ( 60 )
)
GO
INSERT INTO dbo.bigrows
SELECT REPLICATE ( ' a ' , 3000 ), REPLICATE ( ' b ' , 3000 ),
REPLICATE ( ' c ' , 2000 ), REPLICATE ( ' d ' , 6 )
-- 2005使用行溢出数据突破了変长列的限制,但这样会增加I/O操作。
IF OBJECT_ID (N ' bigrows ' ,N ' U ' ) IS NOT NULL
DROP TABLE dbo.bigrows
GO
CREATE TABLE dbo.bigrows
(
a varchar ( 3000 ),
b varchar ( 3000 ),
c varchar ( 3000 ),
d varchar ( 3000 )
)
GO
INSERT INTO dbo.bigrows
SELECT REPLICATE ( ' a ' , 3000 ), REPLICATE ( ' b ' , 3000 ),
REPLICATE ( ' c ' , 3000 ), REPLICATE ( ' d ' , 3000 )
SELECT object_name ( object_id ) as name,
partition_id,partition_number as pnum,rows,
allocation_unit_id as au_id,type_desc as page_type,total_pages as pages
FROM sys.partitions p
JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE object_id = object_id (N ' dbo.bigrows ' );
-- ======================================================================
-- 非聚集索引通过聚集索引进行查找
-- ======================================================================
-- SQL2005新增用于查看表中各索引的统计的动态函数
SELECT * FROM sys.dm_db_index_physical_stats( DB_ID (N ' AdventureWorks ' ), OBJECT_ID (N ' Sales.SalesOrderHeader ' ), NULL , NULL , ' DETAILED ' );
-- 索引的深度
SELECT INDEXPROPERTY ( object_ID ( ' Sales.SalesOrderHeader ' ),N ' PK_SalesOrderHeader_SalesOrderID ' , ' IndexDepth ' )
SELECT INDEXPROPERTY ( object_ID ( ' Sales.SalesOrderHeader ' ),N ' AK_SalesOrderHeader_SalesOrderNumber ' , ' IndexDepth ' )
-- 聚集索引查找,因为索引的深度为3,所以此时逻辑读为3
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = 43659
-- 非聚集索引通过聚集索引进行查找(逻辑读有5次,因为聚集为3,非聚集为2)
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderNumber = N ' SO45283 '
-- ======================================================================
-- 复合索引测试
-- ======================================================================
BEGIN TRANSACTION
GO
DROP INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
(
CustomerID,
CreditCardID
) WITH ( STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
GO
COMMIT
UPDATE STATISTICS Sales.SalesOrderHeader IX_SalesOrderHeader_CustomerID
-- 查看此索引的统计信息
DBCC SHOW_STATISTICS( ' Sales.SalesOrderHeader ' ,IX_SalesOrderHeader_CustomerID)
SELECT COUNT ( * ) -- EQ_ROWS等于上限值的个数
FROM Sales.SalesOrderHeader
WHERE CustomerID = 1
SELECT COUNT ( * ) -- RANGE_ROWS此范围内不包括边界的记录数
FROM Sales.SalesOrderHeader
WHERE CustomerID > 1 AND CustomerID < 19
SELECT COUNT ( DISTINCT CustomerID) -- DISTINCT_RANGE_ROWS 此范围内不包括边界的不同记录数
FROM Sales.SalesOrderHeader
WHERE CustomerID > 1 AND CustomerID < 19
SELECT 1.0 * COUNT ( * ) / COUNT ( DISTINCT CustomerID) -- AVG_RANGE_ROWS 数据分布程度
FROM Sales.SalesOrderHeader
WHERE CustomerID > 1 AND CustomerID < 19
-- 查看Sales.SalesOrderHeader表中索引信息
SELECT * FROM
sys.dm_db_index_physical_stats( DB_ID (N ' AdventureWorks ' ), OBJECT_ID (N ' Sales.SalesOrderHeader ' ), NULL , NULL , ' DETAILED ' );
-- IX_SalesOrderHeader_CustomerID的索引编号为5深度为2,叶级有71个页面,根叶1页
SELECT QUOTENAME (name)
FROM sys.indexes
WHERE object_id = 722101613 AND index_id = 5 ;
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO
-- 非聚集索引查找+键值查找(逻辑读为8,非聚集索引查找2页加每个值一次聚集索引查找)
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 19965
-- 非聚集索引扫描+键值查找(因此使用复合索引除第一个字段外,不会进行索引查找)
SELECT * FROM Sales.SalesOrderHeader WHERE CreditCardID = 15466
-- 覆盖索引查找(逻辑读为2,说明只在非聚集索引中查找到了所需的数据)
SELECT SalesOrderID,CreditCardID FROM Sales.SalesOrderHeader
WHERE CustomerID = 19965
-- 作为查询条件的字段会自动创建统计
SELECT * FROM Production.Location WHERE ModifiedDate > ' 20070101 '
-- 所有自动创建的统计
SELECT m.name + ' . ' + object_name (s. object_id ) as [ table ] ,s.name as [ statistics ] ,c.name as [ column ]
FROM sys.stats s
join sys.stats_columns sc
on s.stats_id = sc.stats_id and s. object_id = sc. object_id
join sys.columns c
on sc.column_id = c.column_id and c. object_id = sc. object_id
join sys.objects o
on s. object_id = o. object_id
join sys.schemas m
on m.schema_id = o.schema_id
where s.auto_created = 1 and o.type = ' U '
SELECT object_name (id),name,statblob -- 统计信息二进制大型对象(BLOB),仅内部使用,返回NULL.
FROM sys.sysindexes
-- SELECT * FROM sys.stats_columns
-- ====================================================================================
-- 选择度过低而无法有效利用索引查找而选择表扫描,查询2004-7-01之后的订单
-- ====================================================================================
USE AdventureWorks;
GO
BEGIN TRANSACTION
GO
DROP INDEX IX_OrderDate ON Sales.SalesOrderHeader
GO
CREATE NONCLUSTERED INDEX IX_OrderDate ON Sales.SalesOrderHeader
(
OrderDate
) WITH ( STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
GO
COMMIT
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO
-- 分别使用2004-7-01低选择度与2004-7-26高选择度比较两种查询的性能差异
SELECT SalesOrderID,OrderDate,Status,CustomerID,TaxAmt,TotalDue
FROM Sales.SalesOrderHeader
WHERE OrderDate > ' 2004-7-01 '
-- 首先在非聚焦索引叶级中扫描获得最大及最小的订单号,由于此时表扫描只是在叶级所以I/O操作会明显减少
DECLARE @min int ,
@max int
SELECT @min = MIN (SalesOrderID), @max = MAX (SalesOrderID)
FROM Sales.SalesOrderHeader
WHERE OrderDate > ' 2004-7-01 '
-- 使用聚焦索引
SELECT SalesOrderID,OrderDate,Status,CustomerID,TaxAmt,TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderID BETWEEN @min AND @max
GO
-- ================================================
-- 什么是查询参数规范SARGS
-- ================================================
-- 1、不要对字段做运算
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID + 1 = 2
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 1
-- 2、不要对字段使用函数(数学函数、日期函数、字符串函数等)
SELECT * FROM Sales.SalesOrderHeader WHERE ABS (SalesOrderID - 44659 ) < 1
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID < 44660 AND SalesOrderID > 44658
-- 3、不要使用负向查询(NOT、!=、<>、!>、!<、NOT EXISTS、NOT IN)
SELECT * FROM Sales.SalesOrderDetail WHERE OrderQty != 1
SELECT * FROM Sales.SalesOrderDetail WHERE OrderQty > 1 OR OrderQty < 1
-- 4、小心使用OR(虽然有聚集索引但是因Status无索引可用只能进行表扫描)
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = 43659 OR [ Status ] = 1
-- ================================================================================
-- 三种联结(嵌套循环、合并联结、哈稀联结),所占用的资源依次增加,SQL优先选用嵌套循环
-- ================================================================================
-- 1、嵌套循环(外表、内表),记录少的表作为外表,在内表中根据每个联结字段的值进行
-- 循环,只有在外表数据量较少时使用
SELECT *
FROM Sales.SalesOrderDetail D JOIN Sales.SalesOrderHeader O
ON D.SalesOrderID = O.SalesOrderID
WHERE O.SalesOrderID BETWEEN 43659 AND 43665
-- 2、合并联结的算法前提是字段已排序
SELECT *
FROM Sales.SalesOrderDetail D JOIN Sales.SalesOrderHeader O
ON D.SalesOrderID = O.SalesOrderID
WHERE O.SalesOrderID BETWEEN 43659 AND 54000
-- 3、哈稀联结(在要联结的字段上无可用索引时使用此连接算法)
IF OBJECT_ID (N ' SumPrice ' ,N ' U ' ) IS NOT NULL
DROP TABLE SumPrice
GO
SELECT O.SalesOrderID, YEAR (O.OrderDate) + MONTH (O.OrderDate) AS Date, CAST ( SUM (D.OrderQty * D.UnitPrice * ( 1 - D.UnitPriceDiscount)) AS decimal ( 18 , 2 )) AS Price
INTO SumPrice
FROM
Sales.SalesOrderDetail D
JOIN
Sales.SalesOrderHeader O
ON D.SalesOrderID = O.SalesOrderID
GROUP BY O.SalesOrderID,O.OrderDate
GO
SELECT O.SalesOrderID,O.
FROM SumPrice D JOIN Sales.SalesOrderHeader O
ON D.SalesOrderID = O.SalesOrderID
WHERE O.SalesOrderID BETWEEN 43659 AND 54000
-- ==========================================================================================
-- 代替LEFT JOIN的方法
-- 为了防止在查询中出现LEFT操作,在设计数据库时应尽量避免关联表中不一致的情况出现
-- 比如产品的类别和具体产品的对应关系中,可在类别加入一个无类别的记录以对应不知是何类别的产品
-- ==========================================================================================
BEGIN TRANSACTION
GO
DROP INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
(
CustomerID
) WITH ( STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
GO
COMMIT
DBCC FLUSHPROCINDB( 8 );
GO
DBCC DROPCLEANBUFFERS;
GO
-- 1、查询所有客户的总订单数量
SELECT C.CustomerID, COALESCE ( SUM (OrderQty), 0 ) AS Quantity
FROM Sales.Customer C LEFT JOIN
( Sales.SalesOrderHeader O
JOIN Sales.SalesOrderDetail D ON O.SalesOrderID = D.SalesOrderID
) ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID
GO
-- 2、替代的查询方式
DECLARE @CustomerInfo table
(
CustomerID int not null ,
Quantity smallint not null
)
INSERT INTO @CustomerInfo
SELECT CustomerID, 0
FROM Sales.Customer;
WITH SumQuantity AS
(
SELECT O.CustomerID, COALESCE ( SUM (OrderQty), 0 ) AS Quantity
FROM Sales.SalesOrderHeader O
JOIN Sales.SalesOrderDetail D ON O.SalesOrderID = D.SalesOrderID
GROUP BY O.CustomerID
)
UPDATE @CustomerInfo
SET C.Quantity = S.Quantity
FROM @CustomerInfo C JOIN SumQuantity S
ON S.CustomerID = C.CustomerID;
SELECT * FROM @CustomerInfo ;
GO
-- ==========================================================================================
-- 控制表之间的联结
-- ==========================================================================================
USE Northwind;
GO
SELECT DISTINCT C.CompanyName AS customer, S.CompanyName AS supplier
FROM dbo.Customers AS C
LEFT OUTER JOIN dbo.Orders AS O
ON O.CustomerID = C.CustomerID
LEFT OUTER JOIN dbo. [ Order Details ] AS OD
ON OD.OrderID = O.OrderID
LEFT OUTER JOIN dbo.Products AS P
ON P.ProductID = OD.ProductID
LEFT OUTER JOIN dbo.Suppliers AS S
ON S.SupplierID = P.SupplierID
-- OPTION(FORCE ORDER);
-- 上面的查询过多的使用外联结,可使用下面的方式替换
SELECT DISTINCT C.CompanyName AS customer, S.CompanyName AS supplier
FROM dbo.Orders AS O
JOIN dbo. [ Order Details ] AS OD
ON OD.OrderID = O.OrderID
JOIN dbo.Products AS P
ON P.ProductID = OD.ProductID
JOIN dbo.Suppliers AS S
ON S.SupplierID = P.SupplierID
RIGHT OUTER JOIN dbo.Customers AS C
ON O.CustomerID = C.CustomerID;
SELECT DISTINCT C.CompanyName AS customer, S.CompanyName AS supplier
FROM dbo.Customers AS C
LEFT OUTER JOIN
(dbo.Orders AS O
JOIN dbo. [ Order Details ] AS OD
ON OD.OrderID = O.OrderID
JOIN dbo.Products AS P
ON P.ProductID = OD.ProductID
JOIN dbo.Suppliers AS S
ON S.SupplierID = P.SupplierID)
ON O.CustomerID = C.CustomerID;
-- ============================================================
-- 存储过程相关
-- ============================================================
USE Northwind;
GO
-- ***********************************************************
-- 1、计划缓存重用造成的无效查询计划
-- 造成上述问题的原因是因为SQLServer为了节省编译的开销会优先考虑使用缓存的计划
-- 2000下只能使用过程级重新编译,2005下提供了语句级重新编译的特性。从而避免了编译
-- 整个过程的开销
IF OBJECT_ID (N ' dbo.usp_GetOrders ' ,N ' P ' ) IS NOT NULL
DROP PROC dbo.usp_GetOrders;
GO
CREATE PROC dbo.usp_GetOrders
@odate AS DATETIME
AS
SELECT c.CustomerID, c.CompanyName,
o.EmployeeID, o.OrderID, o.OrderDate
FROM dbo.Orders o
join dbo.Customers c
on o.CustomerID = c.CustomerID
WHERE OrderDate >= @odate ;
-- OPTION(RECOMPILE);
GO
-- 选择度高的测试
EXEC dbo.usp_GetOrders ' 19980506 ' ;
-- 选择度低的测试
EXEC dbo.usp_GetOrders ' 19960101 ' ;
-- 查看缓存计划
SELECT *
FROM sys.syscacheobjects
WHERE sql NOT LIKE ' %cache% '
AND sql LIKE ' %usp_GetOrders% ' ;
-- 清除缓存计划
DBCC FREEPROCCACHE;
GO
-- ***********************************************************
-- 2、参数嗅探测
-- 以当前日期插入一条记录,高选择度查询
INSERT INTO dbo.Orders(OrderDate, CustomerID, EmployeeID) VALUES ( GETDATE (), N ' ALFKI ' , 1 );
GO
ALTER PROC dbo.usp_GetOrders
@d AS INT = 0
AS
DECLARE @odate AS DATETIME ;
SET @odate = DATEADD ( day , - @d , CONVERT ( VARCHAR ( 8 ), GETDATE (), 112 ));
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= @odate ;
GO
-- 查看表dbo.Orders空间情况
SELECT * FROM
sys.dm_db_index_physical_stats( DB_ID (N ' Northwind ' ), OBJECT_ID (N ' dbo.Orders ' ), NULL , NULL , ' DETAILED ' );
-- 此时虽然选择度高,但是因为在过程编译时优化器无法根据参数进行最优化
EXEC dbo.usp_GetOrders;
-- 使用聚集索引代替非聚集索引进行查询的参数嗅探测试
USE AdventureWorks;
GO
IF OBJECT_ID (N ' dbo.GetCutomerOrderByDate ' ,N ' P ' ) IS NOT NULL
DROP PROC dbo.GetCutomerOrderByDate;
GO
CREATE PROCEDURE dbo.GetCutomerOrderByDate
@start datetime
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @min int ,
@max int
SELECT @min = MIN (SalesOrderID), @max = MAX (SalesOrderID)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @start
SELECT SalesOrderID,OrderDate,Status,CustomerID,TaxAmt,TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderID BETWEEN @min AND @max
END
GO
DBCC FREEPROCCACHE;
GO
-- 低选择度测试
EXEC dbo.GetCutomerOrderByDate ' 2004-5-01 '
-- 高选择度测试
EXEC dbo.GetCutomerOrderByDate ' 2004-7-26 '
-- 缓存计划
SELECT *
FROM sys.syscacheobjects
WHERE sql NOT LIKE ' %cache% '
AND sql LIKE ' %GetCutomerOrderByDate% ' ;
-- ***********************************************************
-- 3、Execute/sp_executesql
USE Northwind;
GO
DBCC FREEPROCCACHE;
GO
--
DECLARE @i AS INT ;
DECLARE @sql AS VARCHAR ( 52 );
SET @i = 10248 ;
WHILE @i < 10252
BEGIN
SET @sql = ' SELECT * FROM dbo.Orders WHERE OrderID = '
+ CAST ( @i AS VARCHAR ( 10 )) + N ' ; ' ;
EXEC ( @sql );
SET @i = @i + 1
END
-- 查看缓存计划,可以看到除了参数化计划外还为每个不同的值保存一个缓存计划
SELECT cacheobjtype, objtype, usecounts, sql
FROM sys.syscacheobjects
WHERE sql NOT LIKE ' %cache% '
AND sql NOT LIKE ' %sys.% ' ;
-- 存储过程中调用同样存在上面的问题,为每个参数分配一个缓存计划
IF OBJECT_ID (N ' dbo.GetOrderByID ' ,N ' P ' ) IS NOT NULL
DROP PROC dbo.GetOrderByID;
GO
CREATE PROCEDURE dbo.GetOrderByID
@i AS INT
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @sql AS VARCHAR ( 52 );
SET @sql = ' SELECT * FROM dbo.Orders WHERE OrderID = '
+ CAST ( @i AS VARCHAR ( 10 )) + N ' ; ' ;
EXEC ( @sql );
END
GO
DECLARE @i AS INT ;
SET @i = 10248 ;
WHILE @i < 10252
BEGIN
EXEC dbo.GetOrderByID @i
SET @i = @i + 1
END
GO
ALTER PROCEDURE dbo.GetOrderByID
@i AS INT
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @sql AS NVARCHAR ( 52 );
SET @sql = N ' SELECT * FROM dbo.Orders WHERE OrderID = @ID '
EXEC sp_executesql
@stmt = @sql ,
@params = N ' @ID as int ' ,
@ID = @i
END
GO
DBCC FREEPROCCACHE;
GO
DECLARE @i AS INT ;
SET @i = 10248 ;
WHILE @i < 10252
BEGIN
EXEC dbo.GetOrderByID @i
SET @i = @i + 1
END
-- 动态语句的选择度测试
IF OBJECT_ID (N ' dbo.usp_GetOrders ' ,N ' P ' ) IS NOT NULL
DROP PROC dbo.usp_GetOrders;
GO
CREATE PROC dbo.usp_GetOrders
@odate AS DATETIME
AS
SET NOCOUNT ON ;
DECLARE @sql AS NVARCHAR ( 100 );
SET @sql = N ' SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= @odate OPTION(RECOMPILE); '
EXEC sp_executesql
@stmt = @sql ,
@params = N ' @odate as datetime ' ,
@odate = @odate
GO
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= ' 19980506 ' option (recompile)
-- 选择度高的测试
EXEC dbo.usp_GetOrders ' 19980506 ' ;
-- 选择度低的测试
EXEC dbo.usp_GetOrders ' 19960101 ' ;
-- 查看缓存计划
SELECT *
FROM sys.syscacheobjects
WHERE sql NOT LIKE ' %cache% '
AND sql LIKE ' %usp_GetOrders% ' ;
-- 清除缓存计划
DBCC FREEPROCCACHE;
GO
-- 动态语句EXEC
IF OBJECT_ID (N ' dbo.usp_GetOrders ' ,N ' P ' ) IS NOT NULL
DROP PROC dbo.usp_GetOrders;
GO
CREATE PROC dbo.usp_GetOrders
@odate AS varchar ( 10 )
AS
SET NOCOUNT ON ;
DECLARE @sql AS VARCHAR ( 120 );
SET @sql = ' SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= ''' + @odate + ''' ; '
print @sql
EXEC ( @sql )
-- OPTION(RECOMPILE);
GO
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= ' 19980506 '
select convert ( datetime , ' 19980506 ' )
-- 选择度高的测试
EXEC dbo.usp_GetOrders ' 19980506 ' ;
-- 选择度低的测试
EXEC dbo.usp_GetOrders ' 19960101 ' ;
-- 查看缓存计划
SELECT *
FROM sys.syscacheobjects
WHERE sql NOT LIKE ' %cache% '
AND sql LIKE ' %usp_GetOrders% ' ;
-- 清除缓存计划
DBCC FREEPROCCACHE;
GO
-- ============================================================
-- 日志相关
-- ============================================================
DBCC LOGINFO;
GO
DBCC SQLPERF(LOGSPACE);
GO
-- 2005
WHILE 1 = 1
BEGIN
UPDATE TOP ( 5000 ) dbo.A
SET CustomerID = N ' ABCDE '
WHERE CustomerID = N ' OLDWO ' ;
IF @@rowcount < 5000 BREAK ;
END
GO
-- 2000
SET ROWCOUNT 5000 ;
WHILE 1 = 1
BEGIN
DELETE FROM dbo.LargeOrders
WHERE OrderDate < ' 19970101 ' ;
IF @@rowcount < 5000 BREAK ;
END
SET ROWCOUNT 0 ;
GO
-- 主键只用来保证数据,而聚集索引影响数据的逻辑排序
-- 下面的语句执行计划中并没有看到排序操作,证明数据是按SalesOrderID排过序的,而且是一种双向链表
SELECT TOP ( 10 ) * FROM Sales.SalesOrderHeader ORDER BY SalesOrderID
SELECT TOP ( 10 ) * FROM Sales.SalesOrderHeader ORDER BY SalesOrderID DESC
SELECT TOP ( 10 ) SalesOrderID FROM Sales.SalesOrderHeader ORDER BY SalesOrderID
SELECT TOP ( 10 ) SalesOrderID FROM Sales.SalesOrderHeader
-- ======================================================================
-- 查看表内数据是如何存储的
-- ======================================================================
SELECT object_name (p. object_id ) as tablename,i.name as indexname,
rows,a.type_desc as page_type_desc,
total_pages as pages,first_page
FROM sys.partitions p
JOIN sys.system_internals_allocation_units a
ON p.partition_id = a.container_id
JOIN sys.indexes i
ON p.index_id = i.index_id AND p. object_id = i. object_id
WHERE p. object_id = object_id (N ' Sales.SalesOrderHeader ' )
-- 查看所含的pages字段最多的则为聚集索引叶级,其余的为别的索引页
-- 0xA01400000100得知第一页为5280,此为聚集索引叶的第一页
DBCC TRACEON( 3604 )
GO
DBCC PAGE(AdventureWorks, 1 , 5280 , 3 ) -- 查看第4~7字节的值是按照SalesOrderID从小到大排列的
GO
-- 查看IX_SalesOrderHeader_CustomerID索引的第一页的第一个值是否为1
-- 0x105200000100得到第一页为21008
DBCC PAGE(AdventureWorks, 1 , 21008 , 1 )
GO
-- ======================================================================
-- 行长度的限制8060
-- ======================================================================
USE tempdb;
GO
IF OBJECT_ID (N ' bigrows ' ,N ' U ' ) IS NOT NULL
DROP TABLE dbo.bigrows
GO
CREATE TABLE dbo.bigrows
(
a char ( 3000 ),
b char ( 3000 ),
c char ( 2000 ),
d char ( 60 )
)
GO
INSERT INTO dbo.bigrows
SELECT REPLICATE ( ' a ' , 3000 ), REPLICATE ( ' b ' , 3000 ),
REPLICATE ( ' c ' , 2000 ), REPLICATE ( ' d ' , 6 )
-- 2005使用行溢出数据突破了変长列的限制,但这样会增加I/O操作。
IF OBJECT_ID (N ' bigrows ' ,N ' U ' ) IS NOT NULL
DROP TABLE dbo.bigrows
GO
CREATE TABLE dbo.bigrows
(
a varchar ( 3000 ),
b varchar ( 3000 ),
c varchar ( 3000 ),
d varchar ( 3000 )
)
GO
INSERT INTO dbo.bigrows
SELECT REPLICATE ( ' a ' , 3000 ), REPLICATE ( ' b ' , 3000 ),
REPLICATE ( ' c ' , 3000 ), REPLICATE ( ' d ' , 3000 )
SELECT object_name ( object_id ) as name,
partition_id,partition_number as pnum,rows,
allocation_unit_id as au_id,type_desc as page_type,total_pages as pages
FROM sys.partitions p
JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE object_id = object_id (N ' dbo.bigrows ' );
-- ======================================================================
-- 非聚集索引通过聚集索引进行查找
-- ======================================================================
-- SQL2005新增用于查看表中各索引的统计的动态函数
SELECT * FROM sys.dm_db_index_physical_stats( DB_ID (N ' AdventureWorks ' ), OBJECT_ID (N ' Sales.SalesOrderHeader ' ), NULL , NULL , ' DETAILED ' );
-- 索引的深度
SELECT INDEXPROPERTY ( object_ID ( ' Sales.SalesOrderHeader ' ),N ' PK_SalesOrderHeader_SalesOrderID ' , ' IndexDepth ' )
SELECT INDEXPROPERTY ( object_ID ( ' Sales.SalesOrderHeader ' ),N ' AK_SalesOrderHeader_SalesOrderNumber ' , ' IndexDepth ' )
-- 聚集索引查找,因为索引的深度为3,所以此时逻辑读为3
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = 43659
-- 非聚集索引通过聚集索引进行查找(逻辑读有5次,因为聚集为3,非聚集为2)
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderNumber = N ' SO45283 '
-- ======================================================================
-- 复合索引测试
-- ======================================================================
BEGIN TRANSACTION
GO
DROP INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
(
CustomerID,
CreditCardID
) WITH ( STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
GO
COMMIT
UPDATE STATISTICS Sales.SalesOrderHeader IX_SalesOrderHeader_CustomerID
-- 查看此索引的统计信息
DBCC SHOW_STATISTICS( ' Sales.SalesOrderHeader ' ,IX_SalesOrderHeader_CustomerID)
SELECT COUNT ( * ) -- EQ_ROWS等于上限值的个数
FROM Sales.SalesOrderHeader
WHERE CustomerID = 1
SELECT COUNT ( * ) -- RANGE_ROWS此范围内不包括边界的记录数
FROM Sales.SalesOrderHeader
WHERE CustomerID > 1 AND CustomerID < 19
SELECT COUNT ( DISTINCT CustomerID) -- DISTINCT_RANGE_ROWS 此范围内不包括边界的不同记录数
FROM Sales.SalesOrderHeader
WHERE CustomerID > 1 AND CustomerID < 19
SELECT 1.0 * COUNT ( * ) / COUNT ( DISTINCT CustomerID) -- AVG_RANGE_ROWS 数据分布程度
FROM Sales.SalesOrderHeader
WHERE CustomerID > 1 AND CustomerID < 19
-- 查看Sales.SalesOrderHeader表中索引信息
SELECT * FROM
sys.dm_db_index_physical_stats( DB_ID (N ' AdventureWorks ' ), OBJECT_ID (N ' Sales.SalesOrderHeader ' ), NULL , NULL , ' DETAILED ' );
-- IX_SalesOrderHeader_CustomerID的索引编号为5深度为2,叶级有71个页面,根叶1页
SELECT QUOTENAME (name)
FROM sys.indexes
WHERE object_id = 722101613 AND index_id = 5 ;
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO
-- 非聚集索引查找+键值查找(逻辑读为8,非聚集索引查找2页加每个值一次聚集索引查找)
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 19965
-- 非聚集索引扫描+键值查找(因此使用复合索引除第一个字段外,不会进行索引查找)
SELECT * FROM Sales.SalesOrderHeader WHERE CreditCardID = 15466
-- 覆盖索引查找(逻辑读为2,说明只在非聚集索引中查找到了所需的数据)
SELECT SalesOrderID,CreditCardID FROM Sales.SalesOrderHeader
WHERE CustomerID = 19965
-- 作为查询条件的字段会自动创建统计
SELECT * FROM Production.Location WHERE ModifiedDate > ' 20070101 '
-- 所有自动创建的统计
SELECT m.name + ' . ' + object_name (s. object_id ) as [ table ] ,s.name as [ statistics ] ,c.name as [ column ]
FROM sys.stats s
join sys.stats_columns sc
on s.stats_id = sc.stats_id and s. object_id = sc. object_id
join sys.columns c
on sc.column_id = c.column_id and c. object_id = sc. object_id
join sys.objects o
on s. object_id = o. object_id
join sys.schemas m
on m.schema_id = o.schema_id
where s.auto_created = 1 and o.type = ' U '
SELECT object_name (id),name,statblob -- 统计信息二进制大型对象(BLOB),仅内部使用,返回NULL.
FROM sys.sysindexes
-- SELECT * FROM sys.stats_columns
-- ====================================================================================
-- 选择度过低而无法有效利用索引查找而选择表扫描,查询2004-7-01之后的订单
-- ====================================================================================
USE AdventureWorks;
GO
BEGIN TRANSACTION
GO
DROP INDEX IX_OrderDate ON Sales.SalesOrderHeader
GO
CREATE NONCLUSTERED INDEX IX_OrderDate ON Sales.SalesOrderHeader
(
OrderDate
) WITH ( STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
GO
COMMIT
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO
-- 分别使用2004-7-01低选择度与2004-7-26高选择度比较两种查询的性能差异
SELECT SalesOrderID,OrderDate,Status,CustomerID,TaxAmt,TotalDue
FROM Sales.SalesOrderHeader
WHERE OrderDate > ' 2004-7-01 '
-- 首先在非聚焦索引叶级中扫描获得最大及最小的订单号,由于此时表扫描只是在叶级所以I/O操作会明显减少
DECLARE @min int ,
@max int
SELECT @min = MIN (SalesOrderID), @max = MAX (SalesOrderID)
FROM Sales.SalesOrderHeader
WHERE OrderDate > ' 2004-7-01 '
-- 使用聚焦索引
SELECT SalesOrderID,OrderDate,Status,CustomerID,TaxAmt,TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderID BETWEEN @min AND @max
GO
-- ================================================
-- 什么是查询参数规范SARGS
-- ================================================
-- 1、不要对字段做运算
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID + 1 = 2
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 1
-- 2、不要对字段使用函数(数学函数、日期函数、字符串函数等)
SELECT * FROM Sales.SalesOrderHeader WHERE ABS (SalesOrderID - 44659 ) < 1
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID < 44660 AND SalesOrderID > 44658
-- 3、不要使用负向查询(NOT、!=、<>、!>、!<、NOT EXISTS、NOT IN)
SELECT * FROM Sales.SalesOrderDetail WHERE OrderQty != 1
SELECT * FROM Sales.SalesOrderDetail WHERE OrderQty > 1 OR OrderQty < 1
-- 4、小心使用OR(虽然有聚集索引但是因Status无索引可用只能进行表扫描)
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = 43659 OR [ Status ] = 1
-- ================================================================================
-- 三种联结(嵌套循环、合并联结、哈稀联结),所占用的资源依次增加,SQL优先选用嵌套循环
-- ================================================================================
-- 1、嵌套循环(外表、内表),记录少的表作为外表,在内表中根据每个联结字段的值进行
-- 循环,只有在外表数据量较少时使用
SELECT *
FROM Sales.SalesOrderDetail D JOIN Sales.SalesOrderHeader O
ON D.SalesOrderID = O.SalesOrderID
WHERE O.SalesOrderID BETWEEN 43659 AND 43665
-- 2、合并联结的算法前提是字段已排序
SELECT *
FROM Sales.SalesOrderDetail D JOIN Sales.SalesOrderHeader O
ON D.SalesOrderID = O.SalesOrderID
WHERE O.SalesOrderID BETWEEN 43659 AND 54000
-- 3、哈稀联结(在要联结的字段上无可用索引时使用此连接算法)
IF OBJECT_ID (N ' SumPrice ' ,N ' U ' ) IS NOT NULL
DROP TABLE SumPrice
GO
SELECT O.SalesOrderID, YEAR (O.OrderDate) + MONTH (O.OrderDate) AS Date, CAST ( SUM (D.OrderQty * D.UnitPrice * ( 1 - D.UnitPriceDiscount)) AS decimal ( 18 , 2 )) AS Price
INTO SumPrice
FROM
Sales.SalesOrderDetail D
JOIN
Sales.SalesOrderHeader O
ON D.SalesOrderID = O.SalesOrderID
GROUP BY O.SalesOrderID,O.OrderDate
GO
SELECT O.SalesOrderID,O.
FROM SumPrice D JOIN Sales.SalesOrderHeader O
ON D.SalesOrderID = O.SalesOrderID
WHERE O.SalesOrderID BETWEEN 43659 AND 54000
-- ==========================================================================================
-- 代替LEFT JOIN的方法
-- 为了防止在查询中出现LEFT操作,在设计数据库时应尽量避免关联表中不一致的情况出现
-- 比如产品的类别和具体产品的对应关系中,可在类别加入一个无类别的记录以对应不知是何类别的产品
-- ==========================================================================================
BEGIN TRANSACTION
GO
DROP INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
(
CustomerID
) WITH ( STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
GO
COMMIT
DBCC FLUSHPROCINDB( 8 );
GO
DBCC DROPCLEANBUFFERS;
GO
-- 1、查询所有客户的总订单数量
SELECT C.CustomerID, COALESCE ( SUM (OrderQty), 0 ) AS Quantity
FROM Sales.Customer C LEFT JOIN
( Sales.SalesOrderHeader O
JOIN Sales.SalesOrderDetail D ON O.SalesOrderID = D.SalesOrderID
) ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID
GO
-- 2、替代的查询方式
DECLARE @CustomerInfo table
(
CustomerID int not null ,
Quantity smallint not null
)
INSERT INTO @CustomerInfo
SELECT CustomerID, 0
FROM Sales.Customer;
WITH SumQuantity AS
(
SELECT O.CustomerID, COALESCE ( SUM (OrderQty), 0 ) AS Quantity
FROM Sales.SalesOrderHeader O
JOIN Sales.SalesOrderDetail D ON O.SalesOrderID = D.SalesOrderID
GROUP BY O.CustomerID
)
UPDATE @CustomerInfo
SET C.Quantity = S.Quantity
FROM @CustomerInfo C JOIN SumQuantity S
ON S.CustomerID = C.CustomerID;
SELECT * FROM @CustomerInfo ;
GO
-- ==========================================================================================
-- 控制表之间的联结
-- ==========================================================================================
USE Northwind;
GO
SELECT DISTINCT C.CompanyName AS customer, S.CompanyName AS supplier
FROM dbo.Customers AS C
LEFT OUTER JOIN dbo.Orders AS O
ON O.CustomerID = C.CustomerID
LEFT OUTER JOIN dbo. [ Order Details ] AS OD
ON OD.OrderID = O.OrderID
LEFT OUTER JOIN dbo.Products AS P
ON P.ProductID = OD.ProductID
LEFT OUTER JOIN dbo.Suppliers AS S
ON S.SupplierID = P.SupplierID
-- OPTION(FORCE ORDER);
-- 上面的查询过多的使用外联结,可使用下面的方式替换
SELECT DISTINCT C.CompanyName AS customer, S.CompanyName AS supplier
FROM dbo.Orders AS O
JOIN dbo. [ Order Details ] AS OD
ON OD.OrderID = O.OrderID
JOIN dbo.Products AS P
ON P.ProductID = OD.ProductID
JOIN dbo.Suppliers AS S
ON S.SupplierID = P.SupplierID
RIGHT OUTER JOIN dbo.Customers AS C
ON O.CustomerID = C.CustomerID;
SELECT DISTINCT C.CompanyName AS customer, S.CompanyName AS supplier
FROM dbo.Customers AS C
LEFT OUTER JOIN
(dbo.Orders AS O
JOIN dbo. [ Order Details ] AS OD
ON OD.OrderID = O.OrderID
JOIN dbo.Products AS P
ON P.ProductID = OD.ProductID
JOIN dbo.Suppliers AS S
ON S.SupplierID = P.SupplierID)
ON O.CustomerID = C.CustomerID;
-- ============================================================
-- 存储过程相关
-- ============================================================
USE Northwind;
GO
-- ***********************************************************
-- 1、计划缓存重用造成的无效查询计划
-- 造成上述问题的原因是因为SQLServer为了节省编译的开销会优先考虑使用缓存的计划
-- 2000下只能使用过程级重新编译,2005下提供了语句级重新编译的特性。从而避免了编译
-- 整个过程的开销
IF OBJECT_ID (N ' dbo.usp_GetOrders ' ,N ' P ' ) IS NOT NULL
DROP PROC dbo.usp_GetOrders;
GO
CREATE PROC dbo.usp_GetOrders
@odate AS DATETIME
AS
SELECT c.CustomerID, c.CompanyName,
o.EmployeeID, o.OrderID, o.OrderDate
FROM dbo.Orders o
join dbo.Customers c
on o.CustomerID = c.CustomerID
WHERE OrderDate >= @odate ;
-- OPTION(RECOMPILE);
GO
-- 选择度高的测试
EXEC dbo.usp_GetOrders ' 19980506 ' ;
-- 选择度低的测试
EXEC dbo.usp_GetOrders ' 19960101 ' ;
-- 查看缓存计划
SELECT *
FROM sys.syscacheobjects
WHERE sql NOT LIKE ' %cache% '
AND sql LIKE ' %usp_GetOrders% ' ;
-- 清除缓存计划
DBCC FREEPROCCACHE;
GO
-- ***********************************************************
-- 2、参数嗅探测
-- 以当前日期插入一条记录,高选择度查询
INSERT INTO dbo.Orders(OrderDate, CustomerID, EmployeeID) VALUES ( GETDATE (), N ' ALFKI ' , 1 );
GO
ALTER PROC dbo.usp_GetOrders
@d AS INT = 0
AS
DECLARE @odate AS DATETIME ;
SET @odate = DATEADD ( day , - @d , CONVERT ( VARCHAR ( 8 ), GETDATE (), 112 ));
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= @odate ;
GO
-- 查看表dbo.Orders空间情况
SELECT * FROM
sys.dm_db_index_physical_stats( DB_ID (N ' Northwind ' ), OBJECT_ID (N ' dbo.Orders ' ), NULL , NULL , ' DETAILED ' );
-- 此时虽然选择度高,但是因为在过程编译时优化器无法根据参数进行最优化
EXEC dbo.usp_GetOrders;
-- 使用聚集索引代替非聚集索引进行查询的参数嗅探测试
USE AdventureWorks;
GO
IF OBJECT_ID (N ' dbo.GetCutomerOrderByDate ' ,N ' P ' ) IS NOT NULL
DROP PROC dbo.GetCutomerOrderByDate;
GO
CREATE PROCEDURE dbo.GetCutomerOrderByDate
@start datetime
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @min int ,
@max int
SELECT @min = MIN (SalesOrderID), @max = MAX (SalesOrderID)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @start
SELECT SalesOrderID,OrderDate,Status,CustomerID,TaxAmt,TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderID BETWEEN @min AND @max
END
GO
DBCC FREEPROCCACHE;
GO
-- 低选择度测试
EXEC dbo.GetCutomerOrderByDate ' 2004-5-01 '
-- 高选择度测试
EXEC dbo.GetCutomerOrderByDate ' 2004-7-26 '
-- 缓存计划
SELECT *
FROM sys.syscacheobjects
WHERE sql NOT LIKE ' %cache% '
AND sql LIKE ' %GetCutomerOrderByDate% ' ;
-- ***********************************************************
-- 3、Execute/sp_executesql
USE Northwind;
GO
DBCC FREEPROCCACHE;
GO
--
DECLARE @i AS INT ;
DECLARE @sql AS VARCHAR ( 52 );
SET @i = 10248 ;
WHILE @i < 10252
BEGIN
SET @sql = ' SELECT * FROM dbo.Orders WHERE OrderID = '
+ CAST ( @i AS VARCHAR ( 10 )) + N ' ; ' ;
EXEC ( @sql );
SET @i = @i + 1
END
-- 查看缓存计划,可以看到除了参数化计划外还为每个不同的值保存一个缓存计划
SELECT cacheobjtype, objtype, usecounts, sql
FROM sys.syscacheobjects
WHERE sql NOT LIKE ' %cache% '
AND sql NOT LIKE ' %sys.% ' ;
-- 存储过程中调用同样存在上面的问题,为每个参数分配一个缓存计划
IF OBJECT_ID (N ' dbo.GetOrderByID ' ,N ' P ' ) IS NOT NULL
DROP PROC dbo.GetOrderByID;
GO
CREATE PROCEDURE dbo.GetOrderByID
@i AS INT
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @sql AS VARCHAR ( 52 );
SET @sql = ' SELECT * FROM dbo.Orders WHERE OrderID = '
+ CAST ( @i AS VARCHAR ( 10 )) + N ' ; ' ;
EXEC ( @sql );
END
GO
DECLARE @i AS INT ;
SET @i = 10248 ;
WHILE @i < 10252
BEGIN
EXEC dbo.GetOrderByID @i
SET @i = @i + 1
END
GO
ALTER PROCEDURE dbo.GetOrderByID
@i AS INT
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @sql AS NVARCHAR ( 52 );
SET @sql = N ' SELECT * FROM dbo.Orders WHERE OrderID = @ID '
EXEC sp_executesql
@stmt = @sql ,
@params = N ' @ID as int ' ,
@ID = @i
END
GO
DBCC FREEPROCCACHE;
GO
DECLARE @i AS INT ;
SET @i = 10248 ;
WHILE @i < 10252
BEGIN
EXEC dbo.GetOrderByID @i
SET @i = @i + 1
END
-- 动态语句的选择度测试
IF OBJECT_ID (N ' dbo.usp_GetOrders ' ,N ' P ' ) IS NOT NULL
DROP PROC dbo.usp_GetOrders;
GO
CREATE PROC dbo.usp_GetOrders
@odate AS DATETIME
AS
SET NOCOUNT ON ;
DECLARE @sql AS NVARCHAR ( 100 );
SET @sql = N ' SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= @odate OPTION(RECOMPILE); '
EXEC sp_executesql
@stmt = @sql ,
@params = N ' @odate as datetime ' ,
@odate = @odate
GO
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= ' 19980506 ' option (recompile)
-- 选择度高的测试
EXEC dbo.usp_GetOrders ' 19980506 ' ;
-- 选择度低的测试
EXEC dbo.usp_GetOrders ' 19960101 ' ;
-- 查看缓存计划
SELECT *
FROM sys.syscacheobjects
WHERE sql NOT LIKE ' %cache% '
AND sql LIKE ' %usp_GetOrders% ' ;
-- 清除缓存计划
DBCC FREEPROCCACHE;
GO
-- 动态语句EXEC
IF OBJECT_ID (N ' dbo.usp_GetOrders ' ,N ' P ' ) IS NOT NULL
DROP PROC dbo.usp_GetOrders;
GO
CREATE PROC dbo.usp_GetOrders
@odate AS varchar ( 10 )
AS
SET NOCOUNT ON ;
DECLARE @sql AS VARCHAR ( 120 );
SET @sql = ' SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= ''' + @odate + ''' ; '
print @sql
EXEC ( @sql )
-- OPTION(RECOMPILE);
GO
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= ' 19980506 '
select convert ( datetime , ' 19980506 ' )
-- 选择度高的测试
EXEC dbo.usp_GetOrders ' 19980506 ' ;
-- 选择度低的测试
EXEC dbo.usp_GetOrders ' 19960101 ' ;
-- 查看缓存计划
SELECT *
FROM sys.syscacheobjects
WHERE sql NOT LIKE ' %cache% '
AND sql LIKE ' %usp_GetOrders% ' ;
-- 清除缓存计划
DBCC FREEPROCCACHE;
GO
-- ============================================================
-- 日志相关
-- ============================================================
DBCC LOGINFO;
GO
DBCC SQLPERF(LOGSPACE);
GO
-- 2005
WHILE 1 = 1
BEGIN
UPDATE TOP ( 5000 ) dbo.A
SET CustomerID = N ' ABCDE '
WHERE CustomerID = N ' OLDWO ' ;
IF @@rowcount < 5000 BREAK ;
END
GO
-- 2000
SET ROWCOUNT 5000 ;
WHILE 1 = 1
BEGIN
DELETE FROM dbo.LargeOrders
WHERE OrderDate < ' 19970101 ' ;
IF @@rowcount < 5000 BREAK ;
END
SET ROWCOUNT 0 ;