SQL Server maintenance script
-
Hi,
I am developing a web app for an external client. The app has already been running for about three years and is administered by an external company. Unfortunately it looks like their DBA doesn't do any ongoing maintenance of the database, consequently the production system runs very slowly.
I would like to send them a script to run into the database to try and boost performance, and have come up with the script below. I was hoping that some of you could have a look at this and suggest where I could make any improvements.
Thanks,
Marcus
------------------------------------------------------------------------
-- Name : update indexes & stats.sql
-- Author : Marcus Malinow , Dec 14th 2006
-- RDBMS : SQL Server 2000/2005
SET NOCOUNT ON
PRINT '---------------------------------'
PRINT '-- Date: ' + CAST(GETDATE() AS CHAR(20))
PRINT '-- Database: ' + DB_NAME()
PRINT '-- Server: ' + @@SERVERNAME
PRINT '---------------------------------'
DECLARE @SQLString VARCHAR(2000) -- String used to hold SQL Statements to be executed.
DECLARE @ObjectID INT -- Not used, but may be useful for enhancements
DECLARE @TableName VARCHAR(120) -- Table name
DECLARE @TableSchema VARCHAR(40) -- Owner of the table
DECLARE c_table CURSOR FOR
SELECT o.name as TableName, USER_NAME(o.uid) as TableSchema, o.id as ObjectID
FROM sysobjects o,
sysindexes i
WHERE o.type = 'U'
AND o.id = i.id
AND i.indid IN (0,1)
AND o.name != 'dtproperties'
OPEN c_table
FETCH NEXT FROM c_table INTO @TableName, @TableSchema, @ObjectID
WHILE @@FETCH_STATUS = 0
BEGIN
select @SQLString = CAST(@TableSchema + '.' + RTRIM(@TableName) AS VARCHAR(100))
PRINT @SQLString
EXEC ('DBCC DBREINDEX(''' + @SQLString + ''')')
EXEC ('sp_recompile ''' + @SQLString + '''')
FETCH NEXT FROM c_table INTO @TableName, @TableSchema, @ObjectID
END
CLOSE c_table
DEALLOCATE c_table
select @SQLString = 'DBCC UPDATEUSAGE (''' + DB_NAME() + ''')'
EXEC (@SQLString)
exec sp_updatestats
SET NOCOUNT OFF
-
a dbcc dbreindex by tablename will also update all stats on the table. sp_recompile is for procs not tables.
I always run dbcc updateusage as part of my maint tasks.
you should prefix your tables with dbo. , system tables are named differently within sql 2005, although the old names are supported as views.
I assume you have tested this first?
Although I cease to be amazed, even at my age, are you really sure there are not maint jobs on your database? It may be bound up as part of the backup for example.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Colin,
thanks, I'll remove the sp_updatestats.
DBCC UPDATEUSAGE is at the end of the script, so I got that covered
tables are prefixed with dbo (or other ownername in the event that they have been created incorrectly). This is @TableSchema.
To be honest I'm not 100% sure that there aren't maintenance jobs already set up, and I suspect that stats are set to auto update, but I'm working with a fairly inexperienced DBA who doesn't seem to be particularly proactive, so before I start changing indexes I wanted to see what sort of performance upgrade I could get by running in a decent maintenenance script.
Thanks again,
Marcus
-
yup totally agree, I'm tuning a production system where the server is outsourced and that's exactly where I started, sadly it's taken a number of weeks without success, and I've now submitted my own code for the index rebuilds as the data centre don't seem to be able to write a full solution. You can check database properties to check for auto update and auto create stats, you need to be 100% on top of your database tuning to consider turning these off, which had been the case on the server I'm working on.
I meant you should prefix the system tables with dbo. , best practice and all that.
If you're unsure of your external company you might want to cast your eye over ntfs fragmentation on your server, that can have quite a negative impact on performance. I was gratified to discover the data centre that manage my server don't defragment but did admit perhaps they should "look into it"
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply