Press "Enter" to skip to content

Fast Queries Can Hurt Performance

Andy Mallon diagnoses a fast query which was hurting performance:

This code is coming from an ORM, which is parameterizing the filters, but not the (unnecessary & arbitrary) TOP value. The DMVs all viewed these are separate queries, so it was not aggregating the stats. It also wasn’t reusing the plan (thus chewing up even more CPU from frequent compiles). If the TOP had not been there, or it had been passed as a parameter, my initial query of sys.dm_exec_query_stats should have found it.

There are a couple of issues Andy works through, and his advice is good:  just because something runs quickly doesn’t mean it can’t (in aggregate) have a negative effect on your server.