linq troubles

[Raphael Gomes]

Greetings,

I've been using linq to sql in a web project that uses tables with
about 1 million records each. Recently, we found the server freezing
and we're isolating the problem in the ms sql server, which is hogging
up resources (memory and processor).

I did a quick readying about the subject, and couln't find where do I
set up linq to free the used resources. Also, regarding the queries,
how do I ensure that linq is being efficient?

Thanks in advance,

 

[Alberto Poblacion]

I've been using linq to sql in a web project that uses tables with
about 1 million records each. Recently, we found the server freezing
and we're isolating the problem in the ms sql server, which is hogging
up resources (memory and processor).

I did a quick readying about the subject, and couln't find where do I
set up linq to free the used resources. Also, regarding the queries,
how do I ensure that linq is being efficient?

Resources on the client side are freed when you call Dispose() on the
DataContext. On the server side, Sql Server will grab as much memory as it
can, and supposedly frees the memory when it finds that it is needed for
other server operations. You can limit the amount of memory used by Sql
Server by setting the Max Memory by means of Management Studio (or by means
of a call to sp_configure).

There are a couple of tools that you can use to verify the query sent by
Linq to sql server. On the server side you can use Sql Server Profiler to
catch all the queries received from the clients and analyze them. On the
Linq side, you can use the Log property of the DataContext to send the
queries to a TextWriter.

 

[Mr. Arnold]

Greetings,

I've been using linq to sql in a web project that uses tables with
about 1 million records each. Recently, we found the server freezing
and we're isolating the problem in the ms sql server, which is hogging
up resources (memory and processor).

I did a quick readying about the subject, and couln't find where do I
set up linq to free the used resources. Also, regarding the queries,
how do I ensure that linq is being efficient?

In the future, you might want to look at the ADO.NET Entity Framework as a
possible solution, in particular, Entity-SQL and Linq-to-Entity for a more
finite control of things, because as I hear it, MS is not going to support
Linq-to-SQL like it will support and enhance the ADO.NET Entity Framework.

 

[Raphael Gomes]

In the future, you might want to look at the ADO.NET Entity Framework as a
possible solution, in particular, Entity-SQL and Linq-to-Entity for a more
finite control of things, because as I hear it, MS is not going to support
Linq-to-SQL like it will support and enhance the ADO.NET Entity Framework.

So, would Linq-to-SQL become a dead end, eventually? Do you have a
source for this bit of info?

 

[Raphael Gomes]

    Resources on the client side are freed when you call Dispose() onthe
DataContext. On the server side, Sql Server will grab as much memory as it
can, and supposedly frees the memory when it finds that it is needed for
other server operations. You can limit the amount of memory used by Sql
Server by setting the Max Memory by means of Management Studio (or by means
of a call to sp_configure).

    There are a couple of tools that you can use to verify the query sent by
Linq to sql server. On the server side you can use Sql Server Profiler to
catch all the queries received from the clients and analyze them. On the
Linq side, you can use the Log property of the DataContext to send the
queries to a TextWriter.

Is the Dispose() method called automatically at some point?

If there are resources being wasted by the DataContext not being
disposed, where should I look for symptoms?

Best regards,

 

[Paul]

From an architectural view I would not be using Linq on such a large
project. Others will disagree but I remain adament

Here is the thoughts of Mario Szpuszta.

http://blogs.msdn.com/mszcool/archi...cess-technology-should-i-use-my-thoughts.aspx

The only thing I disagree with here is that once a framework has been
established for ADO.NET manual coded DALs and you abstract layers correctly
(Providers from DAL) it can be as flexible and as RAD as any of the other
methods. You just need that initial time to set up a framework.

Presuming you cannot change all this. I would Trace the DB activity for a
good period and use this to try to identify what is happening.

- Repeated calls
- Long running queries/Transactions
- Transactions running in order?
- Locking Problems / Deadlocks

Try to identify if SQL Server or the App is the problem, and obviously fix
as appropriate. SQL Server changes are always the easiest but not always the
right answer.