FAST n Query Hint

SQL Server 2005, SQL Server 2008, TSQL
Just because a doctor says take two aspirin, taking an entire case won't make you better. If one is good and two is better, 2 million might not be best.  What the heck am I talking about? We have an application that for one reason and another, has some overly complex procedures and queries. A lot of time and effort has been spent getting these queries to work well. Some of that time & effort came from one Microsoft consultant, Bill Sulcius. He's a great guy, very smart, very helpful and I learned a lot from him. On a few of the queries that Bill helped tune, he added some query hints, FAST 1 and KEEPFIXED PLAN. Where he added these hints, he could demonstrate, with good sets of data…
Read More

SCOM Custom Performance Collection

SCOM
I've spent five days struggling with this, but I finally have a custom performance collection up and running. The documentation available for Operations Manager 2007 (SCOM), especially for customization, especially for databases, is weak, and that's being kind. However, there are some guys working hard out there to make a difference. If you need help, the System Center Forum is the place to go. In particular, this little ditty on using property bag objects finally cracked the code for me. The good news, I'm going to write this up in an article for Steve over at SQL Server Central. The better news, I can finally report progress in my daily stand-ups. Sheesh!
Read More

SCOM VBScript Testing: The Answer

SCOM
It took a while, but I finally tracked down the information I needed. SCOM has an SDK. In the SDK is a nice little page showing how to test & debug scripts. One of the links on that page leads out here to a great article on the VBScript debugger. That's what I needed (along with the DLL's to register). Anyway, I've almost got my first monitoring script up & running. I'll post it on here once I do.
Read More

Did I mention that I love Red Gate's Data Generator?

Tools, TSQL
Because I do. I'm working on a set of tests for an article comparing TOP, MAX & ROW_NUMBER. I have a simple data structure and I need a bunch of data in order to create my tests. I wanted that data to be distributed a certain way, to mimic some production system behavior I've seen in the past. Last night I got it all set by mucking about with the seed values of the various columns to get it just right and load up millions of rows in only a few minutes and doing this all on my lap top. Great tool!
Read More

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

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

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