September 17, 2019 at 8:37 am
Hi,
I need some help on checking at the performance of a stored procedure's in my SQL Server. I come from Oracle DB's background so I'm not familiar how should i check for performance related issue in MSSQL.
In Oracle, there's report such as AWR which I could use to pin point the query's SID and get it's execution plan. Does mssql have anything similar to it? I've been googling around and found most of the suggestion requires you to run tracing while executing the stored proc. However the problem is, the issue only occurs once a month & during normal days it's running fine.
September 17, 2019 at 2:27 pm
Well do you call the SP with different parameters once a month compared to what you normally do daily?
September 17, 2019 at 8:50 pm
It depends on if you are looking for the information like AWR or just looking for query plans. You can get information similar to AWR by enabling the Data Collector and going from there. For just query plans, there are different DMV/DMFss that can provide query plans - you don't necessarily need to capture the plan when you execute the query or trace it when the query is running. Objects such as sys.dm_exec_cached_plans, sys.dm_exec_query_plan and other related views can help pull together the information you want. In your case, the plan may age out of cache in between runs if it runs once a month, it really depends. You may want to start by looking at the following article has a pretty extensive explanation of the various ways to get plan information:
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply