May 15, 2011 at 1:06 pm
Use MyDb
Go
select p.database_id,p.object_id,p.index_id,p.index_type_desc,
p.page_count,p.avg_fragmentation_in_percent,o.name objectnames,o.type_desc,o.modify_date
from sys.dm_db_index_physical_stats (17,NULL,NULL,NULL,NULL) p
join sys.objects o
on o.object_id=p.object_id
where page_count>1000 and avg_fragmentation_in_percent>30
order by avg_fragmentation_in_percent desc
May 15, 2011 at 1:27 pm
sqldba_icon (5/15/2011)
select p.database_id,p.object_id,p.index_id,p.index_type_desc,p.page_count,p.avg_fragmentation_in_percent,o.name objectnames,o.type_desc,o.modify_date
from sys.dm_db_index_physical_stats (17,NULL,NULL,NULL,NULL) p
join sys.objects o
on o.object_id=p.object_id
where page_count>1000 and avg_fragmentation_in_percent>30
order by avg_fragmentation_in_percent desc
Try using the following:
For better, quicker answers on T-SQL questions, click on the following...
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/
May 15, 2011 at 2:21 pm
Thanks. Doesn't help much. I need to know number of pages,object name also. What do you think about my query?
May 15, 2011 at 2:26 pm
sqldba_icon (5/15/2011)
Thanks. Doesn't help much. I need to know number of pages,object name also. What do you think about my query?
Have your tried including an the INNER JOIN on the sys.indexes Table?
Please look more closely at the example and feel free to experiment.
For better, quicker answers on T-SQL questions, click on the following...
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/
May 15, 2011 at 3:34 pm
copy / paste, setup daily runs and you're done.
May 15, 2011 at 11:59 pm
Check this one
---- Query to find the Fill Factor and index fragmentation
SELECT
db_name() AS DbName,
SCHEMA_NAME(B.schema_id) AS SchemaName
, B.name AS TableName
, C.name AS IndexName
, A.INDEX_DEPTH as Index_depth
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent
, A.page_count
, GetDate() as [TimeStamp]
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.INDEX_DEPTH >2
ORDER BY A.avg_fragmentation_in_percent DESC
Thank You,
Best Regards,
SQLBuddy
May 16, 2011 at 12:02 am
Ninja's_RGR'us (5/15/2011)
copy / paste, setup daily runs and you're done.
+10
No sense in re-inventing the wheel when someone's already done so and ironed out the bugs for you.
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
May 16, 2011 at 11:31 am
Ninja's_RGR'us (5/15/2011)
copy / paste, setup daily runs and you're done.
I didn't see anything on Michelles Post about the script but do you know if anyone has converted this to work with SQL 2000 OR at least with a DB on SQL Server 2005 that is seto to a compatability level of 8.0?
Thanks
Kindest Regards,
Just say No to Facebook!May 16, 2011 at 12:39 pm
I don't think compatibility level would affect it. It should work .... give it a try.
If you are running on a SQL 2000 instance, then you would need to find a script written for 2000.
May 16, 2011 at 12:55 pm
homebrew01 (5/16/2011)
I don't think compatibility level would affect it. It should work .... give it a try.If you are running on a SQL 2000 instance, then you would need to find a script written for 2000.
Unfortunately it does affect it. I have a SQL Server 2005 instance with 2 non-SYSTEM DB's. One is set to a acompatability bative to 2005 and teh otehr is set to 80.0 for backwards compatability with 2000 (per requirements of the software vendors whose app accesses that DB).
If I run the code in my 2005 DB there are no errors. When I try to use it the DB with a compatability set to 8.0 I get an error in the Object_ID() function located within the line..
sys.dm_db_index_physical_stats(@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode) AS ps
the thing does not like the use of Object_ID() and without that dynamic capabaility to specify the tables ID the script can only be run for a single table without some heavy re-write.
Thanks
Kindest Regards,
Just say No to Facebook!May 16, 2011 at 2:22 pm
I don't have a server to test but you should be able to set the object id in a variable and use it there.
May 16, 2011 at 8:43 pm
It doesn't work if there is a 80 compatibility mode database. It gives an error for DB_ID() function. If you manually enter the Database ID in its place it will work.
But still it will not return correct information as the DMVs are new to SQL 2005. That's the inherent limitation.
For SQL 2000 dbs you need to use...DBCC SHOWCONTIG
Thank You,
Best Regards,
SQLBuddy
May 16, 2011 at 11:18 pm
sqlbuddy123 (5/16/2011)
It doesn't work if there is a 80 compatibility mode database. It gives an error for DB_ID() function. If you manually enter the Database ID in its place it will work.
It should work if run from a compat mode 90 DB (like master) and pointed at the DB in question. It's only the compat mode of the current DB that's checked, not the one the queries refer to.
But still it will not return correct information as the DMVs are new to SQL 2005. That's the inherent limitation.
Why is this a limitation? The DBs are on SQL 2005, not 2000. Compat mode 80 does not make DMVs unavailable.
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
May 17, 2011 at 7:08 am
DBID () func in the DMV will return in a error for 80 comp Mode dbs. It doesn't return complete information for 80 comp mode databases. May be it's due to the difference in the architecture and how DMVs work. It's based on my past experience.
Thank You,
Best Regards,
SQLBuddy
May 17, 2011 at 7:25 am
sqlbuddy123 (5/17/2011)
DBID () func in the DMV will return in a error for 80 comp Mode dbs. It doesn't return complete information for 80 comp mode databases. May be it's due to the difference in the architecture and how DMVs work.
However it works fine if run from a compat mode 90 database (like master) and pointed at a compat mode 80 database. There's no limitation on the DMVs in compat mode 80, just in the passing of a non-constant to a function.
Try it
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 24 total)
You must be logged in to reply to this topic. Login to reply