April 21, 2010 at 12:32 am
Hi,
Is there any t-sql commands to get the no. of data pages contained by a particular table and the no. of index pages for a index?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 21, 2010 at 1:02 am
I think that you are looking for sp_spaceused. If you’ll run it with a parameter that contains table name, it will give you the (among other things) the amount of space that is used by the table and the tables’ indexes. You can read more details about it in BOL (Books On Line – SQL Server’s help that is installed with the client tools).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
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/
April 21, 2010 at 1:05 am
You can also use
select * from sysindexes
or you can create a join with sys.allocation_units, sys.partitions, sys.indexes and sys.objects to create very detailed information about each partition.
/SG
April 21, 2010 at 4:18 am
Hi Adi,
We got microsoft recommendation like this..
An index that is more than 5 percent or less than 30 percent fragmented should be reorganized. In addition, an index that is more than 30 percent fragmented should be rebuilt.
Priority in both of these processes should be given to indexes which consume 1000 or more data pages.
So I want to know the how many pages an index is containing.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 21, 2010 at 5:54 am
Sumanta-1056020 (4/21/2010)
Hi Adi,We got microsoft recommendation like this..
An index that is more than 5 percent or less than 30 percent fragmented should be reorganized. In addition, an index that is more than 30 percent fragmented should be rebuilt.
Priority in both of these processes should be given to indexes which consume 1000 or more data pages.
So I want to know the how many pages an index is containing.
The information already provided could do what you need. You can also use the old DBCC showcontig, but that's being deprecated.
Instead, I'd recommend using the DMV, sys.dm_db_index_physical_stats. You can query this directly and get exactly what you need. It's much easier to work with than the old DBCC mechanisms and clearer than trying to piece together all the system tables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2010 at 6:30 am
Sumanta-1056020 (4/21/2010)
Is there any t-sql commands to get the no. of data pages contained by a particular table and the no. of index pages for a index?
I would probably use the method mentioned by Grant - sys.dm_db_index_physical_stats...but can I ask why you want to know how many pages there are?
April 21, 2010 at 7:02 am
Paul White NZ (4/21/2010)
Sumanta-1056020 (4/21/2010)
Is there any t-sql commands to get the no. of data pages contained by a particular table and the no. of index pages for a index?I would probably use the method mentioned by Grant - sys.dm_db_index_physical_stats
One problem with sys.dm_db_index_physical_stats is that it can take a fair amount of time and resources to run it on a large table.
Using the system tables i mentioned is much faster, especially if you plan to run it on all the tables in the database.
But on the other hand, if you are really trying to find indexes that would benefit from a reorg sys.dm_db_index_physical_stats is exactly what you need. It will tell you both the number of pages in the index and the fragmentation in percent.
/SG
April 21, 2010 at 7:15 am
Stefan_G (4/21/2010)
Paul White NZ (4/21/2010)
Sumanta-1056020 (4/21/2010)
Is there any t-sql commands to get the no. of data pages contained by a particular table and the no. of index pages for a index?I would probably use the method mentioned by Grant - sys.dm_db_index_physical_stats
One problem with sys.dm_db_index_physical_stats is that it can take a fair amount of time and resources to run it on a large table.
Using the system tables i mentioned is much faster, especially if you plan to run it on all the tables in the database.
But on the other hand, if you are really trying to find indexes that would benefit from a reorg sys.dm_db_index_physical_stats is exactly what you need. It will tell you both the number of pages in the index and the fragmentation in percent.
/SG
It is a tad costly, but not like the DBCC check.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2010 at 7:24 am
Stefan_G (4/21/2010)
One problem with sys.dm_db_index_physical_stats is that it can take a fair amount of time and resources to run it on a large table.
This is true, but some quite widely-used automated scripts use this method (Michelle Ufford's is one example) quite happily.
Using the system tables i mentioned is much faster, especially if you plan to run it on all the tables in the database.
If you just want the page sizes and no other data, then yes, I might use system metadata - but not the deprecated sysindexes 😛
But on the other hand, if you are really trying to find indexes that would benefit from a reorg sys.dm_db_index_physical_stats is exactly what you need. It will tell you both the number of pages in the index and the fragmentation in percent.
Quite. 🙂
April 21, 2010 at 7:31 am
Grant Fritchey (4/21/2010)
It is a tad costly, but not like the DBCC check.
LIMITED mode just scans level 1 of the index structures (not the leaf) - or just the IAM and PFS pages for a heap - and so is usually pretty swift.
April 21, 2010 at 7:41 am
Paul White NZ (4/21/2010)
Stefan_G (4/21/2010)
One problem with sys.dm_db_index_physical_stats is that it can take a fair amount of time and resources to run it on a large table.This is true, but some quite widely-used automated scripts use this method (Michelle Ufford's is one example) quite happily.
Using the system tables i mentioned is much faster, especially if you plan to run it on all the tables in the database.
If you just want the page sizes and no other data, then yes, I might use system metadata - but not the deprecated sysindexes 😛
The only reason I mentioned sysindexes is that it is the easiest way to get the information the OP asked originally asked for. 🙂
LIMITED mode just scans level 1 of the index structures (not the leaf) - or just the IAM and PFS pages for a heap - and so is usually pretty swift.
And, if what you are trying to do is to create a report of the 10 most expensive tables and indexes in terms of size, using sys.dm_db_index_physical_stats is a really bad idea.
Running such a report on my fairly large data warehouse can take 20 minutes of intense disk activity. And yes, I use LIMITED. :w00t:
Basing the report on the appropriate catalog views makes it instant. 😎
/SG
April 21, 2010 at 7:52 am
Paul White NZ (4/21/2010)
Grant Fritchey (4/21/2010)
It is a tad costly, but not like the DBCC check.LIMITED mode just scans level 1 of the index structures (not the leaf) - or just the IAM and PFS pages for a heap - and so is usually pretty swift.
Don't get me wrong, I'm advocating it's use, very much. But I've seen it be slow. It's still better than the DBCC.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2010 at 8:04 am
Stefan_G (4/21/2010)
The only reason I mentioned sysindexes is that it is the easiest way to get the information the OP asked originally asked for.
Fine. The only reason I mentioned that it is deprecated and only provided for backward-compatibility, is that it is 🙂
And, if what you are trying to do is to create a report of the 10 most expensive tables and indexes in terms of size, using sys.dm_db_index_physical_stats is a really bad idea.
Which part of the phrase "I would probably use the method mentioned by Grant" means "always use it" to you?
Running such a report on my fairly large data warehouse can take 20 minutes of intense disk activity. And yes, I use LIMITED.
Um, we aren't discussing your warehouse...
Basing the report on the appropriate catalog views makes it instant.
If, as I already said, all you want is a page count. I don't think anyone has disagreed with this - just the appropriate view to use...
April 21, 2010 at 8:12 am
Thank you all for the reply.
Anybody knows T-SQL commands to get the data pages for a particular table.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 21, 2010 at 8:21 am
Paul White NZ (4/21/2010)
And, if what you are trying to do is to create a report of the 10 most expensive tables and indexes in terms of size, using sys.dm_db_index_physical_stats is a really bad idea.
Which part of the phrase "I would probably use the method mentioned by Grant" means "always use it" to you?
Running such a report on my fairly large data warehouse can take 20 minutes of intense disk activity. And yes, I use LIMITED.
Um, we aren't discussing your warehouse...
Basing the report on the appropriate catalog views makes it instant.
If, as I already said, all you want is a page count. I don't think anyone has disagreed with this - just the appropriate view to use...
Hey, take it easy.
The only thing I am trying to say is that in some cases there can be serious performance problems with sys.dm_db_index_physical_stats. 🙂
You probably already knew this, but the the OP and everyone else reading this might not.
/SG
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply