Proportional Fill Algorithm With Tempdb Data Files

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Proportional Fill Algorithm With Tempdb Data Files

Author

Topic

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2009-01-12 : 15:14:01

I believe I understand the reason why you can achieve tempdb performance benefits from creating one data file per CPU. What I am curious about now is how well the proportional fill algorithm keeps the files sizes similar. I created tempdb with 8 data files, one file per CPU. (4 CPU x 2 Core Each = 8). Each data file is 512MB with 100MB Growth. The log file is 1024MB with 100MB Growth.

After one week I checked sys.master_files and can see each data file at 512MB. sysfiles however reports different sizes as seen below.

name Size MB Growth
-------------------- ---------------------- -----------
tempdev1 2314.375 100
tempdev2 1244.1875 100
tempdev3 892.75 100
tempdev4 888.25 100
tempdev5 673.3125 100
tempdev6 690.8125 100
tempdev7 674.0625 100
tempdev8 988.6875 100
templog1 1024 100

The size in sysfiles appears to be the current size, not the initial size. BOL says SIZE listed in sys.master_files is the current size, but that is clearly not the case, at least for tempdb.

I expected to see similar data files sizes due to the proportional fill algorithm, but I'm not seeing that. Is what I see above normal behavior for the algorithm or is something wrong?

Thanks, Dave

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-12 : 15:41:24

I have tempdb configured with multiple data files in all of my production systems and I do not see this issue in sys.master_files. My systems show the same value in the size column for all data files.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2009-01-14 : 08:51:59

That's what I expected to see. Below is what I ran to create the files. Any idea why the files wouldn't be growing at the same rate?

Are any of your servers running 2005 SP2 - 64bit? I want to make sure this isn't a 64bit problem.

ALTER DATABASE tempdb
MODIFY FILE(NAME = tempdev, FILENAME = 'H:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TempDB_Data1.MDF', SIZE = 512MB, FILEGROWTH = 100MB)

ALTER DATABASE tempdb
MODIFY FILE(NAME = templog, FILENAME = 'H:\Microsoft SQL Server\MSSQL.1\MSSQL\Logs\TempDB_Log1.LDF', SIZE = 1024MB, FILEGROWTH = 100MB)

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = 'H:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TempDB_Data2.NDF', SIZE = 512MB, FILEGROWTH = 100MB)

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev3, FILENAME = 'H:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TempDB_Data3.NDF', SIZE = 512MB, FILEGROWTH = 100MB)

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev4, FILENAME = 'H:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TempDB_Data4.NDF', SIZE = 512MB, FILEGROWTH = 100MB)

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev5, FILENAME = 'H:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TempDB_Data5.NDF', SIZE = 512MB, FILEGROWTH = 100MB)

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev6, FILENAME = 'H:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TempDB_Data6.NDF', SIZE = 512MB, FILEGROWTH = 100MB)

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev7, FILENAME = 'H:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TempDB_Data7.NDF', SIZE = 512MB, FILEGROWTH = 100MB)

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev8, FILENAME = 'H:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TempDB_Data8.NDF', SIZE = 512MB, FILEGROWTH = 100MB)
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-14 : 12:39:39

All of my systems are running SP2 64-bit, specifically build 3282 which I believe is cumulative update package 9.

I don't know why your system is behaving that way. Are you able to open a case with Microsoft on it to confirm if everything is correct?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2009-01-16 : 17:47:04

I'm trying to hold off on getting Microsoft involved. Lately support has not been good. We have spent a lot of time on the phone speaking with multiple engineers and reviewing the same information with each person. I now dread calling because I know what first-line support will do 75-80% of the time. I'll keep checking the forums and Google.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-16 : 17:58:10

I have the same issue with PSS. We escalate through our TAM to get a higher engineer when we are hitting a dead end.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2009-01-16 : 18:09:32

Our TAM didn't help our latest issue. I haven't been too pleased with the quality of people we've been assigned by the TAM. We had a cluster "expert" assigned in 2007 or early 2008 to help us build our first cluster. He missed so many things that I ended up finding on my own.

Today we closed a Sharepoint deadlock issue because we were told that deadlocks are normal with Sharepoint. I can understand occasional deadlocks, but not every time a Sharepoint crawl occurs. And sometimes there is only one deadlock and other times there are over 10. To me that's just sloppy coding on their part. I could live with the deadlocks if it is occurring on a dedicated Sharepoint DB server, but its on a shared server. Now we run the risk of missing legitimate deadlocks from other apps. Oh well. I'm starting to vent. Sorry about that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources