Slow SQL performance

Question

I have a request as follows:

SELECT COUNT(Id) FROM Table 

The table contains 33 million records - it contains the primary key in Id and other indexes.

The request takes 30 seconds.

The actual execution plan shows that it uses clustered index scanning.

We analyzed the table and found that it was not fragmented using the first query shown in this link: http://sqlserverpedia.com/wiki/Index_Maintenance .

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

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 that I noticed is that the database will grow in units of 1 MB.

This is a living system, so we limited ourselves to what we can play with - any ideas?

UPDATE:

OK - we improved performance in the question of interest by adding new non-clustered indexes to the corresponding columns so that it does not become critical.

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

We all think that this is due to the fact that Autogrowth is set to 1 MB, and not to a larger number, but was surprised that it has this effect. Could MDF fragmentation on disk be a possible cause?

Answer 1

Is this a frequently read / pasted / updated table? Is there an update / insert operation at the same time as your choice?

My guess is a delay due to competition.

I can start counting 189 meter lines in 17 seconds on my dev server, but nothing else hit this table.

If you are not too worried about competition or absolute accuracy you can do:

exec sp_spaceused 'MyTableName' which will give the score based on metadata.

If you need a more accurate account, but not necessarily, if it reflects the simultaneous activity of DELETE or INSERT , you can make your current request with the NOLOCK prompt:

SELECT COUNT(id) FROM MyTable WITH (NOLOCK) , which will not get row level locks for your query and should work faster.

Answer 2

Thoughts:

• Use SELECT COUNT(*) , which is suitable for "row count" (according to ANSI SQL). Even if the identifier is PK and therefore is not NULL, SQL Server will read the ID. Not a line.

• If you can live with an approximate number of samples, use sys.dm_db_partition_stats. See My answer here: The fastest way to count the exact number of rows in a very large table?

• If you can live with dirty reads, use WITH (NOLOCK)

Answer 3

 use [DatabaseName] select tbl.name, dd.rows from sysindexes dd inner join sysobjects tbl on dd.id = tbl.id where dd.indid < 2 and tbl.xtype = 'U' select sum(dd.rows)from sysindexes dd inner join sysobjects tbl on dd.id = tbl.id where dd.indid < 2 and tbl.xtype = 'U' 

Using these queries, you can get the score of all tables within 0-5 seconds

use the where clause as per your requirement .....

Answer 4

Another idea: when files grow with 1 MB parts, they can be fragmented in the file system. You do not see this with SQL, you see this with the disk defragmenter.