Where did that money go?

This might be something most developers are already aware of, but I have been familiar with SQL Server mostly as a DBA, not actively busy with the meaning of data and it therefore came as a complete surprise to me.

Question: How much is $1/$345, rounded at 4 decimals?

Take a calculator and it will tell you: 0.0029.

Take Excel and it will tell you: 0.0029.

Take SQL Server and it will tell you: 0.0028.

Euh… wait a minute… did you say 0.0028? Isn’t that 3-4% off?

Seems SQL Server, when using the MONEY datatype, doesn’t round values, but truncates them instead. See the example below. I think it’s pretty shocking.

declare @m1 money, @m2 money, @r1 decimal(19,4)
declare @d1 decimal(19,4), @d2 decimal(19,4), @r2 decimal(19,4)
declare @f1 float, @f2 float, @r3 float;
set @m1 = 1.00;
set @m2 = 345.00;
set @r1 = @m1/@m2;
set @d1 = 1.00;
set @d2 = 345.00;
set @r2 = @d1/@d2;
set @f1 = 1.00;
set @f2 = 345.00;
set @r3 = @f1/@f2;
select @r1, @r2, @r3;

Bad Habits to Kick (T-SQL best practices)

I am currently busy reviewing and updating the internal programming guidelines and best practices for SQL Server programming. While looking on the net for other opinions on subjects, I came across a nice set of articles on best practices from Aaron Bertrand: http://sqlblog.com/blogs/aaron_bertrand/archive/tags/best+practices/default.aspx, a recommended read for all T-SQL programmers!

 

A interesting follow-up on one of the articles was made though by Adam Machanic that undermined one of our own best practices for a bit (which was to not use conversions or functions on the left side of a WHERE clause, because it prevents indexes from being used effectively). What Adam showed was that in SQL 2008, in conjunction with the addition of the DATE type, the query optimizer received a minor upgrade, which makes the following query use an Index Seek instead of a full Index Scan.

SELECT
    COUNT(*)
FROM #dates
WHERE
    CONVERT(DATE, the_date) = CONVERT(DATE, GETDATE());
GO

I am interested in how many more of these query optimizations have been implemented and will be implemented in the future.

Full details on his blog: http://sqlblog.com/blogs/adam_machanic/archive/2009/10/20/what-happened-today-date-and-date-ranges-over-datetime.aspx

Filtered Indexes

One of the cool new features of SQL 2008 is Filtered Indexes. A Filtered Index allows you to create an index on just a subset of data, using a filtering predicate. The reduction in index storage space can be significant.

For example, the AdventureWorks database has a Production.BillOfMaterials table with 2679 rows. The EndDate column has only 199 rows that contain a non-NULL value and the other 2480 rows contain NULL. A filtered index on only non-NULL values would therefore consume less then 10% of the original space of a regular index.

For a query like:

SELECT ProductAssemblyID, ComponentID, StartDate 
FROM Production.BillOfMaterials 
WHERE EndDate IS NOT NULL     
        AND ComponentID = 5     
AND StartDate > ’01/01/2008′ ;

The following filtered index could be created:

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;

It’s good to realize that columns used in the predicate are not required to be included in the index itself. Notice that the index itself does not contain EndDate. It’s only included in the filter predicate. Because it’s included in the filter, the index will be used by the query.

 

Besides the reduction in storage space, a smaller index will also improve performance of queries, by requiring less reads and a lower cache memory footprint.

 

To see which indexes have filters defined, execute the following query:

SELECT name, filter_definition
FROM sys.indexes
WHERE  has_filter = 1

 

Note: Filtered indexes can, of course, only be applied to non-clustered indexes.

SQL SERVER – 2008 – SCOPE_IDENTITY() and @@IDENTITY Bug with Multi Processor Parallel Plan

When a parallel plan is executed SCOPE_IDENTITY or IDENTITY may produce inconsistent results. The bug is active in SQL Server 2008 and any earlier version. See http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811

 

N.B. : The specific circumstances on the occurrence of the bug are still unknown to me. Pinal Dave has written a blog about it earlier, so he might know some more details: http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/.

 

Anyway, Microsoft recommends to use the OUTPUT clause to read the identity instead of @@identity or SCOPE_IDENTITY() :

The following code snippet shows more detail on how to use the OUTPUT statement to return the Identity column of the newly inserted record. When a trigger is used on insert the OUTPUT value cannot just be returned, it must be used in conjunction with the INTO statement.
ID is the identity column which is not inserted but is retrieved using the OUTPUT statement

    Declare @InsertedData table (ID int)
    INSERT INTO Orders (VendorOrderID, StoreID, DateCreated)
        OUTPUT Inserted.ID
        INTO @InsertedData
    VALUES (@VendorOrderID, @StoreID, @DateCreated)
    SELECT ID FROM @InsertedData

 

Full reply from Microsoft (quote from connect site)

Yes, it’s a bug – whenever a parallel query plan is generated @@IDENTITY and SCOPE_IDENTITY() are not being updated consistenly and can’t be relied upon. The few workarounds I can offer you for now:
1. Use MAX_DOP=1 as you are already using. This may hurt performance of the SELECT part of your query.
2. Read the value from SELECT part into a set of variables (or single tabel variable) and then insert into the target table with MAX_DOP=1. Since the INSERT plan will not be parallel you will get the right semantic, yet your SELECT will be parallel to achieve performance there if you really need it.
3. Use OUTPUT clause of INSERT to get the value you were looking for, as in the example I give further below. In fact I highly recomend using OUTPUT instead of @@IDENTITY in all cases. It’s just the best way there is to read identity and timestamp.
4. Changing autostas is NOT a good workaround. It may hide the problem for a while but a prallel plan will get produced eventually.
5. Force serial plans for entire server via sp_configure ‘max degree of parallelism’ option.
Now about life beyond workaround. We will fix it in SQL 2008. If you need this to be fixed also in SQL 2005 you will have to request a QFE through official support channels. I can’t promise anything either, as usual it’s a factor of complexity of request, resource availability, quality of workarounds etc.
In the end, thank you once again for reporting this. You have saved plenty of headaches for many people.
Denis Altudov, Microsoft.
=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
             Example as promised:
=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
DECLARE @MyNewIdentityValues table(myidvalues int)
declare @A table (ID int primary key)
insert into @A values (1)
declare @B table (ID int primary key identity(1,1), B int not null)
insert into @B values (1)
select
    [RowCount] = @@RowCount,
    [@@IDENTITY] = @@IDENTITY,
    [SCOPE_IDENTITY] = SCOPE_IDENTITY()
set statistics profile on
insert into _ddr_T
output inserted.ID into @MyNewIdentityValues
    select
            b.ID
        from @A a
            left join @B b on b.ID = 1
            left join @B b2 on b2.B = -1
            left join _ddr_T t on t.T = -1
        where not exists (select * from _ddr_T t2 where t2.ID = -1)
–option (maxdop 1) –!!! If you uncomment this line, it works
set statistics profile off
select
    [RowCount] = @@RowCount,
    [@@IDENTITY] = @@IDENTITY,
    [SCOPE_IDENTITY] = SCOPE_IDENTITY(),
    [IDENT_CURRENT] = IDENT_CURRENT(‘_ddr_T’)
select * from @MyNewIdentityValues
go
=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/

Posted by Microsoft on 6/18/2008 at 11:55 AM

Unfortunately, after evaluating our fix options we have come to conclusion that we can’t fix this for SQL 2008. We’re not doing this lightly, I have spent close to a month trying to make a robust fix for this, but amount of required work did not fit into this release.
If you come across this issue you will have to resort to one of the workarounds I have put in my previous post.

SSIS: Set "work-offline" without opening the project

If you’ve done much work with SSIS, you’re probably aware that on opening a package in BIDS, SSIS validates all the objects in the package. This can cause the packages to open very slowly, particularly if it has connections to a database that is currently unavailable. Fortunately, there is a way to enable the Work Offline option prior to actually opening the project.

Here are the details: http://agilebi.com/cs/blogs/jwelch/archive/2009/05/12/setting-work-offline-without-opening-the-project.aspx.

What’s your personality type?

Knowing your own strengths and weaknesses can really help in both work and private live, especially if you also know the personality type of the other people your dealing with. Certain personalities can clash if they don’t recognize and respect each others perspective. On the other hand, having different types of people in a group can create the balance that is needed for a project to be successful. Knowing your personality type can also help you explain your work preferences to your current or future employer or team members.

I have done a MBTI assessment and the result was that I am a ISTJ type. Since MBTI is an internationaly recognized methology, there are lots of websites with information. They can tell you your strengths, weakness, work place preferences, your role in relations, personal growth strategies, career options, etc. The site https://www.personalitypage.com/ gives a good amount of information, which was very accurate for my situation.

There are no official online tests available, as they need to be taken under supervision of an MBTI-certified official, but there are a few unofficial test available, like this one: Jung Typology Test.

SQL Core team visited SQLPASS NL meeting

Yesterday I visited another SQLPASS NL meeting. This time we had a session with the SQLCAT team who brought with them 2 members of the SQL Core development team.

The purpose of the meeting was for the team to get feedback on the issues that there are out there amongst us.

I issued the following features requests:

- The ability to read from a mirrored database. For example through read snapshot isolation. This way you could use database mirroring for both high-availablity and scale-out purposes.

- The ability to switch in and switch out filegroups in a database, comparable to partition switching.

- The ability to set a filegroup to read-only without having to go into single-user mode.

- Better support for restore operations in the GUI. Just like the Guided Restore of Quest’s Litespeed (it will ensure that additional backup files are automatically added to the restore when a transaction log or differential backup is selected using the restore wizard.)

- GUI support for restore of partial backups.

What is high on your wishlist for the SQL Server DBMS?

 

P.S. : Although most of the issued raised where already on there priority list, we should not expect them to be in the next release (SQL 2008 R2), as they try not to introduce any major changes to the SQL Core engine with this release. R2 will be focused on BI (Gemini), Azure, Madison and StreamInsight.

Do-it-yourself scheduling in SQL Server

I could have given this post the title "Resolving scheduler contention for concurrent BULK INSERT" like the original article, but I thought this one would be more ‘catchy’.

 Anyway, here’s another excellent article from Thomas Kejser, one of the hero’s from the SQLCat team with a good background of the internals of the SQLOS Scheduling mechanism.

You should definitely read it if you ever wondered something like ‘Why is there only one CPU core at work when I have 4 of them’.

http://sqlcat.com/technicalnotes/archive/2008/04/09/resolving-scheduler-contention-for-concurrent-bulk-insert.aspx

How to handle rotten bits

Bit-rot? Yes,it exists..

A quote from the article DBCC Checks and Terabyte-Scale Databases:

“.. It turns out that there are rare circumstances in which data on storage media can become corrupted in the absence of I/O ("bit rot")…”

The article is a must read for any DBA that has not implemented a DBCC CheckDB as part of his maintenance schedules.

It’s NUMA time!

I must confess; I didn’t know. I didn’t know  Non-Uniform Memory Architecture (NUMA) had arrived in the mainstream computer world. Sure I knew about NUMA; Expensive hardware systems, like Mainframes and large Unix system have been using it for years.

But in fact, since the arrival of the AMD Opteron, Intel’s Itanium and especially now with the new Nehalem processor (Core i7), even simple dual processor systems have NUMA onboard:

As an independent DDR3 IMC is integrated on each socket for the interface to access memory, the IMC platform remarkably increases the bandwidth (the peak bandwidth of DDR3-1333 can be up to 32GB/s, 4-6 times wider than previous platforms), reduces the memory latency, improves performance, and offers each CPU a fast channel to local memory. Unlike previous generation platform, IMC platform uses NUMA architecture for memory access, thus greatly advancing the performance of NUMA-aware applications. DDR3 IMC supports up to 96GB DDR3 memory capacity per CPU interface, and even up to 144GB in the future, which provides a strong memory support for high-end enterprise computing.

(Quote from Learning Experience of NUMA and Intel’s Next Generation Xeon Processor I)

Fortunately, MS SQL Server 2008 (as did 2005) fully supports NUMA.