Diskspace requirement for 1M records
-
Hello Team,
I have a requirement where I need to see how much space is used per each record for each database and I would like to know if how much disk space is required for 1M records to be inserted.
Thx, - DBA_007 - Tuesday, February 19, 2019 9:08 AM
It depends on the columns on the row, how much data is in those columns (for nvarchar and varchar) , what indexes you have on the table etc. You need to get a rough idea but after that an easy way to get the size is to use one of the standard reports on the database "Disk Usage By Top Tables", this gives details of the space used by the data, the space used by the indexes and the number of rows on the table.
-
Thanks,
Can you explain with an example -
Just create the table, populate it with as many realistic rows as you can then run the report.
To run the report you just right click on the database in object explorer, as in the picture below:
-
There are many, many articles addressing just this out there in the GoogleVerse.
SQL Calculate table row size
But you can do this by hand as well. For each field, look at the data type and the amount of space required for that data type.
SQL data types storage size
For instance, let's say you have 3 fields, ID (int), StateID(char(2)) and CityName(varchar(20)).
An int requires 4 bytes
The char field has been set to 2, so it requires 2 bytes
The varchar field is variable length, but to calculate what the row size could be, you have to use worst case scenario that all 20 bytes could be used, so, 20 bytesThis gives us a row size of 26 bytes.
To confirm:
CREATE TABLE SizeTest (IDn INT, StID CHAR(2), CityName VARCHAR(20))
SELECT SUM(c.length) SizeOf from dbo.SysColumns c WHERE c.id = object_id('SizeTest') -
Thank you, could you also let me know how to calculate index size and how much space is needed for index drive?
- DBA_007 - Tuesday, February 19, 2019 12:14 PM
This documentation has links to estimate the size of a table, heap, clustered index and nonclustered index:
Estimate the Size of a DatabaseSue
-
Thanks all,Can you explain with an example to calculate index size and how much space is needed for index drive.
The document is too heavy. -
also take in consideration that having compression turned on (page or row, or even columnstore) will change the space requirements. many times significantly, specially with columnstore.
- DBA_007 - Tuesday, February 19, 2019 12:58 PM
Jeepers, I don't want to come off as a jerk, but if you are a DBA, you really need to take what has been given above and figure it out. The research into this will benefit your skills. I believe this is one of the foundational principles of being a DBA ... not necessarily knowing this information immediately, but taking the knowledge shared by others (above) and applying it to your situation.
All the information you need is above. It is now your turn to take this, research and build your answer.
This will make you a better DBA overall.
-
Sorry... posted on the wrong thread.
--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)
Intro to Tally Tables and Functions - DBA_007 - Tuesday, February 19, 2019 12:58 PM
You really need to get used to documentation instead of complaining that the "document is too heavy" especially if you're going to carry "DBA" as part of your handle. 😉
Now, with the idea of teaching a man to fish... run the following code and study the data in all the columns returned as well as the names of the columns.
SELECT * FROM sys.dm_db_partition_stats;
Then, do an internet search for "sys.dm_db_partition_stats" and read about it because it has everything you need to do your calculations except for the hint that if you divide the number of pages something uses by 128.0, it will return the size of those pages in Mega-Bytes.I'm not going to do the whole thing for you because, like Davis suggested, it's time for you to put your shoulder to the wheel and learn something new but I will give you a head start. You can run the following code by itself to see what it does. Then, put that code into a CTE and build the outer query to do all of the simple arithmetic that you're talking about.
SELECT DBName = DB_NAME()
,SchemaName = OBJECT_SCHEMA_NAME(ps.object_id)
,ObjectName = OBJECT_NAME(ps.object_id)
,DataSizeMB = SUM(CASE WHEN ps.index_id <= 1 THEN reserved_page_count/128.0 ELSE 0 END)
,IndexSizeMB = SUM(CASE WHEN ps.index_id >= 2 THEN reserved_page_count/128.0 ELSE 0 END)
,TotalSizeMB = SUM(reserved_page_count/128.0)
,RowCnt = SUM(CASE WHEN ps.index_id <= 1 THEN row_count ELSE 0 END)
FROM sys.dm_db_partition_stats ps
WHERE OBJECT_SCHEMA_NAME(ps.object_id) <> 'sys'
GROUP BY ps.object_id
;Once you're done with the modifications I suggested to get your data/index calculations out of that, you should study all of the functions I used, read the following article about CrossTab queries (http://qa.sqlservercentral.com/articles/T-SQL/63681/), and study why I used <=1 and >=2 to discriminate against the type of indexes by index_id.
Then lookup "sp_msforeachdb" for your next question. 😉 Either that or lookup "sys.databases" and figure out how to write your own.
--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)
Intro to Tally Tables and Functions
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply