3

Its pretty much all in the title. I have a SQL Server which is showing that 90%+ of CPU Usage and Logical IO Performed is due to "Adhoc Queries" The rest rest of the usage shows up under the actual DB name. This is all from the "Server Dashboard" report.

I have a few databases on this server which are accessed directly by vendor applications of which I have no control. They are very low usage in general; I have two databases that are accessed and used by internal applications written in Entity Framework and Linq-To-SQL. I'm wondering if that is why my "Adhoc Queries" are showing such high use instead of the names of my internal databases.

The reason I'm asking is because I've been noticing somewhat degregated performance and the CPU on my server is running 95% on sqlserver.exe process for extended periods of time, with minimal "down spikes" to 10-15% use.

Improve this question

1 Answer 1

Reset to default
3

Yes. Linq-To-Sql and EF submit dynamic (Ad-Hoc) queries to the database. You can prove this by running a profiler trace and debugging the app.

The good news is that you'll always get the most up-to-date query plan, based on current statistics. The bad news is that the plan will have to be compiled on each execution. This is rarely good, and only when the query commonly returns varying amounts of data.

I see this every day. Developer Bob is more concerned with RAD (kicking the app out the door and looking like a rock star) than app performance. He uses this new, bleeding edge, auto-magical sql generation code tool. Queries are fast from the app because there's only 2500 records in the database and everything fits in memory. 12 months down the road, app volume has went up 100000 %, it's now super dog slow, and the db gets blamed. The solution is to either re-write the app with proper sql or throw hardware at the problem. In my opinion, auto-magical sql insures that your' app will not scale well with significant volume.

"Dynamic code generation is the fastest way to build slow SQL and the slowest way to build fast SQL. " Brent Ozar

Your biggest challenge is not in compiled code speed. It's in your ability to efficiently read and parse datasets which do not fit in memory.

Improve this answer
1
  • 1
    Good to know they are ad-hoc, but I don't see any issues with scalability. Queries from my applications are all parameterized, so only one execution plan is generated. Turns out I was missing an index on a query that was being hit hard. I was mostly wondering if there was a way to tell the difference between queries from my Entity Framework apps vs. me messing around in SSMS to verify my results.
    – Nate
    Commented Dec 21, 2011 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.