none
Max Workspace Memory (KB) is high

    Dotaz

  • Hello All,

    Now a days my SQL server memory useses increase almost 1 GB and i found that max workspace Memory(KB) is too high how a can reduce this.
    we are runinig lots of BULK stmnt in our database and i know this counter use memory for hash, sort and bulk operation.
    any suggestion to reduce such merory uses.


    Regards,


    Varun Jha
    30. července 2009 8:40

Odpovědi

  • Hi Varun

    This is Mark Han, Mirrosoft SQL Support Engineer. I'm glad to assist you with the problem.

    According to your description, I understand that since SQL server memory useses increase almost 1 GB and the that max workspace Memory(KB) is too high, you would like to reduce the max workspace Memory(KB). if I misunderstand anything, please let me know, that will help us to resolve the issue quickly.

    in order to address your concerns, I would like to explain the following
    1 at first, I would like to explain what is the Maximum max workspace Memory(KB). amount of memory available for executing processes such as hash, sort, bulk copy, and index creation operations. Therefore, when we are runinig lots of BULK stmnt in our database, it is normal that the max workspace Memory(KB) will increase.

    2 to further diagnose the problem, we need to check if there are some SQL memory bottlencks when the SQL server memory useses increase almost 1 GB. So, we should monitor the issue until it happens and check the  following performance count

    ·         Buffer cache hit ratio

    ·         Page life expectancy

    ·         Checkpoint pages/sec

    ·         Lazy writes/sec


    when we found the checkpoing page/sec and Lazy writes/sec is high, we could know the SQL has memory bottlececks.

    3 after check the performance log, if we found the SQL doesn't has the memory bottlenecks, since SORT and HASH operators also will require a lot of workspace memory, we could use SQL profiler to verify what query/command run SORT and HASH operators and then we might be able to optimiz these query so that the max workspace Memory(KB) is able to descrease

    4 for this kind of  problem, we also need to check if there is missing indexes and need to delete some unuseful index to improve the performance of the SQL

    Besides, Based on the current situation, since we need to run lots of BULK stmnt in then database and according to the workload, the SQL requests more  memory; So we also could add more memory for the SQL Server. I understand that there are just 4 GB memory on the machine; So let's add  more physical momery on the machine and allocate more memory to the SQL (set Max memory)

    if you have any questions on the above, please let me know.

    I look forward to hearing from you. Thanks.

    Regards
    Mark Han

    5. srpna 2009 4:13

Všechny reakce

  • in Server Exploere of SSMS(SQl Server Management Studio )  Right click on the server the click on the memory
    here you can specify min and max memory

    http://msdn.microsoft.com/en-us/library/ms181453.aspx

    Arif



    --------------------------------------------------------------------------------
    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution

    30. července 2009 10:32
  • How much memory running on server, Max memory option can be helpful on 32 bit server with AWE switch enable.

    **Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79
    30. července 2009 11:36
  • 4 gb total
    3gb allocated to sql server

    Varun Jha
    30. července 2009 11:41
  • Varun, I hope that u have enabled AWE option, just confirm it by click on running values.

    I think u should work on query optimization first, may be u can solve it by creating some non cluster indexes. Just run the sql profiler or u can get missing indexes information through dmv. Also use perfmon and include  Buffer Manager counter and see buffer cache hit ratio and page life expectancy. page life expectancy should more than 400 and buffer cache hir ratio should be 99-100.





    **Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79
    30. července 2009 11:53
  • 1) Buffer cache hitration is 99.89
    2) page life expectancy is 178458
     they both are good i will some missinf indexes and do some query optimizqtion to reduce utilization...


    Thanks

    Varun Jha
    30. července 2009 17:45
  • Hi Varun, I think you have to work on performace tuning, pls find missing indexes and also delete indexes which are not in used.
    **Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79
    31. července 2009 6:00
  • Thanks Raj

    Thanks for your support. iam working on it. need your support in future.

    Regards,
     


    Varun Jha
    31. července 2009 6:18
  • Hi Varun

    This is Mark Han, Mirrosoft SQL Support Engineer. I'm glad to assist you with the problem.

    According to your description, I understand that since SQL server memory useses increase almost 1 GB and the that max workspace Memory(KB) is too high, you would like to reduce the max workspace Memory(KB). if I misunderstand anything, please let me know, that will help us to resolve the issue quickly.

    in order to address your concerns, I would like to explain the following
    1 at first, I would like to explain what is the Maximum max workspace Memory(KB). amount of memory available for executing processes such as hash, sort, bulk copy, and index creation operations. Therefore, when we are runinig lots of BULK stmnt in our database, it is normal that the max workspace Memory(KB) will increase.

    2 to further diagnose the problem, we need to check if there are some SQL memory bottlencks when the SQL server memory useses increase almost 1 GB. So, we should monitor the issue until it happens and check the  following performance count

    ·         Buffer cache hit ratio

    ·         Page life expectancy

    ·         Checkpoint pages/sec

    ·         Lazy writes/sec


    when we found the checkpoing page/sec and Lazy writes/sec is high, we could know the SQL has memory bottlececks.

    3 after check the performance log, if we found the SQL doesn't has the memory bottlenecks, since SORT and HASH operators also will require a lot of workspace memory, we could use SQL profiler to verify what query/command run SORT and HASH operators and then we might be able to optimiz these query so that the max workspace Memory(KB) is able to descrease

    4 for this kind of  problem, we also need to check if there is missing indexes and need to delete some unuseful index to improve the performance of the SQL

    Besides, Based on the current situation, since we need to run lots of BULK stmnt in then database and according to the workload, the SQL requests more  memory; So we also could add more memory for the SQL Server. I understand that there are just 4 GB memory on the machine; So let's add  more physical momery on the machine and allocate more memory to the SQL (set Max memory)

    if you have any questions on the above, please let me know.

    I look forward to hearing from you. Thanks.

    Regards
    Mark Han

    5. srpna 2009 4:13
  • Hi Mark,

    How do I know the checkpoing page/sec and Lazy writes/sec is high?  Do you have example?  Which tool can i check/monitoring the checkpoing page/sec and Lazy writes/sec?  If we do have the problem(is high), how do we fix the problem?  Add memory?

    Here is the results from sysperfinfo table:

    Lazy writes/sec: cntr_value: 13171, cntr_type: 272696320

    CheckPoint pages/sec: cntr_value: 65542, cntr_type: 272696320

    Page life exectancy: cntr_value: 4226

    cashe hit ratio: 2864

    We have a cluster db 2003 server with two database instances, here is config for the physical memory:

    * database instance 1: 7GB;

    * database instance 2: 7GB;

    * OS: 2GB

    When i look at performance from task manger, here is the results:

    * physical memory(k),  total  16771724  Available:  772876   system cashe: 856088

    Totals: Handles 23292  Threads 1018  Processes 79

    --------

    Questions:

    *  Why availabel memory only 772MB, I checked we didn't run any job at this time (when we run the job, the server is   very low)

    * There are only 15 processes that I can view from task manager, total mem usage is 92 MB

    * Where is the total 16 GB?  I'm not looking for available 16GB, should I have more than 700 mb availble or even close to 10 -12 GB?

    Please advise and help. 

    Thanks,

    Qin


    12. července 2011 18:27