February 1, 2013 at 8:59 am
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.
February 1, 2013 at 10:19 am
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; Theyll drag you down to their level and beat you with experience
February 1, 2013 at 10:21 am
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://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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2013 at 10:56 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 1, 2013 at 1:08 pm
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?
February 1, 2013 at 1:20 pm
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" 🙂
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 1, 2013 at 1:22 pm
February 4, 2013 at 1:04 am
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).
February 5, 2013 at 10:59 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply