Does anyone has experience in monitoring Sql server memory leak? I am suspicious of some of the user stored procedures causing the memory leak. Can anyone who has such experience explaine how to find the offened stored procedures?
Thank you very much for any kind of suggestions and recommendations!
Unless you're talking about extended stored procedures, then stored procs themselves don't have memory leaks as such (like normal code), however, they may be doing things inefficiently, or you may have missing table indexes.
The best way to start is to use the SQL Profiler tool to monitor stored procedure execution and look at the duration field. If you capture results to a table, you can then select the slowest queries by ordering on the duration column descending. You should also group by the procedure name to look at the procedures executed most often.
The combination of the slowest queries, and those executed most often will give you the best place to start. From there it's a case of looking at the execution plan (in query analyser) of the procs and looking for ways to optimise the tsql or add/modify indexes.
No comments:
Post a Comment