September 12, 2011 at 5:33 pm
Hi,
We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases.
We have 2 dedicated drives for Tempdb on SAN with 50 GB space. Both tempdb data & log files are created with default size. I would like to presize them.
What are the best values to start with?
U ->Tempdbdata having tempdb.mdf file
V->Tempdblog having templog.ldf file
Thanks
September 12, 2011 at 8:12 pm
Mani,
It depends on how your tempdb is used. As a rule of thumb, It is usually advantageous to make several tempdb data files and place them all on the same tempdb drive. tempdb log only needs a single file. Size the data files to be all the same size and be sure to not auto shrink them.
So if you have 50GB, you'll probably want to leave some space free on the drive, so maybe 3 files of exactly 10GB? Or 5 files of 5 GB each? Something like that. The number of files depends on the number of CPUs you have on your system, with 1 file for every 2 CPUs, or 1.5 CPUs, or 1 CPU. Depends on who you talk to, but a few files would be good to start and it'll be in the ballpark, then you can monitor tempdb usage from there to ensure there are not page allocation waits on GAM/SGAM pages in tempdb.
Brent Ozar has some great scripts to help determine tempdb allocation contention, and Robert Davis has a nice PowerPoint slide deck about tempdb that you can google.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
September 12, 2011 at 11:18 pm
In addition to what Jim said, configure datafile autogrowth in MB and logfile growth in %.
----------
Ashish
September 13, 2011 at 12:44 am
All auto grow should be configured in MB, especially the log. A % can cause nasty surprises with larger files, and it's the log that takes longer to grow and usually the one that the growth is time-sensitive.
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
April 11, 2013 at 6:16 am
Never ever use % on log file or DB...
I just had my first problem with that. I have 2 companies with the same software that we provide and both have the same hardware same configuration on tempdb and all databases but for some reason one company was having problems at the peek work load.
It drove me crazy for 3 days. The server would just stop doing anything for about 3 min and then come back to normal like nothing had ever happened. Well after 3 days i found out that the most used DB on the server on one company had a data filegrowth 200 MB and log 200Mb and the other company was 1Mb on data file and 10% on log file.
Well thats all good when your DB is 100Mb but in my case the log file was 21gb and 10% on that is about 2.1Gb and it was stopping everything until it got the space. Imagine if i had the 10% on my data file which is 48Gb.
anyways never ever use % on data or log files.
April 11, 2013 at 7:00 am
sharepoint 2010 does not seem to be a big user of tempdb. I have one installation with an initial size for tempdb of only 200mb and there has never been a growth.
---------------------------------------------------------------------
April 11, 2013 at 7:02 am
Please note: 2 year old thread.
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
April 11, 2013 at 8:18 am
<sigh>
:doze:
---------------------------------------------------------------------
January 15, 2014 at 11:10 am
I know this an old post, but just going throw the comments and trying to get an answer here. I found that Microsoft does not say anything about using percentage autogrowth for tempdb, all it says to allow automatic auto grow, http://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
Any thoughts about what type of file growth increment to use?
March 10, 2015 at 6:18 pm
the intention with auto growth is to intervene before it automatically grows. the auto growth is just a stop-gap emergency measure
March 11, 2015 at 3:40 pm
Regarding the type of file growth increment to use, it depends upon your database traffic. Ideally, you should not have to continuously have your database autogrow so if your autogrowth increment is set at 50MB, but it's growing every hour, then drastically increase your autogrowth increment. I always start at autogrowing 1GB because the databases I work with are usually not smaller than 1GB.
You can see how frequently your database is growing by going into the Disk Usage report as provided as a canned report in SSMS.
January 10, 2017 at 7:41 am
I realize this is a very old thread but here is my .02 having managed numerous SharePoint databases. After all someone may still end up at this thread looking for tempdb sizing help.
If you plan to run DBCC CHECKDB on the SharePoint Content databases (you are planning to do this right?) size the tempdb to 1.25 x the size of the largest database.
Best practice for tempdb is always to disable autogrowth and presize the files to whatever you need and make sure all files are sized the same in order to keep allocation across the files even.
Start with 4 data files and increase if you see GAM or SGAM latch contention on any of the tempdb files. This can be seen in the sys.dm_os_waiting_tasks DMV where the wait type will be a page latch wait, and the resource will be a file in database 2 (tempdb).
January 10, 2017 at 10:17 am
Never disable autogrowth on tempdb -- it could bring the entire instance to a waiting state. Instead, put alert(s) into place to make sure you are made aware if/when tempdb data file(s) does ever autogrow.
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".
January 10, 2017 at 10:34 am
ScottPletcher (1/10/2017)
Never disable autogrowth on tempdb -- it could bring the entire instance to a waiting state. Instead, put alert(s) into place to make sure you are made aware if/when tempdb data file(s) does ever autogrow.
Amen to that. I don't know how the idea disabling autogrowth on TempDB ever became a so called "best practice".
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2017 at 11:07 am
That's simply not true. An instance will not hang because you filled the tempdb database. Queries will fail and the instance will throw errors like 1105, but it most certainly won't hang.
If you do what you say and enable auto-growth for tempdb, be sure to limit the max size of the files or one run-away query can cause everything to hang. Consider what happens if that run-away query keeps auto-growing tempdb and it fills the disk leaving no room for your user database log files to grow. Now enabling auto-growth on tempdb (without setting a reasonable max size) has actually caused the instance to hang.
Best practices for tempdb are well documented. Put it on it's own drive, size all files equally, and preallocate their sizes (equally!) to fill that drive. If you have more than one file, setting auto-growth on any of the files will cause them to grow unevenly resulting in unbalanced use of the files and potentially resulting in GAM/SGAM contention on a busy server.
MS best practice for tempdb is 1 file per CPU up to a max of 8.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply