Reindexing - 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 2000 Forums

SQL Server Administration (2000)

Reindexing

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-17 : 12:52:18

Prior advice I'd received was to manually run SQL 2000 reindexing on off-hours and visually watch for errors.

I'd really like to schedule a job to do the same thing at midnight. Is this a bad thing?

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-17 : 12:55:13

Yes run DBCC DBREINDEX after hours. If you can't afford the DBCC DBREINDEX hit at any time of the day, run DBCC INDEXDEFRAG instead. You do not need to visually watch for errors. You just run it. DBCC CHECKDB is what you watch for errors. I usually put e-mail notifications in these types of jobs so that I am paged when they fail.

We can afford a minimal hit at night, so we go with DBCC DBREINDEX. We run it every night around 2am. Full backups run around midnight.

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-17 : 13:06:19

Thanks Tara. I'll set it up.

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-17 : 15:49:59

Best method?

I've got a stored proc to schedule, the content is

DBCC CHECKDB

DBCCREINDEX
...
DBCCREINDEX
RETURN

Is there any error checking needed to abort the script at individual steps. I suppose bracketing it with begin tran won't help?

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-17 : 16:09:28

You do not need to abort the script. I wouldn't put these commands inside the same procedure. DBCC CHECKDB should be one and DBCC DBREINDEX should be in another. Separated so that you can schedule them at different times. Do you need a procedure for this? You can simply schedule:

DBCC CHECKDB(DBName1)
GO
DBCC CHECKDB(DBName2)
GO

You only would want a procedure if you want to loop through the databases.

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-17 : 16:21:18

I guess a procedure is not needed, and I did think about it though.

It seemed easier to me to write a stored proc because I spend all my time in that environment now. It seemed easier from a maintenance point of view to know that

exec dbo.JOB_DBCC_REINDEX

Would run at 4AM every night (I only have one, not many databases).

If I need to change the job, I adjust the stored proc.

Well it seemed like a good idea anyway. What's wrong with it?

-- On the subject of splitting the DBCC CHECKDB into a different JOB step, I could do, but I was hoping that the DBCC CHECKDB would raise and error and abort the procedure (including the reindexing steps) if an error was encountered.

Otherwise, it's all serialized in a single proc, so in effect it's all scheduled (wince) serially anyway.

Another factor here is that my DBs are tiny compared to yours. Worse case, the entire procedure will run in 3 minutes.

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-17 : 16:23:01

-- removed -- human error, error, error, ....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-17 : 16:25:43

Nothing is wrong with it. It's just a preference thing. I put maintenance queries into stored procedures when it needs to do a bunch of stuff.

I am not sure if DBCC CHECKDB would abort the procedure on error. I suspect it would.

As far as sizes go, yeah I've got some very large databases, so we like to spread out the commands. So some stuff will run at 10pm, then others at midnight, then more at 2am, etc...

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-17 : 16:34:38

This is deep.

Can you give me a call to discuss it further?

My number is 867 5309, and I'm short on cash !

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-17 : 16:35:07

Sorry, I couldn't resist.

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-17 : 16:36:20

Thanks for all your help Tara.

I imagine you need to spread your maintenance procs over time because they crunch for several minutes ?? Like what? 10 or 20 minutes?

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-17 : 16:42:42

Well on the very large databases (over 100GB), the backups were taking a couple of hours. We purchase SQL Litespeed, so now for all databases on that one box, it is taking just over an hour, and I think it totals about half a terabyte in databases. DBCC CHECKDB and DBCC DBREINDEX take over 3 hours to run on that box as well. On this server, we do a full backup each night, but CHECKDB and DBREINDEX only once per week. The data isn't as critical as on other servers.

On the other servers, the applications take a hit when DBCC DBREINDEX runs. On the large tables, we spread out DBREINDEX so that certain indexes are touched each night but not all. For the small tables, we rebuild all indexes each night. It took a while to figure out which ones could run and at what time with minimal impact. Minimal impact meaning as less of an impact at night so that we don't get paged.

Tara

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-02-17 : 17:31:19

Tara,
This is totally off topic here, but what sort of hardware does your system run on? That's a pretty big database. If you can't say, I totally understand.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-17 : 17:44:36

The very large databases are on very sad hardware. I only help out with the database administration aspect on it. I have made lots of recommendations that will improve things, but only some make it in. They are moving the databases to a new server soon. They purchased a HP Proliant DL580. It'll have 4 CPUs and 8GB of RAM (yeah not enough, I know). We'll eventually cluster it. The databases will reside on Compaq's EVA SAN solution, which is extremely fast. The bottleneck on the current hardware is CPU and RAM. Current CPU is 2 CPU (sort of slow CPUs) and only 1GB of RAM.

We've got a lot of work to do on that server. Hopefully, I'll be able to get more control of it.

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-17 : 17:06:12

Once, a long long time ago....

Someone gave me a script (I think it was Tara)

SET NOCOUNT ON
SELECT 'DBCC CHECKDB'
SELECT 'DBCC DBREINDEX([' + u.name + '.' + o.name + '], '''', 80)'
FROM sysobjects o
INNER JOIN sysusers u ON o.uid = u.uid
WHERE o.name <> 'dtproperties' AND o.type = 'U'
ORDER BY o.name

Anyone have the same thing that'll build

DBCC INDEXDEFRAG

for the entire database??

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-17 : 17:50:07

Yep, my query, but remove the second line.

SELECT 'DBCC DBREINDEX([' + u.name + '.' + o.name + '], '''', 80)'
FROM sysobjects o
INNER JOIN sysusers u ON o.uid = u.uid
WHERE o.name <> 'dtproperties' AND o.type = 'u'
ORDER BY o.name

If no one gives you one, I can work on it tomorrow.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-17 : 18:19:52

I had a similar script so it was easy to get this done:



SET NOCOUNT ON

DECLARE @TableName sysname
DECLARE @IndexName sysname

DECLARE DEFRAG CURSOR
   FOR 	SELECT T.name, I.name
	FROM SYSOBJECTS T
	INNER JOIN SYSINDEXES I ON T.id = I.id AND T.name <> I.name 
	WHERE T.type = 'U' AND T.status > -1
	AND I.name NOT IN (	SELECT SI.name FROM SYSOBJECTS O
				INNER JOIN SYSINDEXES SI ON O.name = SI.name
				WHERE O.xtype IN ('PK', 'F')	)
	ORDER BY I.name
OPEN DEFRAG
FETCH NEXT FROM DEFRAG INTO @TableName, @IndexName
WHILE @@FETCH_STATUS = 0
   BEGIN
	IF (SELECT INDEXPROPERTY(OBJECT_ID(@TableName), @IndexName, 'IsStatistics')) = 0
		EXEC ('DBCC INDEXDEFRAG(0, ' + @TableName + ', ' + @IndexName + ')')
	FETCH NEXT FROM show_statistics INTO @TableName, @IndexName
   END

CLOSE DEFRAG
DEALLOCATE DEFRAG

I would change the EXEC to a PRINT to see if it is grabbing all of the indexes. I have only briefly tested it. Let me know if it works. If so, I'll probably put it in my blog for others to see.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources