Identify Long Running Queries with Foglight

April 15, 2019.....Let’s give a shout out to all those men and women running the Boston Marathon today.

Man, running for that long must be taxing. Taxing…..wait…what day is it again?

So, as my way of giving a nod to long runners, in my SQL-nerd way, I thought I’d write a blog about Long-Running….QUERIES.

 

One of the primary functions of a DBA is to find and eliminate Long-Running Queries (LRQ). These LRQs rear their ugly head, cause slow response time, and any associated business operations are negatively affected.

What causes long running queries?

There are several reasons for LRQs including locked resources, insufficient memory, poor query design, or missing indexes to name a few.

How can I identify them quickly so I can address them in an efficient manner?

The traditional (reactive) method for a DBA is to wait to hear from end users complaining of ‘slowness’. 

Proactively identify LRQs with Quest’s Foglight. Here are a few ways....

(Click on the image to enlarge)

1. Top 10 SQL by Active Time: Overview, order by Active Time

2. Session details: Activity > Sessions > Select a session, note Elapsed Time

 

3. Performance Investigator by SQL Statements dimension: SQL PI > sort by Active Time

 

SQL PI / Performance Investigator can help find resource–intensive queries as well…by Workload etc. These may be candidates for SQL Tuning…hints, re-writes, Index suggestions.

Review these blogs for more information about initiating a tuning session from within Foglight.

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

https://www.quest.com/community/quest/performance-monitoring/b/performance-monitoring-blog/posts/tune-your-queries-with-sql-optimizer-pt-2

4. Be alerted about related activity – Long Running Locks, Jobs.

 

 5. Reports: ‘Reports’ option (top right of DB home page) > Top Statements

(Top SQL Batches, SQLServer). *These can be scheduled.

Thinking about long-running reminds me of the movie, Forrest Gump. In particular, I can’t help but think about the woman speaking to Forrest on the park bench….

“ And so, you just ran! “

https://www.google.com/search?q=Forrest+you+just+ran&rlz=1C1GGRV_enUS761US761&oq=Forrest+you+just+ran&aqs=chrome..69i57j0.5838j0j8&sourceid=chrome&ie=UTF-8

For more information or to request a trial, visit the product page: Foglight database monitoring.

Anonymous