Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I see a high CPU pressure in the SQL Server and SQL Sentry shows only the following command which consumes the CPU abnormally. I think this is an internal process of the database engine but what stuff could be triggered this command?
Note: I am sure this is only a little part of the SQL statement which is used by the SQL Server
OPEN plan_handle_cursor;
I think this is an internal process of the database engine but what stuff could be triggered this command?
You could use monitoring tools (example Redgate SQL Monitor) where you can view, say, top 10 queries by CPU, reads etc. for a given period of time. Sort by CPU time, it will show top SQL statements that consumed most CPU. Expand any of these statements (example "OPEN plan_handle_cursor") , it will show a batch (or SP) to which your problematic statement belongs.
I am quite sure other monitoring tools (that are not free) can do the same.
You could also use free diagnostic tools, like:
sp_WhoIsActive - https://github.com/amachanic/sp_whoisactive/releases
Run it with "@get_plans = 2" parameter, to get execution plan. If you spot an "OPEN plan_handle_cursor" query in results in "sql_text" column, click on the "query_plan" column value, it will show an execution plan along with other statements in the batch
sp_PressureDetector - https://erikdarlingdata.com/sp_pressuredetector
sp_BlitzWho - https://downloads.brentozar.com/FirstResponderKit.zip
Update: for SQL Sentry, refer to this link: https://docs.sentryone.com/help/top-sql It contains instructions on how to use Performance Analysis Top SQL. You should be able to get execution plan and full batch text to which "OPEN plan_handle_cursor" sql statement belongs
