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?.

Defragging Indexes for Beginners
Indexes over time become fragmented and as a result don’t work quite as well as they should.  This short talk will show you how an index can become fragmented and will also show you the steps to fix index fragmentations. 

Effective Indexes for Beginners
From SQL Server 2005 onwards, we have been able to use a DMV to identify which of our indexes are effective and which of our indexes can be disabled.  This short talk will show you how and why you should be checking the effectiveness of your indexes. 

Presented by Amanda Jackson
Consultant/Developer with Fronde Systems Ltd with almost 20 years experience in the IT industry. Amanda is currently focusing on SQL development and BI and is working towards Microsoft BI development and maintenance certification. In the past she has covered most roles in IT including: Developer, Network Admin, Change Control Manager, Source Control & Build Manager, QA, Games Development, Novell DBA.
Amanda is also the founder of Girl Geek Dinners in New Zealand

Register here

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