April 11, 2013 at 1:22 am
HI to every one
can any one guide me to find out this
if any incident occurred in sql server and i was asked to take a report of the last one hour [or] halfanhour what should i do? and
how to List out all tables indexes having index fragmentation more than 50% of a database.
for this particular period for all tables in a entire database
--
Thanks
Naga.Rohitkumar
April 11, 2013 at 3:32 am
What do you mean by 'more than 50% of the database'?
If you want indexes that have a logical fragmentation higher than 50%, just query sys.dm_db_index_physical_stats and filter on the logical_fragmentation_in_percent column.
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
April 11, 2013 at 3:37 am
HI thanks for ur reply
i mean "List out all tables indexes having index fragmentation more than 50% of a database."
Thanks
Naga.Rohitkumar
April 11, 2013 at 3:39 am
Repeating the exact same sentence doesn't explain anything.
Do you want indexes that have more than 50% logical fragmentation? If so, see my previous reply. If not, then please explain what 'more than 50% of the database' means.
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
April 11, 2013 at 3:43 am
Yes sir
want indexes that have more than 50% logical fragmentation? from all indexes of tables in a database
Thanks
Naga.Rohitkumar
April 11, 2013 at 5:05 am
Answered that one in the first reply.
GilaMonster (4/11/2013)
If you want indexes that have a logical fragmentation higher than 50%, just query sys.dm_db_index_physical_stats and filter on the logical_fragmentation_in_percent column.
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
April 11, 2013 at 5:13 am
thank u sir
Thanks
Naga.Rohitkumar
April 12, 2013 at 6:44 am
Use this sql.
SELECT
B.name AS TableName
, C.name AS IndexName
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent
, A.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B
ON A.object_id = B.object_id
INNER JOIN sys.indexes C
ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D
ON B.object_id = D.object_id AND A.index_id = D.index_id
WHERE C.index_id > 0
and A.avg_fragmentation_in_percent >50
April 13, 2013 at 12:42 am
thanks and i got the same from google and issues is solved if any thing regarding required i will revert back
Thanks
Naga.Rohitkumar
July 11, 2013 at 12:58 pm
@SSChasing Mays
What an awesomely useful query! Thanks for sharing!
July 12, 2013 at 6:14 am
Make sure when you run that query you change the database your in to match the one your querying information about. Otherwise you will get information that might not be correct when the object_id function runs, as object_id is unique for that database.
Joie Andrew
"Since 1982"
December 17, 2013 at 6:45 am
I could not get this to work if the DB is running in SQL 2000 compatibility mode. I think for that the script needs to be changed a bit. Otherwise works well.
December 17, 2013 at 7:41 am
Try this.
SELECT
B.name AS TableName
, C.name AS IndexName
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent
, A.page_count
FROM sys.dm_db_index_physical_stats(null,NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B
ON A.object_id = B.object_id
INNER JOIN sys.indexes C
ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D
ON B.object_id = D.object_id AND A.index_id = D.index_id
WHERE C.index_id > 0
December 17, 2013 at 7:51 am
David Knapp (12/17/2013)
I could not get this to work if the DB is running in SQL 2000 compatibility mode. I think for that the script needs to be changed a bit. Otherwise works well.
Change FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A to use a fixed value for the first parameter rather than the function. The function in parameters is disallowed under compat mode 80.
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
December 17, 2013 at 7:53 am
Neeraj Dwivedi (12/17/2013)
Try this.SELECT
B.name AS TableName
, C.name AS IndexName
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent
, A.page_count
FROM sys.dm_db_index_physical_stats(null,NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B
ON A.object_id = B.object_id
INNER JOIN sys.indexes C
ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D
ON B.object_id = D.object_id AND A.index_id = D.index_id
WHERE C.index_id > 0
That won't work well. With null as the first parameter all databases' index physical stats are pulled, but the sys.objects and sys.indexes are local to the database this is run in, all the other indexes will be filtered out by the inner join or, if the objectID happens to match an object in the current database, return incorrect object names.
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply