Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Time Bomb Design - A Longer Fuse


Time Bomb Design - A Longer Fuse

Author
Message
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3681 Visits: 3116
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

Newbie on www.simple-talk.com
sipe_16
sipe_16
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 15
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
alen teplitsky
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1595 Visits: 4621
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
alen teplitsky
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1595 Visits: 4621
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
Disney Hammerschmidt
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 185
Hi.

Great article.

I suggest more references for ISO rules.

Tks a lot. Cool

Disney
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16453 Visits: 13204
Nice (lengthy) article with great examples!



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4612 Visits: 9545
Sometimes people make poor data type choices or implement redundant columns due to misguided notions about best practices. For example, I once was hired as a contractor to delevepe reports for a data warehousing project. The first thing that really jumped out at me was that every integer column in the fact tables was defined as BigInt.

[sex] bigint not null,
[marital_status] bigint not null,
[center_id] bigint not null,
...
about 30 other bigint indicators or foreign key columns
...

Rather than 8 byte Big integers, for most of these columns, a single byte TinyInt or 2 byte SmallInt would have been more than enough. When I asked the DBA / data modeler why he had chosen BigInt for all these columns, his reply was that they were using the 64bit edition of SQL Server Enterprise and therefore 64 bit integers would perform better. He could offer no performance test results or even links to a single white paper to back that assertion. However, he did have bragging rights for owning the largest data warehouse in the agency's history.

Another example is creating a clustered index on something like LastName, FirstName, PhoneNumber. This not only leads to page splits, fragmentation, and bloated indexes, but also excessive I/O and blocking.

Other things I've seen are GUIDs used as unqiue surrogate key when a 4 byte integer would have been more appropriate or even a GUID column, which seems to serve no purpose, in addition to an integer surrogate key.

Perhaps my biggest data modeling pet peve is when date/time values are contained in a VarChar column. Not only does this consume 20+ bytes, compared to only 4 bytes for SmallDateTime, but often times the values are sporatically miscoded with invalid dates or multiple applications code the dates in multiple formats, resulting in data type mismatch errors and convoluted case expressions in the SQL statement to decode the data. The reason typically provided for this is that different applications need to display the date in different formats. Of course, this can be better achieved by storing dates as Date in the database using a formatting mask on the application form controls.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
alen teplitsky
alen teplitsky
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1595 Visits: 4621
sometime last year in one of the linkedin groups i'm there was a huge religious battle between GUID PK supporters and the rest of us. after a while people got tired of it because the GUID side liked GUID's just because

MS is guilty of some of this stuff as well. this year we set up perfmon to run and collect data from numerous servers and log to a database. the schema is horrendous and i had to do a lot of casting and converting to be able to read the data
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
David.Poole (5/26/2010)
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.

What are you using to measure the space you get back, David?
Do you see any space being reclaimed? Does the large table shrink to the size of the small table?

If you can give me enough detail so I can replicate what you are seeing, I'll happily investigate! :-)

When you say 'defragging the index', do you mean you are running ALTER INDEX REORGANIZE / DBCC INDEXDEFRAG?
The article only mentioned a full rebuild of the index, so I concentrated on that (the 'defrag' routines work a bit differently).

Anyhow, if you have a few minutes to confirm the details, that'll help a lot.

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4612 Visits: 9545
See if executing a CHECKPOINT updates your allocation stats.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search