Erland Sommarskog (SQL Server MVP) has just announced beta version of his utility stored procedure that can be used to track down blocking situations.

Go to beta_lockinfo website
Go to Erland Sommarskog's website

Tomorrow I'm going for holidays! I will take a rest a little (but not much!). Me and my family are going to the seaside. There will be a lot of fun!

Have you ever used ROUND function in your queries? If you have than maybe you have discovered that there are some problems with the results it sends to a client. Especially when you use SSMS or SQLCMD tools. Examples?

Execute the following in SSMS:

SELECT ROUND(99.95,0)

The result you receive is some kind of artithmetic overflow error.

An error occurred while executing batch. Error message is: Przepełnienie arytmetyczne.

The remedy is simple - just multiply ROUND(...)  by 1.0 to receive the correct result.

And now, try to execute it in SQLCMD. The result?

------
.00

What the result should be? 100.00 of course... BTW, Query Analyzer displays the proper result.

Also try the following:

USE tempdb
GO
SELECT TOP 1 ROUND(0.95,0) col INTO temp FROM sys.databases
EXEC sp_help temp
DROP TABLE temp

See the precision (=2) and scale (=2) of the created column. Try to set a value of decimal(2,2) to 1 in T-SQL :-)

This issue has been reported as a bug by Erland Sommarskog:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=288555

Today Marek Adamczuk found some KB on performance issue caused by procedure cache flushing. We have seen this behaviour several times and have not located the cause of cache flushing. And it seems that we had the problem because of ... database restore!!!

You can also flush the entire procedure cache with single query that stresses CPU!!!
And this is by design!!!

Read the KB to know more.
http://support.microsoft.com/kb/917828

I've decided to send it as a bug. You can vote for this opinion (click below).
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287295

Today I was really surpised by SQL Server 2000 BOL... There is CREATE SCHEMA statement in SQL Server 2000! I don't know if someone made a mistake and put it into wrong build of the server :-) The statement does not do anything special. It can group several statements like CREATE TABLE, CREATE VIEW and DCL statements and execute them all or none in case of error.

There's always something new in this server everyday... :-)

Project ITCore.pl goes into testing phase. ITCore.pl will be the biggest Microsoft community website in Poland. I was selected te be the leader of testers. My duty is to schedule tests and to divide testers into groups. I just can't wait to start testing! 

More on ITCore.pl project: www.itcore.pl.

Last month I made some research on using SQL Server Browser service. I put my reflections and some common knowledge into short article available on Technet Poland. Click below to read the article.

SQL Server 2005 - SQL Server Browser service

There are two new MVP awardees in Poland: Grzegorz Tworek (Windows - Security) and Kazik Kuta (Visual Developer - Visual C#). Congratulations to both professionals! Special congratulations to Grzegorz, who seems to be the most Vista and Windows Server 2008 capable system engineer in Poland right now :-)

If you have lots of stored procedures in your database and you are not afraid of recompilations, well, you should be!!! Every single SP drains from cache size limit... Let's see how we can calculate maximum size of procedure cache.

SQL Server 2005 RTM & SP1: 75% of server memory from 0-8GB + 50% of server memory from 8Gb-64GB + 25% of server memory > 64GB

SQL Server 2005 SP2: 75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB

What does it mean? It means that if you have for example a server with 32GB RAM dedicated for SQL Server, SQL Server 2005 RTM and SP1 cachestore limit will be 75% X 8 + 50% X (32 - 8) = 18GB.

But... If you have the same server with SQL Server 2005 SP2, the limit will fall down to 5.8 GB...

Why am I writing this? Because as a DBA I would like to have power to leverage between buffers and cache. And because I've seen situations when lack of memory caused huge recompilations (for example, I don't know why but only couple tens of plans were cached instead of thousands).