Solved

Create a TEMPDB Database to each CPU on Server

Posted on 2011-03-25
4
571 Views
Last Modified: 2012-05-11
Hi Experts
What is the way to create a TEMPDB database for each CPU on the Server? and then how assign every database to each CPU?

thanks.
0
Comment
Question by:saulpilot439
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 37

Accepted Solution

by:
Neil Russell earned 125 total points
ID: 35218283
QUOTED FROM: http://www.sqlservercentral.com/Forums/Topic391927-360-1.aspx

Very very very important - NOT filegroups, files. For any database, not just tempdb, you may gain performance by having the same number of files per filegroup as you have cpu sockets or cores ( don't do this for hyperthreading ).

Putting tempdb on it's own array is another matter and not directly related to database files.

here's an example to create an 8 file tempdb

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),

( name = tempdev5, Filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db5.mdf',size = 60Mb,filegrowth = 20Mb),

( name = tempdev6, Filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db6.mdf',size = 60Mb,filegrowth = 20Mb),

( name = tempdev7, Filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db7.mdf',size = 60Mb,filegrowth = 20Mb),

( name = tempdev8, Filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb_db8.mdf',size = 60Mb,filegrowth = 20Mb)

go



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ 
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 35218345
Oh and you dont need to assign a db file to a cpu. The whole point is that if you have 2 x Quad core CPU's then thats 8 threads. Wach thread can access 1 file, so in theory 8 threads = 8 files in a group = the setup shown above.
0
 

Author Closing Comment

by:saulpilot439
ID: 35218349
Great, Greate Answer Neilsr thank very Much with this I have my problem solved.

Thanks Expert.
0
 

Author Comment

by:saulpilot439
ID: 35218394
Thanks very Much Neilsr.

This answer help me  for to night on my change. Thanks
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question