SQL Server Performance

SQL Server using max memory

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

  1. Ksr39 Member

    HI Experts,

    I have a small concern again on SQL Server Memory issue as one of my prod server runs on SQL Server 2005 Enterprise edition 64 Bit, on windows 2003 enterprise 64bit edition. The Ram on the machine is 32GB and allotted memory for SQL Server is 26GB but even it’s taking much than that. Now I am not able to find which is taking much memory from SQL Side.
    Neither more applications nor instances are there, as it is a single instance with a 4 small databases size of 30GB. I have run some DMV sys.dm_exec_query_stats_qs and sys.dm_exec_sql_text and some more but couldn’t find any. Can you please let me know where i need to find what is happening?

    Thank you all in advance
  2. Luis Martin Moderator

  3. Ksr39 Member

    Hi Luis,
    I have gone through the link its good to know how much memory should be allocated to the X64bit, Yeah we have set the max memory to the server it is 24000MB and the total memory in the server is 32GB.
    We have set up page file on two different drives in the server C: and D: and the size allocated on each drive is 4096MB the total is 8192MB and both the drives have enough space after allocating the space C: is having 17.5GB and D: is having 30.5GB, even though the developer’s see the CPU, RAM Memory and virtual memory fully utilized 100%. I have no idea which is taking the max memory and CUP.
    Thank you in advance.
  4. Luis Martin Moderator

    Are you using performance monitor to measure sql max memory used to confirm your issue?. I mean, performance monitor can show target and used sql memory.
    If yes, please share sp_configure results with as.
  5. Ksr39 Member

    Hi Luis,
    Please find the results for sp_configure as an attachment, and we are seeing the cpu spike occasionally to 100% and VMemory is utilized high as shown below.
    Memory Manager KB
    ------------------------------ --------------------
    VM Reserved 34463224
    VM Committed 25855216
    AWE Allocated 0
    Reserved Memory 1024
    Reserved Memory In Use 0
  6. Ksr39 Member

    please find the SP_Configure resuts as an attachment,its an .png file
  7. Ksr39 Member

    Hi Luis,
    whem i saw the memory used in task manager in process tab i found this please let me know what exactly the sql is utilizing

    image name username sessionID CPU Mem Usage PeakMem Usage I/O Reads I/O Reads Bytes
    sqlservr.exe qdpclusmgr 0 00 25,735040K 26,101,232K 19,323,725 2,245,655,018..
  8. Luis Martin Moderator

    Task manager is not the tool.
    You have to use Performance Monitor and add counters like: SQL Server Memory manager-->Target SQL Memory and Total SQL Memory.
    Also you can add Processor --> %CPU used and Process --> %CPU sqlsrv.
    Are you using Virtual Server?.
  9. Ksr39 Member

    Hi Luis,
    No we are using a physical dedicated server for the database engine and I will run the profiler by using these counters which you have mentioned and i will post the results what I found in that.

    Thank you in Advance
  10. Luis Martin Moderator

    Not Profiler (good idea to find long query whit high cpu). For counters I mentioned above, use Performance Monitor.
  11. Shehap MVP, MCTS, MCITP SQL Server

    Kindly note that memory utilization depends on lots of parameters and factors that can exhaust memory rapidly like broad searches selecting wide ranges of data entity , Huge bulk OLTP transactions like DWH or have huge stress workload on production DB server with lots of reporting queries consuming much workspace memory ..

    To be able track and capture such broad queries , you can follow my blog http://www.sqlserver-performance-tuning.com/apps/blog/show/12926697-t-sql-performance-optimization-1-6- to learn more about this regard

    Anyway , it is heavily recommended to use MAX memory setting to retain maximum memory utilization of SQL Server at a certain limit and keep a memory margin for other OS services ( 3 GB is recommended here)..

    But for sure , this maximum value can be reached once a time either daily or weekly …etc , so you have to configure a memory recycle job to clear memory by a certain frequency matching up with consumption rate as follows:



    EXECsys.sp_configureN'max server memory (MB)',N'5000'





    EXECsys.sp_configureN'max server memory (MB)',N'26000'



  12. Ksr39 Member

    Hi Luis,
    i will let you know the resuls as soon as i get them, thanks for the support.

    The link was useful and thank you for the link, I have a small doubt if I run the DBCC query which u gave I think it will erase all the cache info and if it so then the SQL Server try to find the info from the Drive to fetch the data for a query, so will it de-grade the performance of the server. and my server Max memory is set to 26GB out of 32GB and rest is used by the OS.

    even then when we set the Max memory to 26GB, I couldn't find why my vertual memory is utilized more.

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

    Impacts might be tangible if to recycle all cache like FREEPROCCACHE and freesystemcache('ALL') where it is expected torecycle the most important portion of cache which is process cache where all query execution plans are stored there and thus Query Analyzer is going to re- estimate all Query execution plans previously cached .

    However , you can get a good performance improvement if to use the 2 kind of caches stated above where they recycle only cached information of session , login token, user token and TokenAccessResult which have nothing to do with Query execution plans and thus performance consistency and their cleanup process is a require process here ..

    Moreover, Ad-hoc queries (T-SQL queries running out of SPs like those coming from .net directly ) can plan a role in Cache consumption that can be controlled by activating the feature 'optimize for ad hoc workloads'as follows:

    SP_CONFIGURE'optimize for ad hoc workloads',1



    But kindly keep in mind it is much preferable to apply it if its memory consumption >=25% that you can determine easily using the below DMV query:

    SELECTobjtypeAS[Cache Store Type],

    COUNT_BIG(*)AS[Total Num Of Plans],

    SUM(CAST(size_in_bytesasdecimal(14,2)))/ 1048576 AS[Total Size In MB],

    AVG(usecounts)AS[All Plans - Ave Use Count],

    SUM(CAST((CASEWHENusecounts= 1 THENsize_in_bytesELSE 0 END)asdecimal(14,2)))/ 1048576 AS[Size in MB of plans with a Use count = 1],

    SUM(CASEWHENusecounts= 1 THEN 1 ELSE 0 END)AS[Number of of plans with a Use count = 1]



    ORDERBY[Size in MB of plans with a Use count = 1]DESC

    DECLARE@AdHocSizeInMBdecimal (14,2),@TotalSizeInMBdecimal (14,2)

    SELECT@AdHocSizeInMB=SUM(CAST((CASEWHENusecounts= 1 ANDLOWER(objtype)='adhoc'THENsize_in_bytesELSE 0 END)asdecimal(14,2)))/ 1048576,

    @TotalSizeInMB=SUM(CAST(size_in_bytesasdecimal (14,2)))/ 1048576


    SELECT@AdHocSizeInMBas[Current memory occupied by adhoc plans only used once (MB)],

    @TotalSizeInMBas[Total cache plan size (MB)],

    CAST((@AdHocSizeInMB/@TotalSizeInMB)* 100 asdecimal(14,2))as[% of total cache plan occupied by adhoc plans only used once]

    IF @AdHocSizeInMB> 200 or((@AdHocSizeInMB/@TotalSizeInMB)* 100)> 25 -- 200MB or > 25%

    SELECT'Switch on Optimize for ad hoc workloads as it will make a significant difference'as[Recommendation]


    SELECT'Setting Optimize for ad hoc workloads will make little difference'as[Recommendation]

    Kindly let me know if any further help is needed
  14. Ksr39 Member

    Hi Luis,
    Please find the results for the counters which you asked me to share..
    Processor Time average of 30-35%
    SQL Server Memory
    Target Server Memory (KB) 24576000
    Total Server Memory (KB) 24576000
    Context Switches/Sec avg 72459.304
    I run the query which you gave but It throws an error saying that some of the columns are not mentioned in the systables. And please find the memory what was allotted to the server.

    name minimum maximum config_value run_value
    Ad Hoc Distributed Queries 0 1 0 0
    affinity I/O mask -2147483648 2147483647 0 0
    affinity mask -2147483648 2147483647 0 0
    affinity64 I/O mask -2147483648 2147483647 0 0
    affinity64 mask -2147483648 2147483647 0 0
    max server memory (MB) 16 2147483647 24000 24000
    max degree of parallelism 0 64 0 0
    min memory per query (KB) 512 2147483647 1024 1024
    min server memory (MB) 0 2147483647 8000 8000
  15. Luis Martin Moderator

    I find no problem with your server.
    SQL use max memory (24Gb and left 8 for OS), CPU is fine.
    May be is time to find long queries and try to optimize those queries.
  16. Ksr39 Member

    Hi Luis,

    Thank you for the reply i will find the long running queries and try to tune them.
  17. Shehap MVP, MCTS, MCITP SQL Server

    For Optimize for Ad-hoc query option , you can use the same query but it seems some typo issue so I attached it better

    Moreover , you can capture long running queries and identifying its impact for CPU , IO using the attached script where it will capture continuously all expensive queries happening on each DB individually and buffer them in a table called "Exp_Detailed" under msdb which I do believe it will be helpful for compromising your case

    Attached Files:

  18. Ksr39 Member

    Hi Shehap,
    Thank you for the query, i have run it on the machine and got the below resluts which i couldn't understand. please help me on this...

    Cache Store Type Total Num Of Plans Total Size In MB All Plans - Ave Use Count Size in MB of plans with a Use count = 1 Number of of plans with a Use count = 1
    Adhoc 64058 2844.890625 2204 1241.5 23237
    Prepared 1504 305.671875 106974 191.789062 550
    Proc 220 63.507812 1746793 5.0625 11
    Trigger 34 5.078125 329039 0.335937 3
    UsrTab 2 0.234375 2 0.117187 1
    View 105 10.375 68 0 0
    Check 4 0.078125 5 0 0
  19. Ksr39 Member

    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
    1241.48 3229.81 38.44

    Switch on Optimize for ad hoc workloads as it will make a significant difference...but how to Optimize the ad hoc workload?
    and th second query is getting an error saying that

    Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 243

    The specified '@notify_email_operator_name' is invalid (valid values are returned by sp_help_operator).
  20. Shehap MVP, MCTS, MCITP SQL Server

    It sounds like you have much Ad-hoc queries running on your production DB server impacting adversely on memory that need to be optimized by just enabling the option 'optimize for ad hoc workloads'as follows:

    SP_CONFIGURE'optimize for ad hoc workloads',1



    For the 2nd script , you have just to replace credentials used there by appropriate credentials according to your AD , but anyways you can use the above one to that bypass completely AD credentials..

    Kindly run it to be able to capture continuously all expensive queries running on your DB Server along with each DB individually and you can share with me anyone needing enhancements to be able to help you compromising you case ASAP

    Attached Files:

Share This Page