Saturday, December 31, 2005

Deprecation event category - Follow up

As today is a boring day (until tonight of course) I tested the Deprecation event in Profiler.

I know Microsoft has done an amazing effort in getting error descriptions as clear as possible and they succeeded quite nice but this is amazing.

I tried a DBCC SHOWCONTIG which will be replaced and this is what I see in Profiler:

DBCC SHOWCONTIG will be removed in a future version of SQL Server. Avoid
using this feature in new development work, and plan to modify applications
that
currently use it. Use sys.dm_db_index_physical_stats instead.


With sp_lock I get the following:



sp_lock will be removed in a future version of SQL Server. Avoid using this
feature in new development work, and plan to modify applications that currently
use it.

The use of more than two-part column names will be removed in a future
version of SQL Server. Avoid using this feature in new development work, and
plan to modify applications that currently use it.



How clear is that?

3..2..1..1

Because of a corrective measure 2005 will be one second longer... enjoy it :-)

Anyway, may I wish you a very happy 2006 where all your dreams come true.
May it also be a year of many SQL Server 2005 implementations in your environment ;-)

Thursday, December 29, 2005

Reporting Services Timeout through SOAP

We have report that is quite large and causes timeouts for one of our offices not surprisingly the one with the most transactions.

So I started looking for the timeout configuration values and I must say Reporting Services has quite a collection of timeout values :-)

We do a nightly generation of PDF files through the means of the RS webservice and it was the call to the webservice that timed out. The behavior wasn't influences by any of the configuration values of Reporting Services. So I started digging deeper and came across the following solution.

Add base.Timeout = 3600 (in milliseconds) to the constructor of the ReportingServiceProxy class.

Troubleshooting Performance Problems in SQL Server 2005

Another great whitepaper by Microsoft.
Find it here

Wednesday, December 28, 2005

Deprecation event category

Except for the unbelievable blocking process monitoring and the amazing deadlock detection in the new SQL Server 2005 profiler I stumbled upon another nifty feature.

It's the Deprecation event. This event is triggered on statements that will no longer be supported in future versions of SQL Server.

I think it's quite funky that Microsoft includes this in Profiler.
I haven't played around with it yet but as soon as I find some time I'm sure going to.

Tuesday, December 27, 2005

OPTION FAST vs CONVERT

I was playing around with the OPTION FAST statement for an update query when I saw a 'weird' behavior.

When using the OPTION FAST SQL Server passed the criteria as a fixed value while without it, it would use a variable because of a conversion.

Then I remembered the following article which states a list of situations where auto-parameterization is not happening and one of it is:

A statement with query hints specified using the OPTION clause.

I was passing a uniqueidentifier between single quotes so apparently the auto-parameterization is storing it as a character variable in the cache.

Another mystery solved :-)

Playing around with DBREINDEX/CREATE INDEX

There are many ways to rebuild your indexes in SQL Server 2000 and I felt like doing some performance testing on the different ways.

A DROP and CREATE is obviously the slowest because you are rebuilding your non clustered indexes twice this way. First he has to change the bookmark to the RID because it is becoming a heap instead of a clustered table and then he has to change the bookmark again to point to the clustering key.

CREATE WITH DROP_EXISTING and DBCC DBREINDEX rebuild the clustered index in one transaction preventing the double rebuild of the nonclustered indexes. When you are rebuilding a unique clustered index the nonclustered indexes aren't being rebuilt at all. The same goes for the CREATE WITH DROP_EXISTING if you are recreating the index on the same key.

Keep in mind that creating a clustered index without specifying the UNIQUE keyword gives the opportunity to insert non unique records. When this happens SQL Server adds 4 extra bytes to every key to guarantee the uniqueness. Another side effect is that nonclustered indexes are being rebuilt because the 'uniqueifier' is regenerated every time you rebuild the index and thus changing the clustering key.

One of the 'benefits' of the DROP_EXISTING method vs the DBCC DBREINDEX is the ability to pass the SORT_IN_TEMPDB option. This forces SQL Server to store the intermediate sort results that are used to build the index in tempdb. Although this may increase the index rebuild performance it does take more diskspace.

SQL Server 2005 introduces the ALTER INDEX statement with a REBUILD option.

Here is a little test script (always recreate the table when testing a reindexing method).
There is no time for the ALTER INDEX because I was testing on SQL Server 2000. But it should be about the same as DBCC DBREINDEX as they are supposed the be equivalents.

SET NOCOUNT ON
IF OBJECT_ID('myTest') IS NOT NULL
DROP TABLE myTest
GO
CREATE TABLE myTest
(myID int,
myChar char(512)
)
GO
DECLARE @i int
SET @i = 0
WHILE @i < 1000000
BEGIN
INSERT INTO myTest (myID, myChar)
VALUES (@i, REPLICATE('Z', 512))
SET @i = @i + 1
END
GO
CREATE UNIQUE CLUSTERED INDEX ci1 ON myTest (myID)
GO
--Create a ridiculous amount of indexes :)
CREATE NONCLUSTERED INDEX ix1 ON myTest (myChar)
GO
CREATE NONCLUSTERED INDEX ix2 ON myTest (myChar)
GO
CREATE NONCLUSTERED INDEX ix3 ON myTest (myChar)
GO
CREATE NONCLUSTERED INDEX ix4 ON myTest (myChar)
GO
CREATE NONCLUSTERED INDEX ix5 ON myTest (myChar)
GO
CREATE NONCLUSTERED INDEX ix6 ON myTest (myChar)
GO
CREATE NONCLUSTERED INDEX ix7 ON myTest (myChar)
GO
CREATE NONCLUSTERED INDEX ix8 ON myTest (myChar)
GO
CREATE NONCLUSTERED INDEX ix9 ON myTest (myChar)
GO
CREATE NONCLUSTERED INDEX ix10 ON myTest (myChar)
GO
CREATE NONCLUSTERED INDEX ix11 ON myTest (myChar)
GO
CREATE NONCLUSTERED INDEX ix12 ON myTest (myChar)
GO
CREATE NONCLUSTERED INDEX ix13 ON myTest (myChar)
GO
CREATE NONCLUSTERED INDEX ix14 ON myTest (myChar)
GO
CREATE NONCLUSTERED INDEX ix15 ON myTest (myChar)
GO

--Drop and create - 00:26:39
DECLARE @Start datetime
SET @Start = Getdate()
DROP INDEX myTest.ci1
CREATE UNIQUE CLUSTERED INDEX ci1 ON myTest (myID)

PRINT CONVERT(varchar,GetDate() - @Start , 108)
--Create with DROP_EXISTING - 00:01:23
DECLARE @Start datetime
SET @Start = Getdate()
CREATE UNIQUE CLUSTERED INDEX ci1 ON myTest (myID) WITH DROP_EXISTING

PRINT CONVERT(varchar,GetDate() - @Start , 108)
--DBREINDEX - 00:01:16
DECLARE @Start datetime
SET @Start = Getdate()
DBCC DBREINDEX(myTest, ci1, 0)
PRINT CONVERT(varchar,GetDate() - @Start , 108)
--ALTER INDEX
DECLARE @Start datetime
SET @Start = Getdate()
ALTER INDEX ci1 ON myTest
REBUILD;
PRINT CONVERT(varchar,GetDate() - @Start , 108)
--Clean up
IF OBJECT_ID('myTest') IS NOT NULL
DROP TABLE myTest
GO