July 29, 2017 at 1:42 pm
Hello,
Is there anyway I can get notified if there is any new plan in place against any specific table object or a procedure that I get notified via email and then I take a quick look to find what is going on?
If there is any suitable way to handle this requirement, pl suggest.
Thanks.
July 30, 2017 at 7:36 am
You could have en extended event session that tracks the recompile and cache insert events, but then you'd still have to check what plans is in cache and have some record of what was there before, and that's likely to put a huge load on the server.
You've got SQL 2016. Use Query Store to track query performance regressions and fix any cases you find, don't go looking for stuff that quite likely is not going to be a problem the majority of the time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2017 at 2:12 pm
SQL-DBA-01 - Saturday, July 29, 2017 1:42 PMHello,Is there anyway I can get notified if there is any new plan in place against any specific table object or a procedure that I get notified via email and then I take a quick look to find what is going on?
If there is any suitable way to handle this requirement, pl suggest.
You're asking for a boatload of emails about plans that have changed. Most people don't realize how many "singleton" plans are formed.
It would be better to track which queries are performance problems and, I have to tell you, your longest running queries won't necessarily be your performance problems. For a quick demo of that, right click on the instance in the Explorer window in SSMS, select <Reports><Standard Reports><Performance - Top Queries by Total CPU Time> (or, Performance - Top Queries by Total IO) and see just one type of example. If you write a query to find the longest compile times on the box, you'll be in for another surprise... especially if your application code uses an ORM to talk with SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 30, 2017 at 2:22 pm
Jeff Moden - Sunday, July 30, 2017 2:12 PMSQL-DBA-01 - Saturday, July 29, 2017 1:42 PMHello,Is there anyway I can get notified if there is any new plan in place against any specific table object or a procedure that I get notified via email and then I take a quick look to find what is going on?
If there is any suitable way to handle this requirement, pl suggest.
You're asking for a boatload of emails about plans that have changed. Most people don't realize how many "singleton" plans are formed.
It would be better to track which queries are performance problems and, I have to tell you, your longest running queries won't necessarily be your performance problems. For a quick demo of that, right click on the instance in the Explorer window in SSMS, select <Reports><Standard Reports><Performance - Top Queries by Total CPU Time> (or, Performance - Top Queries by Total IO) and see just one type of example. If you write a query to find the longest compile times on the box, you'll be in for another surprise... especially if your application code uses an ORM to talk with SQL Server.
How to check for longest compile times of all the procedures? Can you share me the script to get it. Long running queries are easier to find -
SELECT st.text,
qp.query_plan,
qs.*
FROM (
SELECT TOP 50 *
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.max_worker_time > 300
OR qs.max_elapsed_time > 300
Thanks.
July 30, 2017 at 3:32 pm
Jeff Moden - Sunday, July 30, 2017 2:12 PMSQL-DBA-01 - Saturday, July 29, 2017 1:42 PMHello,Is there anyway I can get notified if there is any new plan in place against any specific table object or a procedure that I get notified via email and then I take a quick look to find what is going on?
If there is any suitable way to handle this requirement, pl suggest.
You're asking for a boatload of emails about plans that have changed. Most people don't realize how many "singleton" plans are formed.
Indeed. From previous investigations I've done, you could be getting thousands of emails an hour if you could do what you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2017 at 3:36 pm
SQL-DBA-01 - Sunday, July 30, 2017 2:22 PMJeff Moden - Sunday, July 30, 2017 2:12 PMSQL-DBA-01 - Saturday, July 29, 2017 1:42 PMHello,Is there anyway I can get notified if there is any new plan in place against any specific table object or a procedure that I get notified via email and then I take a quick look to find what is going on?
If there is any suitable way to handle this requirement, pl suggest.
You're asking for a boatload of emails about plans that have changed. Most people don't realize how many "singleton" plans are formed.
It would be better to track which queries are performance problems and, I have to tell you, your longest running queries won't necessarily be your performance problems. For a quick demo of that, right click on the instance in the Explorer window in SSMS, select <Reports><Standard Reports><Performance - Top Queries by Total CPU Time> (or, Performance - Top Queries by Total IO) and see just one type of example. If you write a query to find the longest compile times on the box, you'll be in for another surprise... especially if your application code uses an ORM to talk with SQL Server.
How to check for longest compile times of all the procedures?
Why?
Compilations, in general, are not a problem. If they are, you don't want the single longest compile time, you want aggregate, just like you don't want the longest running queries, you want to know on aggregate how bad queries are.
A query that compiles 10 times an hour (generates the same plan) and takes 500ms to compile is far more of a problem than one that compiles once a day and takes 5 seconds. (and, btw, short of nested views upon nested views, you're unlikely to see a 5 second compile)
All of that is trivial to get with the Query Store, which you have since you're on SQL 2016 (and if you're not using it, you're missing out on an incredible amount of information)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply