Filtered Index in sql server (is null condition):
In our pursuit to optimize query performance for a scenario where we needed to update rows with a null value in the "Location" column and a specific pattern in the "IPAddress" column, we experimented with different approaches utilizing filtered indexes.
I have a query:
?UPDATE [Sessions]??
?SET Location = SYSDATETIME()??
?WHERE Location IS NULL??
?AND IPAddress LIKE '12%'??
We had very less rows in the table with "Location IS NULL",??like around 15 rows out of a billion rows in the table satisfying this condition
Our initial attempt involved creating a filtered index (IDX_Speedup_01) solely on the "IPAddress" column where the "Location" is null. However, despite expectations, this approach still resulted in key lookups, indicating suboptimal performance.
1st approach
CREATE NONCLUSTERED INDEX [IDX_Speedup_01] ON [Sessions]
(
[IPAddress] ASC
)
WHERE ([location] IS NULL)
So as stated in https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/filtered-index-with-column-is-null blog, it will do a scan on the index and then do a lookup on the location column using primary key index.
In response, we refined our strategy and explored two alternative approaches. First, we created another filtered index (IDX_Speedup_02), this time including the "Location" column in the index definition. Although this eliminated the key lookups, it introduced an eager spool operation, which, while an improvement, still fell short of optimal efficiency.
CREATE NONCLUSTERED INDEX [IDX_Speedup_02] ON [Sessions]
(
[IPAddress] ASC,
[location]
)
WHERE ([location] IS NULL)
Finally, we opted to include the "Location" column as an included column in the index (IDX_Speedup_03). This approach proved to be more effective, as it minimized the need for additional operations such as key lookups or eager spools, resulting in improved query performance.
In comparing the performance of index options 2 and 3, we observed that including the "Location" column as an included column yielded better results, suggesting that this approach may be more suitable for our specific scenario.
In summary, our exploration of filtered indexes showcased the importance of iteratively refining our indexing strategies to achieve optimal query performance, ultimately leading to the identification of a solution that best meets our performance objectives.
Scenario 2:
In our ongoing efforts to enhance query performance, we encountered an intriguing scenario involving an update operation on the "UniqueSessionId" column in the "Sessions" table. The objective was to set the value of "UniqueSessionId" based on a combination of other columns if it was initially null.
UPDATE [Sessions]
SET UniqueSessionId = COALESCE(ServerName, '') + '-' + SessionId + '-' + CONVERT(VARCHAR(12), CAST(CreatedTime AS DATE), 120)
WHERE UniqueSessionId IS NULL
After careful consideration, we devised an indexing strategy to optimize the execution of this update query. The approach involved creating a nonclustered index (IDX_Speedup_04) on the "rowid", primary key column, supplemented with included columns such as "ServerName," "SessionId," "CreatedTime" and "UniqueSessionId." Additionally, we applied a filter to target only rows where "UniqueSessionId" is null, aligning with the condition specified in the update statement.
This indexing strategy aimed to streamline the query execution by providing a more efficient access path to the rows requiring update. By including the necessary columns as included columns in the index and restricting it to rows where "UniqueSessionId" is null, we sought to minimize unnecessary index scans and expedite the update process.
In conclusion, our approach to index optimization reflects a thoughtful consideration of the query's requirements and the underlying data characteristics, with the goal of enhancing performance while maintaining scalability and resource efficiency.
CREATE NONCLUSTERED INDEX [IDX_Speedup_04] ON [Sessions]
(
[rowid] ASC
) include (ServerName,SessionId, CreatedTime , UniqueSessionId)
WHERE ([UniqueSessionId] IS NULL)
Feel free to write comments to add your findings on same. By annotating our strategies with thoughtful observations, we contribute to a shared understanding of the rationale behind our decisions and pave the way for continuous improvement. Your willingness to contribute to this collaborative process is greatly appreciated.
Dear Experts, Share Your Valuable thoughts about my latest article : Mr. Rahul Kapoor, Mr. Ajay Gupta, Ashi Garg, Neha Maheshwar, Mr. Gaurav Bhardwaj , Gulnawaz Khan