January 28, 2011 at 6:57 am
Hi,
I have to create a new database .
I need around 15 Tables to be created .
2 of the Tabled have around 80 columns .
Rest of them have 5-10 columns .
The total rows of all the Tables would be around 5,00,000 .
My Approach :
I checked the size of bytes on each column of the table and summed it up of the total row.
And multiplied the number to total no of rows(approximate rows) .
That would be the total size of the data on the Table . SO, the total of all the tables will give the number .
I will calculate the index size and add it to the Data size. So, this will be the total estimate .
Please correct me if I am wrong ...
ANd
Can anyone give me an easier approach to analyze (or) how to do an estimate of the Database ?? And how do we calculate the index size ??
Regards
Kay
January 28, 2011 at 7:00 am
This is roughly what to do. There is some overhead, but once you get to millions of rows, it doesn't matter.
Don't forget that the clustering key is in all indexes, so if it's not listed as a column, it's still there.
January 28, 2011 at 7:06 am
- You should also estimate how many rows you expect to fit into a single page (8060 bytes) and project that to your full set.
- if you designed your system to use uniqueidentifiers for clustering key :sick: prepare for huge fragmentation. +50%
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 31, 2011 at 3:36 am
Thank you all for the Info..
Regards
Kay
January 31, 2011 at 4:44 am
i'd fill test tables with 100000 rows then use that to multiple out to 5,000,000 rows (i think you said 5MM). i mean to populate with real data (maybe you source can provide a test file), because often there are going to be columns which are not fully populated especially 80 column tables. if you consider every column contains data, you get an extra high estimate.
January 31, 2011 at 7:17 am
One last point, make sure you have free space in the files. You should not be growing SQL Server data files every time you add data. You want enough space in there to handle the data growth for 1-4 months at a time.
February 1, 2011 at 3:49 am
I would go a bit beyond Steve's advice about growth.
Certainly you must add enough free space to your database to allow maintenance tasks to work. If you are doing your index rebuilds with a simple DBCC DBREINDEX() then you will need the same amount of free space as table space. If you do a more intelligent process of only rebuilding indexes for the tables that need it then you only need enough free space to hold a copy of your largest table. After you work out this base amount of free space, add maybe 10% to the free space value as a contingency.
Many installations work on the principle that a new application should not need any additional hardware resource for a full year after initial installation. This means you should calculate the expected growth of your DB and ensure you order enough disk space, CPU power and memory to cope after 1 year of use. Certainly you should not expect production support staff to be finding extra space for your database every few weeks - this is a very poor use of an expensive resource.
Estimating growth can be tricky but you must do this. Most places I have worked in have a 3 to 6 month lead time between a DBA saying 'we need more disk' and the space being made available for the DBA to use. Your manager typically has to persuade their manager to raise a capital expenditure request, which then needs approval. The disk vendor then has their own lead time to deliver. Then it has to be installed and formatted into the appropriate RAID groups. Finally the storage administrator has to carve it into LUNS, attach them to your server and let you use them.
Having worked out what space you need for the database, don't forget to calculate the space needed for backups. The number of backups on disk should comply with site policy, so don't allow for just 1 if your policy says you need 3. If your DB will use Full Recovery mode then you also need to allow space for log backups.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 1, 2011 at 4:12 am
Can anyone give me an easier approach to analyze (or) how to do an estimate of the Database ?? And how do we calculate the index size ??
by default if you do not mention the size of the data/log file, it will be created with similar size of model.
Growth you can estimate by pre and post run of sp_spaceused.
As well there are scripts available on this website which can give you the growth information order by tablesize/indexsize.
----------
Ashish
February 2, 2011 at 6:23 am
Additional comments:
1) I advise my clients to size a database to contain 12-18 months worth of data and evaluate monthly or at least quarterly.
2) Recall that the clustering key is carried as the row pointer on every nonclustered index you create. So if you make a fat clustered index (don't forget the 4-byte uniquefier if the clustered index isn't unique) you get a bloated nonclustered index.
3) All indexes should NOT have a default fill factor. Each should have a fill factor appropriate for what it contains and how it is used from a DML perspective.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply