| Author |
Topic |
|
efelito
Constraint Violating Yak Guru
478 Posts |
 Posted - 2008-11-13 : 23:05:22
|
| Hi all. This might not be the right place for this, but I am at a loss and looking to find a direction. I believe I might be seeing some corruption or some other issue in the procedure cache. Every few weeks or so we have a web page start timing out with a script error. It's important to note that it's a script error and not a timeout on the database connection object.When I run a profiler trace I see a starting event, but never get a completed event. When I run the query I captured in the trace in a tool like SSMS, it returns results just like it should. I wrote a small test application which calls the problem procedure and it sits hung for ever. It doesn't obey the timeout on the connection string. The only way I can get the applications to run the query is to either run an alter on the procedure or to flush the procedure cache. Once I do either of those the procedure starts returning results from the applications as expected. That's what makes me lean towards some kind of issue with the cached plan. We are running SQL Server 2005 Enterprise - 9.00.3042.00 (X64) on a Dell 1950 with 2 Quad Core Processors and 32 GB of RAM. Any leads or nudges in the right direction would be greatly appreciated.Jeff Banschbach, MCDBA |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-14 : 10:23:21
|
| Haveyou checked page life expectancy and Procedure cache and Buffer cache hit ratio? Looks like your plan is of no use as lazy writer writes pages to disk or page life expectancy <300? Check whether parameter sniffing is causing recompilation of SP? |
 |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2008-11-14 : 10:41:03
|
| I have been keeping an eye on some of those numbers. Our page life expectancy averages around 75 seconds during peak system usage. The buffer cache hit ratio averages just over 99% and the procedure cache hit ratio averages around 95% (99% on object plans and 78% on sql plans). I'm pretty sure all of those are pretty healthy except maybe the page life expectancy. Thoughts on how to proceed?Thanks,Jeff Banschbach, MCDBA |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2008-11-14 : 10:48:52
|
| FYI, I just did a little digging and I see that MS recommends page life expectancy be around 300 seconds, so it looks like we're dealing with some memory pressure. I've seen memory pressure cause some seemingly random issues. Knowing that I'm going to ask a silly question. Does anyone know if memory pressure can cause the issue I described above?Moderator - Please feel free to move this to a different forum as it's looking like it has nothing to do with data corruption at this point.Thanks,Jeff Banschbach, MCDBA |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-14 : 11:01:19
|
| Yes 75 is too less for page life expectancy. That mean pages doesn't stay too much in buffer. What about lazy writes/sec? Is your Server 64-bit or 32-bit and what editions? |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2008-11-14 : 11:45:13
|
| Lazy Writes/Sec are averaging around 40. We are running SQL Server 2005 Enterprise - 9.00.3042.00 (X64) on a Dell 1950 with 2 Quad Core Processors and 32 GB of RAM.While page life expectancy is low, I'm a bit confused as to why the buffer cache hit ratio is so high. I would think that the cache hits would be lower if it wasn't finding the data it is looking for in the cache.It looks like we might be seeing some pressure from the plan cache. I ran dbcc freeprocedurecache and we've been averaging around 200 seconds on page life expectancy since then. Thanks,Jeff Banschbach, MCDBA |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-14 : 12:08:48
|
quote:
Originally posted by efelito
Lazy Writes/Sec are averaging around 40.It is too high.It should be <20.It indicates pages are flushed to disk and cache is not big enough. We are running SQL Server 2005 Enterprise - 9.00.3042.00 (X64) on a Dell 1950 with 2 Quad Core Processors and 32 GB of RAM.While page life expectancy is low, I'm a bit confused as to why the buffer cache hit ratio is so high. I would think that the cache hits would be lower if it wasn't finding the data it is looking for in the cache.Actually, Cache hit ratio is not real time measurement.It gives averages since last time SQL Server was started.It looks like we might be seeing some pressure from the plan cache. I ran dbcc freeprocedurecache and we've been averaging around 200 seconds on page life expectancy since then. Have you enabled 'lock pages in Memory'? It is very important to enable in 64-bit server for EE edition? We have solved so many issues with it?Thanks,Jeff Banschbach, MCDBA
|
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2008-11-14 : 12:23:27
|
First off thanks for all your help so far.  Lock Pages In Memory is granted to the account running SQL Server.In the end, it looks like we are seeing memory pressure which is proven by the low PLE number combined with the high lazy writes. I believe that it is mostly procedure cache pressure and that I know which application the pressure is coming from. I'm sure there is some pressure caused by poor indexes as well. Since we are maxed out on the RAM in these servers already, my only quick resolution is to run dbcc freeproccache periodically to keep PLE up. Then I'll start hitting the problem application.Thanks again and let me know if you have another suggestions. If you're going to be at PASS next week I'd be happy to buy you a beer. Thanks,Jeff Banschbach, MCDBA |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-15 : 13:58:42
|
| Here is good articles that explain about memory pressure http://sqlblog.com/blogs/lara_rubbelke/archive/2008/04/18/memory-pressure-on-64-bit-sql-server-2005.aspx |
|
|
|
|
Procedure Cache