5

I have been asked this question in an interview that how much would be the DB size for a standalone SQL server if 650MB of data is processed every month. I couldn't give any answer to this question in my interview and later on didn't find any answer either. Any help would be highly appreciated.

13

This is an open-ended interview question that doesn't have a precise answer. The interviewer might expect you to ask additional questions for clarity.

Actual space requirements will depend on what is meant by "processed" (inserted?), data retention (are data ever deleted?), column data types, indexes, storage overhead, use of compression features (including columnstore), plus additional free space for routine maintenance.

4
  • Thanks Dan, for the reply. I should have indeed asked them questions for further clarity, but I never actually dealt with this before. So, if we are trying to install a new standalone server, then how can we determine this deciding factors before hand. Will that be depending on some previous setup experience or is there any starting point? In my interview they said if they want to set a new standalone server, what would I do?
    – dbt
    Feb 24 '21 at 12:44
  • 1
    @dbt, there are many factors to consider in the real world to create a properly sized server and, yes, prior experience helps greatly. Besides storage, one needs to consider memory, CPU, infrastructure capabilities, and expected workload (OLTP, reporting, mixed, number of concurrent queries, etc.). Initial sizing is only best guess and it helps if the infrastructure is flexible enough to easily adjust resources later. Private or public cloud helps in that regard.
    – Dan Guzman
    Feb 24 '21 at 13:11
  • Thanks Dan. I will keep these factors in mind. And would get back , if any further query arises.
    – dbt
    Feb 24 '21 at 13:29
  • “Doesn’t have a precise answer” is pretty generous. Feb 25 '21 at 4:01
11

These kind of questions are stupid interview questions in my opinion, and definitely need more context. Perhaps they just want to see your thinking and what follow up questions you'd ask for clarity.

If by processed they mean 650 MB of data is added every month (and even here would be an assumption that the added 650 MB is data + all indexes + any other supplemental objects consuming space), and there's no other context given so we have to assume they mean purely the database's total data file sizes (not the Transaction Log files or anything else - since those would depend on unknown variables like recovery models and backup frequency) then the size of the database is 650 MB times the number of months it's been online.


As discussed in the comments: In the simplest example, if a brand new database is created with Full Recovery Model, it has no indexes (or anything else that would cause duplication of data), and 650 MB of data is written to it. At that moment in time time about 1.3 GB of disk space is currently being consumed, 650 MB for the data and 650 MB for the transaction that inserted the data into the database and is now sitting in the Transaction Log. Because they didn't provide any of those additional details in my most simplest of examples, you can only assume they mean the raw data at best, which is 650 MB at this point in time.

But you can see how variable it can get fast, even in a simple example. Even in that example I assume by the word processed they mean added. But processed could mean 350 MB were added and then 300 MB were deleted. Now the database size (for just the raw data) is only 50 MB. In my previous example, the total disk space consumed would be about 700 MB because there is 650 MB of transactions that occurred and sitting in the transaction log plus an additional 50 MB of actual data in the database at this point in time.

If we knew more information such as how often the Transaction Log is backed up, then that might change the answer above as well. For example, if Transaction Log backups occurred every 5 minutes, and if 350 MB of data was inserted into that same example database above then 7 minutes later 300 MB was deleted, the total space consumed on disk is currently 350 MB. 50 MB of actual data + 300 MB currently sitting in the Transaction Log for the deletion operation. The initial insert operation's transaction is gone at this point in time because after a backup of the Transaction Log occurs, the transactions get flushed from it, and the space they were previously consumed gets re-used by new transactions.

So unfortunately there's a lot of ways this question can be interpreted, and it's best to show your knowledge in the interview by asking the right follow up questions such as "Are we talking only database file data or Transaction Log data as well?", "What kind of Recovery Model is in place?" (Simple vs Full Recovery Model will result in a different amount of data being stored in the Transaction Log), "What type of backups are scheduled and how often are they ran?", "Does the database have indexes, and what sizes do they consume for 650 MB of database data?" etc. If the interviewer knows what they're doing then those kind of follow up questions will show them it's not the actual answer to the question that matters, because you demonstrated your competency by asking what is necessary to know to be able to solve that kind of question.


To your last question on increasing the available Disk Space and Memory on the fly: yes it's definitely possible to add more Disk Space and Memory on the fly, and even while keeping the database online. The way to actually do it will depend on your server host, whether it's an on-prem server or in the cloud, a physical server or a VM, and the providers of each when applicable.


Finally here are some blogs I find are really helpful (in no particular order):

  1. Josh Darnell's Blog
  2. Brent Ozar Team's Blog
  3. Erik Darling's Blog
  4. Aaron Bertrand's Blog on SQLPerfofmance.com
  5. Aaron Bertrand's Other SQL Blog
  6. Kendra Little's Blog
  7. RedGate's Simple Talk Blog
17
  • Thanks a lot J.D. Because if they would have asked me about a flat file storage or about any other volatile storage, I could have made an attempt to answer. But disk size and DB size of a SQL server was literally out of my scope.
    – dbt
    Feb 24 '21 at 12:32
  • So,by this additional attributes like indexes, consuming space, the number of transactions that the database needs to store can be predefined or preconceived before getting into action? And what you mentioned later is 650MB*No of months, what if the server is new, can we multiply 650*(no of months the server would be kept active?
    – dbt
    Feb 24 '21 at 12:38
  • 1
    You are right. I would work a bit on this topic for sure.
    – dbt
    Feb 24 '21 at 12:57
  • 1
    Now things are wide clear. Do you write any blogs? Then I would surely like to follow for such intuitive topics. Thanks again J.D.
    – dbt
    Feb 24 '21 at 13:00
  • 1
    thanks again. Those links would be really useful:). I saw your modified answer, thanks for your immense efforts.
    – dbt
    Feb 24 '21 at 13:36
5

There is no single right answer to a vague question like this, and the interviewer isn't expecting one (if they know anything themselves of course). They are looking to test how you realise the question is too vague and how you might start trying to answer such a question in a real situation - to see if you understand what extra details would be needed and how you might go about asking for or finding those details from the documentation, dev team, or by inspecting an existing database.

The only wrong answers are no answer (including a straight "I don't know") or the naive one (650MB * months) without any caveats.

I would probably answer a question like this with something like

The naive answer would be to just multiply that 650Mb by the number of months we expect the service to run, assuming that amount includes index growth & other side costs, and that we are keeping all the incoming data each month, but to give a proper estimate we need to know how much of the processed data is new, or updates for existing data, or to be discarded as it is already present but being resent by an external system, and we'd need to know more detail of how the data is stored and indexed which would have significant effects on the storage requirements. Also how the data is processed will add other temporary storage needs due to work tempdb and transaction log growth.

That tells them you know the question is vague, that you know what the naive and probably incorrect answer is (so you'd recognise such an answer coming from elsewhere), and that you know the sort of detail needed in order to progress towards a useful estimate. They may then ask you to expand on some of those points, or ask you more specific questions branching of from what you've said.

8
  • Thanks David. I couldn't answer because I wasn't knowing what to answer. My fault for sure. But thanks for simple and but concise explanation. I really appreciate. A question related to your explanation: can the space required for TempDB could be a deciding factor as well?
    – dbt
    Feb 24 '21 at 14:23
  • 1
    @dbt - I wouldn't say "fault". Simply knowledge you didn't yet have which you have since taken the effort to attain. Growth is good! As a general thing interviewers will use general/vague questions to get you talking about what you know or have experienced, and if you are looking for DB related jobs sizing can be very important (particularly as a DBA but also as a dev). TempDB is a "side point" to this question. Something to make them know you are aware of rather than go into in detail. Processing a lot of data in one go could spool chunks of stuff to there so it needs appropriate space too. Feb 25 '21 at 10:24
  • 1
    @dbt - also I'd second following the sources J.D. linked to - even if you only skim them most of the time it'll help you be aware of what your unknowns are (such surface information means you at least know what to look for if deeper knowledge is needed). Also if something on there seems confusing, try take time to play with the concept yourself, start with their examples then make up your own on a local instance (the full developer edition is free these days), such experimentation can make things stick in your head better than just reading about the concepts. Feb 25 '21 at 10:40
  • Thanks David. These valuable suggestions would surely help me to grow my knowledge and skills. I would surely play with different example sets. This is indeed a great platform where I could communicate and get experts advice. Thanks a ton again..
    – dbt
    Feb 25 '21 at 11:26
  • @dbt Hopping in the comment thread here because David makes a great point regarding the resources I linked. I often am quick to recommend resources but forget to mention how I've learned from them. Only a couple years ago I knew almost nothing on how SQL Server actually worked, I was a very basic database developer that could craft a query but had no idea how that query was processed, what happens under the hood, and how to manage SQL Server. Then one day I started listening to Brent Ozar's "Office Hours" SQL podcast, basically where people would write in their SQL questions and his team...
    – J.D.
    Feb 25 '21 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.