This query will help you discover queries which you may not realise take such a long time. For example maintenance queries running more often than necessary.
I also like to look at the logical reads and assess whether there are opportunities for index tuning and sql query tuning
SELECT DISTINCT TOP 20
est.TEXT AS QUERY ,
eqs.execution_count AS EXEC_CNT,
eqs.max_elapsed_time AS MAX_ELAPSED_TIME,
ISNULL(eqs.total_elapsed_time / NULLIF(eqs.execution_count,0), 0) AS AVG_ELAPSED_TIME,
eqs.creation_time AS CREATION_TIME,
ISNULL(eqs.execution_count / NULLIF(DATEDIFF(s, eqs.creation_time, GETDATE()),0), 0) AS EXEC_PER_SECOND,
total_physical_reads AS AGG_PHYSICAL_READS
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_sql_text( eqs.sql_handle ) est
Question: How can I schedule a Powershell Script with Windows Task Scheduler? I’d like to execute the Powershell Script once a day . The Powershell Script will be in a batch file, along with other Powershell Scripts
Answer: Executing a Windows batch file with Powershell Scripts is a straightforward process.
Before you set up the Task Scheduler , check you can execute the Windows batch with the included Powershell script and get the expected result. Attempt to complete this step with the relevant permissions.This step allows you to confirm the batch file and scripts are working as expected.
Open Windows task scheduler
Create new task
In the process of creating a new task , use the Actions tab to add the details of the batch file you’d like to execute.
Question: How can I find Key Lookups in Execution Plans for a specific table?
I executed the script on Find last time of update on a table and found there were a high amount of lookups on some high usage tables. I’d like to be able to find the statement and execution plans with high key lookups
Answer: Key Lookup is a bookmark lookup on a clustered index . According to the MS documentation a "key lookup is always associated with a Nested Loop".Depending on the circumstances and if an excessive amount of key lookups occurs this may be a strong indicator that some query tuning is required.
There are always a number of questions you should ask when deciding on how much time to spending on tuning query. It will depend on asking the right question and understanding the context. Experience plays a major part on these decisions , a SQL expert can be worth their weight in gold.
So what’s the big deal about excessive key lookups ? Here are some general points about the impact of high key lookups
Increased IO – More calls may be made to the disk
Higher CPU – Extra cycles may be required to process data
Response time to the end user or process gets slower