Using sys.dm_db_index_physical_stats

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 2008 Forums

SQL Server Administration (2008)

Using sys.dm_db_index_physical_stats

Author

Topic

tpowell_3557
Starting Member

9 Posts

Posted - 2010-09-28 : 10:30:53

I'm trying to get the fragmentation on only a single index using the following code:

Select *
From sys.dm_db_index_physical_stats
(DB_ID('MyDBName'),
OBJECT_ID('MyTableName'),
1 ,
NULL,
N'Limited')

Where 1 is the desired index. This code works if the third parameter is NULL but with a "1" I get the following error:

Msg 2561, Level 16, State 11, Line 1
Parameter 3 is incorrect for this statement.

Any help would be appreciated and thanks for your time.

Tom

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-28 : 10:50:02

Does 'MerchantProcessorActivity' have a clustered index?

--
Gail Shaw
SQL Server MVP

tpowell_3557
Starting Member

9 Posts

Posted - 2010-09-28 : 11:16:16

Yes, it has a clustered index, that's the "index" (#1) I want info on.

Thanks,

Tom

Tom

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-09-28 : 11:21:45

Try substituting null for the third parameter for DMF and see what are the values it returns

tpowell_3557
Starting Member

9 Posts

Posted - 2010-09-28 : 11:57:06

It works fine with NULL but I'm not sure what that is for, the clustered index? The Index_ID for the clustered index is in fact 1. Also, though there is an index with Index_ID = 2 on that table using a 2 returns the same error.

Thoughts?

Tom

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-28 : 15:35:36

Are you in the correct database when you run that? Remember Object_name is current database only unless you use both parameters. If you're in the wrong DB, that function will return NULL and, when the 2nd parameter is null the third must be null.

Null for the 3rd parameter means all indexes on the specified table.

--
Gail Shaw
SQL Server MVP

tpowell_3557
Starting Member

9 Posts

Posted - 2010-10-01 : 11:37:13

I was in the wrong DB, THANKS! I guess I thought since I supplied the DB name I was good, but it makes sense that Object_ID would require the right DB. Subtle. Thanks again!

Tom

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources