SQL Server Performance

How to get SQL2000 to use more memory?

Discussion in 'Performance Tuning for DBAs' started by nshaan1, Dec 18, 2002.

  1. nshaan1 New Member


    I have a 4 CPU, 4GB RAM machine having SQL2000 (Enterprise Edition) loaded. My (java) web application is loaded on the same machine on Tomcat. My problem is that neither the application nor SQL2000 utilizes the available processor or memory. As a matter of fact, I get the same performance as I get on a single processor, 256MB RAM machine !!!!.

    What could be the reason for this? I would appreciate any inputs to solve this problem. Thanks..
  2. satya Moderator

    What are the min and max memory settings on the server and how about the OS.
    Capture trace from PERFMON for those system and SQL counters to get some more information.

    Satya SKJ
  3. nshaan1 New Member

    No changes have been made to the default settings of SQL. Can u pl. provide more details about how to use perfmon ?
  4. satya Moderator

  5. sqljunkie New Member

    I would follow Satya's advice...

    If your statements are not parallelizing you may not see a performance improvement. Also, if you're pushing the same workload at the 4 CPU machine as you are at the 1 CPU machine you may not see any benefit. If you can add more users to test out your 4 CPU box you may see that it can handle more work at the same time without the response time (or query execution time) increasing...

    Also, could there be something in you Java layer that is limiting performance?
  6. bradmcgehee New Member

    What is your buffer hit cache ratio? If it is above 99%, then it is getting all the memory it thinks it needs for the time being. Are you using the /3GB switch in your boot.ini in order to take advantage of the 4GB of RAM? Is the SQL Server memory settings set to dynamic, or have they been fixed?

    Brad M. McGehee
  7. nshaan1 New Member

    I have not checked the buffer hit cache ratio. I shall do that now. I added the /3GB switch, but what I understand is that this switch would only help if SQL needs more than 2GB. My app does not go to that level at all. The SQL memory settings are set to dynamic.

    In response to Satya's question - Yes, presently we are pushing the same workload as a dual/single CPU to the 4 CPU machine (i.e. a single user). What we were expecting is that the time taken for various processes even for a single user would be comparatively much less on a 4GB RAM 4 CPU machine...

  8. nshaan1 New Member

    I apologize, it was rortloff and not Satya who has indicated the workload issue.
  9. satya Moderator

    Have seen information listed on this website about workload for dual or multi processors.

    Satya SKJ
  10. sqljunkie New Member

    Perhaps other people on this board has comments about this but I think that if portions of your SQL statements do not parallelize (or do not parallelize well) then the only benefit you'll see in a single user case with a similar workload is maybe the difference in processor speed. In general insert, delete and certain update statements do not parallelize. You should check your select statements and see if they are parallelizing. Those should run faster if they are parallelizing.

    Just out of curiosity what is the sp_configure setting for "Max Degree of Parallelism"
  11. satya Moderator

    I believe the similar topic is going on this section, worth looking into it for information.

    Satya SKJ
  12. nshaan1 New Member

    The Max degree of parallelism is set to 5. We have tried lowering this, but did not see a considerable difference. We have also increased the settings for Memory Per Query to a greater value.
    The application is very database intensive - lot of selects, inserts.

    I am no wondering if a higher RAM and CPU would really make a difference in performance for a single user because when I run the same process on a lower end machine, with a single user, it just consumes a finite amount of memory. There is no increase in consumption of memory or processor just because it is available.
  13. bradmcgehee New Member

    The only way to really know if a bigger server will help the performance of your application is to perform benchmarks on the big and smaller servers. As has already been mentioned, a bigger server many not help performance in all cases. I suggest you try to spend a little time performing some benchmark testing, and for now, using all of the default SQL Server settings. See this article for more information on how to perform benchmarking:http://www.sql-server-performance.com/performance_monitoring_tutor_part1.asp

    Brad M. McGehee

Share This Page