Foglight can help identify SQL Tuning candidates?
Yes!
Foglight can tune the SQL?
With seamless integration with Quest’s SQL Optimizer, yes!
OK, let’s stick a fork in this....
(Click on images to enlarge)
What is SQL Optimizer?
Caution: There are blogs within this blog...here's to hoping you can climb out after diving in!
Note the 2-part blog series below:
The first blog details the following:
- Why Tune?
- Where can I get SQL Optimizer to try it myself?
The Why and Where (Pt 1): https://www.quest.com/community/quest/performance-monitoring/b/performance-monitoring-blog/posts/tune-your-queries-with-sql-optimizer-part-one-why-tune-and-where-can-i-get-it
The second blog describes:
- What users can accomplish with SQL Optimizer
- A general workflow within SQL Optimizer
The What and How (Pt 2): https://www.quest.com/community/quest/performance-monitoring/b/performance-monitoring-blog/posts/tune-your-queries-with-sql-optimizer-pt-2
** P.S. If you already own Quest’s wildly popular Toad (DBA edition), you likely already have these SQL Optimizer capabilities and the workflows are the same.
Now, let’s build from the information above!
Foglight expert, Darren Mallette, posted a great read that steps users through a way to identify and isolate SQL from within Foglight…then tune the SQL with SQL Optimizer.
Hopefully, that will have you well on your way to efficiently tuning SQL statements.
Are you beginning to feel like a finely-tuned SQL athlete ready to take on SQL?
Here’s another way to leverage the power of Foglight’s Performance Investigator to identify statements that need a closer look, understand access paths and associated statement metrics, and if need be, spin up a tuning session to get hints, re-writes and/or indexing suggestions.
I’ve received many inquiries about tuning stored procedures. Though SQL Optimizer cannot tune stored procedures as a ‘single unit’, it can provide suggestions to improve individual statements within the stored procedures. With this in mind, the workflow described below can help Foglight users…
- Identify the most resource-intensive statements within stored procedures
- Analyze the plan
- Initiate a tuning session
First, navigate into Foglight's Performance Investigator (PI).
Then, choose the window of time you'd like to search by clicking on the date in the top right.
When using the navigation tree on the left side, and expanding any of the nodes called 'data dimensions', it is listed in order of most workload-intensive to least workload-intensive.
So, across the instance, users can quickly find the most resource-consuming SQL statements...or...statements within T-SQL Batches / Stored Procedures.
From here, choose the 'Analyze Plan' button to better understand the statement's path.
Via the tabs, the Plan Analysis interface allows users to better understand the plan details, cost of the operators...even broken down to CPU and I/O cost, and the objects involved.
Finally, highlight the statement to tune and choose the 'Tune SQL' button. This launches SQL Optimizer to start a tuning session.
For more information, visit Foglight database monitoring and download a free trial.
