check index defragementation on SQL server 2012 with SP1

Dear all,

the following script run for a long long time on a SQL server 2012 DB with SP1:

SELECT DB_NAME(a.database_id) as [Database Name]
		,OBJECT_NAME(SO.id) as [Table Name]
		,a.object_id as [Table Object ID]
		,b.name as [Index Name]
		,a.index_id
		,a.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b 
	ON a.object_id = b.object_id 
	JOIN sys.sysobjects AS SO
	on b.object_id = SO.id 
	AND a.index_id = b.index_id
where a.database_id = db_id() and SO.xtype = 'U'
order by a.avg_fragmentation_in_percent DESC
GO

Open in new window


any one know why ? this script just check index fragmentation.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
Depend on the database size. You are checking for all existing indexes in all tables of the database.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Also check if there's no blocking processes running.
marrowyungSenior Technical architecture (Data)Author Commented:
"Depend on the database size. You are checking for all existing indexes in all tables of the database. "

only this script has problem on SQL server 2012 with SP1

yeah, this is what I want. all index of all table and see fragmentation .

"Also check if there's no blocking processes running. "

no lock/blocking
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Vitor MontalvãoMSSQL Senior EngineerCommented:
How much time it took to run completly?
marrowyungSenior Technical architecture (Data)Author Commented:
I don't know, I add print (@sql), so I can see how far it goes. I will close this one and update you later if needed.
marrowyungSenior Technical architecture (Data)Author Commented:
I am doing this also, rebuild index:

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
 SET @fillfactor = 80
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 = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
 PRINT (@sql)
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
 CLOSE TableCursor
DEALLOCATE TableCursor
 GO
 
 EXEC sp_updatestats; 

Open in new window


it can take a long time and right now I see this but the script keep going, any reason why ?

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.65168'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
A

Open in new window


can't see why !
marrowyungSenior Technical architecture (Data)Author Commented:
I double check one index defragementation script from MS :

https://gallery.technet.microsoft.com/scriptcenter/Check-SQL-Server-a-a5758043

still seems loads/hangs there for a long time.

can't see why!
Vitor MontalvãoMSSQL Senior EngineerCommented:
it can take a long time and right now I see this but the script keep going, any reason why
You are rebuilding and not reindexing what means it recreates all the indexes so it will really take long if you're dealing with large tables.
About the error, you need to check the SQL statement and you can do that since you are printing it before executing it.
Vitor MontalvãoMSSQL Senior EngineerCommented:
By the way, if you are running those scripts as part of maintenance tasks then I would recommend you to use Ola Hallegren's solution. It's worldwide known and big companies are using it.
marrowyungSenior Technical architecture (Data)Author Commented:
"About the error, you need to check the SQL statement and you can do that since you are printing it before executing it. "

it doesn't work, this is the message from the print statement.

last time I tried it doesn't appear.

" then I would recommend you to use Ola Hallegren's solution. It's worldwide known and big companies are using it.

we are going to do that too but I have to some script for it.

so that one is faster ?
marrowyungSenior Technical architecture (Data)Author Commented:
hey, as you talk about that tools, I find it can't do log shrinking, right?
Vitor MontalvãoMSSQL Senior EngineerCommented:
it doesn't work, this is the message from the print statement.
Try to print the variables values before defining the @sql command.

so that one is faster ?
Isn't a matter of execution speed but a matter of doing only what's necessary. Not all indexes needs to be rebuilt or neither reindexed. Ola's script will chose for you which ones need to be reindexed and which ones need to be rebuilt and won't touch in those ones that doesn't need to be done anything.
marrowyungSenior Technical architecture (Data)Author Commented:
I am doing this :

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
 SET @fillfactor = 80
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 = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
 PRINT (@sql)
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
 CLOSE TableCursor
DEALLOCATE TableCursor
 GO
 
 EXEC sp_updatestats; 

Open in new window


message is:

"ALTER INDEX ALL ON mobile.alerts REBUILD WITH (FILLFACTOR = 80)
ALTER INDEX ALL ON dbo.MSG_USERS REBUILD WITH (FILLFACTOR = 80)
ALTER INDEX ALL ON dbo.tablediff_dbo_vms_cse_shipment REBUILD WITH (FILLFACTOR = 80)
ALTER INDEX ALL ON dbo.item_pb_exceptions REBUILD WITH (FILLFACTOR = 80)
ALTER INDEX ALL ON dbo.USER_INFO REBUILD WITH (FILLFACTOR = 80)
ALTER INDEX ALL ON dbo.tmp_flr_item_htsus REBUILD WITH (FILLFACTOR = 80)
"

it just keep going but I see those appear and I can't see why.

"Isn't a matter of execution speed but a matter of doing only what's necessary."

yeah, only high fragmentation need rebuilt !
marrowyungSenior Technical architecture (Data)Author Commented:
probably those table dbo.65168
Vitor MontalvãoMSSQL Senior EngineerCommented:
Add brackets to the table name:
SELECT OBJECT_SCHEMA_NAME([object_id])+'.['+name + ']' AS TableName
marrowyungSenior Technical architecture (Data)Author Commented:
do you think if I use Ola's script will not have this problem ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ola's script is tested world wide so I think someone there already had that issue and Ola's solved it :)
marrowyungSenior Technical architecture (Data)Author Commented:
AHAH.

I am waiting for it to finish !  I am running it
Vitor MontalvãoMSSQL Senior EngineerCommented:
How big is your database?
marrowyungSenior Technical architecture (Data)Author Commented:
wait, how to change the ola's index rebuilt script to do full index rebuild on all index?

change this:

EXECUTE dbo.IndexOptimize @Databases = ' vms_cascade_test ',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Open in new window


to

EXECUTE dbo.IndexOptimize @Databases = ' vms_cascade_test ',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Open in new window


or

EXECUTE dbo.IndexOptimize @Databases = ' vms_cascade_test ',
@FragmentationLow = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Open in new window

?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Don't change the fragmentation options but the fragmentation level thresholds:
@FragmentationLevel1 = 0,
@FragmentationLevel2 = 1,

so, when it finds a fragmentation level equal or higher than 1% it will rebuild.

It's all in the link to his script:
"FragmentationLevel1

Set the lower limit, as a percentage, for medium fragmentation. The default is 5 percent. This is based on Microsoft’s recommendation in Books Online.

IndexOptimize checks avg_fragmentation_in_percent in sys.dm_db_index_physical_stats to determine the fragmentation.
 
FragmentationLevel2

Set the lower limit, as a percentage, for high fragmentation. The default is 30 percent. This is based on Microsoft’s recommendation in Books Online.

IndexOptimize checks avg_fragmentation_in_percent in sys.dm_db_index_physical_stats to determine the fragmentation"
marrowyungSenior Technical architecture (Data)Author Commented:
"It's all in the link to his script:
"FragmentationLevel1"

basically this is the full documentation of the index rebuild script from ola?

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

so how about the •DatabaseIntegrityCheck SP and •DatabaseBackup SP?

only these online manual ?

https://ola.hallengren.com/sql-server-backup.html
https://ola.hallengren.com/sql-server-integrity-check.html

"Don't change the fragmentation options but the fragmentation level thresholds:
@FragmentationLevel1 = 0,
 @FragmentationLevel2 = 1,
 so, when it finds a fragmentation level equal or higher than 1% it will rebuild.
IndexOptimize checks avg_fragmentation_in_percent in sys.dm_db_index_physical_stats to determine the fragmentation.
"

so  @FragmentationLevel2 = 10 mean for high fragmentation index, it any index has high fragmentation of 10%, then full rebuild index?
so @FragmentationLevel1 = 10 mean for medium fragmentation index, it any index has high fragmentation of 10%, then full rebuild index?

or how can it know it is high /medium ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you check the script code you'll understand it better.
I never changed those values because it's the Microsoft's recommendation:
- Less then 5% of fragmentation do nothing;
- More then 5% and less then 30% of fragmentation perform reindex;
- More then 30% of fragmentation perform rebuild.

so how about the •DatabaseIntegrityCheck SP and •DatabaseBackup SP?
 only these online manual ?
Yes. Everything is in his website. You can download each maintenance plan isolated or download all in a single script (maintenancesolution.sql)  available in the main page.
marrowyungSenior Technical architecture (Data)Author Commented:
"Yes. Everything is in his website. You can download each maintenance plan isolated or download all in a single script (maintenancesolution.sql)  available in the main page. "

yeah, done that.

from the web example:

B. Rebuild or reorganize all indexes with fragmentation and update modified statistics on all user databases

EXECUTE dbo.IndexOptimize
 @Databases = 'USER_DATABASES',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL',
 @OnlyModifiedStatistics = 'Y'

Open in new window


as @FragmentationLevel2 = 30, so it means:

- More then 5% and less then 30% of fragmentation perform reindex;
 - More then 30% of fragmentation perform rebuild.

so for any index need reorganize index, it is about 5-30% , more than 30% will preform total rebuild, that why @FragmentationLevel1 = 5, @FragmentationLevel2 = 30 ? which is the medium level and
high fragmentation, right?

and if I put the database name from @Databases = 'USER_DATABASES', to @Databases = '<my DB name>', it will only check the index of that DB but not all, right?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes for both questions.
marrowyungSenior Technical architecture (Data)Author Commented:
I am sorry, both means:

1) so for any index need reorganize index, it is about 5-30% , more than 30% will preform total rebuild, that why @FragmentationLevel1 = 5, @FragmentationLevel2 = 30 ? which is the medium level and
 high fragmentation, right?

and 2)and if I put the database name from @Databases = 'USER_DATABASES', to @Databases = '<my DB name>', it will only check the index of that DB but not all, right?

?
Vitor MontalvãoMSSQL Senior EngineerCommented:
1) Yes. Less than 5% (@FragmentationLevel1 = 5) it won't perform any defrag. Between 5% (@FragmentationLevel1 = 5) and 30% (@FragmentationLevel2 = 30) will Reorg and more than 30% (@FragmentationLevel2 = 30) will Rebuild. You can call it Medium Level to the interval 5-30 and High Level to > 30%.

2)Yes, giving an existing name to the @Databases variable it will perform the job only for that explicit database.
marrowyungSenior Technical architecture (Data)Author Commented:
2)Yes, giving an existing name to the @Databases variable it will perform the job only for that explicit database. "

tks very much, are you using it too anyway you genius ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
LOL
Unfortunally due to our company policy we don't use Ola's solution but I tested it for some weeks and works perfectly. I even changed some emails with Ola because he didn't have a script for Restore.

Next year I'll try to convince again my company to use Ola's solution.
marrowyungSenior Technical architecture (Data)Author Commented:
"Unfortunally due to our company policy we don't use Ola's solution but I tested it for some weeks and works perfectly. "

wowo policy ? dangerous, Here we like that and we deploy that via soon.

"I even changed some emails with Ola because he didn't have a script for Restore."

you change some email means you change the script to implement the restore of database?
Vitor MontalvãoMSSQL Senior EngineerCommented:
No, means that I sent him an email asking him if we will work on Restore module and he answered me back. His email address is in his webpage so everybody is free to contact him :)
marrowyungSenior Technical architecture (Data)Author Commented:
someone will just angry if you ask him as you don't pay for them. difficult person.

also if I remove @FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,

is that mean the script will just rebuild all index ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
someone will just angry if you ask him as you don't pay for them. difficult person.
Not Ola. His payment is recogniztion. Just check the worldwide organizations that are using his solution. And those are the ones that gave him permission to publish their name.


also if I remove @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,

 is that mean the script will just rebuild all index ?
No. If you don't provide those values it will use them anyway since they are the default value. You can confirm that looking at the SP definition.
marrowyungSenior Technical architecture (Data)Author Commented:
"No. If you don't provide those values it will use them anyway since they are the default value. You can confirm that looking at the SP definition. "

oh,..
they are doing sth like this:

EXECUTE dbo.IndexOptimize @Databases = 'xxx',
@FragmentationLow = 'INDEX_REBUILD_OFFLINE',
@FragmentationMedium = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 1,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@LogToTable = 'Y'

Open in new window


so in this way, they way to rebuilt most of the index, agree?
Vitor MontalvãoMSSQL Senior EngineerCommented:
so in this way, they way to rebuilt most of the index, agree?
Depends on how many have more than 30% of fragmentation but will for sure reindex more since they'll do it for fragmentation level >= 1% instead of 5%.
marrowyungSenior Technical architecture (Data)Author Commented:
marrowyungSenior Technical architecture (Data)Author Commented:
yeah tks, victor.
marrowyungSenior Technical architecture (Data)Author Commented:
hi victor,

sorry come back to this question and we found some index is not rebuiding:

EXECUTE dbo.IndexOptimize @Databases = 'xxx',
@FragmentationLow = 'INDEX_REBUILD_OFFLINE',
@FragmentationMedium = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 1,
@FragmentationLevel2 = 4,
@UpdateStatistics = 'ALL',
@LogToTable = 'Y'

Open in new window


we are using this and we can't see why we are doing index rebuild offline all the time by @FragmentationLow, @FragmentationMedium and @FragmentationHigh , then no matter what the value of this @FragmentationLevel1, @FragmentationLevel2 is, we still doing rebuild offline and table locked ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
By your example I don't know if you understood how that SP works.
Your saying to always INDEX_REBUILD_OFFLINE starting with FragmentationLevel1 >= 1%, so this will matches almost 100% of the database indexes.

What exactly is the rule that you want to be performed?
marrowyungSenior Technical architecture (Data)Author Commented:
"Your saying to always INDEX_REBUILD_OFFLINE starting with FragmentationLevel1 >= 1%, so this will matches almost 100% of the database indexes."

exactly what we are thinking, this is why I come and ask, I don't think this operation making any sense at all, someone set that up there.

we try to make it better.

so for any index has fragementation level between 1% and 4%, always index reoraganise and any fragementation higher than 4% will rebuild online should be the one if we don't do this:


@FragmentationLow = 'INDEX_REBUILD_OFFLINE',
@FragmentationMedium = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_OFFLINE',

usually from your experience, when should we do that above?
Vitor MontalvãoMSSQL Senior EngineerCommented:
From my experience you shouldn't change nothing. Ola's script runs with default values that are the ones recommended by Microsoft.
marrowyungSenior Technical architecture (Data)Author Commented:
"you shouldn't change nothing"

should change nothing, right?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes. Change nothing :)
marrowyungSenior Technical architecture (Data)Author Commented:
tks very much.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.