I run this one pretty frequently when we need to figure out what procs are killing a complex ETL process and what exactly about them is making the server cry. So basically, if it's on the development server, I'll do a
DBCC FREEPROCCACHE and a
DBCC DROPCLEANBUFFERS, run the entire set of ETLs, then run this query and then dig deeper in to the query plans that look suspect (high *Scan counts usually, sometimes lots of Hash Matches or Merge Joins). On a production server, the clearing of the proc cache and dropcleanbuffers can be problematic so I'll often just run the query after a scheduled ETL run. If you want to see the query plans mapped out visually, click on the query_plan value and SSMS will open up the XML. Then save that XML file as a .sqlplan file. Once you have that, close the XML and then open the .sqlplan file.
The other day, a co-worker requested an auto-suggest feature in the user name field of a web app that I had written a few months ago. Why? Not sure - to save 6 keystrokes, I suppose - but then again, 6 keystrokes at a time, it becomes justifiable eventually... And really, who can turn down a challenge like that?
- Post-Implementation Numbers:
- Average Keystrokes saved per day: 179.4
- Characters in Scripts (Client and Server, no formatting): 4886
- Payback time: 27.24 Days
Not bad in terms of effort. Even calculating in the difference between developer salary and data entry salary - the payback is still within 3 months. (Assuming we develop in a vacuum and ignore opportunity costs - i.e. we had nothing better to do at the time.)