Add Data files dynamically based no of cpu's

Question

Add Data files dynamically based no of cpu's

@SQLFRNDZ

SSCrazy Eights

Points: 9095
More actions
December 19, 2012 at 5:44 pm

#268360

Here is the scenario:
Step1: Verify number of CPU's
SELECT cpu_count FROM sys.dm_os_sys_info
Step2: Verify number of Datafiles
select COUNT(*)-1 from tempdb.sys.sysfiles
Step 3: If Count in steps and step2, we need add additional files using t-sql ?
Can I get help with script please ?
[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Are these the only considerations for this script? Or do you also want to adhere to BP recommendations as can be found here[/url]?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 2

Yes, I need only the script..Appreciate your help!

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Err...bad idea. Really bad idea.

One file per core is an old recommendation, it's not a good idea under all circumstances without consideration or investigation.

http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 4

I knew it is bad practise for tempdb but want to see how we can write a query to dynamically add files based on a condition.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Well, what have you got for your script so far?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 6

SQLRNNR (12/20/2012)
Well, what have you got for your script so far?

This is what I got so far and I need help with looping script to add files to number of times based on condition cpu_count.

Here is the script I'm able to write so far

DEclare @value1 int

DEclare @value2 int

DECLARE @OUTPUT Varchar(100)

select @value1=COUNT(*)-1 from sys.sysfiles

SELECT @value2=cpu_count FROM sys.dm_os_sys_info

select @OUTPUT =

Case when @value1 = @value2 THEN 'GoodToGo'

ELSE "-- Needs a Script Here --"

END

SELECT @OUTPUT [TEMPDB FILES]

GO

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

@SQLFRNDZ (12/20/2012)
This is what I got so far and I need help with looping script to add files to number of times based on condition cpu_count.
Here is the script I'm able to write so far
DEclare @value1 int
DEclare @value2 int
DECLARE @OUTPUT Varchar(100)
select @value1=COUNT(*)-1 from sys.sysfiles
SELECT @value2=cpu_count FROM sys.dm_os_sys_info
select @OUTPUT =
Case when @value1 = @value2 THEN 'GoodToGo'
ELSE "-- Needs a Script Here --"
END
SELECT @OUTPUT [TEMPDB FILES]
GO

Ok, from there - what would you pseudo-code to achieve your goal?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events