Kev's Life

I come accross a great artical by David Poole on SQLServercentral about Time Bomb Coding

Well worth the read

At some point in your career you will be asked to get involved in diagnosing performance problems with a database application. Sometimes the diagnosis is simple but in other cases the causes of poor performance are subtle. From my own experience I have noticed that subtle performance bugbears conform to a pattern and I refer to this pattern as “Time Bomb Coding”. The pattern I am referring to has the following properties

• The system is well established
• There has been no significant alterations to the system for some considerable time
• Statistics and indexes are kept up-to-date
• Execution plans appear reasonable at first glance
• The database design appears to be simple and clean
• Traffic has not changed much over time

Despite this the system performance has been steadily degrading!

If this seems familiar to you then you are probably a victim of time bomb coding. In this article I should like to give a few of the many examples of this phenomenon I have come across.

http://www.sqlservercentral.com/articles/Performance+Tuning/69337/

Great blog entry about using Surrogate keys in a datawarehouse.

Why I prefer surrogate keys instead of natural keys in database design

Simply put:

I prefer using surrogate keys because natural keys are by default a subject to change which is a bad behavior for a row identifier.

But let’s dig a bit deeper into each key type to see why this is. Here’s a little table with column names that tell us what kind of a key each column is.

Surrogate keys

A surrogate key is a row identifier that has no connection to the data attributes in the row but simply makes the whole row unique. And that property is also the downside of it. Because it has no connection to the data attributes we can have two rows with the exact same data in all columns except the key column. This is usually handled at the application side and is an acceptable downside.

An example of a surrogate key is an integer identity or a GIUD unique identifier. I’ve never seen another data type being used as a surrogate key successfully. Both have their pros and cons though.

via Why I prefer surrogate keys instead of natural keys in database design.

Did your backup program/utility leave your SQL Server running in an squirrely scenario?

My colleges asked me if ’squirrely’ is a technical term and for this post the answer is yes. CSS is not going to deny support to customers but SQL Server was not tested in this scenario so you may have chased yourself up a tree, hence I use the term squirrely.

SQL Server 2005 introduced snapshot databases and modified DBCC to create secondary snapshot streams for online DBCC operations. The online DBCC creates a secondary stream of the database files that is SPARSE. CSS has found that if a 3rd party backups and utilities or NT Backup is used against the database files the SPARSE setting may get incorrectly, propagated to the parent stream. In the case of DBCC this is the original database files(s).

Repro

via CSS SQL Server Engineers : Did your backup program/utility leave your SQL Server running in an squirrely scenario?.

I have just read a very good Blog on Cloud computing by Dave Pinalkumar MVP SQL Server

I would highly recommend checking his blog out

Blog

I found this blog about using MS SQL Profile to help locate issues in large complex stored proc’s.

Well with a read and bookmark

We’ve all had them. One of those stored procedures that is huge and contains complex business logic which may or may not be executed. These procedures make it an absolute nightmare when it comes to debugging problems because they’re so complex and have so many logic offshoots that it’s very easy to get lost when you’re trying to determine the path that the procedure code took when it ran. Fortunately Profiler lets you define custom events that you can raise in your code and capture in a trace so you get a better window into the sub events occurring in your code. I found it very useful to use custom events and a database snapshot to debug some code recently and we’ll explore both in this article. I find raising these events and running Profiler to be very useful for testing my stored procedures on my own as well as when my code is going through official testing and user acceptance. It’s a simple approach and a great way to catch any performance problems or logic errors.

SQL Team Mike Femenella