Perf. counters not affected by memory encrease
-
Hi!
I had a server 6xP-III Xeon 700 2MB / 4GB RAM etc.
Before going to a new server I had the following values for Performance:
Cache Hit Ratio ~75%
SQL Compilations/sec ~30
SQL Re-Compilations/sec ~6
Now I am using 4xP-IV Xeon 2800 2MB Hyper threading enabled/ 16GB RAM etc.
And theses values:
Cache Hit Ratio ~72%
SQL Compilations/sec ~35-40
SQL Re-Compilations/sec ~8
The values for larger number of (SQL Compilations/sec & SQL Re-Compilations/sec) may be explained by better performance -> larger user activity, but I expected to have at least 90% for Cache Hit Ratio...
What could be wrong?
I am having and had MS SQL 2000 Ent sp3 with flags -T1118 & -T2861
The server has ~60GB in databases files.
The only thing was changed is the OS:
Win 2000 Adv Server sp4 -> Win 2000 Datacenter Server sp4
-
My guess is that AWE is not set up properly. What is SQL Server's memory usage? (SQL Server: Memory Manager: Total Server Memory (KB) or just Mem Usage for sqlservr.exe in Task Manager.)
--Jonathan
--Jonathan -
I have AWE enabled for MS SQL, also I have /PAE and 3G switches in boot.ini.
Max/Min Server Memory are both set to 15700.
Task manager does not show correct memory usage with AWE enabled (~183000KB), but available memory is correct (~250000KB)
SQL Server: Memory Manager: Total Server Memory (KB) shows ~15518000KB.
-
Wait a minute...
If you're performing more query plan compilations now, then your cache Hit Ratio would of course decrease; this would be normal after startup because the procedure cache starts out empty. That counter is the percentage of existing query plans that are used. Unless compiled plans were being flushed due to memory ceded to other processes, it should equilibrate to about the same percentage you saw before changing hardware, if all else remains the same.
I was thinking of the Buffer Cache Hit Ratio number; perhaps you were, too.--Jonathan
--Jonathan -
quote:
If you're performing more query plan compilations now, then your cache Hit Ratio would of course decrease; this would be normal after startup because the procedure cache starts out empty. That counter is the percentage of existing query plans that are used. Unless compiled plans were being flushed due to memory ceded to other processes, it should equilibrate to about the same percentage you saw before changing hardware, if all else remains the same.This would be normal after startup, but the Cache Hit Ratio should go up soon, cause the server has much more memory now to keep compiled plans. When I changed memory size from 2GB to 4GB on the old server the Cache Hit Ratio went up from ~70% to ~75%.
-
Regardless you should see an increase to I would suggest right at 100% with that much more RAM. Check in task manager and look at how much RAM SQL is using, what else is using memory. Stop any unneccessary services such as autoupdate and others. Also make sure that windows settings are for processor schedulaing to be balanaced against background services more so that programs and memory to be for system cache. (Right click my computer and goto performance items on Advanced Tab in properties.)
-
quote:
quote:
If you're performing more query plan compilations now, then your cache Hit Ratio would of course decrease; this would be normal after startup because the procedure cache starts out empty. That counter is the percentage of existing query plans that are used. Unless compiled plans were being flushed due to memory ceded to other processes, it should equilibrate to about the same percentage you saw before changing hardware, if all else remains the same.This would be normal after startup, but the Cache Hit Ratio should go up soon, cause the server has much more memory now to keep compiled plans. When I changed memory size from 2GB to 4GB on the old server the Cache Hit Ratio went up from ~70% to ~75%.
There may be many reasons for this, and I don't find it unusual. You obviously have queries that are getting recompiled for various reasons (cf the other counters you are monitoring). Each recompilation decreases the Cache Hit Ratio. You may have enough memory both before and after the hardware change that query plans are not being aged out of cache, so you will not see an increase in the ratio due to that. Unless you can exactly duplicate the application conditions before and after the change, I would just expect the hit ratio to be similar, not exactly equal and certainly not necessarily higher.
--Jonathan
--Jonathan -
How much memory SQL Server uses from Task Manager? Task manager wouldn't give your accurate information about how much memory processes use if you run Windows DataCenter.
Do you grant SQL Server service with "Lock Page in Memory" policy?
AWE does not extend the address space of a process. You can look at AWE memory as a very fast external storage. I believe the recompilation are still processed within the 3GB memory spaces the SQL Server can address.
-
quote:
I believe the recompilation are still processed within the 3GB memory spaces the SQL Server can address.
This is correct and could be relevant. The procedure cache is limited to the buffer pool space in the non-AWE (below 4GB) memory area. If you have multiple databases, though, you can use multiple instances and each instance will be able to take up to 3GB of the AWE memory for procedure caching. What is the number of pages in the procedure cache?
--Jonathan
--Jonathan -
2Antares686:
quote:
Regardless you should see an increase to I would suggest right at 100% with that much more RAM.- Did not quite understood this tricky phrase.
quote:
Check in task manager and look at how much RAM SQL is using,...-Task manager does not show correct memory usage with AWE enabled (~183000KB for sqlserver process), but available memory is correct (~250000KB)
SQL Server: Memory Manager: Total Server Memory (KB) shows ~15518000KB.
quote:
what else is using memory. Stop any unneccessary services such as autoupdate and others. Also make sure that windows settings are for processor schedulaing to be balanaced against background services more so that programs and memory to be for system cache. (Right click my computer and goto performance items on Advanced Tab in properties.)- Datacenter has no auto update service, others are turned off. Even if some are working, could they eat up so much memory (close to 16GB)?
Should I choose "Optimize performance for: Applications" ?
-
2Allen_Cui:
quote:
How much memory SQL Server uses from Task Manager? Task manager wouldn't give your accurate information about how much memory processes use if you run Windows DataCenter.-Task manager does not show correct memory usage with AWE enabled (~183000KB for sqlserver process), but available memory is correct (~250000KB)
SQL Server: Memory Manager: Total Server Memory (KB) shows ~15518000KB.
quote:
Do you grant SQL Server service with "Lock Page in Memory" policy?-Yes
quote:
AWE does not extend the address space of a process. You can look at AWE memory as a very fast external storage. I believe the recompilation are still processed within the 3GB memory spaces the SQL Server can address.Now I am having:
PagesMB
~193900015 148totalpages
~178900013 977databsepages
~1300001 016procedure cachepages
~1440001 125stolenpages
~560044freepagesSo I have only 1GB for procedure cachepages, that means, that 3GB is not limiting the server. I had only ~300MB for procedure cachepages, on the old server -> new server allocates much more memory for procedure cache.
-
2Jonathan:
quote:
This is correct and could be relevant. The procedure cache is limited to the buffer pool space in the non-AWE (below 4GB) memory area. If you have multiple databases, though, you can use multiple instances and each instance will be able to take up to 3GB of the AWE memory for procedure caching. What is the number of pages in the procedure cache?- See answer 2Allen_Cui: I have much less then 3GB for procedure cache.
-
quote:
2Allen_Cui:quote:
How much memory SQL Server uses from Task Manager? Task manager wouldn't give your accurate information about how much memory processes use if you run Windows DataCenter.-Task manager does not show correct memory usage with AWE enabled (~183000KB for sqlserver process), but available memory is correct (~250000KB)
SQL Server: Memory Manager: Total Server Memory (KB) shows ~15518000KB.
quote:
Do you grant SQL Server service with "Lock Page in Memory" policy?-Yes
quote:
AWE does not extend the address space of a process. You can look at AWE memory as a very fast external storage. I believe the recompilation are still processed within the 3GB memory spaces the SQL Server can address.Now I am having:
PagesMB
~193900015 148totalpages
~178900013 977databsepages
~1300001 016procedure cachepages
~1440001 125stolenpages
~560044freepagesSo I have only 1GB for procedure cachepages, that means, that 3GB is not limiting the server. I had only ~300MB for procedure cachepages, on the old server -> new server allocates much more memory for procedure cache.
Nothing unusual here, then. Due to all available memory being used for data caching, the lazywriter is aging out query plans, so the Cache Hit Ratio equilibrates to approximately the same value as before the memory addition. I.e. your databases are large enough that they are using all the cache they can get for data caching, including "stolen" pages from the procedure cache.
--Jonathan
--Jonathan -
quote:
Nothing unusual here, then. Due to all available memory being used for data caching, the lazywriter is aging out query plans, so the Cache Hit Ratio equilibrates to approximately the same value as before the memory addition. I.e. your databases are large enough that they are using all the cache they can get for data caching, including "stolen" pages from the procedure cache.- So what it is? Bad application/database design? Is there a way to correct this?
Some MS SQL options?
-
Just to notice:
I have a -T2861 flag turned on, that does not allow to flush out of cache zero-cost plans.
I was thinking of the Buffer Cache Hit Ratio number; perhaps you were, too.Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply