SQL Server Performance

SQL Server using max memory

Discussion in 'ALL SQL SERVER QUESTIONS' started by Ksr39, Sep 21, 2012.

  1. Ksr39 Member

    Hi Shehap,

    After running the query i got the below results

    Cache Store Type Total Num Of Plans Total Size In MB All Plans - Ave UseCount Size in MB of plans with a Usecount = 1 Number of of plans with a Usecount = 1
    Adhoc 61865 2837.304687 3760 1358.726562 24933
    Prepared 1382 295.609375 194294 189.96875 507
    Proc 238 71.015625 2691547 4.523437 17
    Trigger 34 5.28125 541380 0.335937 3
    UsrTab 2 0.234375 3 0 0
    View 156 15.398437 73 0 0
    Check 6 0.117187 11 0 0

    Current memory occupied by adhoc plans only used once (MB) Total cache plan size (MB) % of total cache plan occupied by adhoc plans only used once
    1358.29 3224.33 42.13

    so what should i do now as i couldnt understand by seeing this.
  2. Shehap MVP, MCTS, MCITP SQL Server

    I think you should have run just this query below :

    SP_CONFIGURE'optimize for ad hoc workloads',1

    So I am expecting you should have this output :

    Configuration option 'optimize for ad hoc workloads' changed from 0 to 1

    And nothing to do after that from your side
  3. Ksr39 Member

    Hi Shehap,
    Thank you for the query and i found some queries which are taking much CPU time now the senior manager is asking me to tune them and i am not aware of queries and tuning them as i was working totally towards Core DBA part, please let me know how to tune the queries.

    Thank you in advance..
  4. Shehap MVP, MCTS, MCITP SQL Server

    You can read more about T-SQL performance optimizations on the 2 different aspects T-SQL code optimization and Index tuning through my series of blogs starting from :


    and ending with


    Despite of that, you can post to me the most heavy expensive query to improve it and take it as good case study for further cases
  5. Luis Martin Moderator

    Agree with Shehap.
    Now, according your questions, in my opinion, you have two ways.
    1) Learn a lot, using Shehap links.
    2) Use DTA to find some indexes to improve performance.

    The right way is 1). If you are not planing to be DBA, then use 2).
  6. Ksr39 Member

    Hi Shehap/Luis,

    Thank you for your links, i will go for 1st option.

Share This Page