SQL Server 2008 start working 15 times faster after rebuild indexes-this is normal or mean there is problem?
Dear experts
I’m a C# database developer with SQL 2008. Before a month I installed SQL Server 2008 R2 over WIndows R2 over super micro MB – X10 and now I detect the follow problem:
One database start work VERY slowly – one complicated reports start require 25 sec, when I start manually my weekly rebuild procedure the report start to finished for 1.5 sec. The rebuild procedure is a SQL Agent task - rebuild index, shrink DB, update statistics over all databases /system DBs included/. I check its history – it is run once time a week. Before I manually start the Maitanance plan, I restart the SQL Servers – it do not help. So I’m sure – the maitanance plan fix the problem.
So now all is fine, but I start wonder – is it normal such behavior ? The DB is not heavy used, I’m afraid that this could mean HDD problem ? Is there somethink additional I should check ? I try in Windows Log, but find no problem…
I’m a C# database developer with SQL 2008. Before a month I installed SQL Server 2008 R2 over WIndows R2 over super micro MB – X10 and now I detect the follow problem:
One database start work VERY slowly – one complicated reports start require 25 sec, when I start manually my weekly rebuild procedure the report start to finished for 1.5 sec. The rebuild procedure is a SQL Agent task - rebuild index, shrink DB, update statistics over all databases /system DBs included/. I check its history – it is run once time a week. Before I manually start the Maitanance plan, I restart the SQL Servers – it do not help. So I’m sure – the maitanance plan fix the problem.
So now all is fine, but I start wonder – is it normal such behavior ? The DB is not heavy used, I’m afraid that this could mean HDD problem ? Is there somethink additional I should check ? I try in Windows Log, but find no problem…
ASKER
To angel
Thanks for your advice. In fact I’m not very aware of indexes and DB performance .. but I’ll become I guess …
My indexes do not have its own filegroup /you mean the index to be saved on a separated file, right?/
I have one clustrered index for every table with INT .It is on primary DB file
What is interesting is that I found the problem, when I get a backup of SQL Server /remote/ and restore it on my DEV computers and the report of my DEV computer is done for 1,5 sec. Then I release the problem is not because of database desing and becuase the report is over 5 mounths, so I start a Maitance plan Rebuild DB on remote server and all is going fine.
The procedure of restoreing a Database from full backup do not include internally reindexes, right? Then why the this fix the problem with DB speed??? My DEV PC is not so fast than the server ...
I get the feeling that the problem is solved actually NOT from the rebuild index, but from page reorder of data /this is included in my Rebuild DB Maitance/. What do you think, how can I make additional check what and why is happened? I’m sure, this will happened again, now the DB is not heavy used but with sure it will be in few months
Thanks for your advice. In fact I’m not very aware of indexes and DB performance .. but I’ll become I guess …
My indexes do not have its own filegroup /you mean the index to be saved on a separated file, right?/
I have one clustrered index for every table with INT .It is on primary DB file
What is interesting is that I found the problem, when I get a backup of SQL Server /remote/ and restore it on my DEV computers and the report of my DEV computer is done for 1,5 sec. Then I release the problem is not because of database desing and becuase the report is over 5 mounths, so I start a Maitance plan Rebuild DB on remote server and all is going fine.
The procedure of restoreing a Database from full backup do not include internally reindexes, right? Then why the this fix the problem with DB speed??? My DEV PC is not so fast than the server ...
I get the feeling that the problem is solved actually NOT from the rebuild index, but from page reorder of data /this is included in my Rebuild DB Maitance/. What do you think, how can I make additional check what and why is happened? I’m sure, this will happened again, now the DB is not heavy used but with sure it will be in few months
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To Scott
Thanks for your time.
For lucky next few mounts my DB will have 1-2 users for few hours per day, it size is now 100 MB so I have a time to test and to learn basic … and I’m surprised that with some small data wrong index could make 15 times faster operations …
I was thinking that shrink make reorder of the pages and improve the speed, look the pic. I’m wrong? In fact you are correct, the shrink command I actually use do not turn on this option, but I can copy/paste t-SQL from here. What do you think? In fact, I have no problem with HDD space at all , I need to use shrink only if it help for performance.
For now I should use Maintenance plan, the business is not so big to pay a DB administrator …
So my Maintenance plan will be:
1. Daily update statistics /only this/
2. One time in a week – Reorganize Index Task /instead of Shrink-this reorder index page for faster read, is that fine?/
3. Once time in a month – rebuild index Task
This look fine to you? Other recommendation?
P.S: I’m sure your notes are correct, anyway what I detect at 100 % that after apply this wrong maintenance plan – one report from 25 sec. start to calculated for 1.5 sec. Too much difference over too small data .. is it possible to be die to HDD bad sectors
Thanks for your time.
For lucky next few mounts my DB will have 1-2 users for few hours per day, it size is now 100 MB so I have a time to test and to learn basic … and I’m surprised that with some small data wrong index could make 15 times faster operations …
I was thinking that shrink make reorder of the pages and improve the speed, look the pic. I’m wrong? In fact you are correct, the shrink command I actually use do not turn on this option, but I can copy/paste t-SQL from here. What do you think? In fact, I have no problem with HDD space at all , I need to use shrink only if it help for performance.
For now I should use Maintenance plan, the business is not so big to pay a DB administrator …
So my Maintenance plan will be:
1. Daily update statistics /only this/
2. One time in a week – Reorganize Index Task /instead of Shrink-this reorder index page for faster read, is that fine?/
3. Once time in a month – rebuild index Task
This look fine to you? Other recommendation?
P.S: I’m sure your notes are correct, anyway what I detect at 100 % that after apply this wrong maintenance plan – one report from 25 sec. start to calculated for 1.5 sec. Too much difference over too small data .. is it possible to be die to HDD bad sectors
Yes, that looks like an excellent adjustment.
I guess it's possible it's a bad drive. But those things don't usually get suddenly better, they just get worse and worse.
I guess it's possible it's a bad drive. But those things don't usually get suddenly better, they just get worse and worse.
ASKER
10x. I apply the new maitance plans + will check for physical bad sectors on HDDs
apart from that:
* do your indexes have their own filegroup?
* do all of your tables have clustered indexes (which should NOT be on the index filegroup, but the regular table filegroup)
anyhow, indeed some "regular" index rebuild may help, though I recommend doing it too often for the full system, but instead eventually "regularly" on a dedicated table, if needed.