SQL Server Interview Question – How to handle large number of insertions in SQL Server?

Q. We are going to get 1 lac records per second in our database like we have in stock market example. So how do you design database for this kind of system.

Answer – Its been a while that I have not published anything. Actually I was busy in doing research on the answer of the above question. I was also thinking whether it is good to ask these kind of question in a half an hour telephonic interviews or not. The reason I am mentioning this because it is a very board and very deep topic and definitely cannot be answered completely in half an hour.

Any ways lets jump to the answer of the above question. So as I explained above it is very broad and deep topic. It involved some specialized hardware. And with in house developers it is not at all possible. People usually call vendors for help like EMC.

Key components we need in this kind of requirement are Hardware resources and software resources.

Software Resources are-

1. EMC Xtreme IO
2. VMware vSphere
3. vMware Centre
4. Microsoft SQL Server
5. Microsoft BenchCraft TPC-E Tool kit

EMC’s Xtreme storage array is an all flash system based on scale out architecture. The system uses building blocks, called X-Bricks, which can be clustered together to grow performance and capacity as required. (Xtreme IO all flash storage) Xtreme IO all flash array resolves DB storage solutions by below-

-Automatically employing all storage system resources Solid State Drives(SSDs) and controllers all the time.
-One can easily create a volume and enable the entire DB to put in there. No planning, provisioning or any tuning steps are required.
-Xtreme IO snapshots to manage multiple instances & copies of database.
-We can increase performance using Xtreme IO Brick if requirement exceeds.

For more details please visit – http://xtremio.com/

VMware vSphere is a complete and robust virtualization platform. It transforms the physical resources of a computer by virtualizing the CPU, RAM, HardDisk and network controller. This transformation creates fully functional virtual machines that run isolated and encapsulated Operating Systems and applications.One of the major points here is vSphere can suppport 62TB virtual machine datafile(VMDK) and Microsoft cluster services(MCS)

For more details please visit – http://www.vmware.com/in/products/vsphere

vMware Centre is used to manage of vSphere

Microsoft SQL Server 2012 – Enterprise Edition

-Consider partitioning since you are getting large number of rows
-Consider different file groups for different partitions
-Consider archiving old data as we can have maximum 1000 partitions

Microsoft BenchCraft TPC-E Tool Kit – is used for OLTP benchmark workload

Hardware Resources are– (Here quantity you can decide based on your requirement)
Storage Array – Xtreme IO X-Bricks
Servers – E.g. 20 Cores, 512GB RAM, 2.9GHz processors
LAN Switches – 32 port non-blocking
SAN Switches – FC Director class switches with 6 blades

Please note that all the information provided above may change depending on your requirements. Hope that you have enjoyed the article. In the next post I shall post some of the interview questions I faced on Sunday !

Cheers!. Thanks for reading !

-Pawan Khowal

MSBISkills.com