Ken Henderson's Guru's Guide to SQL Server Architecture

Please check Ken's post.
Let him know how you feel about including OS information in his new book!

NOLOCK vs Clustered Index Order Part III

Ok... even worse now :-p
Issuing a DBCC DBREINDEX solves the NOLOCK order problem but then again... not if you have multiple datafiles in your database.

Oh boy :-)

NOLOCK vs Clustered Index Order Part II

Still haven't figured out why exactly it's happening but here's a script for the people that want to try.

CREATE TABLE tblClustered
(ID int,
MyDate smalldatetime,
TestField varchar(50))
GO
CREATE CLUSTERED INDEX ixID ON tblClustered (ID, MyDate)
GO
DECLARE @i int
SET @i = 0
WHILE @i < 1000
BEGIN
INSERT INTO tblClustered (ID, MyDate, TestField) VALUES (RAND() * 1000, CONVERT(varchar, getdate(), 112), REPLICATE('T', 50))
SET @i = @i + 1
END
SELECT * FROM tblClustered (NOLOCK)
SELECT * FROM tblClustered

DROP TABLE tblClustered

NOLOCK vs Clustered Index Order

I'm trying to understand something.

I have a simple table with a Clustered Index on an integer field.

When I use 'SELECT * FROM tblClustered' the records are nicely ordered by the clustered index key. When I use 'SELECT * FROM tblClustered (NOLOCK)' however, the records are returned in a semi-random order. With semi-random I mean that the order is always the same but they are not sorted by the clustered index key. The queryplan is the same, I've tried DBCC DROPCLEANBUFFERS, CHECKPOINT, Restart of the SQL Server Service, you name it...

Obviously you should never rely on the clustered key for the sort order but I'm just trying to figure out what's happening 'inside' that makes this difference.

More to follow!

Performance Fix for AMD Dual Core users

Something for freaks like me :-)
There seems to be a performance fix for AMD Dual Core users with Windows XP SP2. It's seems like you have to contact Microsoft for the fix and it can't be just downloaded... weird :-s

Source

Business Intelligence

Hmm... now that SQL Server 2005 is getting closer I'd like to acquire some knowledge about Business Intelligence.

Let's start with this article

SQL Server 2005 Mission Critical High Availability

High availability with SQL Server 2005
Lots of interesting information here

SQLPass.NL

Check out our very own PASS for Dutch speaking people :-)
http://www.sqlpass.nl

Atlas - .NET feat. AJAX

AJAX rocks!
Check out this preview site.

Windows Vista Release Schedule

Check it out here

Funny news

How about this woman :)
Here

Filter table on NULL value in Reporting Services

I was trying to find the correct filter statement for a NULL value in Reporting Services but I always seemed to fail.

So what's the trick?

Filter: =IsNothing(Fields!YourField.Value)
Operator: =
Value: =True

Notice the = before True as this really makes a difference!

Query Optimizer vs Statistics

A nice Technet article on how the query optimizer uses statistics
Here

More on LINQ

Nice article on MSDN about LINQ

Visual Studio 2005 CTP Uninstaller

Aha... official tool by Microsoft

SQL Dependency Viewer

The people from RedGate keep amazing me with great tools.
Check out this tool... bye bye select * from sysdepends :-)

Been a while

It's been a while but there is a very good reason for that :-(

There were some complications with the pregnancy of my girlfriend. The little one wanted to get out already but my girlfriend is only 30 weeks pregnant so it has to stay in a couple of weeks longer.

Anyway, a week and lots of medication later it seems to be under control... for the time being.

So as of tomorrow I should be back on track and posting interesting SQL stuff.

LINQ

A very weird innovation for a DBA :-)

LINQ

MSDN Webcasts

We all love them but can we find the time to listen to them?
Well Jeffrey Palermo found an interesting way... here

Database Mirroring out???

Hmmm I was reading this article by Paul Flessner when I came across this:

One lesson we learned is that we believe our Database Mirroring feature
needs more time in the hands of customers before we make it generally available
for production use. The feature is complete, has passed extensive internal
testing, and we are committed to making it generally available in the first half
of 2006. We will continue to field-test the feature with customers and will
release it for general use as soon as you tell us it is ready.

September CTP

Hmmm PDC is busy... where's my September CTP of SQL Server?? :-)
Aaah here it is

Another holiday...

Aaah another week @ home!
Although holiday is a big word... some heavy construction work going on :-p I have to prepare the room for my first little one which is on the way!

Happy happy, joy joy :-D

Microsoft hates DBAs

Funny article about SQL Server 2005 and the CLR integration :-)

bit or tinyint

I'm looking for some information about the following:
Is it better to use a tinyint field instead of a bit field if you are going to index it? Some people say it would probably be better to use a tinyint because the bit has to be extracted from the bitmask causing some overhead. I haven't found any real deep technical information about this but it sounds acceptable.

So if any of you have real hardcore stuff please post a comment :-D

In case I find some info I'll obviously send a little update!

Insufficient result space to convert uniqueidentifier value to char.

I needed a char representation of a GUID field but couldn't retrieve it because of an obscure error :-s

Insufficient result space to convert uniqueidentifier value to char.

This was caused by a CAST as varchar without specifying the length (or a too short length).

Just using CAST(field as char(36)) solved the problem.

Understanding TempDB, table variables v. temp tables and Improving throughput for TempDB

Yet another great post of one of my favorite SQL Server gurus :)

Finding Duplicate Indexes in Large SQL Server Databases

A nice article on how to detect duplicate indexes.
I suppose it happens more than you think...

Create a PDF from a stored procedure without using 3rd party utils

Hmm, a very funky way to create a PDF in SQL Server :-)
Check it out.

I can't say that I'm a fan of such practices but who knows someone might be able to use it out there.

Uninstalling CTP

Want to get rid of previous CTP installations easily?
Check out this post. The VS 2005 Diagnostic and Uninstall Tool does the sometimes difficult task for you!

Override isolation level

When setting the transaction isolation level it could be nice to be able to control this on table level regardless of the level specified. SQL Server supports this by adding a WITH in the FROM clause.

Eg.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT Field FROM table t1
INNER JOIN table2 t2 WITH (READUNCOMMITTED) ON t1.ID = t2.ID

This would cause serializable locks on t1 and read uncommitted on t2.

String Summary Statistics

I found this little interesting information in this article

SQL Server 2005 includes patented technology for estimating the selectivity of LIKE conditions. It builds a statistical summary of substring frequency distribution for character columns (a string summary). This includes columns of type text, ntext, char, varchar, and nvarchar. Using the string summary, SQL Server can accurately estimate the selectivity of LIKE conditions where the pattern may have any number of wildcards in any combination. For example, SQL Server can estimate the selectivity of predicates of the following form:

Column LIKE 'string%'
Column LIKE '%string'
Column LIKE '%string%'
Column LIKE 'string'
Column LIKE 'str_ing'
Column LIKE 'str[abc]ing'
Column LIKE '%abc%xy'

If there is a user-specified escape character in a LIKE pattern (i.e., the pattern is of the form LIKE pattern ESCAPE escape_character), then SQL Server 2005 guesses selectivity. This is an improvement over SQL Server 2000, which uses a guess for selectivity when any wildcard other than a trailing wildcard % is used in the LIKE pattern, and has limited accuracy in its estimates in that case. The String Index field in the first row set returned by DBCC SHOW_STATISTICS includes the value YES if the statistics object also includes a string summary. The contents of the string summary are not shown. The string summary includes additional information beyond what is shown in the histogram. For strings longer than 80 characters, the first and last 40 characters are extracted from the string and concatenated prior to considering the string in the creation of the string summary. Hence, accurate frequency estimates for substrings that appear only in the ignored portion of a string are not available.

MySQL

I've been playing with MySQL for a project of a friend of mine (http://www.yawn.be).
I honestly love SQL Server even more now :-) I don't want to talk bad about MySQL itself because I don't know enough about the product to do so. But the tools provided are really sad as opposed to the tools that are included with SQL Server. I just wanted to know the queries that were run and their io, cpu, duration etc (<3 Profiler<3) but apparently there is no way with the standard tools to determine this. Graphical Query plans or even plans like the Text Query Plans in SQL Server... couldn't find it :-(

MSDN users to get early access to Visual Studio 2005 tools

According to this article in Infoworld we will have access to a release candidate of SQL Server 2005 and Visual Studio 2005 in September! The final versions would be available mid to end October.

Very good news!

Using Sp_configure To Change a Value Will Issue DBCC FREEPROCCACHE

Another little known fact I guess but Brian Moran pointed out that issueing an sp_configure triggers the DBCC FREEPROCCACHE which removes all cached procedure plans.

SQL Server Magazine's Reader's Choice Awards

An the award goes to....... A First Look at SQL Server 2005 for Developers.

Congratulations to the authors!

Buy

Adding a non nullable column without a default

When trying to add a column with a default that is not nullable to an existing table you get an error message (even when there are no records in the table)

ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column 'NewColumn' cannot be added to table 'tbl_test' because it does not allow nulls and does not specify a DEFAULT definition.

There is however an easy workaround.

ALTER TABLE dbo.tbl_test
ADD NewColumn smalldatetime NOT NULL
CONSTRAINT DF_ToDrop DEFAULT ('2000-01-01')

ALTER TABLE dbo.tbl_test
DROP CONSTRAINT DF_ToDrop

Google Talk

Google released an IM GoogleTalk.
Looks like MSN could get a decent competitor

Ken Henderson

For those of you that don't have it already... buy the book!

sql_variant

sql_variant is one of the datatypes that I don't often see being used although it has it's advantages. It's kind of like a varchar where you would be able to store numeric characters, alphanumeric characters, dates, ... with the difference that on a sql_variant field you would be able to determine of which datatype the value is.

One tip I would give you is to always explicitly cast the value you insert (or update) to the datatype you want it to be. Apparently SQL Server uses 2 extra bytes to determine the extra information like the datatype etc. (eg. int would be 6 bytes).

The SQL_VARIANT_PROPERTY function gives you information about the variant itself like datatype, total bytes, precision ...

For more information visit:
sql_variant
SQL_VARIANT_PROPERTY

An interesting Q&A with Kimberly L. Tripp

Read it here

Back and already having fun

Aaah back from my 2 week holiday. I can't say I'm thrilled to be back but I can live with it ;-)

Especially when I find things like this :-)

Here's a nice article about fragmentation

Holiday

Hi,

I've got 2 weeks off so you might not read very much this week and the week to come :)
Take care

Give the New PIVOT and UNPIVOT Commands in SQL Server 2005 a Whirl

Nice article about PIVOT and UNPIVOT in SQL Server 2005

This must be painful

Paul Thurrott wrote an article where he urges us to boycott IE because of it's lack of support for standards. Now someone has written a nice reply to this...

Ouch ouch ouch

Let’s get into standards now. Guess what, Paul? Your site,
WinSuperSite.com currently has 124
validation errors, according to the W3C’s Markup Validation Service.
Even worse, the page which contains your “Boycott IE” story currently has 207
validation errors. Both pages don’t even define the page’s doctype,
which is almost always the first line of the web page.

Page splits

Page splits are not too good for performance and should be closely watched.
One way of determining whether you are having page splits and how many is to use the function ::fn_dblog (@StartingLSN, @EndingLSN). Passing NULL to the two parameters makes sure that the whole log is being read.

eg.

SELECT [Object Name], [Index Name], COUNT(*)
FROM ::fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_SPLIT'
GROUP BY [Object Name], [Index Name]
ORDER BY COUNT(*) DESC

You can reduce page splits by specifying a good fill factor. There is no real rule of thumb about fill factors. It all depends on the rowsize and number of rows inserted (between index rebuilds). So specifying a good fill factor and a DBCC DBREINDEX from time to time will minimize the number of page splits.

An unexpected error occurred in Report Processing. (rsUnexpectedError)

I'm creating one of the most important reports for the business and I'm having a weird problem. The report renders just fine in the browser but as soon as I try to export it to a PDF (which is basically what we do with all our reports - of course automatically through the webservice) it crashes with the above mentioned error.

After some surfing I came across an article in the Reporting Services newsgroup where one MattiasT had the same problem and he found the conditions for it to happen.

1. You are using a table.
2. The HideDuplicates property is used on a detail cell in this table.
3. The table contains a subreport.

And sure enough... when I remove the HideDuplicates the report generates just fine.

Unfortunately there seems to be no solution for the time being, except for changing the way you create the report because having all 3 conditions is a prerequisite.

TempDB and missing Device

Just had a nice little incident :-s

One of our development servers was used for tests with a SAN disk. They moved our TempDB to the SAN and after the tests removed the SAN disks. No problem, but they forgot to move the TempDB location to the local disks. Result? No more SQL Server :-x

In order to get it back up we did the following:
Start SQL Server from the command prompt with the following syntax
sqlserver -f -c -T3609 -T4022

-f: minimal configuration mode
-c: not as a service (console mode so you can follow the nice little thingies SQL Server does)
-T3609: skip tempdb creation at startup
-T4022: bypasses automatically started procedures

Then we updated sysdatabases and sysaltfiles (I know it's hardcore) with the correct file locations.

Restart SQL Server and GO GO GO

The Daily WTF (What The F*ck)

For those of you that don't know the site, this is really one you should visit every day ;-)
It's a website that gathers programming stupidity on a daily basis.

Connected Systems Developer Competition - Win $50,000 USD

If you are feeling very lucky ;)
Check out this link.

Mindi Abair

Heard some nice music yesterday. I was chilling with some music on SmoothJazz when I heard a song by Mindi Abair called Make A Wish, very nice indeed! When I was all chilled out I switched to Radio Paradise which is really a very good radio station.

Check them out.

Week @ Home

Aaah, just got back from a little holiday :-s
It's good to be back but I liked it more being home ;-)

CHAR and NULL

CHAR fields always take up the space assigned to them. Now you may think that it wouldn't be the case if it was NULL because that is determined by a bitmask in the row data (this bit is always present in SQL Server 2000 even if your column is not nullable).

This was true in SQL Server 6.5 but not in SQL Server 2000. So if you really need a nullable CHAR field it may be better to define the field as VARCHAR.

Report Packs

If you are working with Reporting Services please don't forget to check here once in a while.
Microsoft provides some nice Report Packs from time to time.

Have fun

New computer - installed

Installed and man does it fly!
I am very pleased with the results although I had some trouble with XP64 and I had to install XP32 again :-(

Kimberly L. Tripp on developing for SQL Server

Check it out here

A bold statement: "Open source could damage the market"

Bob Hayward, senior vice president and chief research officer, Gartner Research Asia-Pacific, said that software developers could be discouraged from creating new software because of the multitude of open source software available for free. This is further driven by major vendors that are making their software available as open source.

Check out the complete article here

Software Patents... No no no

The EU has voted against "Software Patents".
Of the 732 people 648 voted no, a clear vote imho.

I suppose this will not be the end of the whole story...

New computer - update

Yes yes yes :)
It has arrived... only the CPU was out of stock so I have to wait a little bit :'( If all goes well it should arrive tomorrow.

I also decided to buy another case :-)

SAN Phase II

We got to talk with the people responsible for the SAN.
Seems our plans are quite useless, the layout of the disks has changed :-( Anyway, we decided on a new plan. Currently +200 disks are assigned to our development servers. We're waiting for the draft as proposed by them (considering our remarks of course).

Enterprise Library - June 2005 Release

Get it here

Microsoft Baseline Security Analyzer 2.0

Download it here

I assume you all know this tool?

Those dreadful 'Software Patents'

Check out this post by Clemens Vasters

Tetris SAN

Hmmm... we're trying to determine the layout of our development servers databases and files on the new SAN. We have to place 7.5TB for 48 servers in 14GB blocks... it looks a lot like tetris :-D

AWE 3GB PAE... WTF?

Lately we have been experimenting with the memory options in our tests of a new server. Sometimes it's not quite clear what all those options do, I'll try to give a short and clear description here.

/3GB

Windows normally allows user mode applications to use 2GB of memory. The switch moves the starting point of the kernel to 3GB making it available to user mode applications. Windows 2003 added an extra switch userva to customize the exact amount between 2GB and 3GB. In short this makes it possible for your applications to use 3GB instead of 2GB of memory. Do note that there may be some drawbacks when your server is very powerful. In some cases the server might be able to handle so many threads that it's 1GB of kernel memory is insufficient.

Note Microsoft Product Support Services strongly recommends using a range
of memory for the /USERVA switch that lies within the range of 2800-3030. This
range is wide enough to provide a large enough pool of system page table entries
for all currently observed issues. Usually a setting of /userva=2800 will
provide close to the maximum available number of system page table entries
possible.

PAE (Physical Address Extension)

PAE allows Windows to use more memory than 4GB in combination with AWE

AWE

AWE is an API that enables programs to address more than 4GB (with PAE). It enables them to reserve memory as 'non-paged' memory. This means that memory taken by AWE is never paged to disk until the applications is closed or explicitly frees it.

8 Steps to better Transaction Log throughput

For those of you who don't read Kimberly Tripp's blog: check out this great post
And after that add her website to your favorites!

New Computer

It's finally time for a new computer. I've got this one for 3 years already and in the geek world that's a mighty long time :-p

This is what I had in mind:

MSI S939 K8N SLI Platinum nForce4UltraSLI 4DCDDR4 2PCI-X Sata 7.1ch FW GLan

AMD Athlon 4200+ 64Bit X2 Dual-Core Processor Socket 939 Boxed with cooler

GEIL Ultra-X Series Dual Channel 1GB 2x512MB PC3200 400MHz Selected 5ns CL 2 5-2-2 GLX1GB3200DC

Western Digital Raptor 74Gb E-Sata 10000rpm 8Mb cache 5,2Mms

Maxtor 300Gb 7200rpm SATA 150 16Mb DiamondMax 10

Gainward PCI-E GT6600GT PowerPack! Ultra/1980PCX TV-DVI 256Mb 2ns DDR3 Fan

Sharkoon Silvation Case - Airflow by included Airtunnel - Alu front panel Screwless - w/o PS

Power Supply 400Watt ATX JJ-400PPGA Silent Intel 2 20/24 pin 8cm SATA - PCI-E Retail + power cable

NEC DVD +R/+-RW ND-3540 Black OEM Dual Layer

DVD LG 8161B 48x16x Tray Blk Black (black or white + black front)

Floppy disk drive Sony 1,44MB Black

A lot of the parts are not available right now so I'll have to wait a while until they are delivered. Finally I will be able to install Windows XP 64 bit, and then probably notice that half my programs no longer work :-s

Any comments on what to buy or not to buy are of course welcome!

Microsoft Update Services

Microsoft has released it's new version of Windows Update.
Not only will it update your Windows but also your Exchange Server, SQL Server, Office, ...

Please check for details as not all versions of the above mentioned products are supported.

Check it out here

BINARY_CHECKSUM AND CHECKSUM

In all the years I've been working with SQL Server I have never seen anyone use these functions.

It's purpose is to build hash indices speeding up queries by matching a 4-byte int instead of a costly character comparison. Especially on large text columns the function can be of use.

For more information check out the BOL.

I'll take the example from BOL (c) Microsoft

-- Create a checksum index.
SET ARITHABORT ON
USE
Northwind
GO
ALTER TABLE Products
ADD cs_Pname AS
checksum(ProductName)
CREATE INDEX Pname_index ON Products (cs_Pname)

/*Use the index in a
SELECT query. Add a second search
condition to
catch stray cases where
checksums match,
but the values are not
identical.*/
SELECT *
FROM Products
WHERE checksum(N'Vegie-spread') =
cs_Pname
AND
ProductName = N'Vegie-spread'

There's also a little brother called CHECKSUM_AGG which returns the checksum of the values in a group. The example in BOL uses it to track changes in data.

SQL Server 2005 System Tables and Views

A nice article about this topic can be found here

Daddy

Since my father is advertising my weblog I feel I should return the favor.
Please visit his website here, although he's from another generation you should really check it out. My parents are quite modern and the website really shows this :-)

Microsoft Anti Spyware

A new beta build has been released
Check out this link for more information

Using Ranking and Windowing Functions in SQL Server 2005

Check the article out here

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

I think I've already posted this link somewhere but I'll post it again as this is a must read!
Go go go

Learning Material for SQL Server 2005

Get it here

MSDN TV - Reporting Services 2005

A nice MSDN TV movie with tips and tricks for Reporting Services in SQL Server 2005:
Download

TempDB guidelines

Wow, I found a really good article on TempDB in SQL Server 2005.
Check it out here and a little update here

SQL Server 2005 Resources

An excellent list of SQL Server 2005 resources can be found here

Assessments for SQL Server 2005

There are some assessments available on SQL Server 2005.
I scored 70% on the SQL Server 2005 for Developers test, not too bad for a first time I hope :-)

In case you wonder, I'm not bored, I'm just testing some processes that take quite some time ;-)

Time for some marketing material :-)

SQL Server outperforms Oracle on SAP (4 CPU platform) here

And a security comparison here

Microsoft Windows Server 2003 Performance Advisor

Check out this cool program from Microsoft!
It's a diagnostics program that collects performance information. Once the program collected the data you can get a nice overview of all the information captured. It is a highly configurable program and not for the faint of heart. It supports trending if you have a SQL Server 2000 database available.

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.