Indexed Views added to database have increased Parse and Compile time dramatically
0
votes
1
answer
48
views
Recently, one of our development teams have added some indexed views based on some very commonly used tables. The problem we are getting now is that queries that used to execute quickly are taking a really long time in parse and compile. These are common tables so the performance hit is throughout our application, but the example query I have is taking 25+s in parse and compile, while it executes in about 250ms. The query references 3 core tables that are referenced in 4 of these indexed views.
My experience with indexed views is limited, but I do understand the concepts, and that SQL Server may consider using the indexed view even if the view isn't referenced in the query. I'd expect a drop in performance with updates and inserts. However, I don't understand why this seems to have such a large impact on the parse and compile time.
When I remove the indexes on these views one by one, the parse and compile time quickly drops and performance is restored. Add them back and it is poor again. And to clarify, the execution plan is always good - the execution time is fast and acceptable - it is just the parse and compile time that is poor.
We are using SQL Server 2022, using native compatibility. I have played with other compat/CE options and 110 (2012) compatibility seems to alleviate the problem, but that is not an acceptable long term solution for us.
Asked by user1359111
(11 rep)
May 29, 2025, 07:15 PM
Last activity: May 30, 2025, 08:34 PM
Last activity: May 30, 2025, 08:34 PM