Extremely Slow Performing SELECT

Question

I have a table with with 7,526,511 records with the following definition:

/****** Object:  Table [dbo].[LogSearches]    Script Date: 12/07/2009 09:23:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[LogSearches](
    [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [Acct_ID] [int] NULL,
    [RecordCount] [int] NOT NULL,
    [PageNumber] [int] NOT NULL,
    [Site_ID] [int] NOT NULL,
    [SearchAPI] [bit] NOT NULL,
    [FormSearch] [bit] NOT NULL,
    [IPAddress] [varchar](15) NOT NULL,
    [Domain] [nvarchar](150) NOT NULL,
    [ScriptName] [nvarchar](500) NOT NULL,
    [QueryString] [varchar](max) NULL,
    [Referer] [nvarchar](1024) NOT NULL,
    [SearchString] [nvarchar](max) NOT NULL,
    [UserAgent] [nvarchar](2048) NULL,
    [Processed] [datetime] NOT NULL,
    [Created] [datetime] NOT NULL,
    [IntegerIP] [int] NULL,
 CONSTRAINT [PK_LogSearches] 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]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[LogSearches] ADD  CONSTRAINT [DF_LogSearches_Processed]  DEFAULT (getdate()) FOR [Processed]
GO

ALTER TABLE [dbo].[LogSearches] ADD  CONSTRAINT [DF_LogSearches_Created]  DEFAULT (getdate()) FOR [Created]
GO

The execution plan looks like this:

StmtText                                                                                 StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                 DefinedValues                                                                                                                                                                                                                                                    EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                                                                                                                                                                       Warnings Type                                                             Parallel EstimateExecutions
---------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ -------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
SELECT TOP 1 * FROM LogSearches                                                          1           1           0           NULL                           NULL                           1                                                        NULL                                                                                                                                                                                                                                                             1             NULL          NULL          NULL        0.0032832        NULL                                                                                                                                                                                                                                                             NULL     SELECT                                                           0        NULL
  |--Top(TOP EXPRESSION:((1)))                                                           1           2           1           Top                            Top                            TOP EXPRESSION:((1))                                     NULL                                                                                                                                                                                                                                                             1             0             1E-07         11848       0.0032832        [LOALogs].[dbo].[LogSearches].[ID], [LOALogs].[dbo].[LogSearches].[Acct_ID], [LOALogs].[dbo].[LogSearches].[RecordCount], [LOALogs].[dbo].[LogSearches].[PageNumber], [LOALogs].[dbo].[LogSearches].[Site_ID], [LOALogs].[dbo].[LogSearches].[SearchAPI], [LOALo NULL     PLAN_ROW                                                         0        1
       |--Clustered Index Scan(OBJECT:([LOALogs].[dbo].[LogSearches].[PK_LogSearches]))  1           3           2           Clustered Index Scan           Clustered Index Scan           OBJECT:([LOALogs].[dbo].[LogSearches].[PK_LogSearches])  [LOALogs].[dbo].[LogSearches].[ID], [LOALogs].[dbo].[LogSearches].[Acct_ID], [LOALogs].[dbo].[LogSearches].[RecordCount], [LOALogs].[dbo].[LogSearches].[PageNumber], [LOALogs].[dbo].[LogSearches].[Site_ID], [LOALogs].[dbo].[LogSearches].[SearchAPI], [LOALo 1             2956.71       8.279319      11848       0.0032831        [LOALogs].[dbo].[LogSearches].[ID], [LOALogs].[dbo].[LogSearches].[Acct_ID], [LOALogs].[dbo].[LogSearches].[RecordCount], [LOALogs].[dbo].[LogSearches].[PageNumber], [LOALogs].[dbo].[LogSearches].[Site_ID], [LOALogs].[dbo].[LogSearches].[SearchAPI], [LOALo NULL     PLAN_ROW                                                         0        1

(3 row(s) affected)

When I run the query it doesn't finish in any sort of reasonable time frame. I've let the query run for over 5 minutes, and it still didn't return the single row I asked for. This type of slow SELECT performance is having other effects on the database, such as making it difficult to get rid of rows we no longer need.

Do you have an idea where my bottleneck might be? The 98 gig database and its logs are running on SQL Server 2008 on a 4-disk RAID 10 with over 100 gigs of free space on the drives.

Answer 1

Have you checked to see if you are having a blocking issue?

Answer 2

Would it be useful to create another table with required structure and copy/"pump" data there, then delete old table and rename new one? You may need to do it in "batches" for specific ID range:

INSERT INTO LogSearches_new ... SELECT * FROM LogSearches WHERE ID BETWEEN 1 AND 999999