性能调优

Question

我有一个性能问题。我的经理让我调优select语句。

我们有一张桌子

SELECT [AcctDetailReportId]
      ,[WorkOrderEneteredDate]
      ,[LocationName]
      ,[LocationNumber]
      ,[District]
      ,[CostCenter]
      ,[GLCode]
      ,[WorkType]
      ,[RequestType]
      ,[RequestCode]
      ,[ServiceLocation]
      ,[Cause]
      ,[Remedy]
      ,[RequestDescription]
      ,[CreatedBy]
      ,[Priority]
      ,[WorkOrderNumber]
      ,[Status]
      ,[DNE]
      ,[InvoiceNumber]
      ,[VendorCode]
      ,[VendorName]
      ,[Quote1]
      ,[Quote2]
      ,[Invoiceid]
      ,[InvoiceSubmittedDate]
      ,[WorkComplete]
      ,[TotalLaborCost]
      ,[TotalMaterialCost]
      ,[SalesTax]
      ,[InvoiceTotal]
      ,[WarrantyExpirationDate]
      ,[UnderWarranty]
      ,[MallName]
      --,[AddressID]
      --,[CommunicationID]
      --,[ContactID]
      --,[StateID]
      --,[CountryID]
      --,[LanguageID]
      --,[AddressTypeID]
      ,[Line1]
      ,[Line2]
      ,[City]
      ,[Province]
      ,[Region]
      ,[ZipPostalCode]
      --,[DeactivateDateTime]
      --,[DeactivateUser]
      ,[CreateDateTime]
      ,[CreateUser]
      --,[PreviousRecordID]
      ,[LocationState]
      ,[CheckNumber]
      ,[CheckDate]

 FROM [Darden].[dbo].[RPT_AccountDetailReport]
GO"

它包含29000条记录。使用聚集索引扫描检索数据大约需要2分钟。

表只有一个聚集索引。

要求是获取表中的所有记录和所有列。但在更短的时间内..

有没有人能帮我...

谢谢,

卡尔蒂克

Answer 1

您是否重新组织/重建了索引下面的脚本将为i.name = '%what you put here%‘的表重新编写/创建所有索引。下面有一堆我过去用过的代码行。

SELECT
--stats.object_id AS objectid,
--QUOTENAME(s.name) AS schemaname,
--stats.index_id AS indexid,
i.name AS index_name,
--stats.partition_number AS partitionnum,
stats.avg_fragmentation_in_percent AS frag,
stats.page_count,
QUOTENAME(o.name) AS objectname,
CASE  
      when stats.avg_fragmentation_in_percent < 30 then 'Reorganize'
      when stats.avg_fragmentation_in_percent > 30 then 'Rebuild'
END AS 'action_to_take',
CASE  
      when stats.avg_fragmentation_in_percent < 30 then 'ALTER INDEX '+i.name+ ' ON ' +DB_NAME()+'.'+QUOTENAME(s.name)+'.'+QUOTENAME(o.name)+' REORGANIZE;' 
      when stats.avg_fragmentation_in_percent > 30 then 'ALTER INDEX '+i.name+ ' ON ' +DB_NAME()+'.'+QUOTENAME(s.name)+'.'+QUOTENAME(o.name)+' REBUILD;' 
END AS 'Statement'
FROM 
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, NULL) as stats,
sys.objects AS o,
sys.schemas AS s,
sys.indexes AS i
WHERE o.object_id = stats.object_id
AND s.schema_id = o.schema_id       
AND i.object_id = stats.object_id
AND i.index_id = stats.index_id
AND i.name is not null and i.name not like '%missing index%'
AND stats.avg_fragmentation_in_percent >= 10.0 
--AND stats.page_count >= 5000
--AND stats.index_id > 0
--and i.name like '%880%'
ORDER BY action_to_take,stats.avg_fragmentation_in_percent desc,stats.page_count desc

或者，您可以单击Display estimated Execution plan & SMS将生成任何需要的索引。

HTH，LarryR...