1 / 2

RECOMPILATION – CHOOSE THE LOWEST NECESSARY GRANULARITY

Recompilation can quickly resolve production issues; however, using the lowest necessary granularity to recompile only the necessary object/entity will mitigate unnecessary adverse effects.

satishkartan
Download Presentation

RECOMPILATION – CHOOSE THE LOWEST NECESSARY GRANULARITY

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. RECOMPILATION – CHOOSE THE LOWEST NECESSARY GRANULARITY Recompilation can quickly resolve production issues; however, using the lowest necessary granularity to recompile only the necessary object/entity will mitigate unnecessary adverse effects. Statement Level:If you want to recompile an Ad hoc query or a statement within a stored procedure, you can use OPTION RECOMPILE hint for statement level recompilation (starting with SQL 2005). This can help with parameter sniffing problems that you may encounter because of use of local variables within the stored procedures. Stored Procedure/Trigger:If you want to recompile a Stored Procedure or trigger, you can use: exec sp_recompile ‘stored procedure name’. This might be necessary when a stale plan is causing performance issues. Sp_recompile removes the store d procedure cached plan from the procedure cache and the SP/trigger gets recompiled next time it is run. Table/View:If a table data changes significantly, you might want to recompile all the stored procdures/queries referencing that table, you can use: exec sp_recompile ‘tablename’. Note that there is also a way to recompile a view using sp_refreshview – this is used to refresh the view if the underlying object has been changed Database:If you want to recompile all SPs in a particular DB, you can use: DBCC FLUSHPROCINDB (‘DBID). This is useful on a test server, where you want to start testing with a cold cache for a single database but do not want to remove cached plans referencing other databases Resource Pool:If you are utilizing resource governor to isolate different workloads, you can use the DBCC FREESYSTEMCACHE (‘cache name’, resource_pool_name). The option to clear cache for a particular resource pool was introduced in SQL 2008. You can specify ALL for cache name to clear all caches associated with a particular resource pool.

  2. Ad-hoc Queries:If you have ascertained that the ad-hoc queries are dragging down the performance of a SQL instance, you can use DBCC FREESYSTEMCACHE (‘SQL Plans’) to clear the ad-hoc plans Server:To clear entire plan cache, you can issue DBCC FREEPROCCACHE. However, be wary of running this in production as the performance may degrade as all queries/SPs will be recompiled next time they are run Satish Kartan has been working with SQL Server for the past 20 years. To read more, please visit Satish Kartan's blog at http://www.sqlfood.com/ where he has shared more details on this.

More Related