One thing that has been tough for me as a DBA is the change in development styles from using stored procedures to developers using LINQ instead.
In case you are not familiar with LINQ, essentially the programming language of choice creates the queries for you on the fly based upon your object model — which was created based upon the database schema.
There are some things that are good to look at as far as optimization. This is where we can assist the developers in getting the best performance out of something we have very little control over!
theText.[text] as QueryText
, stats.execution_count AS ExecutionCount
, stats.max_elapsed_time AS MaxElapsedTime
, ISNULL(stats.total_elapsed_time / stats.execution_count, 0) AS AvgElapsedTime
, stats.creation_time AS LogCreatedOn
, ISNULL(stats.execution_count / DATEDIFF(s, stats.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats stats
CROSS APPLY sys.dm_exec_sql_text( stats.sql_handle ) theText
WHERE execution_count > 50
AND theText.[text] like ‘%@p[_][_]linq[_][_]0%’
ORDER BY stats.max_elapsed_time DESC
If you run this query [and you have the space on your server for query plans to be saved] – you will get a listing of the cached query plans that were called by LINQ. The most common issue I have seen is where LINQ is sending over nvarchar but the database is set to a varchar field. Make your developers aware and they can adjust their code appropriately so that it sends the correct data typing. Keep an eye on the AvgElapsedTimes, obviously the lower the better! Feel free to lower the execution_count value if you want more results. As always, if possible, run on development servers so the code can be fixed there first.
Questions? Comments? Post below!