MS - Storage Top Ten - SQL Server Forums

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 2000 Forums

SQL Server Administration (2000)

MS - Storage Top Ten

Author

Topic

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-12-04 : 07:35:38

I just found the Microsoft SQL Server Best Practices site and was interested by the storage top ten
[url]http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx[/url]
A lot of this is generalities as Microsoft can't give vendor specific guidelines (Or so my vendor tells me when I quote this to them :) ). The one that caught my eye, and one I can implement on several of my SQL Servers are these

Make sure to move TEMPDB to adequate storage and pre-size after installing SQL Server.
Performance may benefit if TEMPDB is placed on RAID 1+0 (dependent on TEMPDB usage).
For the TEMPDB database, create 1 data file per CPU, as described in #8 below.

It is recommended to have .25 to 1 data files (per filegroup) for each CPU on the host server.
This is especially true for TEMPDB where the recommendation is 1 data file per CPU.
Dual core counts as 2 CPUs; logical procs (hyperthreading) do not.

But how to go about creating Seperate Data files for Tempdb, every time the server is restarted (And on a couple of my SQL Servers thats a weekly Occurrence! Vendor states that it benefits performance, DBA Says it doesn't . . Management Say Follow the Vendors recomendations . . DBA Says Vendor Supply documentation as to why this is required . . . Vendor goes deaf)

So I know the Syntax goes something like

ALTER DATABASE <db_name, Sysname , DBName>
  ADD FILE ( NAME       = <logical_file_name , , DBCurrName>_<data_or_log , dat|log , dat>
           , FILENAME   = '<OS_Drive, , D:><OS_Path, , \data\><db_name, Sysname , DBName>\<OS_Filename, ,Filename.ndf>'
           , FILEGROWTH = <filegrowth , 128 | 10 , 128><suffix, % | KB | MB | GB | TB , MB>  
           , SIZE       = <size , , 512><suffix , KB | MB | GB | TB , MB>
           , MAXSIZE    = <maxsize , 512|UNLIMITED , 512><suffix, KB | MB | GB | TB , MB>
           ) TO FILEGROUP <DB_Filegroup, , [PRIMARY]>

but just how to run it when SQL Server Starts? After all When it starts tempdb is created from model, so is there an OnStartup parameter somewhere that allows you to hook in a stored procedure/ tsql? Of course as part of this I could also create tempdb to be the appropriate size as well.

Any Ideas?

--
Regards
Tony The DBA

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-04 : 07:54:00

I don't know about whether you can schedule statements at startup, but you can definitely mark stored procedures to be run at the startup using:

sp_procoption 'proc1', 'startup', '1'

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-12-04 : 08:19:38

"For the TEMPDB database, create 1 data file per CPU, as described in #8 below."

I've heard that, but never actually done it. Critical that they are the same size I believe.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources