Indexing problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Indexing problems

Author

Topic

DeepSeed
Yak Posting Veteran

52 Posts

Posted - 2011-03-15 : 16:51:18

Hello SQL Team

Our SQL database tends to stop responding from time to time. It gives timeout errors and the CPU maxes to 100%. We find that queries that used to take 1 second to complete now take 40 seconds to complete when the databases go into this mode.

Then we run this command and the problem goes away for a bit.

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

Which tells me that we have INDEX problems.

So my question is this. Why are we having index problems and how can we keep the tables from needing to be re-indexed.

If the answer is to run this command every day then there is a problem with that. When we run this command our database becomes essentially unavailable, it complains of locked tables and takes a very long time to respond while this is running. We have defined our database as being "always available" so we can't have daily maintenance periods.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-15 : 16:55:33

What edition and version of SQL Server are you using? REBUILD will lock the tables unless you are using Enterprise edition, consider REORGANIZE instead.

By the way, DBCC DBREINDEX is deprecated in 2008 (the forum you are posting in). Use ALTER INDEX instead and only rebuild/reorganize those that need it. Consider using an intelligent rebuild/reorganize script that will handle this for you, such as mine: http://weblogs.sqlteam.com/tarad/archive/2009/11/03/DefragmentingRebuilding-Indexes-in-SQL-Server-2005-and-2008Again.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DeepSeed
Yak Posting Veteran

52 Posts

Posted - 2011-03-15 : 17:15:00

We are using SQL 2008 (sql 2005 on a few instances).

So if I add this SP and run it, it will accomplish the same task but without causing the locking/performance issues we see when running REINDEX?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-15 : 17:16:19

Well it depends. What edition are you using? If you are using Enterprise edition, then most reindexing can be done ONLINE. If you aren't using Enterprise, then it has to done OFFLINE. In the OFFLINE case, many people will go the REORGANIZE route instead. Just depends on your uptime requirements and if you have a slow period or a maintenance window.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DeepSeed
Yak Posting Veteran

52 Posts

Posted - 2011-03-15 : 17:17:52

Sorry forgot to mention that, we are using Standard.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-15 : 17:23:37

Then you won't be able to use the ONLINE option of ALTER INDEX. You can choose to use REORGANIZE instead as that's an online operation, however it doesn't do as good of a job as REBUILD. REBUILD is typically done once fragmentation is over 30%.

By the way, my code ensures that the proper settings are put in place for ALTER INDEX.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DeepSeed
Yak Posting Veteran

52 Posts

Posted - 2011-03-15 : 17:59:17

Ok, so with Standard my only real option is to use this command.

EXEC sp_MSforeachtable @command1="print '?' ALTER INDEX ALL ON ? REBUILD WITH ONLINE = ON"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-15 : 18:07:15

No. You need to re-read my replies. And you should not be using a script like that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DeepSeed
Yak Posting Veteran

52 Posts

Posted - 2011-03-15 : 18:40:30

Opps I meant REORGANIZE not REBUILD. Sorry! Is there something else wrong with this script, can I modify your script to work with SQL standard edition?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-15 : 18:51:18

My script works with all editions.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DeepSeed
Yak Posting Veteran

52 Posts

Posted - 2011-03-15 : 18:57:24

Ok, but it will run in OFFLINE mode in Standard edition?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-15 : 18:58:31

My script handles all scenarios. Pass it REORGANIZE if you can't afford an OFFLINE REBUILD that'll happen with Standard edition. And if you find you must do a REBUILD and can't "afford" the OFFLINE part, then you'll need to upgrade to Enterprise edition.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DeepSeed
Yak Posting Veteran

52 Posts

Posted - 2011-03-15 : 19:01:30

Ahhh, ok thanks!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-15 : 19:05:02

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources