March 1, 2010 at 8:46 am
Hello
We’re running windows 2 node cluster(active\passive) 2003 ent, sql 2005 sp3, 16 cpu, 32 GB memory with AWE enabled.
Database size is 600 mb on average.
Number of databases – 100.
As per perfmon, the number of sql compilations is above 100 per sec
Performance on the box is great, however from time to time our ‘memory wait’ on the box is going up
Causing slowness and database lock escalations. I found that high number of compilations are due to the
High number of ‘dynamic queries’ ran against the databases.
Here is my question for you gentlemen:
At the time when we have elevated # of ‘dynamic queries ‘ ran against the dbs – I’m getting the ‘memory wait’ spikes.
How is that possible that ‘something ‘ is waiting for that memory to be ‘available’ if I have 26GB(out of 32 GB total) allocated to sql?
Database size is 600 mb on average.
Thanks,
March 1, 2010 at 11:08 am
Look at the SQL server log. Is SQL Server getting its memory trimmed during this period of time? Also, If you are running a 64 bit machine turn off AWE. Also, grant the account running SQL Server the privilege to lock pages in memory.
Regards,
Toby
March 1, 2010 at 11:15 am
it's a 32bit, not 64...
and it does have 'lock pages in memory', i did it during the original server build.
can you please elaborate on 'memory trimmed'? Not sure what it means.
March 1, 2010 at 11:30 am
If the account has lock pages in memory then the trimming probably isn't applicable. This can happen when the O/S decides to reclaim memory from SQL Server. The fact that you are running a 16 cpu 32GB machine on a 32 bit platform is somewhat disturbing - no offense intended.
I assume your min/max memory for SQL Server is 26GB. I also assume you have your PAE switch set. I would look at how much memory is being consumed by the buffer pool and by the procedure cache and then look at what the page life expectancy is for the buffer pool during the period in question. Also, you might want to recheck your underlying assumptions about how SQL Server is using the memory just to make sure it actually consuming the memory you think it is. If you don't have any monitoring tools you can use the DMV's. I have a decent one to tell you what is in the buffer cache.
Regards,
Toby
March 1, 2010 at 12:07 pm
Min 1GB, Max 26GB, total windows 32GB
PAE switch is set
Buffer cache size 23.5
Hit Rate 99.9
page life 37 days
Proc cache size 513MB
Hit Rate 65.4
What do you think?
Also, 'period in question' happaned to be 4 days ago.
Is there a away to check 'historical' page life?
Thank you for your time, BTW!
March 1, 2010 at 1:17 pm
The stats seem good on your server other than procedure cache hit ratio - but that is to be expected with dynamic SQL. It also looks like there is no memory pressure what so ever with the buffer pool life expectancy so long. I don't have any solid theories at this point, other than a funny feeling that either the O/S or SQL Server is trying to do something strange during the affected period. I would look in the SQL Server log and the Application and System logs and check the events for the affected time period.
Regards,
Toby
March 2, 2010 at 7:50 am
Could well be a memtoleave problem, since you are 32bit. -g startup parameter can be used to increase this. You may wish to get a professional involved to help you - some of this stuff can be quite complex and deep.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 2, 2010 at 2:18 pm
One thing you forgot to mention (or I did not see) is the version of SQL. Is it Enterprise or Standard? Standard Edition will not take the lock pages in memory by default.
One other thing to consider is that, if memory serves me well, AWE only applies to buffer pool, not to other parts of the SQL memory, so it cannot be used for cached plans.
March 2, 2010 at 2:29 pm
SQL is Standard....
Windows 2003 - Enterprise
I have't had any problems applying 'lock pages in memory' and enabling AWE.....
But even if you are right about 'buffer pool only'......2 GB of standard sql memory should be more than enough
to hold even big number of 'execution plans', how much space can the exec plan take....
March 2, 2010 at 2:47 pm
Please refer to this link for the lock pages in memory issue
http://www.sqlservercentral.com/articles/Lock+Pages+in+Memory/67975/
For memory usage, have you tried the report "Memory Consumption" ?
One of my servers (the heaviest on ad-hoc SQL) reports about 5 Gb for CACHESTORE_SQLCP, so the AWE limitations could be the cause of memory contention.
March 2, 2010 at 3:41 pm
SD1999 (3/2/2010)
SQL is Standard....Windows 2003 - Enterprise
I have't had any problems applying 'lock pages in memory' and enabling AWE.....
But even if you are right about 'buffer pool only'......2 GB of standard sql memory should be more than enough
to hold even big number of 'execution plans', how much space can the exec plan take....
Cached plans can take a LOT of memory, and they aren't the only things that compete for that memory space.
Oh, you could also search web for documentation on dbcc memorystatus and gather information from that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 3, 2010 at 4:56 am
Hi,
what sticks out in this scenario to me is the large number of databases on a single instance! SQL will be constantly re-allocating memory to the active DB at that time. possibly a cause of memory waits.
was/is a particular infrequently used database in use at the time of the waits.
Perfmon stats can be persisted to disk as can a profiler traces, try these over ad see if they can throw out any unusual trends
March 3, 2010 at 10:51 pm
The cached plans are stored in the bufer pool. In act, it's generally the largest part of the buffer pool.
You can improve the cache hit for your plans by forcing parameterization of your dynamic SQL. Also, extensive use of dynamic SQL also causes the security token cache to be larger. And even though the security toekn cache is very small, you can see significant performance issues if it grows too large. SQL should be automatically trimming this cache, but sometimes on a very busy system, it's not able to trim it as fast as it grows.
What we don't know so far is what is causing the memory wait. What task is waiting for memory? I suspect that SQLGuru may be correct that it is a memtoleave (non-buffer pool memory) usage issue. There may not be enough memory left for the OS and non-buffer pool processes.
Another possibility is that you have a query that is performing a hash join on very large tables which requires a very large amount of memory.
March 4, 2010 at 12:30 pm
According to Quest Performance Analysis, tasks that were in the queue were a load of 'other dynamic sqls' for 6-7 'victim' databases.
Out of 32Gb on the server - 26 were allocated for sql and 8GB still left at OS disposal, don't you think this is enough?
Yes, we do have a big number of joins in those 'dynamic sqls', however looks like only indexed(PK) columns have been used in them.
Also, average db size is 400mb only.
March 4, 2010 at 1:31 pm
The cached plans are stored in the bufer pool. In act, it's generally the largest part of the buffer pool.
Robert, that is not true at all, at least not since SP1 drastically restricted the allowable size of the procedure cache.
OP: You have been going back and forth on this for 3 days now. It certainly must be past time to get either Microsoft CSS or a qualified consultant on board to assist?? You simply cannot effectively hunt-and-peck your way to a solution on this type of problem. :ermm:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply