Size of table

Question

Size of table

caz100

SSCommitted

Points: 1827
More actions
September 23, 2020 at 4:49 pm

#3790434
Hi
This probably a silly question but.....
If I query the size of a table and it shows 20 gigs in size, when I do a select * from that table from another server will I bring back 20 gigs over the network?
thanks in advance
- This topic was modified 4 years, 1 month ago by caz100.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

More or less, eventually, depending on whether you're looking at raw or compressed data size... And assuming your client doesn't time out or crash from an out-of-memory condition.

Once client-side, the data may balloon significantly larger in a client like SSMS which formats the data.

Sergiy SSC Guru Points: 110208 More actions · Answer 2

The table size usually includes the size of all indexes created on the table.

for SELECT * query you need to know the size of the index with indid=1 or 0.

_____________
Code for TallyGenerator

caz100 SSCommitted Points: 1827 More actions · Answer 3

Thanks can you explain the index = 1 or 0 please ?

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 4

caz100 wrote:

Hi
This probably a silly question but.....
If I query the size of a table and it shows 20 gigs in size, when I do a select * from that table from another server will I bring back 20 gigs over the network?
thanks in advance

You've not explained what you used to "query the size" of the table. To Sergiy's good point, you might be including all of the indexes, which could be as much or more than the data in the "table". I have table in quotes because if it's a HEAP, you need to look at Index 0 (which is the HEAP itself). If it has a Clustered index, you need to check the size of Index 1, which is the Clustered Index and that's where the data in a "clustered table" lives.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

carsten.saastamoinen Old Hand Points: 341 More actions · Answer 5

You can get the information from this select-statement. It shows the size of the table and the size of indexes in 8KB pages.

SELECTi.name,  
ps.index_type_desc,
ps.index_id,
ps.index_level,
ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID('databasename'), OBJECT_ID('tablename'), NULL, NULL, 'DETAILED') AS ps 
INNER JOIN sys.indexes AS i
ON ps.object_id = i.object_id AND ps.index_id = i.index_id;

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 6

It's a shame that the OP didn't explain how they determined the table size.

carsten.saastamoinen wrote:

You can get the information from this select-statement. It shows the size of the table and the size of indexes in 8KB pages.
SELECTi.name,  
ps.index_type_desc,
ps.index_id,
ps.index_level,
ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID('databasename'), OBJECT_ID('tablename'), NULL, NULL, 'DETAILED') AS ps 
INNER JOIN sys.indexes AS i
ON ps.object_id = i.object_id AND ps.index_id = i.index_id;

Yes, you can certainly do it that way but, if the table is large, that can take an awfully long time and use quite a few resources. With that being said, consider using the following, instead. It's nearly instantaneous and is a whole lot more lightweight insofar as system resources.

 SELECT  SchemaName = OBJECT_SCHEMA_NAME(object_id)
        ,ObjectName = OBJECT_NAME(object_id)
        ,SizeMB     = SUM(reserved_page_count /128.0) --There are 128 pages per MB.
   FROM sys.dm_db_partition_stats 
  WHERE object_id = OBJECT_ID('SomeSchemaName.SomeObjectNameHere')  --Schema name and object name here
    AND index_id IN (0,1) --Heap or Clustered Index, which is where the data lives (comment out to include all indexes).
  GROUP BY object_id
;

The reason I made the previous post is that I had hoped the OP would respond with more detail because this particular DMV contains information about Reserved, Used, In Row, LOB, and Out of Row information and the OP also didn't mention if he wanted to include the indexes or not.

Depending on what's in the table and how the OP determined the size, they may be quite surprised when they do their SELECT * especially if they have any LOBs or other out-of-row things going on because they may have not taken it all into account.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

caz100 SSCommitted Points: 1827 More actions · Answer 7

Thanks for your responses. The internal report on top table size was sufficient for me

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 8

.. post deleted. Didn't see the OP's latest post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 9

... post deleted. Accidental duplicate.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)