January 11, 2009 at 4:54 am
Hi,
can anyone help we with the syntax of the
sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, NULL)
and the sys.indexes
to view the index name,frag level,table name and etc?
THX
January 11, 2009 at 6:09 am
Do you want to see all the indexes in all of the databases on the server? If index_physical_stats is run for one database, it's easy to get index names. If it's run for the entire server, the only thing that's easy to get is the table name (provided you're on at least SP2) because sys.indexes is specific to a database.
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
January 11, 2009 at 7:37 am
hi,
I've create this script that rebuild or reorganized my index according
to the frag level.
how i can run this script on a few databases?(it can be a stored proc or one job with cursor)?
THX
--start script
declare @DB_ID as smallint
declare @IG_DUP_KEY smallint
declare @TABLE_NAME as varchar(1000)
declare @INDEX_NAME as varchar(1000)
declare @FRAG as float
declare @RUNINDEX as varchar (1000)
DECLARE CRS_INDEX_MAINT CURSOR LOCAL FAST_FORWARD
FOR
SELECT object_name(b.object_id)as 'table_name',name as 'index_name', avg_fragmentation_in_percent,b.ignore_dup_key
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 AND a.index_id = b.index_id
where name is not null
order by object_name(b.object_id) asc
OPEN CRS_INDEX_MAINT
FETCH NEXT FROM CRS_INDEX_MAINT INTO @TABLE_NAME,@INDEX_NAME,@FRAG,@IG_DUP_KEY
WHILE @@FETCH_STATUS = 0
BEGIN
if @FRAG < 30.0
begin
SET @RUNINDEX = N'ALTER INDEX ' + @INDEX_NAME + ' ON ' + @TABLE_NAME + ' REORGANIZE WITH ( LOB_COMPACTION = ON )'
--print @RUNINDEX
end
else if @FRAG > 30 and @IG_DUP_KEY = 1
begin
SET @RUNINDEX = N'ALTER INDEX ' + @INDEX_NAME + ' ON ' + @TABLE_NAME + ' REBUILD WITH ( FILLFACTOR = 90, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF )'
--print @RUNINDEX
end
else
begin
SET @RUNINDEX = N'ALTER INDEX ' + @INDEX_NAME + ' ON ' + @TABLE_NAME + ' REBUILD WITH ( FILLFACTOR = 90, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )'
--print @RUNINDEX
end
EXEC (@RUNINDEX);
PRINT N'Executed: ' + @RUNINDEX;
FETCH NEXT FROM CRS_INDEX_MAINT INTO @TABLE_NAME,@INDEX_NAME,@FRAG,@IG_DUP_KEY
END
CLOSE CRS_INDEX_MAINT
DEALLOCATE CRS_INDEX_MAINT
--end script
January 11, 2009 at 9:31 am
If you want to check all databases, you can specify NULL as the value for all parameters. However I wouldn’t do that. If you want to check the fragmentation for few databases, you can run your script few times and each time run it in a different database.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 11, 2009 at 11:56 am
thanks,i know that,but my question is how in the script i can adjust it to work on a databases that i specified(4-5 databases)?
THX
January 11, 2009 at 2:11 pm
There are few ways. If you want to run it as a job you can use the same script in different steps and run each step in a different database.
If you want to use it interactively, you can just run it from SSMS 4 times and each time run it in a different database.
If you want to run it as a batch but not as a job you can use the USE DBname statement (where DBName is the name of your database). You have a choice of copying the same script 4 times in the same batch and each time before the script specify which database to use, or to create a cursor with the database’s name and add a loop in your script and in each step of the loop use a different database’s name.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 11, 2009 at 11:42 pm
Mad-Dog (1/11/2009)
Hi,can anyone help we with the syntax of the
sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, NULL)
and the sys.indexes
to view the index name,frag level,table name and etc?
THX
SELECT a.index_id, name, database_id, avg_fragmentation_in_percent,index_type_desc,fragment_count,page_count
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
--------------
IF OBJECT_ID('USP_ExecReorgRebuildIndex', 'P') IS NOT NULL
DROP PROCEDURE dbo.USP_ExecReorgRebuildIndex
GO
CREATE PROCEDURE dbo.USP_ExecReorgRebuildIndex
(@DataBaseName AS VARCHAR(128),
@Exec AS INT,
@Print AS INT,
@Threshold AS INT)
AS BEGIN
/*
Parameters:
@DataBaseName = Database Name
@Exec: Performs REORGANIZE or REBUILD index
(with @Exec = -1 Run the commands,
with @Exec <> -1 Does not run the commands)
@Print: Print command or not
(with @Print = -1 Print the commands,
with @Print <> -1 Does not print the commands)
@Threshold: Threshold of DM_DB_Index_Physical_Stats
*/
DECLARE @index_id AS INT,
@index_name SYSNAME,
@action_to_do AS VARCHAR(1024),
@avg_fragmentation_in_percent FLOAT,
@table_name AS VARCHAR(512)
DECLARE CUR CURSOR FOR
SELECT
a.index_id,
b.name index_name,
a.avg_fragmentation_in_percent,
CASE WHEN (a.avg_fragmentation_in_percent <= 30)
THEN 'ALTER INDEX '+LTRIM(RTRIM(b.name))+' ON '+LTRIM(RTRIM(s.name))+'.'+LTRIM(RTRIM(c.name))+' REORGANIZE'
ELSE 'ALTER INDEX '+LTRIM(RTRIM(b.name))+' ON '+LTRIM(RTRIM(s.name))+'.'+LTRIM(RTRIM(c.name))+' REBUILD'
END AS action_to_do,
LTRIM(RTRIM(s.name))+'.'+LTRIM(RTRIM(c.name)) AS table_name
FROM
sys.dm_db_index_physical_stats (DB_ID(@DataBaseName), NULL, NULL, NULL, NULL) AS a
JOIN
sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
JOIN
sys.objects AS c ON c.object_id = a.object_id
JOIN
sys.schemas AS s ON s.schema_id = c.schema_id
WHERE (b.name IS NOT NULL)
AND (a.avg_fragmentation_in_percent > @Threshold)
ORDER BY a.avg_fragmentation_in_percent DESC
OPEN CUR
FETCH NEXT FROM CUR INTO @index_id, @index_name, @avg_fragmentation_in_percent, @action_to_do, @table_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@Exec = -1)
BEGIN
EXEC(@action_to_do)
EXEC('UPDATE STATISTICS ' + @table_name + ' ' + @index_name)
END
IF (@Print = -1)
BEGIN
PRINT @action_to_do
PRINT 'UPDATE STATISTICS ' + @table_name + ' ' + @index_name
PRINT '@'
END
FETCH NEXT FROM CUR INTO @index_id, @index_name, @avg_fragmentation_in_percent, @action_to_do, @table_name
END
CLOSE CUR
DEALLOCATE CUR
END
--EXEC USP_ExecReorgRebuildIndex 'TestDB', -1, 0, 10
January 12, 2009 at 1:47 pm
Try the below mentioned modified code provided by Paresh:
IF OBJECT_ID('USP_ExecReorgRebuildIndex', 'P') IS NOT NULL
DROP PROCEDURE dbo.USP_ExecReorgRebuildIndex
GO
CREATE PROCEDURE dbo.USP_ExecReorgRebuildIndex
(@Exec AS INT,
@Print AS INT,
@Threshold AS INT)
AS BEGIN
/*
Parameters:
@Exec: Performs REORGANIZE or REBUILD index
(with @Exec = -1 Run the commands,
with @Exec <> -1 Does not run the commands)
@Print: Print command or not
(with @Print = -1 Print the commands,
with @Print <> -1 Does not print the commands)
@Threshold: Threshold of DM_DB_Index_Physical_Stats
Sample Execution: Exec dbo.USP_ExecReorgRebuildIndex 1,1,10
*/
DECLARE @index_id INT,
@index_name SYSNAME,
@action_to_do VARCHAR(1024),
@avg_fragmentation_in_percent FLOAT,
@database_name VARCHAR(25),
@Object_name varchar(50),
@sql varchar(4000),
@thresholdvalue varchar(10),
@iRowCount INT
DECLARE @t_TableNames_Temp TABLE
(database_name SYSNAME)
Create table #fragmentdata
(
Index_ID INT,
index_name SYSNAME,
avg_fragmentation_in_percent FLOAT,
action_to_do VARCHAR(1024),
Objectname VARCHAR(50),
database_name VARCHAR(25)
)
Declare @databasename VARCHAR(512)
Set @thresholdvalue=@threshold
INSERT @t_TableNames_Temp
SELECT name
FROM master.sys.databases where name in ('northwind')--YOU CAN MENTION THE REQUIRED DBNAMES HERE
ORDER BY name
--Getting row count from table
SELECT @iRowCount = COUNT(*) FROM @t_TableNames_Temp
WHILE @iRowCount > 0
BEGIN
SELECT @databasename = database_name from @t_TableNames_Temp
Set @sql='
SELECT
a.index_id,
b.name index_name,
a.avg_fragmentation_in_percent,
CASE WHEN (a.avg_fragmentation_in_percent <= 30)
THEN ''ALTER INDEX ''+LTRIM(RTRIM(b.name))+'' ON ''+LTRIM(RTRIM(s.name))+''.''+LTRIM(RTRIM(c.name))+'' REORGANIZE''
ELSE ''ALTER INDEX ''+LTRIM(RTRIM(b.name))+'' ON ''+LTRIM(RTRIM(s.name))+''.''+LTRIM(RTRIM(c.name))+'' REBUILD''
END AS action_to_do,
LTRIM(RTRIM(s.name))+''.''+LTRIM(RTRIM(c.name)) AS Object_name,
'''+@databasename+''' AS DB_Name
FROM
sys.dm_db_index_physical_stats (DB_ID('''+@databasename+'''), NULL, NULL, NULL, NULL) AS a
JOIN
'+@databasename+'.sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
JOIN
'+@databasename+'.sys.objects AS c ON c.object_id = a.object_id
JOIN
'+@databasename+'.sys.schemas AS s ON s.schema_id = c.schema_id
WHERE (b.name IS NOT NULL)
AND a.avg_fragmentation_in_percent >'+@thresholdvalue+' ORDER BY a.avg_fragmentation_in_percent DESC'
Insert #fragmentdata
exec (@sql)
DECLARE CUR CURSOR FOR
Select * from #fragmentdata
OPEN CUR
FETCH NEXT FROM CUR INTO @index_id, @index_name, @avg_fragmentation_in_percent, @action_to_do, @Object_name,@database_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@Exec = -1)
BEGIN
EXEC(@action_to_do)
EXEC('UPDATE STATISTICS ' + @database_name + ' ' + @index_name)
END
IF (@Print = -1)
BEGIN
PRINT 'MANU JAIDKA'
PRINT (@action_to_do)
PRINT 'UPDATE STATISTICS ' + @database_name + ' ' + @index_name
END
FETCH NEXT FROM CUR INTO @index_id, @index_name, @avg_fragmentation_in_percent, @action_to_do, @Object_name,@database_name
END
DELETE FROM @t_TableNames_Temp WHERE @databasename = database_name
SELECT @iRowCount = @iRowCount - 1
CLOSE CUR
DEALLOCATE CUR
END
END
HTH!
MJ
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply