create procedure dbo.RecompileAllProcedures It recompiles all procedures, functions and inline functions in all schemas. This solution is based upon the other answer here, but takes schemas into account and considers special characters in the name. If you need help with a specific query, you should ask a separate question. Memory doesn't solve every performance problem in SQL Server, and a Seek isn't necessarily the finish line of performance tuning. I can't guarantee the new plan will be better. You do this at your own risk in production. If you're not sure about that, you can run DBCC FREEPROCCACHE to clear out the plan cache. You may not have ever set Max Server Memory though. That is effectively 'recompiling' all of those things you mentioned, because they won't have a stored plan in cache to re-use. If you add memory (even if it's a Hot Added to a VM), and increase Max Server Memory to match, your plan cache will clear out. While "throw more memory at it" can help some database performance issues, at least temporarily, if your bottlenecks are very CPU bound rather than memory and/or IO bound then adding more memory will have very little effect. There is sometimes more to optimisation than preferring seeks over scans and so forth, sometimes an index scan is more efficient than many executions of seek operations, and the cost estimates upon which the percent figures you are looking at are calculated are that (estimates) at best (a useful guide but sometimes far from at all accurate). SELECT += 'EXEC sp_refreshview '''++''''+CHAR(10) FROM sys.objects WHERE IN ('V') ĮXEC execute plans show 80+ percent of the run time is on a clustered index seek so I don't think there is much more I can do to optimise the stored procedures. You can similarly mark them as needing to be reassessed to make sure stored plans and other meta-data is not stale with sp_refreshview, by small modifications to either the cursor or ad-hoc SQL methods shown above: DECLARE NVARCHAR(MAX) = '' ![]() SELECT += 'EXEC sp_recompile '''++''''+CHAR(10) FROM sys.objects WHERE IN ('P', 'FN', 'IF') ĮXEC I find this form sometimes throws people due to looking set-based but building the string up iteratively, and not being a standard SQL pattern)Īnother set of objects that might be a similar concern here is views. Or you could produce ad-hoc SQL and run that via EXEC, takes less code which might be marginally more efficient: DECLARE NVARCHAR(MAX) = '' You can run sp_recompile on everything by using a cursor to produce ad-hoc SQL for each and run it, if you think that will help: DECLARE C CURSOR FOR (SELECT FROM sys.objects WHERE IN ('P', 'FN', 'IF'))
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |