SQLTeam.com | Weblogs | Forums

Defrag indexes on a 973.311 records table in a SQL Express 2012


#1

I got a SQL Express 2012 database with a table with almost 1 Million of records. I would like to defrag the indexes of that table with a script via task scheduler but a i'm kind of newbie on indexes task. So my question is ¿How often should i rebuild all the index of that table? The use of the database is not so much only 10 people every day use. But there is some indexes that get very defragmented very soon. Is too much to reindex all indexes every week? I plan to run the script on the server at downtime (i.e. 12 p.m.).

My script is:

USE mybd;
GO
ALTER INDEX ALL ON dbo.srv
REBUILD WITH (SORT_IN_TEMPDB = ON );
GO

Thanks in advance. And sorry if this is not the right subforum.


#2

I would suggest that you Defrag every night, but only for tables that are fragmented above a threshold that you determine makes it worth doing.

I suggest you use Minion Reindex, rather than making your own. You will have to use Windows Task Scheduler as there is no SQL Agent Scheduled Tasks in SQL Express (AFAIK)

http://minionware.net/reindex/


#3

Hi, Kisten, thanks for the tip and the link.
The problem is that that Minion Reindex is too complex for a little db as this. I only have problems on one table, others are much little. Also there is now a lot of db copies of the main db in the sql server for developing purposes that do not want to be reindexed. I have read that it can be configured for only one db or table but a dont think that worth spend time configuring Minion Reindex. Am i wrong? If not do you think is this a proper script for only one table in one db?

Thanks.


#4

Yes, your description sounds right. The problem that I foresee is that building your own Backup and Index maintenance solutions is fairly complex. We have our own here, built before 3rd party scripts were readily available, and it represents many (as in MANY MANY!!) hours of development, research and tinkering. Minion have done all that hard work ... but you are right, if you just install it then it will "take care of everything" until you figure out how to config it for just one database (and maybe just one-or-two tables / indexes).

Other problem is that because you have SQL Express you don't have SQL Agent Scheduler, and most (all?) 3rd party scripts will be built to work with SQL Agent.

Are you sure you have a problem that needs solving (i.e. do you have performance problems caused by fragmented indexes) or are you just assuming that you do because the table is large?

Things I have read recently suggest that people rebuild indexes far more often than is necessary (for performance) - probably because SQL has improved over the versions but DBAs are still doing maintenance based on what they HAD to do 10 versions ago!!

For example, if you benchmark a particularly important query, then manually DEFRAG (or just DROP and re-CREATE the index) does the benchmark test then improve dramatically?

Other question is whether all the DEV databases you have on that server would also improve if defragged - if so maybe defragging "everything" is not such a bad thing, even if redundant. You would have to consider how much disk / log space was used/needed by the defragging process - it may be considerable (well, the first time at least). Keep an eye on how much your LOG .NDF (and, to a lesser extent, your DATA .MDF) files grow. Be prepared to shrink the LOG files back to their original size (do NOT shrink the DATA files, as that will undo the benefits of the index rebuilding)


#5

Thank for the detailed answer.

This was begun when clients on an app behind this db complaint about hangs of the app when accesing some reports filtering on some fields. Those fields are from that particulary "big" table and rebuilding the index on that fields solved the problem. I have set the script on the windows task scheduler on my machine (no rights for doing on the server) every 3 days a week. But now im thinking on doing on the whole table everynight on the server (asking for right to do it) via again then windows scheduler task on server.
Developer databases are just copies of the main db and get deleted and recreated from backups from the main db so at end they will be recreated from the main db with full reindexed table. No complains about dev. db til now.


#6

Sounds OK.

I wonder if the Index Fragmentation is the problem. Maybe STATS are being rebuilt Sync and that is causing later queries, on that table, to stall until the Stats rebuild is completed. It is possible (and usually preferable) to set the Stats Create to ASync (which is NOT the default).

Might also be possible to tune the query - if that is the only one that is causing user complaints? I would take a BACKUP of the DB at the point at which things are "bad", restore that on DEV and play with the query.

It is also possible to just copy the STATS from Production to DEV to force the query to run (with those "bad stats") to then have a fiddle ... but if possible I think that restoring a known-bad Backup from PRODUCTION onto DEV would be preferable. Opportunities to test the query, as well as trying various index rebuild strategies, and checking which ones improve things.

Maybe bigger (or smaller) FILL FACTOR will help. Maybe adding more "covering" columns to the index (hard to do those, AND test the fragmented state ...). Maybe the query is using nested VIEWs or somesuch that are creating inefficiencies.

I also think it would be worth looking at index REORG, rather than REBUILD, which I find more efficient on large indexes (less LOG and Data space requirements, and thus quite possible that doing that EVERY night would be fine, take very little time each time, and use very little additional Log and Data space).


#7

P.S. Might be worth logging the Fragmentation Levels of that Table / Index over time - hourly even maybe?

And also any re-create of the STATS on that Table / Index

They may shed some light on how quickly things become "bad" and thus how often you need to do some maintenance.


