Reading results from sys.dm_db_index_physical_stat

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Reading results from sys.dm_db_index_physical_stat

Author

Topic

bryan42
Starting Member

28 Posts

Posted - 2007-03-06 : 13:04:19

Could you help me understand the results of running sys.dm_db_index_physical_stats?

select top 10 * from sys.dm_db_index_physical_stats(db_id('web_db1'), OBJECT_ID('tblUser'), NULL, NULL, 'DETAILED');

I see this table. Two HEAP indexes seem to have issues. Shall I simply rebuild the index for this table. How may I convert a table to have a clustered index if it has lots of transactions?


obj_id | avgFrag% | frag_cnt | avgFragPages | avgPageSpace% | recCnt | index_type
21575115	0	1	1	19.25	18	HEAP
21575115	0	1	1	3.31	18	NONCLUSTERED INDEX
47339233	68.21	122	9.06	94.6	222716	HEAP
47339233	0	19	26.16	99.63	222716	NONCLUSTERED INDEX
47339233	0	1	2	73.66	497	NONCLUSTERED INDEX
47339233	0	1	1	0.57	2	NONCLUSTERED INDEX
47339233	0	21	24.9	99.94	222716	NONCLUSTERED INDEX
47339233	0	1	2	80.74	523	NONCLUSTERED INDEX
47339233	0	1	1	0.59	2	NONCLUSTERED INDEX
53575229	33.33	5	19.2	98.96	23306	HEAP

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-03-09 : 12:30:51

In SSMS, go to the table in that database. Right click on the indexes and create a new one.

bryan42
Starting Member

28 Posts

Posted - 2007-03-09 : 20:42:44

Thanks. Is there an easy way to use an object ID to obtain the table name?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources