, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

SQL Server Interview Question – How do you manage very large tables in SQL Server ?

This question has been asked to me many times. So finally I have decided to make the answer online, many more people also wrote blogs about this. I also have some points. The performance of very large table can be measured by following things-

  1. Load Data Performance
    1. How quickly we can insert data into very large tables.?
  2. Query Performance
    1. How our queries are performing which includes large tables?
  3. Data Management Performance(Backups, Rebuilding Indexes, Updating Statistics)
    1. How quick you can take your backups, rebuild indexes and update statistics.?

Solutions to this problem are-

  1. Use Multiple File Groups and Files
  2. Insert data into empty UnIndexed tables and then create indexes on it.
  3. Use Partitioned Tables
  4. Use ReadOnly File Groups
  5. Check Constraints

Now I will discuss the above solutions in details.

Use Multiple File Groups and Files
Here you need to spread data to as many as disks possible. This will help in storage and provide good performance.

Check constraints informs SQL Server which data to be inserted in which table.

Table Partitioning

We will get good performance for queries that specify predicates that allow SQL Server to do partition elimination. Also note that this feature is only available in enterprise version. Other advantages are given below-

  • Table can spans multiple file groups. It gives us huge advantage on performance and storage.
  • We can load data and extract data as a metadata-only operation with SWITCH commands.
  • Partition wise update stats(Starting SQL Server 2014).
  • Partition level index maintenance. If you have SQL Server 2014 so you can even do online-rebuilds per partition.
  • Column Store Indexes at partitions (Starting SQL 2012). 
  • Per Partition Compression.

Insert data into empty UnIndexed tables and then create indexes on it.

Well if insert data into a big table with multiple indexes, it will take more time then inserting data into a empty table and build indexes on it.

Use ReadOnly File Groups

  • It just requires one backup after becoming read only
  • Don’t require any maintenance
  • Don’t require page lock and row locks

I hope you have enjoyed the article. Cheers, Thanks for reading !

-Pawan Khowal