Ran into this a while back, and we finally found a root cause so, I thought Id put it out here in hopes that it saves at least 1 person the amount of head bashing I had with it
Windows 2003 Enterprise R2 SP2 w/32GB RAM
SQL Server 2005 standard ed SP3 64bit active/passive cluster
We started seeing this glorious message in the SQL Server Error log.
A significant part of sql server process memory has been paged out. This may result in performance degradation. Duration XX seconds. Working set (KB) XXX, committed (KB) XXX, memory utilization 0%
The message varied slightly but the essence was always the same.
This error message can be too common on systems where SQL memory is misconfigured or where something is unduly pressuring SQL for memory. In this case a quick verification of the settings showed that everything was in order. The first 2 times this happened it was the middle of the night during backups(in the SLA window), so no one really noticed a performance degradation. We didn’t think much of it at the time but in hindsight, we should have.
Monday morning 8 AM, developer makes a bad update to the database, No problem I say, Litespeed can rollback the transaction, So I start to copy the full db backup+tran logs off the server (~25gb) this is the way we process litespeed recoveries through the log reader. About 3 minutes later the server became totally unresponsive, and the error about paging the SQL process memory was logged. At the time I didn’t put 2 and 2 together as this particular server runs a varied workload of about 1500 batches/sec and has anywhere from 1200-2500 connections open at a time, so It could have been anything! After some further digging I figured out that the file copies were causing the sql memory to get paged out. At the time I had never heard of a file copy causing an issue in SQL Server!
The experts weigh in
While looking at the issue 2 perfmon counters stuck out–> Memory\Cached Bytes and Memory\Avail MBytes. While file copies were happening the cache bytes counter would increase very quickly while the avail bytes counter would drop, once the available mbytes dropped to 0 sql server started to page memory out. After a bit of paging, the errors were logged that SQL had its memory paged out and SQL became unresponsive. Since this was a high priority system, I did what any good SQL Server DBA would do, I contacted a few people in my network who may have seen this before. Interestingly enough I got the exact same response from every one of them, “use lock pages in memory” and don’t use windows explorer to do huge file copies as this is a known “problem”.
Even though I trusted my sources of info, I had a hard time believing that file copies of sizes all the way down to 1GB would cause this sort of havoc without this being something Bing+Google would know about (different file sizes mattered, some sizes worked fine, some would cause the problem).
Workarounds not welcome
I had a valid workaround with lock pages in memory and not using explorer for file copies but, I don’t normally like workarounds such as this on systems as important as this one is to us. After a few server rebuilds we finally figured out that we could reproduce this issue on any win2k3 R2 ent ed 64bit server, this would be the clue we finally needed to make a breakthrough. After rebuilding the systems from scratch and loading no drivers except SAN we noticed that we couldn’t cause the error! So, after painstakingly adding each and every piece of our standard server build we realized that Symantec AV (10.1.9.9) was the cause. Yes, another file system filter driver was misbehaving.
In looking back through the change communication, we ID’d where a new version of AV was pushed out and we just didn’t hit the error soon enough after the installation to put 2 & 2 together. Since disabling AV wasn’t an option we started trying to find a setting that specifically caused the problem and happened across a change that could be made and allow AV to run and SQL to not get paged out. By unchecking the network scanning options, the windows cache no longer increases during a (network) file copy. problem solved!!
Some time in the future vendors are going to figure out how to write good file system filter drivers, or they are going to stop trying to use them! After fighting this issue for a few weeks (or was it months) I can only hope this happens sooner rather than later
In Houston? Hungry for lunch? Want to learn SQL Server from an expert? Head over to the Microsoft office and learn the top SQL mistakes and how to avoid them from Kevin Kline
Not in Houston? or hungry? there’s always the live meeting option!
Here are the details from the HASSUG Site:
Houston Area SQL Server User Group Monthly Meeting Reminder
When: Tuesday, April 13, 2010 – 11:30am-1:00pm
Where: Microsoft Houston Office
2000 W. Sam Houston Pkwy. S. #350
Houston, Texas 77042-3615
Pizza and drinks provided by Idera.
Conference Call for audio – 1-888-320-3585 (passcode 76027128)
Topic: Top 10 Mistakes on SQL Server
I’ve been focusing on different aspects of speakers and their interactions with PASS lately. Id like to expand on that and take a large detour.
The AHA Moment
last week while going over some ideas and upcoming changes to the PASS Program Committee with my PASS boss, Jeremiah Peschka I had what I think is a great idea, and Id like to throw it out here and see If I cant help get it off the ground.
It seems like a lot of the suggestions I’ve been getting lately with regards to the program haven’t really been relevant to the problem I was trying to solve. At first I thought I wasn’t explaining myself properly or I was writing in the same manner my brain works (all over the place) . Then It donned on me, maybe I’m trying to solve the wrong problem. (I know this was definitely one of my greatest moments of clarity). In a lot of what we do in the program committee we’re really not positioned to help speakers grow perse. But, speakers still need help, they still want to refine their craft, learn new tricks, etc. I know I for one would love to get started speaking but, while I can tell who is a good speaker, I certainly don’t know how they got that way, surely they weren’t born with it!!!
The BIG Idea
Why cant we make a new Virtual Chapter within PASS for speaker development. I think this would provide a great venue for speaker development, It would be great if we could get the best speakers within the SQL community to give a monthly livemeeting about the different aspects of developing speaking skills. The topics could range from how to prepare for a topic, how to craft a great abstract, how to create great audience interactivity, how to create great demo’s/powerpoints etc etc. There are literally hundreds of topics that I could see being presented that would be of great use to developing speakers. If the idea takes off, I could foresee an exchange of sorts, where hopeful speakers are matched up with experts for mentoring. Think SQLMatch.com where you look for a mentor for your SQL related speaking! I think with my grand total of 0 sessions experience I wouldn’t be an appropriate person to lead this VC but, I would certainly be willing to help another more qualified person with the details and help out anywhere needed.
How can I see this through
If only I had some contacts in the SQL speaker community who would be willing to present on these topics? Surely there are some talented SQL Speakers that would be willing to step up to the plate and offer to help out those of us just trying to start the process of learning to present. Getting willing speakers to talk about these topics should be the hard part, the scheduling/leading the VC should be pretty easy but its still additional work.
So…… Which SQL Community Speaker/Leader wants to help me, help the community, and make this happen? I’m totally open to any ideas, I’d just like to see this get started!
I’ve been kicking around several ideas in the program committee and a couple of them have to do with what information PASS releases, specifically information about the speakers. The general question I’ve been trying to come up with ideas about, and the subject of this blog post is:
Should PASS Release Speaker Evaluation Scores to the public
As with all things, there are goods and bad’s to releasing this data. And there are even more possible ways to release the data.
Personally, I’d like to release aggregate scores for every session at every summit. I’m not sure that is a practical option though. I’m hoping some people out in the community might tell me that I’m thinking this through too much and no one cares, after all many of our speakers use speakerrate.com which is totally open.
Everyone who purchased the Summit DVD’s would know when looking at their session lists which to focus on first
Every speaker would know exactly how they compared to others, I think this would be especially helpful to speakers who are just starting their craft.
New conference attendee’s would know which speakers have better ratings and could plan their itineraries appropriately.
Every speaker may not appreciate their scores being published (especially lower rated speakers)
Privacy: See above
We could simply require that if you want to speak at the Summit, we will release the results. We could put some language in the terms that are agreed to by the speakers, actually I think we could do this without changing the current terms but, I do not think this is the right thing to do, it just doesn’t pass the smell test for me.
We could include an opt-in on the speaker terms, which would allow disclosure. While this would be the easiest option to implement with the least amount of backlash, I don’t think it really accomplishes much, mainly because without the complete picture of the scores you wouldn’t know how the speakers ranked or if you were a speaker it would make it tough to know for sure where your session stood.
We could release the complete score list to every speaker, this would solve the speakers knowing where they rate in the crowd. Unfortunately It wouldn’t help those in the community know which speakers were the best.
As with all things PASS I’m open to suggestions, and maybe I’m missing something, let me know
Last time I wrote about limiting speakers to 1 session per summit, I appreciate the feedback, and thought id take another turn at this topic.
As I was reading the comments it reminded me that last year, I was asked by PASS marketing to pull a list of what we considered “new presenters”. I thought I would revisit that list and give some of the statistics I discovered.
I want to start by saying I did this research almost 9 months ago, and I didnt reverify that this information was correct enough to post, it was pulled from their submitted bio’s. So feel free to point out if something is wrong.
We had 85 unique community speakers in 2009. Of these 85 I was able to identify 17 people who were new to the summit within the prior year (08 and 09)
Gail Shaw 08
Grant Fritchey 08
Denise McInerney 08
Denny Cherry 09
Jacob Sebastian 09
Brent Ozar 08
Rod Colledge 09
Jamon Bowen 09
Jonathan Kehayias 09
Michelle Ufford 09
Ravindra Gurram 09
Jason Massie 09
Rob Garrison 09
Glenn Berry 09
Kendal Van Dyke 09
Kevin Guinn 09
Trevor Barkhouse 09
Every speaker on this list wasn’t new to speaking, they were new to speaking at the PASS Summit. Which is exactly the kind of new speakers we’re looking for.
At first glance Id be willing to say that PASS is already doing a pretty good job of involving new speakers. Using these numbers it looks like we already incorporated approximately 20% new speakers within a particular selection year. While ultimately I think that number could(should) inch up even higher, Im happy to know that we’re not doing so badly at this that I want to go visit an Access conference just to raise my spirits.