Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Add Data files dynamically based no of cpu's Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 5:44 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:29 PM
Points: 477, Visits: 1,050
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 ?



--SQLFRNDZ
Post #1398721
Posted Wednesday, December 19, 2012 9:24 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:08 PM
Points: 17,967, Visits: 15,977
Are these the only considerations for this script? Or do you also want to adhere to BP recommendations as can be found here?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1398759
Posted Thursday, December 20, 2012 11:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:29 PM
Points: 477, Visits: 1,050
Yes, I need only the script..Appreciate your help!


--SQLFRNDZ
Post #1399073
Posted Thursday, December 20, 2012 12:26 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
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 2008, MVP
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

Post #1399087
Posted Thursday, December 20, 2012 4:29 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:29 PM
Points: 477, Visits: 1,050
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.




--SQLFRNDZ
Post #1399130
Posted Thursday, December 20, 2012 4:33 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:08 PM
Points: 17,967, Visits: 15,977
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
Post #1399131
Posted Thursday, December 20, 2012 5:42 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:29 PM
Points: 477, Visits: 1,050
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



--SQLFRNDZ
Post #1399139
Posted Thursday, December 20, 2012 9:30 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:08 PM
Points: 17,967, Visits: 15,977
@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
Post #1399174
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse