LINQ to SQL : Too much CPU Usage: What happens when there are multiple users.

Question

I am using LINQ to SQL and seeing my CPU Usage sky rocketting. See below screenshot. I have three questions

• What can I do to reduce this CPU Usage. I have done profiling and basically removed everything. Will making every LINQ to SQL statement into a compiled query help?

• I also find that even with compiled queries simple statements like ByID() can take 3 milliseconds on a server with 3.25GB RAM 3.17GHz - this will just become slower on a less powerful computer. Or will the compiled query get faster the more it is used?

• The CPU Usage (on the local server goes to 12-15%) for a single user will this multiply with the number of users accessing the server - when the application is put on a live server. i.e. 2 users at a time will mean 15*2 = 30% CPU Usage. If this is the case is my application limited to maximum 4-5 users at a time then. Or doesnt LINQ to SQL .net share some CPU usage.

Answer 1

Profile. Profile. Profile.

Profile to find out exactly which query is taking the most resources and improve the performance of that query. You can use the Log property of the DataContext to view the SQL - see this article. You can get the query plans for a query in SQL Server - see this article.

Examples of ways to improve a query:

• Add missing indexes.
• Rewrite the query to take advantage of the indexes that are already there.
• Don't fetch too much data per query - use paging and only fetch more rows when requested. Don't fetch fields you don't need.
• Don't fetch too little data per query - don't make a loop fetching one row at a time. Fetch many rows at once.

Once you have done that, profile again to check if you have improved the performance of that query. If not, repeat until you have.

Then profile again to see what the next killer query is and repeat the process until your performance is acceptable.

You say you already have profiled, but you haven't posted any profiling information such as queries, query plans, execution times, query frequency, etc. Without more profiling information all we can do is guess.

Answer 2

Compiled queries will not "get faster" with more use. The primary benefit of compiled queries is to save the need for the LINQ engine to repeatedly perform the translation process every time it is called.

As far as the CPU usage goes, if this is your development machine, the odds are very good that something else is going on to cause such high activity. Even if this is a dedicated database server, I would highly suggest using SQL Profiler to investigate what statements are being generated by your LINQ queries. It may require tweaking your schema, your code or your database settings to get the usage back to a more acceptable level.

Answer 3

1. Are there any common queries that can be cached?
2. Can the linq queries be rewritten
3. Is most of the query processing being done on the web server or in SQL?
4. Are you running both SQL sever and web server on the same box? Do you have a test environment that mimics the production environment?