June 2005 - Posts

BINARY_CHECKSUM double whammy

Foolish me. Twice this week I've been burned by SQL Server 2000 BINARY_CHECKSUM. 

For those of you who haven't used this, SQL Server BOL states "BINARY_CHECKSUM can be used to detect changes to a row of a table."

First burn: I was trying generate a unique value based on a 20 column reporting key (for ad hoc aggregates).  Out of 50,000 rows, it assigned a hand full the same value (even though the 20 keys were different!).

Second burn: instead of using a multiple column LEFT JOIN to evaluate if a composite key existed in the source table, I did a BINARY_CHECKSUM value subquery.  For example:

SELECT col01, col02, col03

FROM TableA

WHERE BINARY_CHECKSUM(col01, col02, col03) NOT IN

     (SELECT BINARY_CHECKSUM(col01, col02, col03)  FROM  TableX)

Well I found out that rows were NOT getting inserted because of a match on BINARY_CHECKSUM to a value that was the same - but with underlying columns that were definitely not the same. 

The lesson?  Use BINARY_CHECKSUM to detect just row changes, but not as a key-lookup function. 

Also, I don't see a reference to this function in SQL Server 2005 June CTP BOL yet?  Have they removed it? Hmmmmm.

 

posted Thursday, June 30, 2005 7:49 PM by joe.sack with 0 Comments

SQL Server 2005 stored procedure signing, encryption examples

For some interesting examples of SQL Server 2005 enryption and stored procedure signing, check out Laurentiu Cristofor's blog:

http://blogs.msdn.com/lcris/default.aspx

 

posted Wednesday, June 22, 2005 2:13 PM by joe.sack with 0 Comments

SQL Server 2005 Road Show

I'm happy to hear that the SQL Server 2005 Road Show is coming back to Minneapolis, as well as other locations:

Portland - July 26
Seattle - July 28
Kansas City - August 9
St. Louis - August 11
Minneapolis - August 16
Phoenix - August 23
Denver - August 25
Atlanta - August 30
Tampa - September 1
Los Angeles - September 8
Philadelphia - September 13

posted Wednesday, June 22, 2005 12:03 PM by joe.sack with 0 Comments

Do you use [Attribute | Field | Column] naming conventions?

Unfortunately, naming convention discussions always stir up strong opinions in the database world, but I'll risk a discussion on this topic anyhow. When I work at different consulting engagements, I'm always interested in seeing how other developers and data modelers do or don't do naming conventions...

Specifically, I'm interested in how/if people apply naming conventions to (take your pick of terms) [attribute | column | field] names in a table/entity. A few years ago I had a nasty Hungarian notation habit which I eventually shook off. I don't like adding extra characters if I don't have to - however lately I've re-introduced a "class words" suffix naming convention into a data warehouse project I'm working on, and I'm thinking of applying it to my OLTP design work.

A few examples...

I'm using a suffix of ADDR for addresses (web site addresses, home address), NM for names, DESC for description, QTY for quantity, AMT for a monetary value, ID for surrogate keys, PCT for percentage, NBR for number, and IND for bit indicators. I'm finding most of my attributes fall into one class or another (without too much trouble). I object to thinking about the "class" as a generalized data type - but instead I think of it more as a user-friendly "what's in that attribute?" meta tag.

I'm interested in hearing comments on techniques you've found successful - which can be consistently (and not painfully) applied.

Thanks!
posted Monday, June 20, 2005 1:51 PM by joe.sack with 0 Comments

SQL Server 2005 Online Page or File Restores

A tip if you are experimenting with the new SQL Server 2005 online PAGE restore functionality in SQL Server 2005 (or the online FILE restore), and you get a message like this after applying a RESTORE DATABASE (with NORECOVERY) and any other tran log backups (up to RECOVERY):

"The roll forward start point is now at log sequence
number (LSN) 17000000031000001. Additional roll forward
past LSN 17000000031800001 is required to complete the
restore sequence."

Online restores require that you also take a BACKUP LOG after the RESTORE DATABASE file or page(s) restore.  Then you apply that log backup (RESTORE LOG). 

posted Friday, June 17, 2005 4:33 AM by joe.sack with 0 Comments

Hello to you - and Hello to SP4 I/O Warnings

Hi All,

I am an avid reader of SQLJunkies.com – and Donny and Doug gave me this new blog (thank you)!

My name is Joe Sack, and I’ve been working as a SQL Server DBA/Developer since 1997. I'm an independent consultant working in the Twin Cities and I wrote a book called "SQL Server 2000 Fast Answers for DBAs and Developers" (originally published by Curlingstone but now owned by Apress). I’m also working on a SQL Server 2005 book right now for Apress which should be ready by the end of the year.

Now to SQL...

I recently added SQL Server 2000 SP4 to both instances of an active/active cluster on Windows 2003 Server. When setting up the hardware, we had several disks go "bad" in the array. Because of this unlucky beginning, I thought it would be wise to really test the cluster hard before letting the company use it.

I created the following basic test: I created a database called PerfTest with a single, fat table (lots of wide, space stealing columns). I created a DTS package which loads a 200MB file into a table. After it loads the data, a clustered index on a varchar column is added to the table (using ascending order). The index is then dropped and then recreated in descending order. The package is scheduled as a job - and will keep executing until I'm sick of testing.

The PerfTest was configured to autogrow in small increments (using "worst practices" to further annoy the RAID 5 array). The production cluster will be using RAID 10, but that's another story. So, sure enough after starting the test, I immediately started seeing the following warnings:

"SQL Server has encountered 178 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [N:\MSSQL$SQL1\data\PerfTest_Data.MDF] in database [PerfTest] (7). The OS file handle is 0x000004A8. The offset of the latest long IO is: 0x0000004a752000"

And:

"Autogrow of file 'PerfTest_Data' in database 'PerfTest' took 196703 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file."

Turns out that with SQL Server 2000 SP4, Microsoft has added additional database and log file I/O warning messages (see Detecting and Resolving Stalled and Stuck I/O Issues in SQL Server 2000 SP 4 ).

I ran this same exact performance test on a direct-attach SQL Server instance, and didn't get these I/O errors. So time will tell if this is just a "nuisance" error that one would expect to see on a clustered environment attached to an external array (not a SAN), or if there are other physical issues at play here.

I wanted to test this on the soon-to-be production cluster, but it uses AWE, and SP4 (as of today) doesn't work with AWE (From Microsoft "Warning: Microsoft has found an issue with the final build of SP4 that impacts customers who run SQL Server with Address Windowing Extensions (AWE) support enabled. This issue only impacts computers with more than two gigabytes (2 GB) of memory where AWE has been explicitly enabled. If you have this configuration, you should not install SP4.")
posted Thursday, June 16, 2005 4:52 PM by joe.sack with 2 Comments