Ajarn's SQL Corner

Work-Around for Odd Profiler Results with EF4

AjarnMark — Tue, 10 Aug 2010 06:47:55 GMT

A couple of months ago I wrote a post about Odd Profiler Results with Entity Framework 4. Thanks to Olaf Tinnemeyer, we now have a simple work-around that he posted to my question on StackOverflow.

When using the Visual Studio tools to create the Entity Data Model, it automatically builds the Connection String for you, and one of the default settings is to set the MultipleActiveResultSets option to True. If you change this setting to False, then Profiler will properly report that the query was executed within the application database.

Please note that I have not done any load testing to determine what type of performance impact this change might have, so YMMV. But for me, I know it will perform acceptably for our needs, and the greater need in my view is to have accurate Profiler results.

I have posted this work-around to the Connect item that I opened. I still believe that this is a bug in SQL Server and hope that Microsoft will address it in a future release. If you share my concern, I encourage you to login and vote this up.

Profiler and Entity Framework Bug Logged to Connect

AjarnMark — Thu, 01 Jul 2010 20:22:57 GMT

As a follow-up to my previous post Odd Profiler Results with EF4, I have now logged a SQL Server bug to Microsoft Connect. If you have similar concerns, I encourage you to logon to Connect and vote it up.

If you have a solution, I encourage you to reply to my blog, or respond to my still unanswered questions on the ASP.NET Forums or Stack Overflow or Server Fault. I will happily mark your answer as correct (assuming that it is).

On Web Camp and Convention Facilities

AjarnMark — Thu, 24 Jun 2010 04:39:51 GMT

I attended the Web Camp in Redmond last Friday at the Microsoft Conference Center. It was a really nice setup in the room with rows of tables (plenty of space between them) and long power strips so every attendee could plug-in their laptop. The week before that, I attended SQLSaturday, also at a Microsoft facility, and it had rows of tables setup and plenty of in-floor outlets. Maybe this is just to be expected at Microsoft…it makes sense that of the events that will be held at a Microsoft facility, it is likely that a large portion of the attendees will have laptops with them. I sure wish the Washington State Convention Center, which is the recent and next location for the PASS Community Summit had a setup like this.

Sure I know that you can’t fit as many people into a room if you set it up with tables, but there are several attendees who are there with laptops open, taking notes and following along, and it sure would be nice. Maybe there is a way the rooms could be configured with chairs-only rows on one side and tables on the other, if it’s not possible to do all tables. Or find some other way to divide the room into sections. Even if you don’t have a laptop, it is nice to have a table to put your notepad on to take notes. At the very least, I would like to see convention centers have more in-floor outlets so that if you’re not going to give me a table, you at least don’t force me to vie for one of the few seats on the outside aisle near a real wall that does have an outlet in it, and then lay out my power cord in a trip hazard. Make it easier (and safer) for everyone.

While at WebCamp, I mentioned my Entity Framework 4 / Profiler conundrum to James Senior (@jsenior) and he asked me to send him an email follow-up which I have done, so hopefully he can find out what is going on with this. It strikes me as really odd that I get correct results in a SQL 2000 instance, but wrong results in a SQL 2008 R2 instance. I wonder what’s different.

How to Answer a Stupid Interview Question the Right Way

AjarnMark — Wed, 16 Jun 2010 05:46:58 GMT

Have you ever been asked a stupid question during an interview; one that seemed to have no relation to the job responsibilities at all? Tech people are often caught off-guard by these apparently irrelevant questions, but there is a way you can turn these to your favor. Here is one idea.

While chatting with a couple of folks between sessions at SQLSaturday 43 last weekend, one of them expressed frustration over a seemingly ridiculous and trivial question that she was asked during an interview, and she believes it cost her the job opportunity. The question, as I remember it being described was, “What is the largest byte measurement?”. The candidate made up a guess (“zetabyte”) during the interview, which is actually closer than she may have realized. According to Wikipedia, there is a measurement known as zettabyte which is 10^21, and the largest one listed there is yottabyte at 10^24.

My first reaction to this question was, “That’s just a hiring manager that doesn’t really know what they’re looking for in a candidate. Furthermore, this tells me that this manager really does not understand how to build a team.” In most companies, team interaction is more important than uber-knowledge. I didn’t ask, but this could also be another geek on the team trying to establish their Alpha-Geek stature. I suppose that there are a few, very few, companies that can build their businesses on hiring only the extreme alpha-geeks, but that certainly does not represent the majority of businesses in America.

My friend who was there suggested that the appropriate response to this silly question would be, “And how does this apply to the work I will be doing?” Of course this is an understandable response when you’re frustrated because you know you can handle the technical aspects of the job, and it seems like the interviewer is just being silly. But it is also a direct challenge, which may not be the best approach in interviewing. I do have to admit, though, that there are those folks who just won’t respect you until you do challenge them, but again, I don’t think that is the majority.

So after some thought, here is my suggestion: “Well, I know that there are petabytes and exabytes and things even larger than that, but I haven’t been keeping up on my list of Greek prefixes that have not yet been used, so I would have to look up the exact answer if you need it. However, I have worked with databases as large as 30 Terabytes. How big are the largest databases here at X Corporation?” Perhaps with a follow-up of, “Typically, what I have seen in companies that have databases of your size, is that the three biggest challenges they face are: A, B, and C. What would you say are the top 3 concerns that you would like the person you hire to be able to address?…Here is how I have dealt with those concerns in the past (or ‘Here is how I would tackle those issues for you…’).”

Wait! What just happened?! We took a seemingly irrelevant and frustrating question and turned it around into an opportunity to highlight our relevant skills and guide the conversation back in a direction more to our liking and benefit. In more generic terms, here is what we did:

Admit that you don’t know the specific answer off the top of your head, but can get it if it’s truly important to the company. Maybe for some reason it really is important to them.
Mention something similar or related that you do know, reassuring them that you do have some knowledge in that subject area.
Draw a parallel to your past work experience.
Ask follow-up questions about the company’s specific needs and discuss how you can fulfill those.

This type of thing requires practice and some forethought. I didn’t come up with this answer until a day later, which is too late when you’re interviewing. I still think it is silly for an interviewer to ask something like that, but at least this is one way to spin it to your advantage while you consider whether you really want to work for someone who would ask a thing like that. Remember, interviewing is a two-way process. You’re deciding whether you want to work there just as much as they are deciding whether they want you.

There is always the possibility that this was a calculated maneuver on the part of the hiring manager just to see how quickly you think on your feet and how you handle stupid questions. Maybe he knows something about the work environment and he’s trying to gauge whether you’ll actually fit in okay. And if that’s the case, then the above response still works quite well.

SQL Saturday 43 in Redmond

AjarnMark — Tue, 15 Jun 2010 05:52:32 GMT

I attended my first SQLSaturday a couple of days ago, SQLSaturday #43 in Redmond (at Microsoft). I got there really early, primarily because I forgot how fast I can get there from my home when nobody else is on the road. On a weekday in rush hour traffic, that would have taken two hours to get there. I gave myself 90 minutes, and actually got there in about 45. Crazy!

I made the mistake of going to the main Microsoft campus, but that’s not where the event was being held. Instead it was in a big Microsoft conference center on the other side of the highway. Fortunately, I had the address with me and quickly realized my mistake. When I got back on track, I noticed that there were bright yellow signs out on the street corner that looked like they said they were for SOL Saturday, which actually was appropriate since it was the sunniest day around here in a long time.

Since I was there so early, the registration was just getting setup, so I found Greg Larsen who was coordinating things and offered to help. He put me to work with a group of people organizing the pre-printed raffle tickets and stuffing swag bags.

I had never been to a SQLSaturday before this one, so I wasn’t exactly sure what to expect even though I have read about a few on some blogs. It makes sense that each one will be a little bit different since they are almost completely volunteer driven, and the whole concept is still in its early stages. I have been to the PASS Summit for the last several years, and was hoping for a smaller version of that. Now, it’s not really fair to compare one free day of training run entirely by volunteers with a multi-day, $1,000+ event put on under the direction of a professional event management company. But there are some parallels.

At this SQLSaturday, there was no opening general session, just coffee and pastries in the common area / expo hallway and straight into the first group of sessions. I don’t know if that was because there was no single room large enough to hold everyone, or for other reasons. This worked out okay, but the organization guy in me would have preferred to have even a 15 minute welcome message from the organizers with a little overview of the day. Even something as simple as, “Thanks to persons X, Y, and Z for helping put this together…Sessions will start in 20 minutes and are all in rooms down this hallway…the bathrooms are on the other side of the conference center…lunch today is pizza and we would like to thank sponsor Q for providing it.” It doesn’t need to be much, certainly not a full-blown Keynote like at the PASS Summit, but something to use as a rallying point to pull everyone together and get the day off to an official start would be nice. Again, there may have been logistical reasons why that was not feasible here. I’m just putting out my thoughts for other SQLSaturday coordinators to consider.

The event overall was great. I believe that there were over 300 in attendance, and everything seemed to run smoothly. At least from an attendee’s point of view where there was plenty of muffins in the morning and pizza in the afternoon, with plenty of pop to drink. And hey, if you’ve got the food and drink covered, a lot of other stuff could go wrong and people will be very forgiving. But as I said, everything appeared to run pretty smoothly, at least until Buck Woody showed up in his Oracle shirt. Other than that, the volunteers did a great job!

I was a little surprised by how few people in my own backyard that I know. It makes sense if you really think about it, given how many companies must be using SQL Server around here. I guess I just got spoiled coming into the PASS Summit with a few contacts that I already knew would be there. Perhaps I have been spending too much time with too few people at the Summits and I need to step out and meet more folks. Of course, it also is different since the Summit is the big national event and a number of the folks I know are spread out across the country, so the Summit is the only time we’re all in the same place at the same time. I did make a few new contacts at SQLSaturday, and bumped into a couple of people that I knew (and a couple others that I only knew from Twitter, and didn’t even realize that they were here in the area).

Other than the sheer entertainment value of Buck Woody’s session, the one that was probably the greatest value for me was a quick introduction to PowerShell. I have not done anything with it yet, but I think it will be a good tool to use to implement my plans for automated database recovery testing. I saw just enough at the session to take away some of the intimidation factor, and I am getting ready to jump in and see what I can put together in the next few weeks. And that right there made the investment worthwhile. So I encourage you, if you have the opportunity to go to a SQLSaturday event near you, go for it!

Odd Profiler Results with EF4

AjarnMark — Thu, 03 Jun 2010 06:07:13 GMT

I have been doing some testing of using the Microsoft Entity Framework 4 with stored procedures and ran across some really odd results in SQL Server Profiler.

The application that is running which uses Entity Framework 4 is a simple Web Application written in C#, and the Entity Data Model is actually contained in a referenced class library of its own. I’ll write more about my experiences with this later. For now the question is, why does SQL Profiler think that the stored procedure is running in Master, and not in my application database?

While analyzing the effects of using custom helper methods on my EDM classes to call the stored procedure, I decided to run Profiler while I stepped through the code so that I had a clear understanding of exactly when and what calls were made to the SQL Server. I ran Profiler switching back and forth between the TSQL and TSQL_SP templates. However, to reduce the amount of results rows I needed to wade through, I set a filter on DatabaseID to be equal to my application’s database. Each time I ran this, the only thing that I saw was an Audit:Login to the database, but no procedure or T-SQL statements executed, yet I was definitely getting results back to my web page. I tried other Profiler templates, still filtering on DatabaseID (tangent: I found, at least back in SQL 2000 Profiler, that filtering on DatabaseID was more reliable than filtering on DatabaseName. Even though I’m now running SQL 2008, that habit sticks with me). Still no results other than the Login. Very weird!

Finally, I decided to run Profiler with no filtering and discovered that that lines which represent my stored procedure and its T-SQL commands are all marked with DatabaseID = 1, which is Master. Why in the world would that be? My procedure is definitely in the application database, and not in Master, and there is nothing funny about the call to the procedure evident in Profiler (i.e. it is not called as MyAppDB.dbo.MyProcName, but rather just dbo.MyProcName). There must be something funny with the way the Entity Framework is wrapping this call, and I don’t like it…I don’t like it one bit. My primary PROD server contains 40+ databases on it, and when I need to profile something, I expect to be able to filter based on DatabaseID (for the record, I displayed DatabaseName in my results, too, and it also shows Master).

I find the same pattern of everything except the Login showing up as being in Master when I run my version that uses standard LINQ to Entities instead of stored procedures, so that suggests it is not my code, but rather something funny with SQL Server 2008 Profiler or the Entity Framework.

If you have any ideas about why this might be so, please comment below.

UPDATE

Trying to isolate variables, I pushed the web application out to a test server with a fresh install of .NET Framework 4, and pushed the database to a few different servers. I have test instances where the database and web server are on the same machine and some where they are on separate machines. That distinction did not make any difference in the results. But the interesting thing is that when the database resides on a SQL 2000 SP3 instance, then Profiler shows that the stored procedure is properly attributed to the application database ID, but when the database resides on a SQL 2008 SP1 instance, then Profiler shows that the stored procedure is attributed as if it were in Master. I used the same instance of SQL 2008 Profiler for all tests, so this is not a discrepancy in the Profiler application itself, although it suggests that there may be something funny in the server that is being monitored. Another variable which I have not yet ruled out is that the SQL 2008 instances are Developer Edition while the SQL 2000 instances are Enterprise Edition. That should not matter, but it is a variable.

UPDATE #2

I have replicated the results using a simpler project where the Entity Data Model is directly part of the Web Application and not contained in a separate class library, and using straight LINQ to Entities and a connection string using a trusted connection as a member of System Administrators server role (previous tests were using SQLAuth with a variety of permissions levels). Still getting the result that Profiler thinks the query is being executed in Master and not in the application database.

UPDATE #3

I have replicated the results on a SQL Server 2008 R2 instance, so this isn’t something that went away with any service pack update so far. I have sent this to James Senior at Microsoft that I met at the Redmond WebCamp to see if he has any insight.

UPDATE #4

This issue has been posted to Microsoft Connect. Also, I created a new web page that loops through several records and updates them. Interestingly, all of the updates except for the first line item for the first update, show as occurring in the application database; but the SELECT and the initial RPC:Starting for the first update show as occurring in Master. This information plus a trace file have been added to the Connect item.

UPDATE #5

A work-around has been identified.

SQL Sentry Truth-Telling and Disk Configuration

AjarnMark — Wed, 02 Jun 2010 06:09:35 GMT

Recently, SQL Sentry told me something about my SQL Server disk configurations that I just didn’t want to believe, but alas, it was true.

Several days ago I posted my First Impressions of the SQL Sentry Power Suite. Today’s post could fall into the category of, “Hey, as long as you have that fancy tool…” Unfortunately, it also falls into the category of an overloaded worker taking someone else’s word for the truth, not verifying it with independent fact-checking, and then making decisions based on that. Here’s my story…

I’m not exactly an Accidental DBA (or Involuntary DBA as Paul Randal calls it). I came to this company five years ago as a lead application developer with extensive experience in database design and development. I worked my way into management, and along the way, took over the DBA responsibilities. Fortunately, our systems run pretty smoothly most of the time, but I’m always looking for ways to make them better and to fit into my understanding of best practices. When I took over as DBA, I inherited a SQL 2000 server with about 30 databases on it supporting our main systems, and a SQL 2005 server with multiple instances. Both of these servers were configured with the Operating System and Application files on the C drive, data files on a different drive letter, and log files on a third drive letter. Even before I took over as DBA, I verified that this was true with a previous server administrator, and that these represented actual separate disks. He stated that they did, and I thought that all was well.

Then one day, I’m poking around inside the SQL Sentry Performance Advisor, checking out features as I am evaluating whether to purchase the product, and I come across a Disk Configuration section. The first thing I notice is that the drives do not have the proper partition offset, which was not at all surprising to me given the age of the installation and the relative newness of that topic. But what threw me for a loop was that the graphic display appeared to be telling me that I did not in fact have three separate drives (or arrays) but rather had two, and that the log files were merely on a separate volume on the same physical array as the OS. I figured that I must be reading it wrong so I scanned the Help file, but that just seemed to confirm my interpretation. Then I thought, “there must be something wrong with the demo version of the software! This can’t be right!” But just to double-check, I went to our current server admin to talk it over with him, and sure enough, SQL Sentry was telling the truth!

I was stunned! I quickly went through the grieving process…denial…anger…reconciliation. Here was something that I thought was such a basic truth that was turned upside down. OK, granted, this wasn’t disastrous. Our databases didn’t suddenly grind to a halt. I didn’t get calls late at night inquiring about the sudden downturn in performance. But it was a bit of a shock to the system, in a good way, to jolt me out of taking what I had believed as the truth for granted, and instead to Trust, but Verify!

Yes, before someone else points it out, I know that there are”free” disk management tools built-in to Windows that would have told me the same thing if I had only looked at them; I did not have to buy a fancy tool to tell me that, but the fact is, until I was evaluating the tool, I had just gone with what I was told, and never bothered to check what was actually there.

So, what things do you believe to be true but you actually never verified?

More Maintenance Plan Weirdness

AjarnMark — Sat, 29 May 2010 05:18:18 GMT

I’m not a big fan of the built-in Maintenance Plan functionality in SQL Server. I like the interface in SQL 2005 better than 2000 (it looks more like building an SSIS package) but it’s still a bit of a black box. You don’t really know what commands are being run based on the selections you have made, and you can easily make some unwise choices without realizing it, such as shrinking your database on a regular basis. I really prefer to know exactly what commands and with which options are being run on my servers.

Recently I had another very strange thing happen with a Maintenance Plan, this time in SQL 2005, SP3. I inherited this server and have done a bit of cleanup on it, but had not yet gotten around to replacing the Maintenance Plans with all my own scripts. However, one of the maintenance plans which was just responsible for doing LOG backups was running more frequently than that system needed, and I thought I would just tweak the schedule a bit. So I opened the Maintenance Plan and edited the properties of the Subplan, setting a new schedule, saved it and figured all was good to go. But the next execution of the Scheduled Job that triggers the Maintenance Plan code failed with an error about the Owner of the job. Specifically the error was, “Unable to determine if the owner (OldDomain\OldDBAUserID) of job MaintenancePlanName.Subplan has server access (reason: Could not obtain information about Windows NT group/user 'OldDomain\OldDBAUserID’..” I was really confused because I had previously updated all of the jobs to have current accounts as the owners. At first I thought it was just a fluke, but it happened on the next scheduled cycle so I investigated further and sure enough, that job had the old DBA’s account listed as the owner. I fixed it and the job successfully ran to completion.

Now, I don’t really like mysteries like that, so I did some more testing and verified that, sure enough, just editing the Subplan schedule and saving the Maintenance Job caused the Scheduled Job to be recreated with the old credentials. I don’t know where it is getting those credentials, but I can only assume that it is the same as the original creator of the Maintenance Plan, and for some reason it insists on using that ID for the job owner. I looked through the options in SSMA and could not find anything would let me easily set the value that I wanted it to use. I suspect that if I did something like executing sp_changeobjectowner against the Maintenance Plan that it would use that new ID instead. I’m sure that there is good reason that it works this way, but rather than mess around with it much more, I’m just going to spend my time rolling out my replacement scripts instead.

Chalk this little hidden oddity up as yet one more reason I’m not a fan of Maintenance Plans.

PASS Professional Development Virtual Chapter Reboot

AjarnMark — Wed, 12 May 2010 06:12:48 GMT

The Professional Development Virtual Chapter for PASS is holding its first virtual meeting on Thursday, May 13 at 1:00 PM Eastern / 10:00 AM Pacfic time. Andy Warren (@sqlandy) will be the speaker. Click here to attend via Live Meeting. Also, check the http://prof-dev.sqlpass.org web site and RSS Feed for ongoing updates and details.

SQL Sentry First Impressions

AjarnMark — Wed, 12 May 2010 06:05:54 GMT

After struggling to defend my SQL Servers from a political attack recently, I realized that I needed better tools to back me up, and SQL Sentry is the leading candidate.

A couple of weeks ago, seemingly from out of nowhere, complaints from the business users started coming in that one of the core internal applications was running dramatically slower than normal, and fingers were being pointed at the SQL Server. Unfortunately, we don’t have a production DBA whose entire job is to monitor and maintain our SQL Servers. The responsibility falls to me to do the best I can, investing only a small portion of my time, because there are so many other responsibilities to take care of, and our industry is still deep in recession. I inherited these SQL Servers and have made significant improvements in process and procedure, but I had not yet made the time to take real baseline measurements or keep a really close eye on the performance. Like many DBAs, I wrote several of my own tools and used the “built-in tools” like Profiler, PerfMon, and sp_who2 (did I mention most of our instances are SQL Server 2000?). These have all served me well for in-the-moment troubleshooting and maintenance, but they really fell down on the job when I was called upon to “prove” that SQL Server performance was acceptable and more importantly had not degraded recently (i.e. historical comparisons). I really didn’t have anything from a historical comparison perspective, but I was able to show that current performance was acceptable, and deflect attention back onto other components (which in fact turned out to be the real culprit).

That experience dramatically illustrated the need for better monitoring tools. Coincidentally, I had been talking recently to my boss about the mini nightmare of monitoring several critical and interdependent overnight jobs that operate on separate instances of SQL Server. Among other tools, I had been using Idera’s SQL Job Manager which is a free tool and did a nice job of showing me job schedules and histories in a nice calendar view. This worked fairly well, and for the money (did I mention it was free?) it couldn’t be beat. But it is based on the stored job history in MSDB, and there were other performance problems that we ran into when we started changing the settings for how much job history to retain, in order to be able to look back a month or more in the calendar view. Another coincidence (if you believe in such things) was that when we had some of those performance challenges, I posted a couple of questions to the #sqlhelp hashtag on Twitter and Greg Gonzalez (@SQLSensei) suggested I check out SQL Sentry’s Event Manager. At the time, I just thought he worked there, but later found out that he founded the company. When I took a quick look at the features & benefits, the one that really jumped out at me is Chaining and Queueing which sounded like it would really help with our “interdependent jobs on different servers” issue.

I know that is a lot of background story and coincidences, but hopefully you have stuck with me so far, and now we have arrived at the point where last week I downloaded and installed the 30-day trial of the SQL Sentry Power Suite, which is Event Manager plus Performance Advisor. And I must say that I really like what I see so far. Here are a few highlights:

Great Support. I had two issues getting the trial setup and monitoring a handful of our servers. One of which was entirely my fault (missed a security setting in SQL 2008) and the other was mostly my fault (late change to some config settings that were apparently cached and did not get refreshed properly). In both cases, the support staff at SQL Sentry were very responsive and rather quickly figured out what the cause and fix was for each of them. This left me with a great impression of the company. Kudos to them!
Chaining and Queueing. While I have not yet activated this feature, I am very excited about the possibilities. We have jobs on three different instances of SQL Server that have to be run in a certain order, and each has to finish before the next can successfully begin, and I believe this feature will ensure just that. It has been a real pain in the backside when one of those jobs runs just a little too long and does not finish before the job on another instance starts, thus triggering a chain reaction of either outright job failures, or worse, successful completion of completely invalid processing.
Calendar View. I really, really like the Event Manager calendar view where I can see all jobs and events across all instances and identify potential resource contention as well as windows of opportunity for maintenance activity. Very well done, and based on Event Manager’s own database of accumulated historical information rather than querying the source instances every time.
Performance Advisor Dashboard History View. This view let’s me quickly select a date and time range and it displays graphs of key SQL Server and Windows metrics. This is exactly the thing I needed to answer the “has performance changed recently” question at the beginning of this post.
Reporting Services Subscription Jobs with Report Name. This was a big and VERY pleasant surprise. If you have ever looked at the list of SQL Server jobs that SQL Server Reporting Services creates when you make a Subscription, you will notice that they all have some sort of GUID as the name of the job. This is really ugly, and really annoying because when you are just looking at the SQL Agent and Job Activity Monitor, if you see that Job X failed, you really do not have any indication in the name or the properties of the Job itself, as to what Report that was for. But with SQL Sentry Event Manager you do. The Jobs list in the Navigator pane in SQL Sentry, amazingly, displays the name of the Report that the Subscription Job is for. And when you open it to see more details, it shows you the full Reporting Services path to that Report, so you can immediately track it down in the Report Manager in case you want to identify/notify the owner or edit the Subscription information. I did not expect this at all, but I sure do like it. HOORAY!

That is just my first impressions from using the tools for a few days. And I haven’t even gotten into how it showed me where I was completely mistaken about one aspect of my SQL Server disk configurations. I’ll share that lesson in another blog entry. But I have to say it again, the combination of Event Manager and Performance Advisor working together have really made me a fan.