0

One index of one my databases has the strange behaviour of getting slower after time goes by.

Even though my maintenance plan includes a 'Rebuild index' step on all user databases. After a while it gets so slow that my entire application/server grids to a halt.

But when I do a manual rebuild on the particular index, the query time is brought down from minutes to half a second.

Why does the 'rebuild index' step of the maintenance plan seem to skip this index, and why does it work manually? (the maintenance plan runs correctly without errors, every night)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Erik
  • 417
  • 4
  • 14
  • What code does the maintenance job run to rebuild indexes on all databases? – Ruslan May 09 '14 at 05:33
  • Have you run a trace on the system? Checked the error logs? – Namphibian May 09 '14 at 06:06
  • ALTER INDEX [NonClusteredIndex-20140414-121115] ON [dbo].[Product] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) At least that's what the maintenance plan designer tells me. – Erik May 09 '14 at 06:10

1 Answers1

0

Fragmentation can make a big difference in indexing. You may need to drop the indexes altogether and re-create them again.

Utrolig
  • 251
  • 2
  • 13
  • If I check the fragmentation of the index when it's slow, it is practically 0 (0.86%, its a pretty static table). – Erik May 09 '14 at 08:25
  • What kind of indexing is in place? If it's columnstore and the server is really busy it could be the IO. And do you use any INCLUDES indexing? Can you please post the table structure with indexing in place? It may help to see it. – Utrolig May 09 '14 at 19:00