February 10, 2015 at 9:48 am
Hi,
I have a couple of questions as a matter of fact.
We are running very small databases at this moment. All of our dbs in a server count to about 28 GB right now. However, we are planning to grow four-folds in the next two years. That means the db size will be now 112 GB. That means our data files also grow four folds, but do the log files and tempdb also need to grow in the same ratio??
As of now, we have 8 processor cores with hyperthreading enabled. The memory being allocated to SQL Server is 22GB and the total server memory is 32GB. Do we need to increase the CPU and memory? If yes, then by how much?
February 10, 2015 at 2:30 pm
If I were you, I will gather a statistic (memory usage, etc...) as a baseline then evaluate from the info you got.
February 10, 2015 at 10:14 pm
Srl832 (2/10/2015)
Hi,I have a couple of questions as a matter of fact.
We are running very small databases at this moment. All of our dbs in a server count to about 28 GB right now. However, we are planning to grow four-folds in the next two years. That means the db size will be now 112 GB. That means our data files also grow four folds, but do the log files and tempdb also need to grow in the same ratio??
As of now, we have 8 processor cores with hyperthreading enabled. The memory being allocated to SQL Server is 22GB and the total server memory is 32GB. Do we need to increase the CPU and memory? If yes, then by how much?
In general I would say that the system you have now should be able to handle the growth you are projecting. You should consider things like ensuring that your log files and primary data files are on separate drives for better long term performance. If tempdb is very active then you could consider putting it on its own drive. another very important factor is additional space to back up your databases as they grow. If you are on at least SQL server 2008R2 then you can use native backup compression and that will help a lot in terms of managing disk space for backups.
As previously suggested, I would start collecting a baseline now so you can see what things look like.There are lots of articles on baselining. I would probably start with perfmon and throw it into some tables using the Windows RELOG command so you can query it. If your company will purchase third party software to do baselining of performance then that is a great option.
Your log file growth is going to be dependent upon a number of things. I would recommend regular querying of sys.dm_io_virtual_file_stats to get a picture of the read and write activity as it stands now and trend it over time. The size of your log file also depends on things like your recovery model. For example, in simple mode part of the log file is going to be marked for re-use at every full backup. If you have a lot of modification queries and your log file is already small then you will start seeing expansion of the log file in between backups. In full recovery mode you will need to take regular transaction log backups in order to manage the size. I would encourage you to look at Kimberly Tripp's articles on transaction log management.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply