SQL Server Performance

How to reduce the CPU Time of procs

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Aston, Jul 9, 2008.

  1. Aston New Member

    Hi ,
    Proc using Dynanic SQL are taking alot CPU time . Can any one guide how to reduce it?
    Thanks
    Aston, Jul 9, 2008
    #2

  2. satya Moderator

    satya, Jul 9, 2008
    #3

  3. Aston New Member

    Hi Satya,
    Thanks for the prompt reply. but this article does not contain any information regarding reducing CPU Time.
    Dynamic SQL we are having using sp_ExecuteSQL correctly. Some other tips that i should see in the proc to reduce the time.
    Thanks Again
    Aston, Jul 9, 2008
    #4

  4. satya Moderator

    The general recommendation is to limit usage of Dynamic sQL due to the factors that you generally don't get the benefits of a compiled query plan. I say generally because if you do it carefully, you can get some plan reuse. Think on the security vulnerability that it provides a huge opportunity for a sql injection attack. Coding to protect against injection attacks is more work, and easily forgotten.
    A good rule to follow is never to grant access to the base tables. Use only the view when coding dynamic sql. You still take the performance hit of the security check. The performance hit is a combination of the time required to resolve the security issue and the time to generate a query plan.
    So taking the above points it is better to know what is running behind that Dynamic SQL within your environment and see to replace the same in using with Stored Produres and Views to obtain the results.
    satya, Jul 9, 2008
    #5

  5. moh_hassan20 New Member

    As you use sp_ExecuteSQL , optimizer may use execution plan , but the dynamic statement is parsed every time it is called.
    and if it is within a loop , it may be slow performance .
    I agree with Satya to convert it to stored procedure, because it is compiled once and optimizer select its execution plan.
    Did you test the execution plan of the dynamic sql - Is it optimized ?
    moh_hassan20, Jul 10, 2008
    #6

  6. satya Moderator

    On second thought I would like to ask what is the CPU usage when these queries are executed?
    satya, Jul 11, 2008
    #7

Share This Page