SQLServerCentral / Discuss Content Posted by Brian Knight / Article Discussions / Article Discussions by Author / The Basics of Sizing a SQL Server Database / Latest Posts

RE: The Basics of Sizing a SQL Server Database

Wayne West — Thu, 21 Aug 2008 08:43:34 GMT

Heh. I think that 2008 re-eval suggestion was mine. :DI'm hoping he'll be at PASS, and doubly-hoping I get to go! Maybe someone can corner him there about it.

RE: The Basics of Sizing a SQL Server Database

dbaker-620086 — Thu, 21 Aug 2008 08:32:31 GMT

the comments on his post suggests a repeat with SQL2008 (now RTM'ed) would be cool to see if anything has changed.If either of you know the dude perhaps you could encourage this!cheersDick

RE: The Basics of Sizing a SQL Server Database

Wayne West — Thu, 21 Aug 2008 08:09:29 GMT

It's not exhaustively scientific, Jeff, but it is interesting. Shea does a lot of cool tests like this, he recently did one on the number of file groups per database that was rather interesting. He also has one on IO performance and block alignment on SANs.I glommed on to him from his SQL Server Administration With Perl book.

RE: The Basics of Sizing a SQL Server Database

Jeff Moden — Wed, 20 Aug 2008 19:38:39 GMT

[quote][b]Wayne West (8/20/2008)[/b][hr][quote][b]Alin Winters (8/20/2008)[/b][hr]Also, you should consider running a 64 bit installation, so you need x64 capable CPU's, 64 bit OS, and 64 bit SQL.[/quote]Check out [b][url=http://sqlblog.com/blogs/linchi_shea/archive/2007/01/02/32-bit-vs-x64.aspx]this article[/url][/b] by MVP Linchi Shea. This is another one of those areas that may require some testing and benchmarks, going 64/64 may not be the best performance model.[/quote]Heh... I love tests. Thanks for the link, Wayne.

RE: The Basics of Sizing a SQL Server Database

Wayne West — Wed, 20 Aug 2008 10:10:13 GMT

[quote][b]Alin Winters (8/20/2008)[/b][hr]Also, you should consider running a 64 bit installation, so you need x64 capable CPU's, 64 bit OS, and 64 bit SQL.[/quote]Check out [b][url=http://sqlblog.com/blogs/linchi_shea/archive/2007/01/02/32-bit-vs-x64.aspx]this article[/url][/b] by MVP Linchi Shea. This is another one of those areas that may require some testing and benchmarks, going 64/64 may not be the best performance model.

RE: The Basics of Sizing a SQL Server Database

CyclingRabbit — Wed, 20 Aug 2008 09:50:59 GMT

Also, you should consider running a 64 bit installation, so you need x64 capable CPU's, 64 bit OS, and 64 bit SQL.

RE: The Basics of Sizing a SQL Server Database

Jeff Moden — Tue, 19 Aug 2008 19:22:26 GMT

[quote][b]Martin Goebbels (8/19/2008)[/b][hr]Gentlemen, that discussion is very interesting, but I'd like to post another issue:Now I know the the disk space I need, but and the rest? I mean, can someone help to find out about the Hardware, how many processors, how much RAM, etc, etc, etc.?Thanks.[/quote]That's pretty simple... how much do you have left in your budget? One consideration on processors that many forget, though, is how will SAQL Server be licensed? By CAL of by processor? Will it impact your cost? Even at that, I'd add as much memory as the operating system can handle regardless of which edition SQL Server will be... memory = speed.

RE: The Basics of Sizing a SQL Server Database

Wayne West — Tue, 19 Aug 2008 15:05:15 GMT

Tough question, Martin, as there are so many variables. OLAP? OLTP? How many users? How many databases on the server? How many instances on the server? Is the server dedicated for SQL Server or is it also doing other things? Characteristics of what users are doing (more query than update, etc)? Type of processes being hosted? Clustered? The minimum requirements that Microsoft publishes for SQL Server are quite low. The reality is that if you're buying a new server, most minimum server levels are far above the minimum requirements from MS.[b][url=http://msdn.microsoft.com/en-us/library/ms143506(SQL.90).aspx]HERE[/url][/b] are the requirements for 2005. You can (allegedly) run SQL Server 2005 on a P3 with 512 meg of ram, but most people would agree that you'd be daft to try such a thing in a production environment. [b][url=http://msdn.microsoft.com/en-us/library/ms143506.aspx]HERE[/url][/b] are the requirements for 2008.An ideal system for general purpose use, as far as I'm concerned, would have a minimum of two dual-core CPUs, 4-8 gig of ram (or as much as the OS/server will allow), and at least 5 disks in RAID-5. In an ideal environment, you might want a fiber channel SAN with at least five IO paths: one for the system drive, one for data files, one for log files, one for tempdb, one for backups. The rule of thumb is that the more disk spindles that you can spread your system across, the better.(No, I'm not a fan of RAID-5, but if you have a small number of disks, whatcha gonna do?)One thing that I would definitely do if you're making your own server, which I don't know that I would recommend, is to make sure your server is on Microsoft's Hardware Compatibility List (HCL). If you're having a problem and you're working with their support, it could come down to "oh, your RAID controller isn't on the HCL, so we can't take this any further."

RE: The Basics of Sizing a SQL Server Database

Martin Goebbels — Tue, 19 Aug 2008 14:35:23 GMT

Gentlemen, that discussion is very interesting, but I'd like to post another issue:Now I know the the disk space I need, but and the rest? I mean, can someone help to find out about the Hardware, how many processors, how much RAM, etc, etc, etc.?Thanks.

RE: The Basics of Sizing a SQL Server Database

Jeff Moden — Mon, 04 Aug 2008 18:04:47 GMT

[quote][b]dbaker (8/4/2008)[/b][hr]simply that either we should either encourage usage of undocumented features [with due warnings] or not [like pregnancy, there's no middle ground][/quote]Heh... it's not the undocumented features we should warn about... it's the documented features that sometimes go away that really wreak havoc. :)

RE: The Basics of Sizing a SQL Server Database

RBarryYoung — Mon, 04 Aug 2008 09:27:54 GMT

Thanks for the feed back, JJ.

RE: The Basics of Sizing a SQL Server Database

JJ B — Mon, 04 Aug 2008 09:08:12 GMT

RBarryYoung: Thanks so much for your information. I had no idea that there was such a discipline around this issue. Very interesting stuff and good points. Thanks. - JJ

RE: The Basics of Sizing a SQL Server Database

dbaker-620086 — Mon, 04 Aug 2008 08:49:37 GMT

simply that either we should either encourage usage of undocumented features [with due warnings] or not [like pregnancy, there's no middle ground]but no matter, I wasn't popping at you ;) and I liked your well-written article:)- you're right, Capacity Planning is ill-understood, and I don't think MS SCOM helps muchDickPS BTW you're not the only DEC-cie [I was Networks and LCG]. a sad waste of opportunity when it went down, but you can't keep the good-guys down for long

RE: The Basics of Sizing a SQL Server Database

Wayne West — Mon, 04 Aug 2008 08:38:51 GMT

[quote][b]dbaker (8/4/2008)[/b][hr]- better solution (for SQL200x) might have been ... PS if you get Smileys in the code replace by closing parenthesis - preview mashes space)space![/quote]Using the Code IFCode shortcuts takes care of the smileys. :)

RE: The Basics of Sizing a SQL Server Database

RBarryYoung — Mon, 04 Aug 2008 08:10:51 GMT

[quote][b]dbaker (8/4/2008)[/b][hr]- criticism smacks of double standards methinks[/quote]Please, explain?

RE: The Basics of Sizing a SQL Server Database

dbaker-620086 — Mon, 04 Aug 2008 07:09:35 GMT

true, is undocumented in distributed BOL content ["offline"]however the docviewer [b]online[/b] search documents it 2001/5/31 by a certain Brian Knightwhat a pity his simplistic article on db sizing [2001/7/25] failed to employ it- criticism smacks of double standards methinksmy defence [for using it despite its inevitable internal evil cursor] isconveniencebecause its there [well for last few versions anyway]widely used by other DBAs and MS practitioners (for similar reasons I guess so has lotsa fans)but you are quite right to point out to newbies that it might go away on some future SQL version!Dick

RE: The Basics of Sizing a SQL Server Database

RBarryYoung — Mon, 04 Aug 2008 06:42:48 GMT

[quote][b]dbaker (8/4/2008)[/b][hr]firstly the usp_databases.txt in author's "Resources:" is ugly with cursors and dynamic-SQL- better solution (for SQL200x) might have been ... exec sp_MSforeachdb 'use[?]; select DATABASE_NAME=db_name() , datsize=sum(case when (status & 0x40)=0 then [size]/128. else 0. end) , logsize=sum(case when (status & 0x40)=0 then 0. else [size]/128. end) from sysfiles ' select *,RUN_DT=getdate() from #databases...[/quote]FYI: "sp_MSforeachdb" is an ugly cursor also, and undocumented at that.

RE: The Basics of Sizing a SQL Server Database

dbaker-620086 — Mon, 04 Aug 2008 04:17:14 GMT

recycling a 2001 article for SQL2000 is unlikely to be optimal for 2008 (SQL2005 going SQL2008)- come on guys, come out of your cave there's been interim life these 7 years you know !firstly the usp_databases.txt in author's "Resources:" is ugly with cursors and dynamic-SQL- better solution (for SQL200x) might have been if object_id('tempdb..#databases') is not NULL drop table #databases create table #databases ( DATABASE_NAME sysname NOT NULL primary key clustered , datsize float NOT NULL , logsize float NOT NULL ) insert into #databases exec sp_MSforeachdb 'use[?]; select DATABASE_NAME=db_name() , datsize=sum(case when (status & 0x40)=0 then [size]/128. else 0. end) , logsize=sum(case when (status & 0x40)=0 then 0. else [size]/128. end) from sysfiles ' select *,RUN_DT=getdate() from #databasesbut here's another for SQL2005/SQL2008 -- show sizes in MB (not 8192 byte pages) and keep as float for accurate trending later select DATABASE_NAME=isnull(D.DATABASE_NAME, L.DATABASE_NAME) , datsize, logsize, ftssize, RUN_DT=getdate() from ( select DATABASE_NAME=db_name(database_id), sum([size])/128. as datsize from sys.master_files where [type]=0 -- ROWS group by database_id ) D full outer join ( select DATABASE_NAME=db_name(database_id), sum([size])/128. as logsize from sys.master_files where [type]=1 -- LOG and has_dbaccess(db_name(database_id)) = 1 -- only accessible databases group by database_id ) L on L.DATABASE_NAME=D.DATABASE_NAME left join ( select DATABASE_NAME=db_name(database_id), sum([size])/128. as ftssize from sys.master_files where [type]=4 -- FULLTEXT and has_dbaccess(db_name(database_id)) = 1 -- only accessible databases group by database_id ) T on T.DATABASE_NAME=D.DATABASE_NAME order by DATABASE_NAMEHTHDickPS if you get Smileys in the code replace by closing parenthesis - preview mashes space)space!

RE: The Basics of Sizing a SQL Server Database

Atif-ullah Sheikh — Sun, 03 Aug 2008 21:57:12 GMT

I wonder why the article is dated 2001/07/25. Is it that old...? Any ways, somewhat information for me (at least) about Datatect 1.6. Will use it to see what it can do for me...Atif Sheikh

RE: The Basics of Sizing a SQL Server Database

RBarryYoung — Sun, 03 Aug 2008 17:45:11 GMT

For many years I worked as a Capacity Planning Consultant, first for Digital, then later on my own. Capacity planning is a lost art now, but was pretty big then. I was fortunate both to be a delivery consultant but also to have the opportunity to participate in the development of both very advanced modeling tools based on QNA (Queuing Network Analysis), and in the creation and refinement of a very sophisticated methodology for structuring and delivering them.I really should say "methodologies" though because Capacity Planning was really just one of a group of different types of studies based on systems performance and sizing along with a family of related methodologies to consistently deliver them with reliable results. The difference between these studies started with the question "What question will this study answer?" The answer to that question would in turn determine the type of study needed.For instance a Capacity Plan answered the question "What configuration changes/expansions will we have to make in the (X) years and when will we have to make them in order achieve and/or maintain specific performance levels across the critical services on this system/server?" That's a mouthful and it reflects the fact that a Capacity Plan one of the highest level services in the discipline. "New System Sizing" on the other hand answered the question "What configuration for a new dedicated system/server do we need to host this new application/service at a specific performance level?"A example of a tamer service might be "Annual Growth Sizing" which would answer the question "What configuration will I need a year from today to maintain the same performance level?" And at the lowest level would be "Storage Sizing" which simply answers the question "How much disk space will we need X month from now." In fact Storage Sizing is so straight-forward that virtually no one pays someone else to do it, but rather it is typically incorporated into the planning and budgeting process. However, it too can share the same core steps that all of the "Present/Future" methodologies in this family share in the abstract: 0. Qualify: What type of question/study is this? 1. Identification: Formally state the problem or question and goals and objectives. 2. Baseline: data collection to quantify the current state of resources and their usage. 3. Forecasting: determine how usage of, or demand for the resources will change in the future. 4. Solutions: Project how forecasted changes will affect the resources and determine what solutions can be implemented. 5. Application: Present and apply the results.Exactly how steps 2 through 4 are accomplished is determined by the type of study and in turn determines the quality of the study. Even with the simplest studies, there is usually more than one way to do a step.For instance with Storage Sizing, step 3, Forecasting future needs can be done either through trending or through business planning. Trending is the process of simply looking at historical growth trends and applying that same growth to the future. Business planning, on the other hand, is the process of taking the results of the organizations planning process, including predicted staffing, new projects and initiatives and expected roll-outs, as well as the impact of specific changes planned, and feeding that into the forecasting prediction. Trending is easier and more straight-forward, but is much more subject to "suprises". Business planning is more difficult and time-consuming and many DBA's and server managers are unfamiliar with it, however it is much more accurate and powerful, especially organizationally and career-wise. First because it takes information from and returns information to executives in terms that they both understand and accept. And secondly because it specifically leverages the organization's own planning process back on itself. Because of this, it is much easier to justify new acquisitions and purchases because you can relate it directly to prior business plans and their financial justifications. Likewise, almost all failures of your planning process can be traced back to failures in the inputs provided to you by the other managers and executives. Garbage-in, garbage-out.

RE: The Basics of Sizing a SQL Server Database

JJ B — Fri, 01 Aug 2008 12:46:24 GMT

I appreciated the article and it is new to me. Why did I like it? Because so far, my databases are so small relatively that stressing over database size is not an issue. However, if I should need to work on a database with large growth potential in the future, I will have to consider this issue. I have been using ERwin for years, but I didn't know about the size estimate capability. This article was at the perfect level for what I need right now: Just to know about the capability in the product I use and a pointer to another product that actually works. I can run with that information in the future if I need it. I didn't need an in-depth article. In fact, an in-depth article at this point would have been counter productive for my situation.Is there a place on this site for high-level articles? I would argue "yes". (On the other hand, if I had been looking for more in-depth info right now, I am sure I would have found this article disappointing too. So, I can understand other people's feelings. I don't know if there is a good answer to this issue or not. You can't meet everyone's needs all the time. All you can do is define standards for what is acceptable for this site.)

