Your browser (Internet Explorer 7 or lower) is out of date. It has known security flaws and may not display all features of this and other websites. Learn how to update your browser.


Why not rebuild all indexes at once?

On my SQL Master Academy trainings for novice and experienced DBA we discuss a lot why and how to perform index rebuilds. And there are constantly a couple of questions about index rebuilds:
•Do I need index rebuild at all?
•Why not rebuild all the indexes of the database as one task?

Let’s discuss those and give some advices and scripts to use.

Be careful when scheduling an online index rebuild

Online index rebuild is a feature available from SQL Server 2005 Enterprise Edition. While it does sound useful and attractive to use you should be careful considering it as part of your database maintenance plan. If you use it as a regular index rebuild operation then you could end up with serious locking and blocking and you cannot be sure and define the approx time of index rebuilds, it could be different every time.

In one of my customers the online index rebuild of the database took between 2 and 12 hours! So we decide to refine the task and go for offline index rebuilds for most of the indexes.

Why is that happening and what is behind the online index rebuilds.

Master database error after applying SQL Server 2008 R2 SP2

When applying SP to SQL Server you need to be careful, I mean really, with planning and attention to every step and especially to errors if any appear.

While I know perfectly the meaning of this sentence I was not as careful as needed applying the SQL Server 2008 R2 SP2, because it was on my laptop, and I did couple of other things meanwhile including listening to my baby girl first songs

So I run the SP2 installation, went trough the Wizard and started the process. I even forgot to close the SSMS and the installation process kindly reminded me that it didn’t have control over SSMS. OK, so I closed it and started the patch again. After several minutes I saw the lovely green icon and the message of successful installation. Great! One more task from the checklist is done, while of course thinking about completely different thinks and watching my baby’s first steps! BUT when I started the SSMS and tried to login the “nice” messages came in.

Hash and Sort Warnings – a common performance problem

Hash and Sort Warnings events are very common in a real production systems. The problem is that we usually don’t know that we have those events, and that they really hammer the performance. I am surprised that in almost every Performance Assesment that I make I find such events during the monitoring.

Having Hash and/or Sort warning means that the query/procedure has problems in the execution plan, generally the execution plan is not the optimal one. As a results more resources could be needed to execute the query than usual.