SQL Server Performance Forum – Threads Archive

Spreading database files

Let me start by saying, I am very new to SQL Server 2000. I have SQL Server Installation which uses only a "Primary File Group" to store all it’s data. I am thinking of seperating "Transaction Log", "System Objects" and "User defined objects" onto different disks. It is very easy to seperate the "Transaction Log" file.
I am trying to define "Secondary file group" and move all the "User defined objects" there. What is easiest way accomplish this task? Regards, Nirmalya
[email protected]

If you have differents physical drives, I suggest to change only the log file to other drive. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Why are you trying to do this in the first place Nirmalya? Are you currently experiencing disk queue issues? What kind of drives (RAID, number of disks, etc) do you have for this server? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

The performance is an issue. I am moving onto a new hardware platform where I will have the luxury of having multiple disk volumes. The fundamentals of "performance enhancements" talks about seperating the "Transaction Log" files and put in a disk volume with RAID 10. This particular SQL Server installation is more of data retrieval application. So the database objects, I was thinking of putting in a different disk volume with RAID5. Furthermore was thinking of seperating the "System" objects from "User" objects in different disk volumes.
Of course at the end of the day, I have to look into the SQL queries which are the culprits.
But trying to cover the low level issues first. Regards, Nirmalya
[email protected]

HI Nirmalya, Yes, placing the Transaction Logs in seperate disk is a good idea to improve the performance. you can use this way how to place the tables in seperate file groups. Search for "filegroups, switching filegroup for table" in BOL: ————————
How to place an existing table on a different filegroup (Enterprise Manager)
To place an existing table on a different filegroup Expand a server group, and then expand a server.
Expand Databases, expand the database in which the table belongs, and then click Tables.
In the details pane, right-click the table, and then click Design Table.
Right-click any column, and then click Properties.
On the Tables tab, in the Table Filegroup list, select the filegroup on which to place the table.
Optionally, in the Text Filegroup list, select a filegroup on which to place any text, image, and ntext columns. —————- Try this and you can check the placement of tables using the query: SELECT
sysFile.groupid AS GroupID
, SUBSTRING(sysFile.groupname,1,30) AS FilegroupName
, SUBSTRING(sysObj.name,1,30) AS ObjectName
FROM sysobjects sysObj
INNER JOIN sysindexes sysIdx
ON sysObj.id = sysIdx.id
INNER JOIN sysfilegroups sysFile
ON sysIdx.groupid = sysFile.groupid
WHERE sysIdx.indid IN(0,1) AND sysObj.xtype = ‘U’
ORDER BY sysFile.groupname, sysObj.ObjectName SELECT
CAST(OBJECT_NAME(sysind.id) AS CHAR(20)) AS TableName
, CAST(sysind.name AS CHAR(30)) AS IdxName
, CAST(sysfg.groupname AS CHAR(10)) AS GroupName
FROM sysindexes sysind
INNER JOIN sysfilegroups sysfg
ON sysind.groupid = sysfg.groupid
INNER JOIN sysobjects sysobj
ON sysind.id = sysobj.id
WHERE sysobj.xtype <> ‘S’
AND sysind.name NOT LIKE ‘_WA%’
ORDER BY sysind.TableName -Johnson

How many disks will you have on the new server? What size and speed are the new disks? What kind of RAID is supported? What kind of controller card(s)? What kind of connection (fiber, SCSI), etc? We can give better advice with this information. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

If the server is primarily for data retrieval, putting the log file on its own drives may be a waste, as its not going to see much activity. It might be a better idea to split your tables across the drives. You can move the clustered index of large, frequently joined tables onto seperate drives for a performance boost.

]]>