SQLServerTimes2

I’ve never had the opportunity to be on the abstract selection committee, so it was interesting to see the process in action. To be clear, I was not on one of the selection committees, but I am on the Program Committee so I was still involved in the process.

The abstract selection committees are chosen out of the group of people that apply to volunteer for the Program Committee. We work to ensure that each team includes at least one person that has been on an abstract selection team in the past. Our hope is that they can provide some additional guidance. We also provide at least one training session to go over the tools and answer any initial questions.

Prior to the call to speakers, the number of allocated sessions are set. They are allocated in total to fit the number of rooms that we have available. That total number is then split between the tracks (Application and Database Development, BI Architecture, Development and Administration, BI Client Reporting and Delivery Topics, Enterprise Database Administration and Deployment and Professional Development) to help make certain that we provide a balanced Summit selection.

Once the call to speakers closed, we knew that the abstract review committees were going to be in for a lot of work. Here are the numbers that we were looking at:

Total # of regular session abstracts submitted: 513
# of regular session community slots allocated: 72

Doing the math, that means that only 14% of the abstracts submitted were going to be selected. Within the tracks, that percentage ranged from 11% to 18%.

During the review process, the individuals on each team go through the abstracts in their track and rate them on 4 different areas – Abstract, Topic, Speaker and Subjective. Each of these areas are rated using a 1-10 scale and there is an area for comments. The abstract section has to do with, among other things, whether the abstract was complete (were session goals identified?), clear (was it easy to understand what the session would be about?) and interesting. The topic referred to the interest in and relevancy of the chosen topic. As far as the speaker – the abstract review teams had access to a report that provided previous Summit evaluation data for previous Summit speakers. They could also draw on personal knowledge or other information that they had access to. All of the individual scores added up to a total rating per abstract for the team.

Once the individual team members were finished with the evaluations, they came together as a team to rank the sessions. Along with looking at the total rating, they also looked at the different topics that were covered to ensure that the sessions covered a broad range of topics. Once the abstracts were ranked, the teams updated the session status to Approved, Alternate or Considered (Not accepted). If the status was Considered, the teams provided a reason as to why the abstract was not selected.

At that point the list of sessions came back to the Program Committee managers. We made certain the correct number of sessions per track were chosen and that no speakers had more than two sessions. There were a couple of cases where speakers had more than two sessions – for these cases, we went back to the teams for additional selections.

That’s it. Well, I guess I mean, those are all of the steps – it’s a ton of work and I’m grateful to everyone involved for all of their hard work. We recognize that there are probably ways to improve the process and we’re in the process of setting up meetings with all of the teams to get their input. I hope this provides clarification to some of the questions that people might have about the abstract selection process.

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.

I ran across an installation issue with SQL Server 2008 on a Windows Server 2008 server the other day that baffled me a little bit.  I was installing an additional instance of SQL Server 2008 on a server that already had a SQL Server 2008 instance and right before the installation completed, it died with the error:  “A MOF Syntax error occurred.”  Further investigation into the Setup Bootstrap logs gave this detail:

An error occurred while processing item 1 defined on lines 14 – 16 in file D:\Program Files\Microsoft SQL Server\MSSQL10.TMS_MODELING\MSSQL\Binn\etwcls.mof.transformed:

2010-05-18 13:41:02 Slp: Compiler returned error 0×800700a4Error Number: 0×800700a4, Facility: Win32

2010-05-18 13:41:02 Slp: Description: No more threads can be created in the system.

2010-05-18 13:41:02 Slp:

2010-05-18 13:41:02 Slp: Sco: Compile operation for mof file D:\Program Files\Microsoft SQL Server\MSSQL10.TMS_MODELING\MSSQL\Binn\etwcls.mof.transformed failed. Exit code 3

2010-05-18 13:41:02 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigNonRC and scenario ConfigNonRC.

Much investigation on the internet turned up a lot of people that have been having this issue, but very few answers.  After many installs and uninstalls, I finally tried the following, which seemed to work:

• I ran the setup.exe as an administrator (right click on setup.exe and click “Run as administrator) even though I am a local administrator on the box.
• I installed SQL Server using the Network Service account instead of the normal domain service account.
• The installation succeeded and I just went into Configuration Manager and changed the service account to the domain account after the installation.

Unlike the similar Reese’s dilemmas, these results were far less favorable…

Reese's are yummy

As many of these issues begin, a developer put together a procedure.  When he ran it locally, it ran in a matter of seconds.  For some reason, though, he wanted the procedure run from a remote server.  When he attempted to run that same procedure from the remote server, it took a number of minutes.    It was apparent that it wasn’t the result set being returned, as there were OLEDB waits while the query was processing.

In looking at the query, it wasn’t anything spectacular.  It dumped some data into a temp table and then joined that table to an existing table and displayed the result.    The problem ended up being the temp tables.  While the query was being executed against the remote server, the temp tables were being created on the server the query was running on.  So, pulling the data into the temp table and joining the two tables all had to occur with data being pulled across the network.

The fix was pretty simple – change the procedure to create a table on the remote server and drop it once the procedure was finished.  The procedure ran in the expected time period.

The SQL Server community never ceases to amaze me.  The number of people that are willing to take time out from their jobs and families to volunteer is especially impressive.

I’ve had the good  fortune to be able to volunteer for the Program Committe this year.  My job is to pull together special projects and whatever other slave work Allen thinks up for me.   I’ve had a number of volunteers that have put great work into our current project.  This project has multiple steps and has required a ton of coordination between the volunteers – but it is all coming together.   It’s something that’s been needed for awhile  and now it’s going to be a reality.   I’d name names, but I know that I’d forget someone.   So thank you to everyone that’s helped out.

A big (virtual) cake for all of you!

It’s not just me, though.  Tim’s in the process of re-starting the Performance VC.  He had mentioned the need for volunteers through our blog, Twitter and Blythe Morrow(Blog/Twitter) put out a call for volunteers on the PASS blog.  He’s been overwhelmed at the number of people that have asked to help out.

For all of you that volunteer for PASS – kudos to you!  For those of you that are thinking of volunteering, but haven’t yet,  get ahold of Tim or me or go here for additional volunteer opportunities.

Not-for-profit organizations can be awesome and extremely tough at the same time.   About 15 years ago, I joined a not-for-profit community service organzation.  Much like PASS, there were local groups, regions and  an international level.  On all three levels, this organization was definitely able to make a difference. I was fortunate to serve at both the club and regional levels and take part in an international event in Japan.  It was definitely a life-changing experience.

One of the problems that I noticed while I was serving on the regional board was that the more that we were able to accomplish, the more that was expected of us.  That was great and it was exciting to see the possibilites, but the problem that we faced is that our resources hadn’t changed.  Because of our tax status, we had to be very careful about how we used the funds raised during our fund raisers and the vast majority of that went to other non-profit organizations that we supported.  In order to give the regions and clubs more funding, we had a couple of options: raise dues, raise conference attendance costs or have more fundraisers during our conferences.  Raising dues and raising conference attendance had the possible side outcome of fewer members or fewer people attending conferences.  Additional fundraisers at the conferences took time away from what we were meeting about.  So we made the decision to make better use of what we had and revisit those ideas in the future.

I see a similar issue with PASS.  We’re incredibly fortunate to have an organization that provides as many resources as PASS does and with a free membership.  While there is a great staff at PASS, much of what gets done here is because of a community of dedicated volunteers.   I’ve been fortunate enough this year to be a part of the program committee and that has allowed me the opportunity to understand more of what goes on at PASS.

There have been many discussions about what PASS does well and less well along with what they should be doing.  The latest discussions have been about the PASS Summit survey results.  There have been a number of blog posts about it - Brent Ozar (Blog/Twitter), Tom LaRock (Blog/Twitter), Steve Jones (Blog/Twitter) and Andy Warren(Blog/Twitter), to name a few.  I’m not picking on these particular bloggers or even this particular discussion topic.  They all make valid points. 

The questions that I found myself asking (and answering) are:  Is this survey the best possible survey?  Probably not.  Did it provide PASS with valuable information?  Yes.  Are there people in the community that might be more skilled in writing/interpreting survey results?  Possibly.  Is paying for a company to write and interpret surveys for PASS the best use for our funds?  I don’t know.

If I were to look at the wish list for PASS, I’m sure that it would be huge.  Especially when it comes to items that require funding.  If there are additional things that are needed that will require additonal funding, that money needs to come from somewhere.  Do we increase the registration cost at the PASS Summit?  Do we institute dues?  Both of those choices have a direct affect on PASS membership and the members that are able to take part in the PASS Summit.  Short of that, we have to look at either companies or individuals that are willing to donate their time and resources.   Anyone that has volunteered for not-for-profit organizations know that getting companies and/or people to donate isn’t always the easiest thing.

I believe that members should continue to provide (constructive) criticism of PASS when it’s needed.  I don’t believe that there should be a step up or shut up attitude.   But if you can’t volunteer, then understand that PASS can’t continue to grow without also growing its resources.  If you have ideas, provide them.  If you have time, volunteer.  If you find that leprechaun at the end of the rainbow, take him out, steal the pot of gold and donate some of it to PASS.

I think most of us will agree that PASS is a pretty amazing organization.  It’s up to us to make it even better.

The Generate Scripts task in SSMS is insanely handy.  A few quick clicks and you can have tables, stored procedures, users, etc. scripted out.  I’ve used it quite a bit, but I ran into an unusual situation yesterday.

I needed to create a database schema and thought I’d use the handy dandy Generate Scripts task.  Popped through the wizard, clicked finish and it errored!  Here was the error message:

I was thoroughly confused – I was running SSMS 2008 against a SQL Server 2008 server.  I wasn’t sure where SQL Server 2005 even came into play.

I went through the process again, this time paying closer attention and noticed this:

Apparently the Script Wizard defaults to SQL Server 2005.  I changed it to SQL Server 2008 and everything ran as expected.  While I had run this task against other SQL Server 2008 instances, apparently none of them made use of the new data types in 2008 and, as a result, didn’t generate errors.  Now why it would default to SQL Server 2008 is an entirely different question….

Okay, maybe I’m being a little sarcastic.  I don’t troubleshoot dynamic SQL very often, so I don’t always see potential issues right away.  For those dear readers who work with it regularly, you should stop reading now – this is all pretty basic – but it took a few minutes out of my day.

This is the only dyna- that I like

My troubleshooting methods consist of displaying the command created by the dynamic SQL and seeing if it runs correctly or if I’m missing a quotation mark or something along the way.  There is probably a better way to troubleshoot, but again, I play with it so rarely that I’m stuck in a rut.

Evaluating Dynamic SQL commands

Late last week, a developer sent the following block of dynamic SQL code because he was having issues getting it to work:

EXEC
('
USE [master];
BEGIN
ALTER DATABASE [random_dbname] SET ONLINE;
WAITFOR DELAY ''00:01'';
END
USE [random_dbname];
'
)

I followed my normal troubleshooting methods and everything worked fine.  Trying to execute it as above, I received the following error message:

Msg 942, Level 14, State 4, Line 7
 Database 'random_dbname' cannot be opened because it is offline.

On first glance, I was confused, because it was obvious that I brought the database online.  I soon realized, though, that everything within the parentheses was being evaluated prior to being executed.  Apparently SQL Server  has a shorter memory than I do.

Breaking it into two separate statements like below accomplishes what needed to happen

EXEC

(‘

– Bring the database online

USE [master];

BEGIN

ALTER DATABASE [random_db] SET ONLINE;

WAITFOR DELAY ”00:01”;

END

‘
)

go

EXEC

(‘ USE [random_db];

/*blah blah blah*/

‘
)

Thinking that this ‘emergency’ had been handled, I went back to my other tasks. 

Database Context and Dynamic SQL

As these thing happen, though, received another call because after he ran all of this, the database context remained Master.  Fortunately, this was easy to explain.  The database context switching exists only during the execution of the EXEC statement and does not persist after its completion. 

None of this is rocket science or even deep SQL knowledge, but maybe it’ll save a minute or two for some other DBA out there.