26 performance tuning questions and solutions, Avoid SQL Server functions in the WHERE clause for Performance, Avoid Using Function in WHERE Clause – Scan to Seek, Avoid Using Functions in WHERE Clause tutorial and example, Functions in the WHERE Clause, How to avoid convert function in the where clause, How to handle large number of insertions in SQL Server?, How to tune SQL queries, Increase SQL Server performance avoid using functions in Where Clause, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, sql - How do I avoid functions like UPPER in where clause, SQL Complex Queries, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, SQL Performance Tuning and Optimization - Where CTEs are stored ?, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Server 2008 Query Performance Tuning Distilled, SQL Server Interview Question - How to handle large number of insertions in SQL Server?, SQL SERVER Interview questions, SQL Server Optimization - Parallel Execution Plans, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL Server Query Optimization: Parallel Plans, SQL SERVER Tips, SQL Tuning Overview, SQL Where Clause Performance, T-SQL Best Practices - Don't Use Scalar Value Functions in Where Clause, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, WHERE Clause Functions: A Word of Caution, Where CTEs are stored ?</, Which one is better for performance Table Variables or Temp Tables – Only in terms of performance optimization?
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 !