Time Bomb Design - A Longer Fuse

Question

Time Bomb Design - A Longer Fuse

David.Poole

SSC Guru

Points: 76000
More actions
May 25, 2010 at 10:46 pm

#97375

Comments posted to this topic are about the item Time Bomb Design - A Longer Fuse
LinkedIn Profile

Viewing 15 posts - 1 through 15 (of 54 total)

You must be logged in to reply to this topic. Login to reply

sporran Grasshopper Points: 20 More actions · Answer 1

Other activity on my PC meant that I was a victim of cursor lag (the blinking cursor, not the SQL one). As a result, when I clicked on what I thought was 5 stars, I actually gave 1 star. Sorry.

Excellent article, with the part about Cinderella users ringing so true. "Rats nest of Excel spreadsheets" indeed!

gserdijn Hall of Fame Points: 3283 More actions · Answer 2

sporran (5/25/2010)
As a result, when I clicked on what I thought was 5 stars, I actually gave 1 star.

Added one star extra. If 3 other persons will do that...

Good article.

Dutch Anti-RBAR League

AJN SSC Veteran Points: 205 More actions · Answer 3

I clicked on 5 stars...something out there decided that should be 4!

I'll be sharing this with a few of my colleagues - I really hope they take the not-so-subtle hint!!

bbirns Grasshopper Points: 23 More actions · Answer 4

bbirns

Grasshopper

Points: 23

May 26, 2010 at 2:25 am

#1172527

Great article - thank you for writing it!

RichB SSCrazy Eights Points: 9651 More actions · Answer 5

Excellent article, thanks for sharing. I look forward to the next installment!

If such a facility existed I would suggest it be pinned, and enshrined in the hall of fame.

However, if you were to write and publish a version of this aimed at SQL Developers it would make a lot of DBA's lives a lot easier.

As it stands forwarding this to the Devs as a standalone might bite back... 😉

Cheers

Rich

dan.browne Grasshopper Points: 23 More actions · Answer 6

Your "Cinderella Data Users" sub heading made me laugh.. I work for a huge organization and work in such a department and only hope someone higher up the food chain here reads your article and takes note.

M$ Office/SQL Express are the only tools available to me, I have projects with deadlines that need meeting just like the heavy hitting DBAs in the organization - I'm sure that this will lead to problems for them should I ever leave but seriously, the support isn't there for me now so I must do what I must.

Excellent article, Thanks!

Paul White SSC Guru Points: 150467 More actions · Answer 7

David,

I just scanned through on a first read, but something important caught my eye:

In the section on changing data types (after rebuilding the clustered index) you say that for SQL Server 2005: "Fixed length datatype sizes are imutable. Changes are only at the metadata level."

This is not so.

Rebuilding the clustered index does indeed reclaim the space. There is a simplified script at the end of this post to demonstrate. I have also tried your script (with my own ALTER TABLE ALTER COLUMN statements) and received the same results.

These are the statements I used with your script:

ALTER TABLE dbo.FinancialStatsLarge ALTER COLUMN AnnualInterestRate DECIMAL(5,2) NOT NULL;

ALTER TABLE dbo.FinancialStatsLarge ALTER COLUMN MonthlyInterestRate DECIMAL(5,2) NOT NULL;

ALTER TABLE dbo.FinancialStatsLarge ALTER COLUMN AnnualFee DECIMAL(9,2) NOT NULL;

ALTER TABLE dbo.FinancialStatsLarge ALTER COLUMN MonthlyFee DECIMAL(9,2) NOT NULL;

ALTER TABLE dbo.FinancialStatsLarge ALTER COLUMN AnnualCost DECIMAL(9,2) NOT NULL;

ALTER TABLE dbo.FinancialStatsLarge ALTER COLUMN MonthlyCost DECIMAL(9,2) NOT NULL;

GO

ALTER INDEX [PK_FinancialStatsLarge] ON dbo.FinancialStatsLarge REBUILD;

Demo:

CREATE TABLE dbo.Immutable

(

row_id INTEGER NOT NULL

IDENTITY (1,1)

CONSTRAINT [PK dbo.Immutable row_id] PRIMARY KEY,

data DECIMAL(18,2) NOT NULL);

INSERT dbo.Immutable (data) VALUES (1.23);

-- Show table page data

DBCC IND (0, Immutable, 1);

-- Redirect DBCC PAGE output from the error log to the client window

DBCC TRACEON(3604);

-- Page 14830 in file 1 was the data page on my test run

DBCC PAGE (0, 1, 15162, 3);

--====================================

-- DBCC PAGE EXTRACT

--====================================

--

-- Slot 0 Offset 0x60 Length 20

--

-- Slot 0 Column 0 Offset 0x4 Length 4 row_id = 1

-- Slot 0 Column 1 Offset 0x8 Length 9 data = 1.23

--

--====================================

-- Alter the column definition

-- Metadata-only change at this stage

ALTER TABLE dbo.Immutable

ALTER COLUMN data DECIMAL(5,2) NOT NULL;

-- Rebuild the clustered index

ALTER INDEX [PK dbo.Immutable row_id] ON dbo.Immutable REBUILD;

-- The data page allocated will have changed

DBCC IND (0, Immutable, 1);

-- Data now on page 15163 in file 1 on my test run

DBCC PAGE (0, 1, 15163, 3);

--====================================

-- DBCC PAGE EXTRACT

--====================================

--

-- Slot 0 Offset 0x60 Length 16

--

-- Slot 0 Column 0 Offset 0x4 Length 4 row_id = 1

-- Slot 0 Column 1 Offset 0x8 Length 5 data = 1.23

--

-- Notice the record as a whole is 4 bytes shorter

-- and column 1 is *physically* 5 bytes now

--

--====================================

DROP TABLE dbo.Immutable;

DBCC TRACEOFF (3604);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

murph_32952 Valued Member Points: 73 More actions · Answer 8

"Cinderella data users"

Very aptly stated.

This is where I live.

Every day.

Nice article.

jrbirdman Valued Member Points: 64 More actions · Answer 9

jrbirdman

Valued Member

Points: 64

May 26, 2010 at 6:37 am

#1172657

Nicely, nicely done. Six stars.

David.Poole SSC Guru Points: 76000 More actions · Answer 10

Paul White NZ (5/26/2010)
This is not so.
Rebuilding the clustered index does indeed reclaim the space.

What am I doing wrong Paul? I don't seem to get any space back (in SQL2005) other than that I would get by defragging the index.

LinkedIn Profile

sipe_16 SSC Rookie Points: 33 More actions · Answer 11

Excellent article - I'll be sharing this with my colleagues 🙂

One comment/question in the naming conventions area though (not to start a heated debate haha)... You state that using column names like ID, Name and Description are like poison. I'm curious why you've come to feel this is more confusing? I've actually come into the practice of doing exactly that because I'm thinking of the table definition as a type and a row as an object, which will most likely be represented in code at one point or another - in one way or another.

When I perform joins I just be sure to use aliases and ensure that the aliases are easy to understand (e.g. if I have a table "Transaction", "tx" would be an appropriate shortening, so "tx.ID" would become very readable). Though I think this is a bit of a moot point because, if you ask me, you should always use aliases when joining and aliases should always be easy to interpret to maintain good readability. Naturally this has the side-effect of bringing the "ID" column back into context.

When I have foreign key column, I like to name the column "{PrimaryKeyTableName}{PrimaryKeyColumnName}" (so, FK column "TransactionID" points to PK column "Transaction.ID" - and (though I'm still not a huge ORM fan yet...) when using an ORM this typically will end up exactly the same in code. When not using an ORM, I would definitely have a "Transaction" object with an "ID" property. "Transaction.TransactionID" just feels silly.

It just brings the references that the code/db make between entities/tables a little bit more in-sync in my opinion.

Thoughts?

Cheers,

Cyle

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 12

David.Poole (5/26/2010)
Paul White NZ (5/26/2010)
This is not so.
Rebuilding the clustered index does indeed reclaim the space.
What am I doing wrong Paul? I don't seem to get any space back (in SQL2005) other than that I would get by defragging the index.

depends on the size of the table, data and the amount of fragmentation

i've rebuilt indexes manually on a table with 200-300 million rows and i've seen space reclaimed with each index rebuilt

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 13

this is my favorite memory

early 2006 we upgrade to SQL 2005. or early 2007. whenever SP2 came out. we spend a few months testing.

start the upgrade late friday or saturday night. we had a cluster running Win2000/SQL 2000 that we upgraded to SQL 2005. In place upgrade. everything goes OK and no complaints to the helpdesk the rest of the weekend.

Monday morning we get complaints of our main CRM app hanging. no idea why. check the console and we see the CPU spiking to 100% for up to 10 minutes at a time. our VP of IT thinks it's a virus. we open a case with PSS and they are swamped. that whole year you send them logs and don't expect to hear back for a week or so because they were so busy.

after a week of going back and forth with PSS and our own troubleshooting we find that it's a few problem queries causing the CPU spikes. one of them was selecting 20 or so columns with a where clause being up to a few hundred int values with an in(). nothing complicated and worked fine in SQL 2000. the SQL 2005 optimizer didn't like it even though it was an index seek or clustered index seek. forgot which one. Dev had to make changes to the query that night and the quick fix was a temp table.

Summary is that when we asked QA about it we found out that they would take the database that we restored and delete most of the data. a million row table would be trimmed to a hundred at most. just enough data for testing. and they would work from home a few days a week and what would happen they would click a button to test the app and go get coffee in the kitchen. come back a few minutes later and it would be done. no time testing to make sure that the action completed in a reasonable amount of time.

Disney Hammerschmidt SSC-Addicted Points: 454 More actions · Answer 14

Hi.

Great article.

I suggest more references for ISO rules.

Tks a lot. 😎

Disney