Difference between DEFRAG and REORGANIZE

Question

Difference between DEFRAG and REORGANIZE

Rob Reid-246754

SSCarpal Tunnel

Points: 4183
More actions
April 21, 2015 at 6:20 am

#307207

Hi
I am simplifying a proc we have that we cannot use half of due to not having enterprise edition or online rebuilds of indexes (2012).
It seems it has been doing nothing for ages due to the 2 threshold parameters and the fragmentation levels being over the DEFRAG, so it should REBUILD but can't.
Therefore I just want a simple job that DEFRAGS/REORGANIZES on all tables over X% fragmentation.
I just want to know after reading http://sqlperformance.com/2013/07/sql-statistics/statistics-updates whether I should call
EXEC sp_updatestats
after my DEFRAG code
or use a
EXEC('UPDATE STATISTICS ' + @tablename + ' ' + @StatisticName)
command in a loop that gets those values e.g
INSERT INTO @tbls (tblschema, tblname,statname)
SELECT [sch].[name], [so].[name], [ss].[name] AS [TableName]
FROM [sys].[stats] [ss]
JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id], [ss].[stats_id]) sp
WHERE [so].[type] = 'U'
AND [sp].[modification_counter] > 0 AND ss.user_created = 0 AND [sp].[modification_counter]/[sp].[rows] > 0.10
ORDER BY [sp].[last_updated] DESC;
SELECT @rowId = MIN(rowId), @maxRowId = MAX(rowId) FROM @tbls;
WHILE @rowId <= @maxRowId
BEGIN
SELECT@tblschema = tblschema, @tblname = tblname, @statname = statname
FROM@tbls WHERE rowId = @rowId;
SET @stmt = 'UPDATE STATISTICS [' + @tblschema + '].[' + @tblname + '] ' + @statname
PRINT @stmt;
EXECUTE (@stmt);
SET @rowId = @rowId + 1
END
or is there is a difference between the two?
I am unsure which one is best to use or is there no difference at all between the two bits of code?
Thanks for any help in advance.
Rob

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Use update statistics with the FULLSCAN option.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rob Reid-246754 SSCarpal Tunnel Points: 4183 More actions · Answer 2

Hi

Sorry, could you give me an example of what you mean.

Is this inside the loop of statistics I am doing (with the EXEC) or is it something else e.g EXEC sp_updatestats with a parameter added to it?

And what is the difference between the two?

Thanks for your help.

Rob

Rob Reid-246754 SSCarpal Tunnel Points: 4183 More actions · Answer 3

e.g like

UPDATE STATISTICS [dbo].[tbl_TRAFFIC_VISITORS] _WA_Sys_0000000C_1DE57479 WITH FULLSCAN

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Rob Reid-246754 (4/21/2015)
e.g like
UPDATE STATISTICS [dbo].[tbl_TRAFFIC_VISITORS] _WA_Sys_0000000C_1DE57479 WITH FULLSCAN

Yup, exactly like that, although you don't need to specify the stats name, specify the table name and all stats on that table will be done in one go.

sp_updatestats uses a sampled update, which means the stats may not be as accurate as they could be.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rob Reid-246754 SSCarpal Tunnel Points: 4183 More actions · Answer 5

Hi Gail

So I guess I need to change my SQL for the loop then as it is showing multiple indexes / statistics for each table. I have tables in that loop appearing 10+ times.

What should the SQL be for my loop?

At the moment it is

SELECT [sch].[name], [so].[name], [ss].[name] AS [TableName]

FROM [sys].[stats] [ss]

JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]

JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]

OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id], [ss].[stats_id]) sp

WHERE[so].[type] = 'U'

AND [sp].[modification_counter] > 0

AND ss.user_created = 0

AND [sp].[modification_counter]/[sp].[rows] > 0.10 -- is this the correct amount?

ORDER BY [sp].[last_updated] DESC;

Thanks for your help!

Rob Reid-246754 SSCarpal Tunnel Points: 4183 More actions · Answer 6

Don't worry I was being a numpty. I could just DISTINCT on table but I would like to know if the

AND [sp].[modification_counter]/[sp].[rows] > 0.10

is the right sum is your eyes?

free_mascot One Orange Chip Points: 27169 More actions · Answer 7

As Gila has mention UPDATE STATISTICS with FULLSCAN is good. Refer following link for your reference:

http://sqlperformance.com/2013/07/sql-statistics/statistics-updates

https://msdn.microsoft.com/en-IN/library/ms187348.aspx

https://msdn.microsoft.com/en-IN/library/ms173804.aspx

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Rob Reid-246754 SSCarpal Tunnel Points: 4183 More actions · Answer 8

Rob Reid-246754

SSCarpal Tunnel

Points: 4183

April 21, 2015 at 11:01 am

#1792489

Thanks