June 26, 2016 at 3:30 pm
I am running SQL Server Express and we have to perform the rebuilding of indexes manually via query and verify that the indexes have been rebuild.
I first run this query to rebuild all indexes in every table of the database
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = 90', SORT_IN_TEMP = ON, ONLINE = OFF)'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
I then used the following script to verify that the indexes are being rebuild by checking the date modified of the statistics.
create table ##stats
(
OBJECTID INT
,STATID INT
,LASTUPDATED VARCHAR (100) NULL
,NAME VARCHAR (100) NULL
)
DECLARE @OBJECTID INT
DECLARE @STATID INT
DECLARE TABLECURSOR CURSOR FOR
SELECT OBJECT_ID, STATS_ID FROM SYS.STATS
OPEN TABLECURSOR
FETCH NEXT FROM TABLECURSOR INTO @OBJECTID, @STATID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO ##STATS
SELECT SP.OBJECT_ID, SP.STATS_ID, LAST_UPDATED, SN.NAME FROM
SYS.DM_DB_STATS_PROPERTIES(@OBJECTID,@STATID) SP
JOIN SYS.STATS SN ON
SN.OBJECT_ID = SP.OBJECT_ID AND
SN.STATS_ID = SP.STATS_ID
FETCH NEXT FROM TABLECURSOR INTO @OBJECTID, @STATID
END
CLOSE TABLECURSOR
DEALLOCATE TABLECURSOR
GO
SELECT * FROM ##STATS ORDER BY LASTMODIFIED DESC
DROP TABLE ##STATS
Q: Why is it that when there is only one table in the database there are a few hundred statistics listed (is it because I am also seeing statistics for index views
Q: Why are not all indexes updated.
Q: How can we determine what indexes/statistics are part of the tables/columns and what are part of the indexed views.
Any help is appreciated.
Jeff
June 26, 2016 at 8:26 pm
jayoub (6/26/2016)
Q: Why is it that when there is only one table in the database there are a few hundred statistics listed (is it because I am also seeing statistics for index views
Index views could certainly be a part of the problem. There's also a thing known as "Column Statistics" that are created when criteria isn't covered by an index.
Q: Why are not all indexes updated.
Because some indexes may be in mixed extents or the system decides that a table is too small to bother with.
Q: How can we determine what indexes/statistics are part of the tables/columns and what are part of the indexed views.
That's a bit more difficult but you can start by looking at the sys.indexes table and the sys.stats table (views, really). See "Books Online" for more information on those and related tables, views, DMVs. and system functions.
Your turn, please. Why on this good green Earth do you have to prove that indexes were rebuilt? Also, what are you doing to keep from blowing out the log files and the MDF file during index rebuilds on larger tables?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2016 at 2:48 am
That is serious overkill. It's extremely unlikely that you need to rebuild all indexes every time. Look up Ola Hallengren's index maintenance script (or go to http://ola.hallengren.com/) and use that.
You don't need to run them manually. While Express doesn't have SQL Agent, the OS it's installed on has the Windows Scheduler.
Your script will rebuild all indexes, no matter what, but your 'check' script checks index and column statistics and column statistics aren't updated as part of an index rebuild, hence won't show a changed updated date
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2016 at 5:28 am
Thank you for the feedback
I don't know why the team lead asks to verify. I think he just wants to know that the script work.
Your help is appreciated
Jeff
June 27, 2016 at 5:29 am
Thanks your help is appreciated
Jeff
June 27, 2016 at 5:58 am
You're welcome but Gail (Gila Monster) is spot on. Rebuilding all indexes is a lot of overkill and causes a huge amount of logging unless you're in the BULK LOGGED or SIMPLE recovery model (if in SIMPLE, you have bigger problems).
Also, just arbitrarily assigning a 90% Fill Factor can be a huge waste for anything that has a proper clustered index where the key is narrow, unique, immutable, and ever increasing. It means that you've wasted 10% of the space for all data older than right about now and have slowed your queries and caused them to use 10% more IO on their best days. It's especially important to NOT waste such space on SQL Express.
Since the code rebuilds the indexes in an OFFLINE manner, the extended run time caused by rebuilding indexes that don't actually need it means that the underlying tables aren't as available as they could be if you only rebuilt indexes that needed it. AND, rebuilding of indexes uses a shedload more resources because any index over 128 extents (that's only 8MB) will be rebuilt and committed before the old index is dropped. For the clustered indexes on large tables, that can be a huge investment in disk space used/database size especially on something small like SQL Express.
You should also look into selectively reorganizing indexes instead of doing carpet bombing by rebuilding every thing. As Gail, also pointed out, Ola Hallengren has a good backup system that's well documented and downloadable for free.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply