SQL查询加速/优化

Question

我正在使用SQLServer2008R2。我尝试用不同的方法改进以下查询。

Method 1:
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT count(*)  FROM [prglog].dbo.[errorlog] WHERE (( [prglog].dbo.[errorlog].[Errordescription] LIKE '%General network error%'  ) 
AND [date]>=dateadd(d,-7,cast(getdate()as date)))  

Method 2:
 DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT count(*)  FROM [prglog].dbo.[errorlog] WHERE (( [prglog].dbo.[errorlog].[Errordescription] LIKE '%General network error%'  )
 AND [prglog].dbo.[errorlog].[Date] BETWEEN dateadd(d,-7,cast(getdate()as date)) and cast(getdate()as date) )  

Method 3:
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT count(*) FROM [prglog].dbo.[errorlog] WHERE (( [prglog].dbo.[errorlog].[Errordescription] LIKE '%General network error%'   ) 
AND id>=(SELECT MIN([errorlog].[id]) FROM [prglog].dbo.[errorlog] WHERE ( [prglog].dbo.[errorlog].[Date] >= dateadd(d,-7,cast(getdate()as date)) )) )  

Method 4:
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT count(*) FROM [prglog].dbo.[errorlog] WHERE (( [prglog].dbo.[errorlog].[Errordescription] LIKE '%General network error%'   ) 
AND id>=(SELECT MIN([errorlog].[id]) FROM [prglog].dbo.[errorlog] WHERE ([prglog].dbo.[errorlog].[Date] BETWEEN dateadd(d,-7,cast(getdate()as date)) and cast(getdate()as date) ) ) )

Method 5:
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT count(*) FROM (SELECT  * FROM [prglog].dbo.[errorlog] WHERE  [prglog].dbo.[errorlog].[Date] >= dateadd(d,-7,cast(getdate()as date)) )tbl WHERE ( tbl.[Errordescription] LIKE '%General network error%') 

错误日志表包含超过150000行。当我检查客户端统计中的查询结果时，几乎是same.Is，还有其他方法来改进查询吗？

表的结构是：

USE [prglog]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[errorlog](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [PCname] [varchar](50) NULL,
    [Username] [varchar](50) NULL,
    [Date] [date] NULL,
    [Time] [varchar](50) NULL,
    [Errordescription] [varchar](max) NULL,
    [LineNo] [varchar](max) NULL,
    [Errorno] [varchar](50) NULL,
    [Procedure] [varchar](255) NULL,
    [Formname] [varchar](50) NULL,
    [Productname] [varchar](50) NULL,
    [Commandstring] [varchar](max) NULL,
    [Exename] [varchar](50) NULL,
    [ExePath] [varchar](255) NULL,
    [ErrorDetails] [xml] NULL,
    [F6 Columns] [varchar](255) NULL,
    [Stage details #] [varchar](255) NULL,
    [F8 Columns] [varchar](8000) NULL,
    [F8 Given By] [varchar](255) NULL,
    [Pro Remarks] [varchar](8000) NULL,
    [Final F6] [bit] NULL,
    [DBname] [varchar](50) NULL,
    [Tablename] [varchar](50) NULL,
    [Serious Error] [tinyint] NULL,
    [Stage #] [tinyint] NULL,
    [Process Id] [int] NULL,
 CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[errorlog] ADD  CONSTRAINT [DF_errorlog_Final F6]  DEFAULT ((0)) FOR [Final F6]
GO
ALTER TABLE [dbo].[errorlog] ADD  CONSTRAINT [DF_prglog_Serious Error]  DEFAULT ((0)) FOR [Serious Error]
GO
ALTER TABLE [dbo].[errorlog] ADD  CONSTRAINT [DF_errorlog_Stage #]  DEFAULT ((1)) FOR [Stage #]
GO
USE [prglog]
GO
CREATE NONCLUSTERED INDEX [Date] ON [dbo].[errorlog] 
(
    [Date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE PRIMARY XML INDEX [ErrorDetails] ON [dbo].[errorlog] 
(
    [ErrorDetails]
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO
CREATE NONCLUSTERED INDEX [Final F6] ON [dbo].[errorlog] 
(
    [Final F6] ASC
)
WHERE ([Final F6]=(0))
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[errorlog] ADD  CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
USE [prglog]
GO
CREATE NONCLUSTERED INDEX [Procedure] ON [dbo].[errorlog] 
(
    [Procedure] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

有时，当我们检查客户端统计数据以进行查询的性能分析时，它会给出从试验到trial.How的不同结果，以检查查询的性能？

Answer 1

没有足够的代表评论，所以在这里张贴。执行计划可能有助于理解。

我想知道WHERE子句中的DATEADD函数是否干扰了SARGability？另外，如果大多数通过日期搜索的查询也通过错误归责进行搜索，反之亦然，我将测试在这些查询上创建覆盖指数。由于不了解此表的数据、exec计划或工作负载特性，很难说出总体效益。试着做这样的事情：

DECLARE @ThresholdDate DATE
SELECT @ThresholdDate = DATEADD(d,-7,cast(getdate()as date))

SELECT count(*)  
FROM [prglog].dbo.[errorlog] 
WHERE  [date]>=@ThresholdDate  
   AND [prglog].dbo.[errorlog].[Errordescription] LIKE '%General network error%'