ch 28 - advanced performance.sql

《SQLServer2000宝典》—包括本书中的所有代码

-----------------------------------------------------------
-- SQL Server 2000 Bible 
-- Wiley Publishing  
-- Paul Nielsen

-- Chapter 29 - Advanced Performance

-----------------------------------------------------------
-----------------------------------------------------------
-- 

-- Index Selectivity
Use CHA2
exec sp_help Customer

DBCC Show_Statistics (Customer, IxCustomerName)


-- Index Tuning Wizard
USE OBXKites
SELECT LastName, FirstName, [Name]
  FROM dbo.Contact
    JOIN dbo.[Order] 
      ON Contact.ContactID = [Order].ContactID
    JOIN dbo.OrderDetail
      ON [Order].OrderID = OrderDetail.OrderID
    JOIN dbo.Product
      ON OrderDetail.ProductID = Product.ProductID
    JOIN dbo.ProductCategory
      ON Product.ProductCategoryID = ProductCategory.ProductCategoryID
  WHERE ProductCategoryName = 'Kite'
  ORDER BY LastName, FirstName

-- The Index Tuning Wizard won't analyze a bacth with an error:
SELECT LastName, FirstName, [rName]
  FROM dbo.Contact
    JOIN dbo.[Order] 
      ON Contact.ContactID = [Order].ContactID
    JOIN dbo.OrderDetail
      ON [Order].OrderID = OrderDetail.OrderID
    JOIN dbo.Product
      ON OrderDetail.ProductID = Product.ProductID
    JOIN dbo.ProductCategory
      ON Product.ProductCategoryID = ProductCategory.ProductCategoryID
  WHERE ProductCategoryName = 'Kite'
  ORDER BY LastName, FirstName


-----------------------------------
-- Query Performance Indicators

USE OBXKites
Set statistics io on 
SELECT LastName + ' ' + FirstName as Customer, Product.[Name], Product.code
  FROM dbo.Contact
    JOIN dbo.[Order] 
      ON Contact.ContactID = [Order].ContactID
    JOIN dbo.OrderDetail
      ON [Order].OrderID = OrderDetail.OrderID
    JOIN dbo.Product
      ON OrderDetail.ProductID = Product.ProductID
  WHERE Product.Code = '1002'
  ORDER BY LastName, FirstName
Set statistics io off 


Set statistics time on 
SELECT LastName + ' ' + FirstName as Customer
  FROM dbo.Contact
  ORDER BY LastName, FirstName
Set statistics time off 

go
Set showplan_all on 
go
SELECT LastName
  FROM dbo.Contact
go
Set showplan_all off
go
-------------------------------
-- Procedure cache

DBCC FREEPROCCACHE

SELECT LastName
  FROM dbo.Contact

SELECT cacheobjtype, usecounts as Count,
    cast(C.sql as Char(35)) as StoredProcedure    
  FROM Master.dbo.syscacheobjects C
  JOIN  Master.dbo.sysdatabases D
    ON C.dbid = C.dbid
  WHERE D.Name = DB_Name() 
    AND ObjType = 'Adhoc'
  ORDER BY StoredProcedure