In general, the fewer recompiles the better. Many recompiles can add an excessive CPU load on SQL Server and slow down everything running on that machine. This not only slows down the process that is running the procedure, but adds extra CPU load on the server. SQL Server pauses the query execution and compiles the stored procedure again. Because of this, SQL Server stores execution plans in the Procedure Cache in the hope that they can be reused later, thereby saving the CPU that would otherwise be required to re-compile the query or stored procedure.Ī Recompile occurs when SQL Server believes that the execution plan for a stored procedure that is currently running may no longer be the best possible plan. This is known as an execution plan.Ī compile can take a relatively large amount of time and CPU resources, especially when the query or stored procedure is large or complex. It comes up with what it considers to be the fastest way of running the query. It then takes into account all the existing indexes, locking methods and join algorithms that could help the query run faster. During a compile, SQL Server looks at the query, including the tables, clause conditions, joins, sub queries, sort and grouping requirements used. This highlights when more than a certain percentage of compilations are due to run-time recompiles.Ī Compile is the process SQL Server goes through to determine how a query or stored procedure will be run. SQL Server 2017 and above hosted on Linux.Collect additional diagnostic information.Transaction Log Shipping Secondary Database.SQL Agent - Long Running Jobs (Percent).Network - SQL Server Packets Error Rate.Monitored Server - SQL Server Unsupported Version.Monitored Server - SQL Server Secondary Connection Failure.Monitored Server - SQL Server Planned Outage.Monitored Server - SQL Server Host Not Defined.Monitored Server - SQL Server Connection Failure.Monitored Server - SQL Server Collection Execution Failure.Monitored Server - SQL Server Alarm Evaluation Failure.Monitored Server - SQL Server Alarm Action Failure.Error Log - Information Severity Message.Error Log - Fatal Table Integrity Suspect Error (severity level 22).Error Log - Fatal Hardware Error (severity level 25).Error Log - Fatal Hardware Error (severity level 24).Error Log - Fatal Database (dbid) Processes Error (severity level 21).Error Log - Fatal Database Integrity Suspect Error (severity level 23).
![sql server recompile sql server recompile](https://www.sqlservercentral.com/wp-content/uploads/legacy/f45eb8f52f8eb5fced0e4e67d7984d1b9bd5b0a5/19421.jpg)
Error Log - Fatal Current Process Error (severity level 20).Error Log - Critical Error (severity level lower than 20).Databases - Dynamic Log File Growths Remaining.Databases - Dynamic Data File Group Growths Remaining.Databases - Data File Group About to Grow.Clusters - File Share Witness Inaccessible.Backup - Hours Since Last Differential Backup.