January 5, 2012 at 9:27 pm
Hi,
The following server model & setup details. this server going to be an install SQL 2K8 enterprise edition for production setup.
Could anyone suggestion me, as per this server there are 2 Physical CPU installed and logical 4 CPU, for the performance improve can create 4 Tempdb data file or 2 Tempdb data file? what about Tempdb log file created?
The following script for sufficient create 4 tempdb data files.
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db1.mdf',size = 60Mb,filegrowth = 20Mb)
use master
go
Alter database tempdb add file (name = tempdev2, filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db2.mdf',size = 60Mb,filegrowth = 20Mb),
( name = tempdev3, Filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db3.mdf',size = 60Mb,filegrowth = 20Mb),
( name = tempdev4, Filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db4.mdf',size = 60Mb,filegrowth = 20Mb),
go
server configuration as below..
OS Name: Microsoftr Windows Serverr 2008 Enterprise
OS Version: 6.0.6002 Service Pack 2 Build 6002
OS Manufacturer: Microsoft Corporation
OS Configuration: Member Server
OS Build Type: Multiprocessor Free
Registered Owner: Windows User
System Manufacturer: HP
System Model: ProLiant DL380 G4
System Type: X86-based PC
Processor(s): 2 Processor(s) Installed.
[01]: x64 Family 15 Model 4 Stepping 3 GenuineIntel ~
3200 Mhz
[02]: x64 Family 15 Model 4 Stepping 3 GenuineIntel ~
3200 Mhz
BIOS Version: HP P51, 7/19/2007
Windows Directory: C:\Windows
System Directory: C:\Windows\system32
Total Physical Memory: 3,583 MB
Available Physical Memory: 2,929 MB
Page File: Max Size: 7,354 MB
Page File: Available: 6,898 MB
Page File: In Use: 456 MB
Page File Location(s): C:\pagefile.sys
Thanks
ananda
January 5, 2012 at 9:39 pm
Hi Ananda, please have a look at the link below..
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
January 5, 2012 at 10:18 pm
Hi,
I gone through that URL..
why adding tempdb data file? main purpose of this reduce the tempdb contention for sql server transaction and improve the performance.
The general rule I know is: 1 data file per logical or physical processor (i.e. a SQL Server 2005 running on 4 quad-core cpus -> 16 cores should have 16 data files for TempDB)
so as per this server CPU, there are 2 physical CPU installed, and logical 4 CPU. I need to create 4 Tempdb data file and 1 tempdb Log file. Is it correct?
Thanks
ananda
January 5, 2012 at 10:43 pm
The general rule I know is: 1 data file per logical or physical processor (i.e. a SQL Server 2005 running on 4 quad-core cpus -> 16 cores should have 16 data files for TempDB)
so as per this server CPU, there are 2 physical CPU installed, and logical 4 CPU. I need to create 4 Tempdb data file and 1 tempdb Log file. Is it correct?
No, the myth has been debunked.
The maximum recommended is 8 tempdb files in total (1 mdf, 7 ndf's), although this is NOT a standard and not guaranteed to improve performance, it could actually decrease performance.
A clear distinction should be made between PAGELATCH and PAGEIOLATCH wait types to determine if additional tempdb data files are in fact required.
The one to one relationship by default is in fact a myth, and could degrade performance if configured incorrectly or without cause.
as far as tempdb log files go, one only, no additional ldf's.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
January 5, 2012 at 11:14 pm
Hi Henrico Bekker, thanks for your reply...
Now I could understand for tempdb adding data files...
I am going to install and kept it only one tempdb data file (mdf) and log file (ldf), by default during install SQL2K8
In feature if PAGELATCH and PAGEIOLATCH wait type increase the wait time then i will add another tempdb data files (ndf).
Could you tell me, what is the acceptable or standard wait time for PAGELATCH and PAGEIOLATCH? it is depending on SQL transaction completed time, and how can capture & monitor these two wait type for last 24 hrous or particular time period ?
Thanks..
January 5, 2012 at 11:23 pm
You can use the DMV to monitor the values: sys.dm_os_wait_stats
a good explanation on the types and what they mean on MS site below.
http://msdn.microsoft.com/en-us/library/ms179984.aspx
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
January 6, 2012 at 3:28 am
In a high volume and perfmormance environment I look to this article a fair bit.
Take it with a pinch of salt and this is based on top end hardware being place.
January 6, 2012 at 7:59 am
I'd start with an mdf an ndf and a log file. But you should always test, not just for performance but for sizing. The nice thing is you can easily add more tempdb files later.
January 9, 2012 at 6:02 pm
adding additional files allows SQL to use more "workers" to allocate new pages as objects are created/dropped in the tempDB. On a busy system there can be a lot of churn happening in TempDB. Every time a temporary object is created the database engine must wait for the pages (SGAM, GAM, PFS etc) to be allocated before moving on with whatever it was doing. Having extra worker threads to create these objects speeds everything up. This is why the CAT team started the suggested "one per processor" recommendation. Having at least one file per processor guarantees that if a worker is running on every processor and all of them request TempDB allocations at the same time it is unlikely they will have to wait.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply