Slow request with stored procedure when requesting new records

Question

Sorry for the wall with the text below ...

I have a slightly strange problem here. I have a fairly large table that stores message tracking log data from Exchange 2007 over the last couple of days. The number of entries is millions (about 10-12 million), and every 30 minutes I enter any new logs from all of our transport servers through the scheduled PowerShell tasks.

Once a day, I run a maintenance task to clear any logs older than one day so that the table does not get too large, although I would like the logs to be a little longer if I could.

The table is called MessageTracking and has a primary key, which is an int IDENTITY column, [MessagelogID] , which is incremented by 1 each record.

The [date-time] column has a non-clustered index for [date-time] .

The Sender and Recipient fields have a full text index.

Users can search a table through a web page with an interface that I wrote in C # Asp.net. The page allows a very simple search with 4 search fields:

• Start DateTime
• End datetime
• Sender
• Recipient

This passes the request to the stored procedure, which actually returns the records. the stored procedure is called GetMessageTracking .

Now about my strange problem. This query returns results in 0 seconds, fast as lightning:

USE [DATABASENAME] GO DECLARE @return_value int EXEC @return_value = [dbo].[GetMessageTracking] @maximumRows = 20, @startRowIndex = 0, @sortExpression = N'[date-time]', @SearchStartDate = N'2012-03-27 13:51', @SearchEndDate = N'2012-03-27 20:09', @SearchSender = N' [e-mail] ', @SearchRecipient = N'Default' SELECT 'Return Value' = @return_value GO 

If I changed the @SearchStartDate parameter an hour in advance, that is, N'2012-03-27 14:51 ' , then it will not be completed in a very long time.

I can assume that it has serious problems with my datetime index, since the full text directory is usually inactive. One of the problems I have is that I literally insert thousands of records per hour, and the index ( IX_DateTime ) is fragmented very quickly, however I cannot think of a great way to stop this.

So my questions are actually doubled:

1) How can I find out what causes this problem when executing queries when searching for new records?

2) Any hints for indexing when a large number of insertions occur?

I thought that maybe these were the execution plans causing this strange behavior, but no, that seems OK. I tried adding the WITH RECOMPILE parameter to the queries, and that didn't make any difference. I also cleared the execution plan cache.

As far as I am sure, my problems are completely related to my indexing.

Thanks!

Answer 1

Using recompilation was a good first step. Thus, we can eliminate the possibility that the parameter is sniffing if there is a problem.

Please post a screenshot of the execution plan. I think the problem will become clear. I assume this is a statistic problem. the query optimizer considers that there are no rows with DateTime> = N'2012-03-27 14:51 'and because of this selects the wrong plan. Try running sp_updatestats.

As for number 2 of your questions: set the index value to a value in the range of 50-80.

Answer 2

Due to the large number of INSERT and DELETE, it is possible that you might have fragmented indexes on your table. The following script (taken from SQL Authority ) will provide you with fragmentation information

 select object_name(i.object_id) as table_name, indexstats.avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'detailed') indexstats inner join sys.indexes i on i.object_id = indexstats.object_id and i.index_id = indexstats.index_id where indexstats.avg_fragmentation_in_percent > 20 

If you find a large amount of fragmentation, you can rebuild or reorganize the indexes. SQL Authority has another article that contains the following information.

Rebuild Index: This process reduces the existing index and recreates the index.

 alter index all on schema.table_name rebuild 

Index Reorganize: This process physically reorganizes the leaf nodes of the index.

 alter index all on schema.table_name reorganize 

Answer 3

I think that you have identified your own problem - fragmentation of indexes with a lot of letters that you make.

You can help solve this problem by adjusting the padding (disk space) allocated for table and index growth.