RE: The Basics of Sizing a SQL Server Database

steve smith-401573 — Fri, 01 Aug 2008 12:17:38 GMT

[quote][b]Steve Jones - Editor (8/1/2008)[/b][hr]We re-run older, popular articles at times. With nearly 200,000 people joining the site in the last year, it's not "old" to them, it's likely the first time they saw it.[/quote]having read many posts from those who commented with 'disappointment' or harsher responses, my take is that this is a very good 'lite' article to help newbies get their feet wet. No newbies seemed to comment, which is consistent with newbie behavior - after you read all the 'old-timer' comments, it can get intimidating, unless you have a pressing need or specific question. I'm disappointed that the link at the end seems to point to Brian's personal bookmarks, rather than to the actual stored procedure source.To Steve Jones: If this is a 'rerun' of an old article, was it updated or simply reprinted? Some of the content could have been revised since then, OR perhaps the links for the subsequent articles for those of us who can't wait for the sequel articles?I was not aware of some of these features being available in ERwin in 2001 - again, the dating is confusing if the article has been revised and brought current.

RE: The Basics of Sizing a SQL Server Database

Steve Jones - SSC Editor — Fri, 01 Aug 2008 08:55:48 GMT

We re-run older, popular articles at times. With nearly 200,000 people joining the site in the last year, it's not "old" to them, it's likely the first time they saw it.

RE: The Basics of Sizing a SQL Server Database

provodnik — Fri, 01 Aug 2008 07:23:12 GMT

Like many others have already mentioned: nice try, no substance, 7 years old. When you publish an article, please check your grammar. A few mistakes here and there for non-native speakers are OK. For a native speaker who teaches others you ought to do better.Keep trying. Thanks for the effort you put into it 7 years ago.

RE: The Basics of Sizing a SQL Server Database

Logicalman — Fri, 01 Aug 2008 05:27:21 GMT

This was a year old??? SEVEN years more like !!!!!! It arrived in my inbox 8/1/08 !!!Nice article, informative - if you wish to purchase third-party software (though a good point made concerning how easy it is to miscalculate the true size of databases by hand).I would ask though: Where was the monitoring after deployment part of the article?The opening paragraph appeared to promise a little more than the content delivered, but still a very interesting read, though there was no attached SProc to download/view.

RE: The Basics of Sizing a SQL Server Database

Anipaul — Fri, 01 Aug 2008 04:37:23 GMT

Sorry I could not learn much from this article. If atleast he could discuss some features of the products he mentioned in the article elaborately, then it would have been fine.

RE: The Basics of Sizing a SQL Server Database

SuperDBA-207096 — Fri, 01 Aug 2008 04:06:55 GMT

