Take the 2-minute tour ×

I had asked a question on stackoverflow: Large SQL Server database timing out PHP web application

The problem was that the query was fast for when a previous date was chosen and slow for a more current date. We fixed it by recreating the index and hence it worked. For a while I thought the index might have gotten corrupted.

Today the issue occurred again (slow) and by recreating it again it ran fast. This was done on the date column which is of type datetime.

Is there a specific reason for this or is the SQL Server 2008 R2 corrupted?

share|improve this question
Could you post the code for the index? –  SchmitzIT Oct 12 '12 at 13:02
Indexes need to be maintained - do you have a maintenance plan in place that e.g. rebuilds the indexes and their statistics every night? –  marc_s Oct 12 '12 at 13:38
i always thought it auto updates. Guess i need to check that again. –  Shakir Oct 12 '12 at 14:59
add comment

2 Answers

up vote 1 down vote accepted

try using UPDATE STATISTICS (Transact-SQL)

Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

UPDATE STATISTICS table_or_indexed_view_name 
            { index_or_statistics__name }
    [    WITH 
            | SAMPLE number { PERCENT | ROWS } 
            | RESAMPLE 
            | <update_stats_stream_option> [ ,...n ]
        [ [ , ] [ ALL | COLUMNS | INDEX ] 
        [ [ , ] NORECOMPUTE ] 
    ] ;

<update_stats_stream_option> ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric_contant ]
share|improve this answer
ok i will do that and see if it works well –  Shakir Oct 12 '12 at 14:59
add comment

It certainly is not corrupted. This never happens. It might be fragmented or have a lot wasted space. This is normal. It happens because of certain write patterns.

Rebuilding is a good countermeasure. You can try reorganizing as an alternative.

share|improve this answer
add comment

Your Answer


