Lowell (4/3/2014)

---

Welsh to capture the code, for example, i decided the dmv's were not enough; not everything exists in the cache to check for performance issues.

Instead i started with a rollover DML trace, capturing pretty much all events on a specific database;

then i created a job that would query that trace and insert results into a permanent Audit Table, like every 15 minutes, looking for any thing WHERE DATEDIFF(SECOND, StartTime, EndTime) > 10

that gave me all queries that took longer than 10 seconds.... a good starting point for me. i could review the TextData, and identify which items are "ok" to run long, and which were not.

the ones that should not run long(think big batch jobs) , i'd address just as you described...indexing, examining execution plans etc.

once you see an example oir two of the linq queries, you can see an offending query and say "i KNOW LINQ created that"

if you cannot tweak it with indexes, it's simple to create a tuned procedure that would return that same data, and you just get with the dev team to change the db layer int he app to use the new alternative.

i later changed my trace to extended events, mostly because Grant Fitchey beat the drum enough about them that it sunk in; i dragged my feet for too long because i could script a trace backwards and forwards, and it was familiar, vs extended events, which were alien to me.