Running Total handling Null Values

IndexProperty

Recently I had problem of slow insert on tables
During optimization we changed FillFactor of indexes and that improved the performance of insert.
We change the fill factor to 50%.

Lets see how to check different propery of indexes

There is a function INDEXPROPERTY which will give us various index properties.

INDEXPROPERTY ( object_ID , index_or_statistics_name , property )

We can get the details from BOL or http://msdn.microsoft.com/en-us/library/ms187729.aspx

We will see some important properties here

–FillFactor

SELECT Indexproperty (Object_id(‘fact_view’), ‘Ix_FACT_VIEW_url_key’, ‘IndexFillFactor’)

–Clustered Index

SELECT Indexproperty (Object_id(‘fact_view’), ‘Ix_FACT_VIEW_url_key’, ‘IsClustered’)

–Unique Index

SELECT Indexproperty (Object_id(‘fact_view’), ‘Ix_FACT_VIEW_url_key’, ‘IsUnique’)

Other  available properties are as below

IndexDepth
IndexID
IsAutoStatistics
IsDisabled
IsFulltextKey
IsHypothetical
IsPadIndex
IsPageLockDisallowed
IsRowLockDisallowed
IsStatistics
 

Impact of FillFactor

Problem

We have a table where we are dumping data on every 5 mins
Every 5 mins we are dumping approx 0.1 to 0.2 million rows
The tables has 5 indexes and we have a complaint that dump is getting slower over the time
It was taking 40-50 seconds , which needs to come down as we had to do other process after the dump

Solution
I checked the tables and could not find anything special
Only way to optimize is to work on Index. As I removed all the indexes the dump completed in 2 seconds
And once again I created all the indexes it took again 40-50 seconds.
AS its live table and being queried I can not remove indexes. So we decided to change the fillfactor to avoid splitting.
So we changed the fillfactor to 50 and effect is as per our expectation. The dump completes in 15 seconds.
As the index pages are created then they have 50% more space, so that there are now less chances of splitting compare to previous.
So it also attributes that page splitting is creating too much resource and time for large tables with heavy insert operations.