November 11, 2009 at 9:31 am
Hello guys first of all thank you for the earlier post that i had made to know all the index with sizes in database. now can someone show me how to see the size of an index in a patricular table in the database.
and yes i also want to see how many times that particular index is being read/write/accessed etc....
November 11, 2009 at 9:33 am
Take a look at sys.dm_db_index_physical_stats and sys.dm_db_index_usae_stats in Books Online. Those should have what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2009 at 9:38 am
but i atleast need a query to see size of index in a table. or can we check from the management studio itself?
November 11, 2009 at 9:46 am
Right-click on the index in Management Studio and go to Properties. It'll have the size in there.
Or, you can select from sys.dm_db_index_physical_stats and get the depth, fragmentation, pages, space used in pages, records, size of records (min, max, and avg). Since pages are 8k each, it's pretty easy to convert to kilo-, meg-, or giga-bytes.
Lots of details available from that function. It's very useful to DBAs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2009 at 9:52 am
but friend once again can get the exact syntax with parameters.
select * from sys.dm_db_index_physical_stats ????
please ...
November 11, 2009 at 10:01 am
Try this:
select *
from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'detailed');
That'll get you all the indexes in the database you are connected to in Management Studio.
If you want only one table, put the ID for that table in there. For example, if you have a table called "dbo.MyTable", in a database named "MyDatabase", it might look like this:
select *
from sys.dm_db_index_physical_stats(
db_id('MyDatabase'),
object_id('MyDatabase.dbo.MyTable'),
null,
null,
'detailed');
The third parameter is the index ID number. If you use that, it'll just give you the specific index (but you have to specify the database and table as well as the index). You can get those by selecting from sys.indexes.
The full syntax and all details of the parameters are in Books Online, or in MSDN. If you Google/Bing the function name, it'll give you the relevant MSDN page.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2009 at 10:01 am
Look it up in Books Online. Far faster than waiting for someone to come along, see your question, look it up in books online and post the answer for you.
http://msdn.microsoft.com/en-us/library/ms188917(SQL.90).aspx
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
November 11, 2009 at 10:06 am
thanks a lot all. appreciate that.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply