Question :
I have a SQL Server 2008 R2 database it is of size 13000.31 MB and my largest table has around half a million records. It is a database that I have bought. It already has indexes but when I run the database it is slow. It is slower than the database it replaced (from the same company) and should be an up grade. I have tried to rebuild the indexes as most of them are sitting at 99%. after the re-build they go to 0. but with in a few minutes they are back at 99%.
Why is this happening?
Answer :
I have tried to rebuild the indexes as most of them are sitting at 99%. after the re-build they go to 0. but with in a few minutes they are back at 99%.
I guess I know the answer here and have seen many times. Can you please check if auto shrink is on for the databases. You can use below query
select name,is_auto_shrink_on from sys.databases where name='db_name'
You must also check if there is any job running daily which is shrinking data file or may be shrinking whole database using DBCC SHRINKDATABASE command or may be using DBCC SHRINKFILE command.
Of course this could only be complete answer if you confirm whether any such activity is going on in database.