#8

I forget to mention that am not dba, just a program developer with admin wannabe functions. xD

How can i log the fragmentation levels on that table? I only have this script to check it.

SELECT index_id, avg_fragmentation_in_percent , page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'gam'), OBJECT_ID(N'srv'), NULL, NULL , 'LIMITED')
ORDER BY avg_fragmentation_in_percent DESC

GO

The same with the re-crate of the STATS. Dont have any script at all.

I will test with REORG in dev db, and follow your tips to check if there is some way to improve the SQL queries involved.

Playing with FILL FACTOR sounds way complex to me.

Thanks very much for the quick answer.


#10

That described me, 10 years ago, too :slight_smile:

insert the output from your SELECT (or the EXEC of a function that outputs them) into a table. Add a column for Date/Time and e.g. Server Name and Database Name. (Server name helpful when the database / etc. is moved to a new server and you are reporting, historically, but otherwise no benefit - its the same on every row - until you move servers :slight_smile: ).

Here's what I use for an Index Reorg and a Rebuild Statistics (REORG Index does not rebuild statistics, whereas Rebuild Index will create fresh statistics)

PRINT 'REORGANIZE [MyDatabase].[dbo].[MyTable] - [MyIndex]'
GO
ALTER INDEX [MyIndex] ON [MyDatabase].[dbo].[MyTable] REORGANIZE
GO
PRINT 'Update Stats '
GO
UPDATE STATISTICS [MyDatabase].[dbo].[MyTable] [MyIndex] WITH FULLSCAN
GO

This may be useful:

-- Date statistics last updated for a given table
SELECT	LEFT(t.name, 30) AS Table_Name,
	LEFT(i.name, 30) AS Index_Name,
	LEFT(i.type_desc, 15) AS Index_Type,
	STATS_DATE(i.object_id,i.index_id) AS Date_Updated
FROM    sys.indexes AS i
	JOIN sys.tables AS t
		ON t.object_id = i.object_id
WHERE	i.type > 0 
--
-- ORDER BY t.name ASC, i.type_desc ASC, i.name ASC 
ORDER BY Date_Updated DESC, t.name ASC, i.type_desc ASC, i.name ASC 

-- Index Last Modified date
DECLARE	@dbid int
SELECT	@dbid = db_id('MyDatabase')
SELECT TOP 100
	[objectname] = object_name(i.object_id)
	, [indexname] = i.name
	, i.index_id
	, o.create_date
	, o.modify_date
FROM	sys.indexes AS i
	JOIN sys.objects AS o
		 ON o.object_id = i.object_id
WHERE	objectproperty(o.object_id,'IsUserTable') = 1
	AND i.index_id NOT IN
		(
			SELECT	s.index_id
			FROM	sys.dm_db_index_usage_stats AS s
			WHERE	    s.object_id=i.object_id
				AND i.index_id=s.index_id
--				AND database_id = @dbid
		)
--
ORDER BY 
	o.modify_date DESC,
	objectname,
	i.index_id,
	indexname asc



-- Find out which tables have auto-update off
SELECT	o.name AS [Table], 
	i.name AS [Index Name],
	STATS_DATE(i.object_id, i.index_id) AS [Update Statistics date],
	s.auto_created AS [Created by QueryProcessor], 
	s.no_recompute AS [Disabled Auto Update Statistics],
	s.user_created AS [Created by user]
FROM	sys.objects AS o WITH (NOLOCK)
	INNER JOIN sys.indexes AS i WITH (NOLOCK) 
		 ON o.object_id = i.object_id
	INNER JOIN sys.stats AS s WITH (NOLOCK) 
		 ON i.object_id = s.object_id
		AND i.index_id = s.stats_id
WHERE	    o.[type] = 'U'
	AND no_recompute = 1
ORDER BY STATS_DATE(i.object_id, i.index_id) ASC;

-- set that property on

ALTER INDEX MyIndex
	ON dbo.MyTabve
SET (STATISTICS_NORECOMPUTE = OFF) 

You can just change it, for an index. If you have, ever, used the standard SSMS maintenance plan that may well have reset ALL the indexes on ALL the tables to the same FILL FACTOR.

If you have an index that only ever fills at one end (e.g. IDENTITY) then there is no point having a FILL FACTOR of less than 100%. So make sure that index is set to 100%.

OTOH if you have an index where the new entries are added at random then it might make sense to have a fill factor of, say, 80% - that would allow space for some additions WITHOUT having to split the page, and splitting the page will cause fragmentation.

On very large indexes the performance might be better if you have 100% FILL FACTOR - every page will be 100% full, so fewer index pages, overall, for SQL to have to read. Downside is every INSERT to a full page requires a page-split. But maybe all your INSERTS are localised - so they cluster in one spot, so once the page is split the new, half-page, will continue to fill up. Overall not too bad.

Definitely something to experiment with IMHO :slight_smile


#11

Thanks, thanks and thanks. I will read this carefully (nor less than 10 times :grinning:)