July is definitely a painful time to be in Tucson. It’s hotter than all get out and monsoon season has usually started, so for awhile we have heat AND humidty. Oh joy. Fortunately we have some SQL
At least this calendar has green on it...
Server based events coming up to take our mind off of the disagreeable weather.
Tim’s heading up the new incarnation of the PASS Performance VC. On July 6, Jason Strate (Twitter/Blog) is going to be presenting a webcast for them entitled: ‘Performance Impacts Related to Different Function Types’. It should be a great session.
On July 17, Phoenix is having it’s first SQLSaturday. That in and of itself is pretty exciting, but Tim and I are going to be presenting two sessions there. This is our first time presenting, so it’ll be a great learning opportunity for us and a potential opportunity for up and coming hecklers. If you’re somewhere around Phoenix, you should take advantage of the opportunity. If you’re not around Phoenix, but want to see what it would feel like to step into an oven, come on out. (see note below)
Then on July 21st, Quest is holding another Virtual Training Event on Performance Monitor and Wait Events. Brent Ozar (Twitter/Blog), Kevin Kline (Twitter/Blog), Buck Woody (Twitter/Blog) and Ari Weil (Twitter) will be presenting. It should make for an interesting and potentially hilarious training event. Aside from it being a great training event, it’s relevant here because they’ll be presenting live from beautiful Tucson. Hopefully we’ll be able to meet them for dinner and take them to another top-notch Old Pueblo eatery.
One final note – the final session lineup for the PASS Summit 2010 will be finalized in July. This is due to a huge amount of great work by the volunteers from the Program Committee. If it’s June and you’re reading this, send some good thoughts their way – they’re busy.
Update: The SQLSaturday in Phoenix has been postponed until Jan/Feb 2011. Hopefully many more people will want to come to Phoenix when it’s not 110 degrees out.
The ability to lock pages in memory is used on 64-bit systems to help prevent the operating system from paging out SQL Server’s working set. This may be enabled when the DBA starts seeing errors like the following:
"A significant part of sql server process memory has been paged out. This may result in a performance degradation."
If you’re running SQL Server 2005/2008 Enterprise, you would take the steps to lock pages in memory and you’re done with it. If you’re on SQL Server 2005/2008 Standard Edition, you still have a ways to go. The ability to lock pages in memory for standard
This flag will not help in this situation
edition is handled through a trace flag. For SQL Server 2005 SP3, you need to apply CU4 . For SQL Server 2008 SP1, you need to apply CU2. Once those CUs have been applied, set trace flag 845 as a startup parameter. Here’s a good ServerFault question that explains how to set a trace flag as a startup parameter.
Once the trace flag was enabled, the memory issues were solved. Day saved, once again. 
As with anything, this has the potential to degrade system performance. In this article, scroll to the section entitled “Important considerations before you assign “Lock Pages in memory” user right for an instance of a 64-bit edition of SQL Server”. Read it thoroughly prior to making any changes to your production systems.
I always thought that Mars was a planet, but apparently it also has to do with multiple pending requests within a single SQL Server connection. MARS (Multiple Active Result Sets) was introduced in SQL Server 2005 and provided the ability to handle these multiple requests. Like
Apparently this isn't the only Mars out there
anything else, though, it has to be used correctly.
About a week ago, I started seeing the following error on one of my servers:
DESCRIPTION: The server will drop the connection, because the client driver has sent multiple requests while the session is in single-user mode. This error occurs when a client sends a request to reset the connection while there are batches still running in the session, or when the client sends a request while the session is resetting a connection. Please contact the client driver vendor.
After digging around and talking to some of the developers in house, I found that they were making use of MARS, but not always correctly. To avoid the error above, ”MultipleActiveResultSets=True” needs to be added to the connection string. Adding that seems to have fixed the issues.
