Advices for best practices for configuring MS SQL Server system databases?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Advices for best practices for configuring MS...

Advices for best practices for configuring MS SQL Server system databases?

Rate Topic

Display Mode

Topic Options

Author

Message

midejan

Posted Friday, February 01, 2013 8:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:37 AM
Points: 4, Visits: 107

Hi,

I need some advices and best practices regarding the size of MS SQL Server system databases. We use MS SQL Server for few services for our product and we provide to the clients sizing for production databases. Now we have a request to provide sizing for MS SQL system databases. How to acomplish that, on what facts? Some recommendations are that master,msdb,model and resource could be configured by default but the size of tempdb is affecting the performance system I should be consider properly.

Is there any documentation regarding this topic or some useful links?

Thanks. Best Regards.

Post #1414721

MyDoggieJessie

Posted Friday, February 01, 2013 10:19 AM

SSCrazy

Group: General Forum Members
Last Login: Saturday, June 15, 2013 11:44 PM
Points: 2,055, Visits: 3,786

These links should help:

General:
http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
http://facility9.com/2009/10/an-introduction-to-sql-server-system-databases/

TempDB:
http://www.idera.com/Downloads/WhitePapers/WP_Demystifying%20tempdb.pdf

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" Wink

Post #1414754

GilaMonster

Posted Friday, February 01, 2013 10:21 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 38,062, Visits: 30,358

Master, model, msdb and resource, leave them alone, unless you have a really good reason to change them (and even then, just model and maybe msdb).

TempDB:
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx
http://support.microsoft.com/kb/917047
http://technet.microsoft.com/library/Cc966545
http://www.sqlskills.com/blogs/paul/category/tempdb/

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1414755

ScottPletcher

Posted Friday, February 01, 2013 10:56 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 1,333, Visits: 1,803

Change all filegrowth to fixed amounts rather than %s. That is, change anything like "10%" to a fixed amount instead, such as "1MB".

If you plan to store packages in SQL, increase the size of msdb accordingly (when you store packages in SQL, they are stored in the msdb database). Pre-allocate enough space all at once to hold what you reasonably expect to need, then increase the filegrowth to a reasonable amount.

Adjust the model db size to what is best for your specific server. Remember, all databases by default use the model db sizes and growths unless you override them.

Also, btw, turn on IFI unless for security reasons you can't. That will dramatically speed up data file growth on all databases, including system ones.

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1414764

Ed Wagner

Posted Friday, February 01, 2013 1:08 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 2,004, Visits: 720

Also, btw, turn on IFI unless for security reasons you can't. That will dramatically speed up data file growth on all databases, including system ones.

What is IFI?

Post #1414830

ScottPletcher

Posted Friday, February 01, 2013 1:20 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 1,333, Visits: 1,803

Ed Wagner (2/1/2013)

Also, btw, turn on IFI unless for security reasons you can't. That will dramatically speed up data file growth on all databases, including system ones.

What is IFI?

Try Googling: "sql server ifi" Smile

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1414835

Ed Wagner

Posted Friday, February 01, 2013 1:22 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 2,004, Visits: 720

Thanks.

Post #1414836

midejan

Posted Monday, February 04, 2013 1:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:37 AM
Points: 4, Visits: 107

Thanks for advices. We initially leaved all the system databases on default but after awhile few problems began to arise. Eventually we increased all the sys databases (enough space on storage), especially tempdb for performance optimization.
Few jobs made some problems so we increased the size of the msdb.
Basically the disk space is not the problem, but just some reasonable explanation for increasing the system databases would be good (tempdb is ok, but the rest we can't explain to the customer).

Post #1415146

Markus

Posted Tuesday, February 05, 2013 10:59 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 11:58 AM
Points: 1,059, Visits: 2,261

Typically I hate the small initial size of the system dbs and the percentage growth. I change master, model, msdb to grow by 10MB for mdf and ldf. I then resize msdb to 50mb for the mdf as maintenance plans, jobs, db mail entries all go in there.

TEMPDB, everyone else has piped in on that one... IE # of data files, sizing and growth. NEVER leave it as it comes from the install if you are running any type of medium to large installation and it will highly fragment the files.

Post #1416004

« Prev Topic | Next Topic »

Permissions