SQL Server narrow plans (per-row)

Hi Friends,

When we update any data which has to modify and update existing non-clustered index using per row is also known as SQL Server narrow plans. In per row operations (narrow plans) the updates to base table and indexes are performed by a single operator and as name suggest one row at a time.

It is possible that SQL Server query optimizer decides to use per-index instead of row however factors like table structure, size and other operations to be carried out by UPDATE statement analysed carefully before choosing the optimal plan. The SQL query below will create a narrow plan.

Note: Click on ‘Display Estimated Execution plan’ if you do not wish to delete data from your sample database or take a backup so that you can revert back the changes.

USE [AdventureWorks2012]

DELETE FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderDetail.PurchaseOrderDetailID = 850

Per-row_Plan

This delete statement used above will update non-clustered index IX_PurchaseOrderDetail_ProductID as well as clustered index. When number of rows are less, query optimizer is likely to choose a narrow plan. A major drawback of this method is non-clustered index rows are updated using clustered key orders which may hurt performance when number of records are more.

See you soon guys, stay tuned.

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *