Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Time Bomb Design - A Longer Fuse Expand / Collapse
Author
Message
Posted Wednesday, May 26, 2010 6:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:45 PM
Points: 2,892, Visits: 1,784
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
Post #928202
Posted Wednesday, May 26, 2010 7:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 20, 2010 8:23 AM
Points: 3, 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
Post #928220
Posted Wednesday, May 26, 2010 7:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:34 PM
Points: 1,414, Visits: 4,539
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #928235
Posted Wednesday, May 26, 2010 7:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:34 PM
Points: 1,414, Visits: 4,539
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.


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #928236
Posted Wednesday, May 26, 2010 7:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 4, 2013 1:27 PM
Points: 10, Visits: 117
Hi.

Great article.

I suggest more references for ISO rules.

Tks a lot.

Disney
Post #928256
Posted Wednesday, May 26, 2010 7:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 13,312, Visits: 10,178
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #928272
Posted Wednesday, May 26, 2010 8:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:00 AM
Points: 1,595, Visits: 4,585
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.

Post #928299
Posted Wednesday, May 26, 2010 8:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:34 PM
Points: 1,414, Visits: 4,539
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #928312
Posted Wednesday, May 26, 2010 9:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #928353
Posted Wednesday, May 26, 2010 9:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:00 AM
Points: 1,595, Visits: 4,585
See if executing a CHECKPOINT updates your allocation stats.
Post #928373
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse