SQL Server Performance

Linq 2 sql performance

Discussion in 'SQL Server 2008 General DBA Questions' started by colinr, Aug 29, 2009.

  1. colinr New Member

    We might need a new forum for this technology!
    Ive taken on a new sql2008 client who is encountering some recent performance problems, Mostly the norm of index tuning but during the review Im seeing high CPU figures on an 8 core x 64 server and high levels of compilation (not re-compilations) and what appears to be dynamic sq lfull of hashes(#) (here we go again) however the Developer(s) were adamant they were not using dynamic sql for the usual reasons.
    So i produced them a list of worst performing transactions with executions in excess of 5000ms most of which were sql-batches submitted to the server and as per the subject of this post they turned out to be "Linq" queries.
    Ive seen Linq being used on sql2005 server and with decent indexing it seemd to be performing ok, but this server has a lot more concurrency and a high transaction load in a synch mirrored environment obviously linq has major benefits to developers, dbas are probably as in this case to last people to know they are being introduced. The development of them seemed to sail through dev staging and perf testing (probably due to the low load imposed during testing.)
    However the normal advice of remove the dynamic sql isnt appropiate here. so how do we optimise the DB's for this onslaught ?
    Or is it simply bad practice of the developers learning to use this new linq technology to allow it to generate dynamic calls to the DB as ive seen articles suggesting the 3.5 framework can bind to stored procs? Binding to stored proces seems to be the answer for me, but for developers the release from stored proc development is a major benefit of linq2SQL
    How are other servers behaving? is this increase in Compilations and CPU load been seen by others and what actions are you taking to cope with it?
    as I say im seeing the issue with sql2008 enterprise on an 8 core x64 server pair (wth synch DB Mirrorred) which is hardly a poor configuration. There is a fair size san doing the disk IO which thankfully isnt part of this perf issue.
    ColinR
  2. moh_hassan20 New Member

    LINQ is one of the best extensions in c# 3 , and linq engine generate optimized parametrized sql statement and pass it to sql database engine using sp_executesql which can have a cached execution plan.

    Developers can enhance the performance of LINQ by:
    - Avoid round trip due to lazy loading between client and server by using dataload options for eager loading.
    -Use Compilequery.compile
    -Using parameter expression

    Developers can log the sql syntax which is generated from linq engine in external file , as a part of debugging in the early stage of development using LOG method, to co-operate with DBA who can use sql profiler to catch the performance issues of the queries.
    In this way there will be a common understanding between developers and dba to tune the LINQ queries, and dba may find the missed index that he can create to speed performance or developers may modify where conditions /join used in LINQ based on advices of dba.

    If you catched the the performance isuues using the sql profiler, so advice the developers to modify the linq query based on your discoveries.

    CPU problems may be a result of not enough memory , so it should be reviewed with catching the memory counters to know the root cause of the problem.
    What is the the memory of the server , RAID level and disks used for the server?
    can you post one of the sql queries you catch using the sql profiler?

Share This Page