29Apr 2008 by Grant Fritchey 9 Comments

nHibernate Recompiles and Execution Plans

nHibernate
One little potential for performance problems that we've seen comes out of how nHibernate generates it's parameratized queries. It limits the length of any parameter to the length of the column, but if the length of that parameter is less than the column, it uses tha smaller length when declaring the variable. This results in a query that looks like this: exec sp_executesql N'INSERT INTO dbo.users (Name, Password, EmailAddress, LastLogon, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)',N'@p0 nvarchar(8),@p1 nvarchar(6),@p2 nvarchar(12),@p3 datetime,@p4 nvarchar(8)',@p0=N'Ted Cool',@p1=N'abc123',@p2=N'ted@cool.com',@p3='2008-04-29 14:10:44:000',@p4=N'ted_cool' Note the parameter @p4 which is the parameter mapping to the primary key for the little sample table. In this query it's declared as nvarchar( 8 ) because 'ted_cool' is composed of eight characters. But if we changed it to 'ted_coolish': exec sp_executesql N'INSERT INTO dbo.users…
Read More
28Apr 2008 by Grant Fritchey 2 Comments

Red Gate’s SQL Prompt Enhanced

Tools
I've been using Red Gate products for years and I've turned into a bit of a cheerleader for them. I can't help it. They have good products.  One of the tools that I've been using for a while (truth told since before it was owned by Red Gate) is SQL Prompt. A new version, 3.8, has recently been released. I've been using for a few days now. It really is an improvement over the previous version. I haven't done system measurements or anything, but it feels faster. It's picked up the schema's from the systems I normally access quite well. It failed when I put it against the SCOM data mart, but so did the prior version. There are quite a few cosmetic enhancements. Some of the pop-ups look a bit…
Read More
28Apr 2008 by Grant Fritchey 2 Comments

A Book

Misc
It's only the E-Book, the print version won't be available for another couple of weeks, but... I HAVE A BOOK PUBLISHED! It's also available here. I can not possibly thank the man who gave me the opportunity, carried my sorry-ass down the track, made me write something that closely approximated English, challenged just about every assumption that I walked in with, in other words, did more to make the book happen than I did, Tony Davis. I also had some simply amazing help from Brad McGehee. Brad must have looked up and double checked every single fact I wrote down because I think he found a correction for most of them. Amazing guys. Thanks so much. When you consider how much time & effort these guys put in, I'm not…
Read More
28Apr 2008 by Grant Fritchey No Comments

Community Building

Misc
Building a community is hard. I'm one of the leaders of the Southern New England SQL Server Users Group, SNESSUG, a PASS affiliate. We've been going for just over a year. We were launched by the drive and initiative of one person, who got us going and then had to leave. We've been on our own for about four months now. It's been difficult without the drive of the founder, but we're getting the job done & moving things forward. Andy Leanard, MVP, Solid Quality Mentor, nice guy (for a DBA), has a series of posts that, while short, are very helpful guide-posts for those attempting to start a community or keep one running (read them bottom to top). If you're in that position or thinking about volunteering at your…
Read More
25Apr 2008 by Grant Fritchey No Comments

Easy Fix To Problem #1

nHibernate, Object Relational Mapping, Tools
I did a little bit, and I mean a little bit, of looking through the documentation on nHibernate and located a spot for the schema, actually  a couple of spots. It can be added to the Hibernate Mapping definition, which will make it a default for all classes within the definition, and by extension all the tables in the database you connect to. You can also add it to the class definition, specifying a particular schema for a given table. So now the query looks like this: exec sp_executesql N'INSERT INTO dbo.users (Name, Password, EmailAddress, LastLogon, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)',N'@p0 nvarchar(9),@p1 nvarchar(6),@p2 nvarchar(13),@p3 datetime,@p4 nvarchar(9)',@p0=N'Jane Cool',@p1=N'abc123',@p2=N'jane@cool.com',@p3='2008-04-25 11:11:48:000',@p4=N'jane_cool' On to the data length problem.
Read More
25Apr 2008 by Grant Fritchey 1 Comment

nHibernate First Impressions

Object Relational Mapping, Tools
If I'm going to have to support it, I want to understand it. So, I got going yesterday, installing nHibernate 2.0 and walking through the Quick Start Guide. My C# is a bit rusty, to say the least, but I managed to sqeak by. The Guide is meant for an older version of nHibernate and there have been a few changes made that affect the code displayed. What that means is, I had to do more than simply type up what was presented to me. Which, was actually good because it forced me to do a bit more learning in order to get everything to work. What I found was interesting. I can see why developers like this. It really does let you treat the database as just another object…
Read More
22Apr 2008 by Grant Fritchey No Comments

LINQ and (by extension) ORM Discussion

Object Relational Mapping
Steve Jones' editorial today was questioning the use of LINQ. His focus was on the security aspects. The discussion went past that. This is a bit of circular reference since I posted over there and linked back to my ORM Concerns post below. There are a lot of interesting points being made. Some of it, from a DBA stand-point, is quite repetitive although I'd prefer to think of it as reinforcing. Steve's editorial is worth a read and the discussion is excellent.
Read More
21Apr 2008 by Grant Fritchey No Comments

SCOM Error

Tools
We've been getting a lot of alerts saying "Performance Module could not find a performance counter." It's been making me nuts. I finally did a search on the web and found an entry over on the Operations Manager Product Team blog. It's a known issues with SP1. I'm posting this, not because I'm complaining. I'm not. SP1 solved several issues that I had and I'm very happy to have installed it. Unfortunately, it introduced problems. Just not as many as it solved.
Read More
21Apr 2008 by Grant Fritchey 2 Comments

SQL Server 2005 SP3

SQL Server
I've said it before and I'll say it again, Microsoft Connect really works. There has been a campaign going throughout the SQL Server community to get a new service pack out for SQL Server 2005. Microsoft had, at the very least, suggested that they were not going to release any more service packs for 2005. That's all changed now. I saw the announcement in Kalen Delaney's editorial from SQL Sever Magazine UPDATE. That's worth a read all on it's own. And consider this, 704 votes made this happen. That's 704 people who took the time & trouble to click their mouse a few times. If that's all it takes to influence Redmond, then you should all be over there using Connect as often as possible to get the fixes, changes or…
Read More
17Apr 2008 by Grant Fritchey 4 Comments

ORM Concerns

Object Relational Mapping
Object Relational Mapping (ORM) software is a great idea. You can't deny that the mismatch between objects and relational data has to be dealt with. Instead of all the time, money and effort being spent here, why not get a tool that does most of the work for you? But... One direction that this can lead is towards dumb databases. After all, if putting a piece of software between the object & the db makes things easier, how much easier if the db and the object look exactly the same. Ta-da! Even less code to write & maintain. Unfortunately, TANSTAAFL (There Ain't No Such Thing As A Free Lunch) still applies. What you save in initial coding you will pay for in reporting, data cleanup, integrity issues, data integration issues...…
Read More