I have a query as follows;

```
 SELECT COUNT(Id) FROM Table

```

The table contains 33 million records - it contains a primary key on Id and no other indices.

The query takes 30 seconds.

The actual execution plan shows it uses a clustered index scan.

We have analysed the table and found it isn't fragmented using the first query shown in this link: <http://sqlserverpedia.com/wiki/Index_Maintenance>.

Any ideas as to why this query is so slow and how to fix it.

The Table Definition:

```
 CREATE TABLE [dbo].[DbConversation](
[ConversationID] [int] IDENTITY(1,1) NOT NULL,
[ConversationGroupID] [int] NOT NULL,
[InsideIP] [uniqueidentifier] NOT NULL,
[OutsideIP] [uniqueidentifier] NOT NULL,
[ServerPort] [int] NOT NULL,
[BytesOutbound] [bigint] NOT NULL,
[BytesInbound] [bigint] NOT NULL,
[ServerOutside] [bit] NOT NULL,
[LastFlowTime] [datetime] NOT NULL,
[LastClientPort] [int] NOT NULL,
[Protocol] [tinyint] NOT NULL,
[TypeOfService] [tinyint] NOT NULL,
  CONSTRAINT [PK_Conversation_1] PRIMARY KEY CLUSTERED 
 (
[ConversationID] 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

```

One thing I have noticed is the database is set to grow in 1Mb chunks.

It's a live system so we restricted in what we can play with - any ideas?

UPDATE:

OK - we've improved performance in the actual query of interest by adding new non-clustered indices on appropriate columns so it's not a critical issue anymore.

`SELECT COUNT` is still slow though - tried it with NOLOCK hints - no difference.

We're all thinking it's something to do with the Autogrowth set to 1Mb rather than a larger number, but surprised it has this effect. Can MDF fragmentation on the disk be a possible cause?