Some days ago in one client site, business users from different departments reported the system was extremely slow, and they got timeout exceptions sometimes.

Long story short, through tedious analysis, we found that a few SQL operations in a transaction took long time to finish. These SQL statements looked like:

Delete from Education.ParentTable where ParentTableID = 10000

The estimate execution plan:

Index-Foreign-Key-Before-2016-08-10


Click here to see the full-size image

Here is the background information:

  1. ParentTableID is the primary key of ParentTable. 10000 above is just an example number.
  2. ParentTable has a child table ChildTable.
  3. There were around 50 million rows in BigTableA and BigTableB, respectively. Around two months ago, a column ‘ParentTableID’ was added to BigTableA, and configured as a foreign key referencing back to ParentTable. Around one week ago, a column ‘ParentTableID’ was added to BigTableB, and configured as a foreign key referencing back to ParentTable.

So to summarize the relationship among ParentTable, ChildTable, BigTableA, and BigTableB:

  1. ParentTable has a child table ChildTable.
  2. Primary key ParentTableID of ParentTable is the foreign key in BigTableA and BigTableB

From the estimated execution plan, we found:

  1. Before a record in ParentTable is deleted, the SQL server will check whether there are child records existed already, and whether ParentTableID was referenced in BigTableA and BigTableB.
  2. Clustered Index Scan on BigTableA and BigTableB was a red flag
  3. 11% cost of Clustered Index Scan seemed low
  4. There were no missing index hints shown on estimated execution plan

Before we dive deep, there are four factors when we consider what real cost is:

  1. Operator Cost
  2. I/O Cost
  3. Subtree Cost
  4. CPU Cost

When we paused mouse over the icons of Clustered Index Scan on the lower right corner of screenshot, we found the cost of I/O was 41.7557, cost of CPU 3.57981. Both numbers weres very high. Please note that  11% cost shown on the execution plan is just operator cost. So it didn’t reflect the true picture, and the real cost of Clustered Index Scan on BigTableA and BigTableB was very expensive.

So according to execution plan, if we created an index of column ParentTableID on BigTableA and BigTableB, the execution plan could change from Clustered Index Scan to Non-Clustered Index Seek, and the performance should be improved tremendously.

Since the column ParentTableID was added into BigTableA and BigTableB as nullable column recently, most of its values are nulls, we created filtered indexes to further improve seek performance. Please refer to this MSDN link, https://msdn.microsoft.com/en-us/library/cc280372.aspx, for filtered indexes.

CREATE NONCLUSTERED INDEX [IX_Dbo_BigTableA_ParentTableID]
ON [dbo].[BigTableA]
(
ParentTableID ASC
)
WHERE ParentTableID IS NOT NULL — filtered index
ON [INDEXES_FILE_GROUP]

CREATE NONCLUSTERED INDEX [IX_Dbo_BigTableB_ParentTableID]
ON [dbo].[BigTableB]
(
ParentTableID ASC
)
WHERE ParentTableID IS NOT NULL — filtered index
ON [INDEXES_FILE_GROUP]

After created these two indexes, we verified the execution plan was just like what we planned out (changed from Clustered Index Scan to Non-Clustered Index Seek), the I/O and CPU cost was thousands times smaller, the system was very fast and responsive, and there were no similar complaints from business users.

Index-Foreign-Key-After-2016-08-10


Click here to see the full-size image

So the lesson learned here is: Please don’t forget to create an index on the foreign key, in most scenarios, especially when you have big tables in OLTP systems.

I still didn’t answer the question, why there were no missing index hints shown on estimated execution plan. I would put some thoughts into it, and see whether it will worth my next topic or not.

 

 

Advertisements