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 «««678910»»»

Disk Is Cheap! ORLY? Expand / Collapse
Author
Message
Posted Friday, April 29, 2011 9:14 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:23 AM
Points: 368, Visits: 1,950
Todd M. Owens (4/27/2011)
...if a table only allows inserts in a version type approach, there is no reason to add columns that capture UpdateDate and/or UpdateUserID. If a table's use case(s) calls for a row to be updated, only then are the Update fields necessary.

And please think twice before setting the CreateDate and the UpdateDate to the same exact value on insert...If 90% or more of the columns are never updated, then there will be an extraneous 8 bytes on each row from the UpdateDate...


Hey there. I definitely agree with both of these and practice them myself.

There might be a circumstance, however, to have both CreateDate and UpdateDate be set upon initial INSERT (and hence UpdateDate will be the same value as CreateDate if the row is never updated). I have seen cases where an ETL process looks at the UpdateDate field to know if it should grab that row and will have the UpdateDate field indexed for that purpose. In this case UpdateDate serves dual-duty by indicating both new and updated rows without having to scan both CreateDate and UpdateDate fields. And to only have a single field (assuming someone might suggest not having a CreateDate field in this case), it is still helpful to see the CreateDate since that information will be gone once the row is updated if you only have the single UpdateDate field. But outside of this specific case I tend to agree that the UpdateDate field should be NULL.

Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #1101133
Posted Wednesday, May 11, 2011 4:45 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:23 AM
Points: 368, Visits: 1,950
Todd M. Owens (4/27/2011)
And please think twice before setting the CreateDate and the UpdateDate to the same exact value on insert: this a clear violation of the limited redundancy principle. If 90% or more of the columns are never updated, then there will be an extraneous 8 bytes on each row from the UpdateDate; multiply that by the number of rows where it should be null and in every table where it is misused, then the numbers get quite large quite quickly.


Hey Todd. I did not notice until just a moment ago when I was working on something else that this statement is not entirely true. While I still do agree that using NULL for UpdateDate when a record has not yet been updated is a better practice, that is merely just a logical consideration (outside of the potential indexing issue I mentioned before). But a fixed-width column (numeric types, date/time types, and char/nchar/binary types) will always take up the same amount of space regardless if it is set to NULL or a value. Meaning, if UpdateDate is a DATETIME field, it will always take up 8 bytes even if it is NULL. And to make sure I did just test this out.

Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #1107402
Posted Wednesday, May 11, 2011 7:52 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:47 AM
Points: 54, Visits: 428
But a fixed-width column (numeric types, date/time types, and char/nchar/binary types) will always take up the same amount of space regardless if it is set to NULL or a value. Meaning, if UpdateDate is a DATETIME field, it will always take up 8 bytes even if it is NULL. And to make sure I did just test this out.


Hmm I wonder if that is true for Oracle, DB2, Teradata, et.al....
Post #1107433
Posted Friday, October 5, 2012 7:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 35,349, Visits: 31,889
Solomon,

I've recently had the need to refer to this article again and I thank you from the bottom of my heart. This "right sizing on steroids" article should be required reading for anyone and everyone who has or will ever write the words CREATE TABLE. My hat is off to you, good Sir. Well done!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1369358
Posted Monday, November 5, 2012 7:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:23 AM
Points: 368, Visits: 1,950
Jeff Moden (10/5/2012)
Solomon,

I've recently had the need to refer to this article again and I thank you from the bottom of my heart. This "right sizing on steroids" article should be required reading for anyone and everyone who has or will ever write the words CREATE TABLE. My hat is off to you, good Sir. Well done!


Hi Jeff and thank you very much for such positive feedback. I apologize for the delayed response but new baby came along less than 2 weeks before your comment and things have been, well, C-R-A-Z-Y!! Also, I had thought of a few minor additions to make and wanted to get them in and published before replying.

I have added:

  • A reference to decreased Page Life Expectancy in the paragraph about memory usage

  • Info about several other datatypes at the end of the main recommendations list, things like: SMALLMONEY, REAL, TIME, etc.

  • Info about some newer features that can help save space in addition to (not in place of) a good model. Namely: SPARSE columns and Row / Page compression



Hopefully the updates make for an even stronger article .

Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #1381085
Posted Friday, October 25, 2013 5:59 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 7, Visits: 36
Excellent article. I think the network bandwidth issue is really important and often overlooked. It is mentioned in the article, but I'd add that a WAN circuit of even just moderate bandwidth can cost as much per month to operate as some disk drives cost to buy. And can easily cost significantly more. So if you are syncing to a DR site, getting the backup image over there, and as mentioned, log shipping or replication, can take a big byte out of it. Bandwidth is also not something you can quickly address if you run out of it! Disks are usually a two week turnaround to buy more and rack them up. WAN circuits are running 120 days or more!


Post #1508422
Posted Friday, October 25, 2013 7:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 1,706, Visits: 4,847
Excellent article. It's funny (in a bad way) that many people say "disk is cheap" until the day that tempdb or transaction log starts filling up and you actually put in a request for more disk storage. In a corporate enterprise envrionment, it's not as if the sysadmin can just drop what they're doing, run down to the local electronics store, pull a $200 drive off the shelf, and pop it in the server. You can blow through thousands of dollars worth of billable time and broken service level agreements just discussing the issue and waiting for it to happen.
Post #1508461
Posted Friday, October 25, 2013 10:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 9, 2014 4:51 PM
Points: 2, Visits: 134
Great article and information (glad it was reposted on SQLServerCentral today)!

In regard to item #10 - while not denormalizing tables definitely applies to transaction systems (OLTP) - so order entry and other application transaction systems retain their integrity and update efficiencies - the presentation tables in a data warehouse (OLAP) are usually denormalized (as a star schema) to improve performance (and to a lesser extent understandability for the report creation users). This storage of redundant data in data warehouses is deemed worthy to reduce report query response time by limiting the depth of the table joins.

A properly designed star schema for the data warehouse presentation fact and dimension tables makes sure the large fact tables are using foreign keys (and the corresponding primary key in the dimension table) of appropriate size for both space saving and performance reasons and also espected growth as you specified in your article for transaction tables. Also numeric and string fields in both the fact (very important because fact tables get very large) and dimension tables would benefit from the space saving techniques you specified.

Thanks again,
Chris Reeve
Post #1508519
Posted Friday, October 25, 2013 10:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:32 AM
Points: 2,398, Visits: 1,479
Eric M Russell (10/25/2013)
Excellent article. It's funny ... You can blow through thousands of dollars worth of billable time and broken service level agreements just discussing the issue and waiting ...


We had an issue like this recently but it was a cert not disk. The time spent was more then the cert. The bloated cost of buying some things is amazing.

Appreciate your work here!

M.


Not all gray hairs are Dinosaurs!
Post #1508527
Posted Friday, October 25, 2013 11:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 1,706, Visits: 4,847
Miles Neale (10/25/2013)
Eric M Russell (10/25/2013)
Excellent article. It's funny ... You can blow through thousands of dollars worth of billable time and broken service level agreements just discussing the issue and waiting ...


We had an issue like this recently but it was a cert not disk. The time spent was more then the cert. The bloated cost of buying some things is amazing.

Appreciate your work here!

M.

What did you mean by a "cert"; some new hardware or software was install, but you had to wait for it to be certified?
Post #1508539
« Prev Topic | Next Topic »

Add to briefcase «««678910»»»

Permissions Expand / Collapse