SQL Server 2005 - June CTP Installed

Aha finally gotten around to installing SQL Server 2005 June CTP.
I wanna play but it's too late :( I need some rest if I want to make it through the week :)

AWE vs SQL Server 2000 SP4 - Fix available

Aha!
The fix for the AWE/SP4 problem has arrived!

Creating covering indexes in SQL Server 2005

CREATE INDEX INCLUDE statement (SQL Server 2005)

I've been testing the new INCLUDE statement in 2005.

From the BOL:

INCLUDE ( column [ ,... n ] )

Specifies the nonkey columns to
be added to the leaf level of the nonclustered index. The nonclustered index can
be unique or nonunique.

The maximum number of included nonkey columns is 1,023 columns; the minimum
number is 1 column.

Column names cannot be repeated in the INCLUDE list and cannot be used
simultaneously as both key and nonkey columns. For more information, see Index
with Included Columns.

All data types are allowed except text, ntext, and image. The index must be
created or rebuilt offline (ONLINE = OFF) if any one of the specified nonkey
columns are varchar(max), nvarchar(max), or varbinary(max) data types.

Computed columns that are deterministic and either precise or imprecise can
be included columns. Computed columns derived from image, ntext, text,
varchar(max), nvarchar(max), varbinary(max), and xml data types can be included
nonkey columns as long as the computed column data types is allowable as an
included column. For more information, see Creating Indexes on Computed
Columns.

Didn't have much time but I did a couple of quick tests. Just created a simple table with a covered index 'the old way' and then 'the new way'. From what I've seen until now the duration is not much less but inserting 10000 records in to the table takes about 10000 reads less with the INCLUDE index. From the info above I suppose it has something to do with the fact that the nonkey column is only present on the leaf level and not throughout the whole tree like a key column.

As soon as I find some time to test is a little deeper I'll try to let you know!

SQL Server 2005 - June CTP

Oh yeah, I forgot to mention it :-s
As you probably already know... SQL Server June 2005 Community Technology Preview is available!

Go go go!

AWE vs SQL Server 2000 SP4

There appears to be a fix for the AWE problem in SP4 of SQL Server 2000.
The bad news is that it's not available yet at least not to the public, although it should be available through PSS.

Report Builder available in Standard Edition of SQL Server 2005 Reporting Services

Some good news from Microsoft, and I quote Paul Flessner:

In February, we announced the product line for SQL Server 2005. As part of
this announcement, we revealed that Reporting Services would ship with
Standard
and Enterprise Editions and that Report Builder would be available
with
Enterprise Edition. Based on your feedback, and because we believe that
reporting is a core scenario for all customers, we've made the decision to
include Reporting Services in all versions for SQL Server 2005 including
Express, Workgroup, Standard and Enterprise Editions. In addition, Report
Builder will be available with Workgroup, Standard and Enterprise
Editions.

I think it's great that Microsoft listens to their users... keep up the good work!

How to avoid "Parameter Sniffing" in SQL Server 2005

Good news for people with 'parameter sniffing' problems.
Although Ken Henderson gave us a nice solution some time ago for SQL Server 2000, SQL Server 2005 will support the OPTIMIZE FOR statement. Adding this to a query makes sure the plan is optimized for the value you specify. And when all else fails you can even force a particular query plan with the new USE PLAN statement. I'll try to give some examples as soon as I find some time.

Visual Studio 2005 and SQL Server 2005 release date announced

Just as announced a couple of months ago (although not officially), Microsoft Sets Visual Studio, SQL Server, BizTalk Server Launch Date in November.

More information here

As part of the keynote address, Paul Flessner, senior vice president of
Server Applications at Microsoft, showed the company’s continued momentum in
preparation for the launch of SQL Server™ 2005, Visual Studio® 2005 and BizTalk®
Server 2006, and announced that these products will be formally launched during
the week of Nov. 7.

Reporting Services still doing a great job

Our Reporting Services solution is still running fine :-D

This gives me the chance to leave for Spain in a happy mood. We (our company) are going on a trip to Spain from Wednesday (yep that's right, tomorrow :p) until Tuesday next week. While we are there there will be 2 courses. One will be about SQL Server 2005 and the other one about Visual Studio 2005 and obviously .NET 2.0. Two great topics imho although many people will disagree :)

The weather is looking quite nice for the time being ;)

Reporting Services doing a great job

In other news, our Reporting Services solution is delivering 20-25 reports per second (easily getting the 8 per second which was our goal. The reports are available around 01:10am where the target is 07:30am, I guess we can generate some more :-D

A couple of minor data issues the first 2 days in production but everything went quite well.

/me is happy

How to avoid "Parameter Sniffing" in SQL Server 2000

Here's a great article about parameter sniffing.
Funny he's using Belgium as an example, it's where I live :)

Tomorrow will be the big day.
The first test run in production today was a bit disappointing :( Instead of 32 we were only getting 17 reports per second. Although that is still twice the amount we need I would have hoped that the performance would be better. The problem is that there are other processes running (and quite heavy ones) while the reports are being generated. Because the new SAN has to be installed we are running all this on local disks atm.

I was browsing http://www.sql-server-performance.com when all of the sudden I saw this article. It's not the article but who wrote it what makes it interesting. It's an ex colleague who apparently found some time to write up this nice article. Nice going Geert ;)

This Sunday will be the release of our Reporting Services solution. I'm getting kind of nervous as they haven't installed our production servers yet and they should be ready by the end of the week. There is a lot of pressure on the solution as the current solution delivers the reports with unacceptable delays. Another problem is that the people from the union are very displeased with this. Generating the reports on time and with the correct information is of major importance.

I'll let you know whether I'm happy or sad come Tuesday :-)

I saw this cool product called SSW SQL Audit.
As soon as I find some time I'll test it. I'll keep you updated.

http://www.ssw.com.au/SSW/Products/ProdSummaryList.aspx

UPDLOCK

The UPDLOCK locking hint is a very interesting one.
It prevents other connections from updating a record but allows other connections to still read the information. It assures you that your process will be the next in line for an exclusive lock. SQL Server actually uses update locks before acquiring an exclusive lock (eg. UPDATE with a WHERE clause). It happens so fast most of the times that they probably won't popup in the locking information you get to see.

To prevent conversion deadlocks (typical in a read and update transaction) it is a good idea to use this locking hint (you'll probably want a REPEATABLE READ or SERIALIZABLE isolation level for these kind of transactions too).

Finally found some time to stress test our Reporting Services solution on the servers that will soon run in production.

With 1 DB server and 2 App servers we are generating 32 reports/sec. Quite a nice result as our target was 8/sec :-)

If you want more information or details feel free to contact me.

Reporting Services weirdness :s

In order to get a text from a database to display in the page footer you have to include a textbox in the data region and then reference this textbox in a second textbox which you create in the footer region.

No problem so far, a bit weird but not too much trouble. However, when exporting to PDF the footer would just display on the first page and not on all the following pages.

I have solved it by defining an extra parameter, clearing the prompt text and setting the default value to From Query. Select the correct Dataset and the field you would like to use.

Works like a charm.

There was one little issue left though. The report would fail if the stored procedure that retrieved the parameter value would not return any records. I have solved this with the following simple yet effective workaround:

DECLARE @Description varchar(1024)

SELECT @Description = [Description] FROM tbl_document_caption WHERE ...

SELECT @Description as [Description]

Check out this website.
It was created by a friend of mine and I promised I would promote it :-D

http://www.lollersaurus.com

It appears that there is an issue in SP4 for SQL Server 2000 with AWE.
From the Microsoft website:

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. Microsoft is currently working on the
problem and will issue an update soon.