CODEWITHSUNDEEP
sql-serverperformancet-sql
oas_public
oas_public

Reputation: 273

what could be the reason for high "SQL Server parse and compile time"?

a little background first:

i have a legacy application with security rules inside the app.
to reuse the database model with an addition app on this model with integreated security model inside! the database, i deside to use views with the security rules inside the view sql.
the logic works well but the perf was not really good (high io cause by scan some/many tbls).
so i use indexed views instead of standard views for the basic coles i need for the security and add additional a view on top of the index view with security rules. works perfect when i see the io perf.

but now o have a poor parse and compile time.

when i erase all buffers a simple sql against the top view deliver this timings:

"SQL Server-Analyse- und Kompilierzeit: 
, CPU-Zeit = 723 ms, verstrichene Zeit = 723 ms.

-----------
7

(1 Zeile(n) betroffen)

#A7F38F33-Tabelle. Scananzahl 1, logische Lesevorgänge 7, physische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische LOB-Lesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.
xADSDocu-Tabelle. Scananzahl 1, logische Lesevorgänge 2, physische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische LOB-Lesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.

 SQL Server-Ausführungszeiten: 
, CPU-Zeit = 0 ms, verstrichene Zeit = 0 ms.

when i execute the same stmt again the parsetime is of course zero.
in past i see sometimes later with the same statement a very long parse time again when i reexecute it >1sec (no dml is done during this time!).
now i have deactivate all statistics automatisations and never see this long parse times again.

but what could be the reason for so a long initial parse and compile time?
this time is very huge and cause a very bad perf on the app itselve with this solution.

is there a way to look deeper inside the parse time to find the root cause for it?

Upvotes: 2

Views: 4072

Answers (1)

oas_public
oas_public

Reputation: 273

the reason for poor compile time is the number of indexed views.

“The query optimizer may use indexed views to speed up the query execution. The view does not have to be referenced in the query for the optimizer to consider that view for a substitution.” https://msdn.microsoft.com/en-us/library/ms191432(v=sql.120).aspx

this means, that the optimizer may check when parse a sql all! index from views.

i have a sample on my db, where i can see this behaviour, that a simple sql on base tables use the index from an indexed view.

as far as good, but when you reach a limit from about 500 idx the system escalate and the optimizer need at least more then 10 times more cpu and memory to calc the plan. this behaviour is nearly the same from version 2008 to 2014.

Upvotes: 1

Related Questions