如何使用TOAD SQL optimize提升SQL server效能

作者： michael - 7月 06, 2018

隨著企業的成長，資料量會越來越龐大，當初開發的SQL，漸漸地開始面臨效能問題，如何有效的使用工具，變成一門很重要的課題。

很多人知道Quest 的TOAD產品，但是卻很少人知道TOAD for SQL server產品裡面有個很優秀的套件-SQL Optimizer，讓我們來開啟它強大的功能吧。

功能介紹1-Optimize SQL

這功能可以幫忙優化SQL。

輸入範例SQL，這範例重點在常見的Not in 用法，

範例使用SQL server 2016的範例資料庫AdventureWorks2016CTP3。

SELECT a1.[BusinessEntityID],a1.[NationalIDNumber],a1.[LoginID],a1.[OrganizationNode],a1.[OrganizationLevel],a1.[JobTitle],a1.[BirthDate]

,a1.[MaritalStatus],a1.[Gender],a1.[HireDate],a1.[SalariedFlag],a1.[VacationHours],a1.[SickLeaveHours],a1.[CurrentFlag],a1.[rowguid]

,a1.[ModifiedDate],a2.[DepartmentID],a2.[ShiftID],a2.[StartDate],a2.[EndDate],a2.[ModifiedDate],a3.[Name],a3.[GroupName],a3.[ModifiedDate]

FROM [AdventureWorks2016CTP3].[HumanResources].[Employee] a1 left outer join [AdventureWorks2016CTP3].[HumanResources].[EmployeeDepartmentHistory] a2

on a1.BusinessEntityID = a2.BusinessEntityID

left outer join [AdventureWorks2016CTP3].[HumanResources].[Department] a3

on a2.DepartmentID = a3.DepartmentID

where a1.[BusinessEntityID] not in (select BusinessEntityID from [AdventureWorks2016CTP3].[Sales].[SalesPerson])

有冠軍獎盃的是最佳解法

原始和優化SQL的執行效能比較。

分析並產出效能比較的報表，支援9種報表格式。

功能介紹2-Optimize Index

輸入測試用的SQL來源檔案，讓工具找出能增進效能的最佳索引。

SELECT TOP (1000) [CustomerID]

,[FirstName]

,[LastName]

,[SSN]

,[CreditCardNumber]

,[EmailAddress]

,[PhoneNumber]

,b.[Name] Territory

FROM [AdventureWorks2016CTP3].[Sales].[CustomerPII] a left outer join [AdventureWorks2016CTP3].[Sales].[SalesTerritory] b

on a.TerritoryID = b.TerritoryID

where a.[FirstName]+' '+a.[LastName] like 'Ja%Price'

and b.[Name] like '%a%'

工具已經幫忙找到4個可以改善效能的Index。

4個Index清單與執行計畫比較。

功能介紹3-Find SQL

在不影響資料庫下，分析資料庫曾有問題的SQL。

分析並歸類SQL。

列出有問題SQL。

功能介紹4-Scan SQL

掃描線上資料庫的SQL和Procedure、Trigger、 View等物件，並發現有問題SQL。

建立測試用的View。

create view v_order_detail as SELECT a.[SalesOrderID],a.[RevisionNumber],a.[OrderDate],a.[DueDate],a.[ShipDate],a.[Status],a.[OnlineOrderFlag],a.[SalesOrderNumber],a.[PurchaseOrderNumber] ,a.[AccountNumber],a.[CustomerID],a.[SalesPersonID],a.[TerritoryID],a.[BillToAddressID],a.[ShipToAddressID],a.[ShipMethodID],a.[CreditCardID] ,a.[CreditCardApprovalCode],a.[CurrencyRateID],a.[SubTotal],a.[TaxAmt],a.[Freight],a.[TotalDue],a.[Comment],b.[SalesOrderDetailID],b.[CarrierTrackingNumber],b.[OrderQty],b.[ProductID],b.[SpecialOfferID],b.[UnitPrice],b.[UnitPriceDiscount],b.[LineTotal],c.[FirstName],c.[LastName],c.[SSN],c.[CreditCardNumber],c.[EmailAddress],c.[PhoneNumber],d.[CardType],d.[CardNumber],d.[ExpMonth],d.[ExpYear],e.[Name],e.[ProductNumber],e.[MakeFlag],e.[FinishedGoodsFlag],e.[Color],e.[SafetyStockLevel],e.[ReorderPoint],e.[StandardCost],e.[ListPrice],e.[Size] ,e.[SizeUnitMeasureCode],e.[WeightUnitMeasureCode],e.[Weight],e.[DaysToManufacture],e.[ProductLine],e.[Class],e.[Style],e.[ProductSubcategoryID] ,e.[ProductModelID],e.[SellStartDate],e.[SellEndDate],e.[DiscontinuedDate],f.[CurrencyRateDate],f.[FromCurrencyCode],f.[ToCurrencyCode],f.[AverageRate],f.[EndOfDayRate],g.[Description],g.[DiscountPct],g.[Type],g.[Category],g.[StartDate],g.[EndDate],g.[MinQty],g.[MaxQty],h.[NationalIDNumber],h.[LoginID],h.[OrganizationNode],h.[OrganizationLevel],h.[JobTitle],h.[BirthDate],h.[MaritalStatus],h.[Gender],h.[HireDate],h.[SalariedFlag],h.[VacationHours],h.[SickLeaveHours],h.[CurrentFlag]

FROM [Sales].[SalesOrderHeader] a left outer join [Sales].[SalesOrderDetail] b on a.SalesOrderID = b.SalesOrderID

left outer join [Sales].[CustomerPII] c on a.CustomerID = c.CustomerID

left outer join [Sales].[CreditCard] d on a.CreditCardID = d.CreditCardID

left outer join [Production].[Product] e on b.ProductID = e.ProductID

left outer join [Sales].[CurrencyRate] f on a.CurrencyRateID = f.CurrencyRateID

left outer join [Sales].[SpecialOffer] g on b.SpecialOfferID = g.SpecialOfferID

left outer join [HumanResources].[Employee] h on a.SalesPersonID = h.BusinessEntityID

工具找到一個有問題View。