November 26, 2002 at 9:12 am
I'm trying to ID problems on a busy server, and I'm fairly certain it has something to do with memory. When users are reporting poor performance, such as slow screen pops and long queries, the pages/sec counter is through the roof, and the free buffers counter dips below 100. This can last for hours at a time, yet the cache hit ratio remains a consistent 99%. From what I have read in BOL, this seems to be a contradiction.
The total processor % is spiking between 80-100 during this time (4 cpus), and there are periods of heavy disk activity as well; but it begins with the paging rising and the buffers falling. It is a SQL 7 box with 2GB ram, so I can't add more memory.
There are no jobs or backups running during office hours, other than quick log backups, and SQLServer is the only significant user of memory or cpu cycles.
Any suggestions as to how to figure out what the underlying problem is?
TIA,
Rob Haas
November 26, 2002 at 1:51 pm
Is the cache hit ratio taking a hit? Have you checked to see if there are lots of physical reads? You can run the queries in QA at the same time using SET PROFILE ON and SET STATISTICS IO ON and see the physical v logical reads. Physical reads are reads to disk where the memory is not holding the data needed.
Also look at recompiles or compiles at a busy time. This might indicate that you do not have enough memory to store the plans.
Steve Jones
November 26, 2002 at 3:31 pm
Looks like your reading loads of data.
Look at the number of requests compared with the number or actual reads. If there are not significantly different your are reading a very large amount of data from disk. Would suggest some table scans, you should not have this in an OLTP system but can in a reporting system
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 27, 2002 at 8:14 am
Thanks to you both for your timely replies. The cache ratio is NOT taking a hit, which is part of what puzzles me.
Steve, would physical reads be the same as page reads?
Simon, can you elaborate a little more on what you mean by requests vs. actual reads? Are these disk IO stats or are we talking about memory?
I have added compiles and recompiles to my perfmon and am waiting to see what happens. Part of the problem is that this is an intermittent problem, so I don't know if it is indicative of a piece of hardware which is starting to fail, or a poorly written but seldom executed procedure, or a rogue process, etc.
If it turns out to be a memory issue, what are my options, given I am already maxed at 2GB memory? I can only think of load balancing and/or significant code revision, or upgrading to S2000 and throwing more ram at it.
December 2, 2002 at 7:44 am
If you're running with 2GB of total RAM on the system, you're not really maxed at memory. You can add 2 more GB of RAM to your system and put the /3GB switch in your boot.ini file.
December 7, 2002 at 6:23 pm
quote:
Thanks to you both for your timely replies. The cache ratio is NOT taking a hit, which is part of what puzzles me.Steve, would physical reads be the same as page reads?
Simon, can you elaborate a little more on what you mean by requests vs. actual reads? Are these disk IO stats or are we talking about memory?
I have added compiles and recompiles to my perfmon and am waiting to see what happens. Part of the problem is that this is an intermittent problem, so I don't know if it is indicative of a piece of hardware which is starting to fail, or a poorly written but seldom executed procedure, or a rogue process, etc.
If it turns out to be a memory issue, what are my options, given I am already maxed at 2GB memory? I can only think of load balancing and/or significant code revision, or upgrading to S2000 and throwing more ram at it.
1) No physical reads are reads from the Hard Drive no data found in cache. You want many logical reads as possible. This means it was able to pull data from memory instead of hitting the IO of the Hard Drive subsytem. If always high on physical then you are suffering a memory bottleneck for sure. But keep in mind don't run the query once as eventually some queries data may be removed as the query to make room for others data.
2) Not fully sure here what his line of thought is unless he is talking about Logical reads and Physical Reads which I am pretty sure he is.
3) Let us know if you get anymore details. Intermittent issues are among the hardest to solve. May try setting up a test server and specifically try everything that would at some point run on the server anyway to see what happens. Also, try using a less beefy box, as it tends to amplify the effects of issues when resources are smaller.
4) SQL 2000 can support up to 64GB of memory depending on the hardware and OS you are running. See "Memory Architecture" in BOL (books online) for additional details and see your hardware specs and OS help on this.
December 10, 2002 at 11:46 am
It's happening right now. The level of disk activity may have been coincidental, as it is low this time. The pages/sec and page faults/sec are off the chart, and the CPU is maxed. It's very much related to the number of user connections.
I am tracing to determine the problems, as deadlocks eventually develop as a result of the slowdown. The problem is that the managers scream at me for running the traces on production since they feel it is taking up too many resources.
The front end is a web app which is firing JSQL statements. I am trying to determine whether it is the code in the web app which is causing the problems, or whether the number of users has outgrown the platform.
December 10, 2002 at 1:22 pm
One thing to keep in mind is that going from 2GB to 4GB isn't really a great benfit. A few hundred MB of RAM. The reason? The OS needs memory to handle the paging of data pages in and out of memory. It also has overhead. The 1.7GB you see is virtual, not physical. When you add the /3GB and assign mem to SQL Server (and you must decide how much to assign, you'll only really go up 500MB or so before you start losing performance. If you're going to add memory, bump up above 4GB so the OS has plenty of RAM as well. I'd go to 6 or 8GB of RAM.
Steve Jones
December 10, 2002 at 5:01 pm
Just out of curiosity since I do have a server I may be increasing the memory on soon. Do you have any supporting documentation or benchmarks to show this?
Thanks
December 11, 2002 at 9:21 am
robhaas is running SQL 7 so /3GB or upgrading SQL are the only options for more memory.
You'll need SQL 2000 to run 6 or 8 GB.
December 11, 2002 at 9:40 am
From SQL 7 BOL
quote:
extended memory size OptionThe extended memory size option is available only for Microsoft® SQL Server™, Enterprise Edition running under future versions of Microsoft Windows NT®, Enterprise Edition, on Intel® and Alpha platforms. SQL Server utilizes the Enterprise Memory Architecture (EMA) feature that will be available on these platforms in this future version of Windows NT. For more information about configuring your system to enable the EMA feature on a particular system configuration, see your Windows NT documentation.
In addition to the EMA feature support available on future versions of Windows NT, some system vendors may make available products for Windows NT, version 4.0, Enterprise Edition, that SQL Server, Enterprise Edition can use for the extended memory size option. On Intel platforms, SQL Server can use a feature known as PSE36, and on Alpha platforms, the feature is known as VlmCache. For more information about availability, installation, and configuration of these products, see your system vendor.
The option indicates the number of megabytes (MB) of memory to use as an extended cache in addition to the conventional buffer pool. The maximum value to which that extended memory size can be set depends upon the total physical memory available and the amount of physical memory used by SQL Server conventional memory. For example, on a computer with 8 gigabytes (GB) of memory and 2 GB of SQL Server conventional memory, a reasonable value for extended memory size might be in the range of 5000 through 6000. (Windows NT on the Alpha platform supports up to 2 GB of conventional memory usage for SQL Server.) If the same 8 GB system had 3 GB of conventional memory usage for SQL Server, then the option should be set in the 4000 to 5000 range (Windows NT, Enterprise Edition with the 4GT feature supports, on Intel platforms, up to 3 GB of conventional memory usage for SQL Server).
Vendor specific hardware features limit the amount of memory that the EMA feature can support, and therefore that can be used by SQL Server. For Intel platforms with appropriate hardware support, this limit is 64 GB. For Alpha platforms with appropriate hardware support, this limit is 32 GB.
extended memory size is an advanced option. If you use sp_configure system stored procedure to change the setting, you can change extended memory size only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.
December 11, 2002 at 11:33 am
No supporting docs (yet). Trying to get some from NETIQ. One of their techs had done a bunch of testing, but I've asked this question of PSS, NetIQ, MS Perf Consultants, and a member of the dev team.
All tended to agree that 4GB wasn't a great # for SQL. Better to jump from 2GB to 6 or 8GB. Mainly because of the paging overhead with AWE. Don't want to starve the OS on this side. They tended to look at the , is it worth getting 500MB more for SQL? Not necessarily for the $$. Better to go ahead and get 2 or 3GB instead.
Steve Jones
January 29, 2003 at 5:12 pm
I believed (I dont deal with these issues a lot) that SQLServer should never have significant Memory:PageFaults -- because it should be managing the available physical memory with its own BufferManager, not via OS pagefile.
Is SQLServer setup with a fixed maximum amount of memory rather than allocate dynamically ? - If this is too high SQLServer keeps asking for more than the OS can spare.
(Else) Are there other services that are competing for memory - web server ? - can they be transferred to another server ?
What number of users ? Is it an option to replicate the database to split the users ? Probably depends on update traffic being not too high ?
I'm asking questions more than professing answers - I'm curious on whether there are alternatives to "buy more RAM".
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply