Odd Profiler Results with EF4

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.

UPDATE #6 – FINAL

Microsoft has announced that they have a fix for this and that it will be coming in the next Cumulative Update. 

SQL Sentry Truth-Telling and Disk Configuration

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?

«June»
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910