I couldn't find the sproc either...Article was high-level, and give a real-world view (easier to use the tools than try and tackle it manually). Thought it was good- maybe could have a bit more meat about sizing db's or some general advice (try to make them big enough to start so they dont expand when you're not expecting it etc.)

RE: The Basics of Sizing a SQL Server Database

ChiragNS — Fri, 01 Aug 2008 03:31:46 GMT

i could'nt download the storedproc.

RE: The Basics of Sizing a SQL Server Database

dbrodine — Thu, 22 May 2008 09:48:48 GMT

You might be interested in "Mathematical Server Sizing" software, freely available on SourceForge. Here's a link.http://sourceforge.net/search/?type_of_search=soft&type_of_search=soft&words=mathematical+server+sizingBe sure and check out the documentation with this project. It describes some sample sizings. For a formal description of the math involved, see IEEE "Computer", July 2006 issue.Good luck!

RE: The Basics of Sizing a SQL Server Database

CyclingRabbit — Sun, 08 Jul 2007 11:00:00 GMT

I have a question regarding the script provided athttp://www.sqlservercentral.com/columnists/bknight/usp_databases.txtHow is this different from running the sp_helpdb procedure which, among other things, returns the sizes of all the databases?Thanks.Alin

RE: The Basics of Sizing a SQL Server Database

d-458788 — Thu, 05 Jul 2007 12:13:00 GMT

...and why did this thing show up in my inbox today as if it was a fresh article??? You mean to tell me that given a full year, they had to recycle the SAME non-article???

RE: The Basics of Sizing a SQL Server Database

d-458788 — Thu, 05 Jul 2007 12:11:00 GMT

errr...ah...Where's the Beef?

Reads like someone spent too much time playing video games and hurridly tossed the article together to meet a deadline...

RE: The Basics of Sizing a SQL Server Database

Jeff Moden — Sun, 01 Jul 2007 23:36:00 GMT

Sorry for being a year late.

I thought the title of this article was "Sizing a Database"... not an advertisement for some 3rd party software.

RE: The Basics of Sizing a SQL Server Database

Brian Munier — Fri, 07 Jul 2006 12:13:00 GMT

Well, If you say so, it must be recent development however. In Dec 2004 they were still on Version 3 which was the same as it had been 2 or 3 years before.

I met another DBA who worked for a company that got bought out by CA and after 3 or 4 years, they hadn't done any bug fixes.

I hope they do support it and improve it.

RE: The Basics of Sizing a SQL Server Database

DavidSimpson — Fri, 30 Jun 2006 09:02:00 GMT

Not promoting one tool over another but CA has done quite a bit for Erwin. They even had a big focus on bug improvments to the point of holding off any improvements... I believe it was around release 4.14. This made a big difference on bugs with RE and complete compares. I'm not on the current release so I cannot make comments on it now.

As for the article, good article for a starting point on sizing issues with a database... as the title indicates. I think the tools mentioned give some basic direction on where to go for further 3rd party help. Listing out additional resources would have helped keep it from sounding a bit like an advertisement but it makes sense that you would focus on the ones you are familiar with.

Any plans for more indepth articles on the subject?

RE: The Basics of Sizing a SQL Server Database

Brian Munier — Fri, 30 Jun 2006 08:27:00 GMT

Not much content.

ERwin WAS a great product, BUT Computer Associates (CA) as is their business model, has not released even bug fixes since they acquired the product. They have done new databases, but that's it.

If you have it, use it. If not buy something else.

If you want to buy the rights from them and revitalize development of the tool, lots of people would love it.

RE: The Basics of Sizing a SQL Server Database

Georges Belhadjali — Fri, 30 Jun 2006 06:46:00 GMT

Agree too, sounds interesting regarding Erwin, the same feature exist in Power AMC (Pub!), nevertheless I would have appreciate more info about how to size the transaction log ...

RE: The Basics of Sizing a SQL Server Database

Jeff Moden — Fri, 30 Jun 2006 05:59:00 GMT

I agree... a decent article... but it DOES sound like an advertisement and provides no real info as to how to do the sizing estimate if you don't have those tools.

RE: The Basics of Sizing a SQL Server Database

Mike Metcalf — Fri, 30 Jun 2006 05:52:00 GMT

I thought that it was a decent article, and one very close to my heart as our DB is currently 1.6TB and growing by 15-30 GB each and every week.

RE: The Basics of Sizing a SQL Server Database

EugeneZ-162636 — Fri, 30 Jun 2006 04:42:00 GMT

Nice try Brian,

But there is not much new ideas and info except advertisement of the Datatect 1.6 (BTW - good tool)

For example - Ok you calculated with Erwin or without a size of database->

Can you tell now how big HDD to buy? How big trans log files can be after reindex? Anything about recovery model in view of the Trans log file? and maintenances? what about statistics (size consuming too)...

Best regards,

EugeneZ