Tag Archives: sql server 2008 – How to handle huge table

SQL Server Interview Question – Can we page splits in heap?

10 Thursday Sep 2015

Posted by in Download SQL Interview Q's, SQL Performance Tuning, SQL Server Interview Questions

≈ Leave a comment

Tags

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


Can we have page split for a HEAP? How can we track page splits?

The answer to this question is We cannot have page splits in heap.

The forwarding pointer is applicable to only heap having non-clustered indexes. SQL Server actually implemented this as a optimization technique to improve heap update performance.

Example – In heap table Customer, We are updating LastName from ‘K’ to ‘I am Pawan Kumar Khowal’.

Now assume that the modified row size has increased and cannot fit on the page it currently resides on. Here the record is moved to a new page, and a small forwarding record is left at the original location. The forwarding record points to the new location of the record, which is known as a forwarded record or forward pointer. This is performed as a performance optimization technique so that all the nonclustered indexes on the heap do not have to be altered with the new location of the heap record.

This improves the performance of INSERT and UPDATE operations; otherwise this will take considerable time for inserting and updating records on heap.

Query to track page splits for an index.

--

SELECT
IOS.INDEX_ID,
O.NAME AS OBJECT_NAME,
I.NAME AS INDEX_NAME,
IOS.LEAF_ALLOCATION_COUNT AS PAGE_SPLIT_FOR_INDEX,
IOS.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(N'DB_NAME'),NULL,NULL,NULL) IOS
JOIN
SYS.INDEXES I
ON
IOS.INDEX_ID=I.INDEX_ID
AND IOS.OBJECT_ID = I.OBJECT_ID
JOIN
SYS.OBJECTS O
ON
IOS.OBJECT_ID=O.OBJECT_ID
WHERE O.TYPE_DESC='USER_TABLE'

--

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

-Pawan Khowal

MSBISkills.com

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

10 Thursday Sep 2015

Posted by in Download SQL Interview Q's, SQL Performance Tuning, SQL Server Interview Questions

≈ Leave a comment

Tags

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


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

MSBISkills.com