placing system table filegroup and Log files on same drive

Is it better to create a seperate filegroup for all user tables ? And if so
should all the system tables that belong to the Primary filegroup reside on
the same drive with the log file . I am using SQL 2000. Which system tables
get hit the most daily for any inserts,updates and deletes that may be
occuring on the server
ThanksDepends on the disk configuration.
I believe it is generally recommended to separate the log file from any data
files, i.e., separate physical disks... however, if you have a multiple
channel controller, with high thoroughput on each channel, you may be able
to get by w/ having the log and the data on the same logical disk.
I believe that all system tables must reside in PRIMARY, I'd have to verify.
If that is true, it doesn't matter what system tables get inserts, updates,
deletes. However, you are taking away some fault tolerance if you put the
system tables and the log files on the same physical disk.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23AKD5sEXDHA.1872@.TK2MSFTNGP12.phx.gbl...
> Is it better to create a seperate filegroup for all user tables ? And if
so
> should all the system tables that belong to the Primary filegroup reside
on
> the same drive with the log file . I am using SQL 2000. Which system
tables
> get hit the most daily for any inserts,updates and deletes that may be
> occuring on the server
> Thanks
>|||You also generally want log files on RAID 1 or 10 as they are continuously
sequentially writing.
Thanks,
Greg H
"Martin Schmid" <martinschmid@.sbcglobal.net.nospam> wrote in message
news:eoDKm2EXDHA.2212@.TK2MSFTNGP09.phx.gbl...
> Depends on the disk configuration.
> I believe it is generally recommended to separate the log file from any
data
> files, i.e., separate physical disks... however, if you have a multiple
> channel controller, with high thoroughput on each channel, you may be able
> to get by w/ having the log and the data on the same logical disk.
> I believe that all system tables must reside in PRIMARY, I'd have to
verify.
> If that is true, it doesn't matter what system tables get inserts,
updates,
> deletes. However, you are taking away some fault tolerance if you put the
> system tables and the log files on the same physical disk.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23AKD5sEXDHA.1872@.TK2MSFTNGP12.phx.gbl...
> > Is it better to create a seperate filegroup for all user tables ? And if
> so
> > should all the system tables that belong to the Primary filegroup reside
> on
> > the same drive with the log file . I am using SQL 2000. Which system
> tables
> > get hit the most daily for any inserts,updates and deletes that may be
> > occuring on the server
> >
> > Thanks
> >
> >
>|||Hassan
If you are using SQL Server 7, it is a good idea to put
your system files in a seperate filegroup. If you get a
corrupted user table, if your system tables are in a
seperate filegroup you can still perform a transaction log
backup prior to invoking DR (If you have DR of course). If
they are in the same filegroup you may not be able to.
This is no longer an issue in 2000.
Hope this helps
John

Placing of Indexes on seperate data file

Is it good practise to put large nonclustered of frequently queried tables on a seperate datafile to the actual table? I.e. a file using a different disk/s.
Thanks
www.SQLporn.co.uk
This was the best case, especially in DB2. I would suggest that in SQL Server it is generally not the case, especially if you choose your storage system carefully. (such as raid 1+0)
|||It's not a good idea to segregate data based only on access frequency. This
will tend to create an unbalanced workload on your i/o subsystem with
relatively few disks doing the lion's share of the work. It's better to
separate data based on sequential, random or mixed access. This will
maximize sequential throughput because random requests won't interfere with
sequential scanning.
Logs are always accessed sequentially and these should be placed on
dedicated drives. Indexes and data objects with mostly random access
patterns and objects with mostly sequential access should be segregated onto
different disks/arrays. The remaining data can be placed in a 'mixed'
filegroup.
However, unless you have predictable data access patterns, it's best to
distribute files evenly over all of your disks rather than micro-manage
object placement. In my experience, this usually provides the best overall
database performance. Run performance tests with a representative
application workload if you feel inclined to play with specialized
filegroups.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rstubbs" <anonymous@.discussions.microsoft.com> wrote in message
news:7DBF9EE7-D750-4E1E-B6A3-3A7E9D28F9C8@.microsoft.com...
> Is it good practise to put large nonclustered of frequently queried tables
on a seperate datafile to the actual table? I.e. a file using a different
disk/s.
> Thanks
> www.SQLporn.co.uk

Placing of Indexes on seperate data file

Is it good practise to put large nonclustered of frequently queried tables on a seperate datafile to the actual table? I.e. a file using a different disk/s
Thank
www.SQLporn.co.ukIt's not a good idea to segregate data based only on access frequency. This
will tend to create an unbalanced workload on your i/o subsystem with
relatively few disks doing the lion's share of the work. It's better to
separate data based on sequential, random or mixed access. This will
maximize sequential throughput because random requests won't interfere with
sequential scanning.
Logs are always accessed sequentially and these should be placed on
dedicated drives. Indexes and data objects with mostly random access
patterns and objects with mostly sequential access should be segregated onto
different disks/arrays. The remaining data can be placed in a 'mixed'
filegroup.
However, unless you have predictable data access patterns, it's best to
distribute files evenly over all of your disks rather than micro-manage
object placement. In my experience, this usually provides the best overall
database performance. Run performance tests with a representative
application workload if you feel inclined to play with specialized
filegroups.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rstubbs" <anonymous@.discussions.microsoft.com> wrote in message
news:7DBF9EE7-D750-4E1E-B6A3-3A7E9D28F9C8@.microsoft.com...
> Is it good practise to put large nonclustered of frequently queried tables
on a seperate datafile to the actual table? I.e. a file using a different
disk/s.
> Thanks
> www.SQLporn.co.uk

Placing of Indexes on seperate data file

Is it good practise to put large nonclustered of frequently queried tables o
n a seperate datafile to the actual table? I.e. a file using a different dis
k/s.
Thanks
www.SQLporn.co.ukThis was the best case, especially in DB2. I would suggest that in SQL Serv
er it is generally not the case, especially if you choose your storage syste
m carefully. (such as raid 1+0)|||It's not a good idea to segregate data based only on access frequency. This
will tend to create an unbalanced workload on your i/o subsystem with
relatively few disks doing the lion's share of the work. It's better to
separate data based on sequential, random or mixed access. This will
maximize sequential throughput because random requests won't interfere with
sequential scanning.
Logs are always accessed sequentially and these should be placed on
dedicated drives. Indexes and data objects with mostly random access
patterns and objects with mostly sequential access should be segregated onto
different disks/arrays. The remaining data can be placed in a 'mixed'
filegroup.
However, unless you have predictable data access patterns, it's best to
distribute files evenly over all of your disks rather than micro-manage
object placement. In my experience, this usually provides the best overall
database performance. Run performance tests with a representative
application workload if you feel inclined to play with specialized
filegroups.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rstubbs" <anonymous@.discussions.microsoft.com> wrote in message
news:7DBF9EE7-D750-4E1E-B6A3-3A7E9D28F9C8@.microsoft.com...
> Is it good practise to put large nonclustered of frequently queried tables
on a seperate datafile to the actual table? I.e. a file using a different
disk/s.
> Thanks
> www.SQLporn.co.uk

Placing MSDE .mdf and .ldf files on seperate drives.

Hi,
I know that SQL Server (MSDE), databases run much faster when the data and log files are on seperate physical devices. I can do this explictly when I write a CREATE DATABASE SQL script, but would like to change the MODEL database so that all new databases use the sperate data/log file assignments by default. I haven't found a way to alter the sysfiles table in model - I can read the data, but it balks at trying to update it. (recordset is not updateable, or adhoc querries not allowed against a system database).

I didn't see any options on the MSDE install that implements seperate data/log paths.

I don't have the SQL Server tools (like Enterprise Manager) available, but I do have VS.NET 03 (Standard). And Web Data Administrator.

Any suggestions? Thanks in advance.When you install MSDE you can add settings to the setup.ini file that specify the TARGETDIR (where the binaries and log files go) and DATADIR (where the data goes).

If you need to move the locations of those after the fact,Books Online contains a topic called "Attaching and Detaching a Database" which explains how.

Terri|||Excellent, thanks for the information! I'll check out the Books online link.

I think part of the problem of finding the information that I'm sure must be in the documentation, is knowing the exact terminology used. "Attaching and Detaching" never made it into my search input box. Thanks again..|||If you haven't yet downloaded and installed Books Online you definitely should! I myself refer to it dozens of times each week. It's a terrific free SQL Server 2000 reference and really helps with synax as well as larger concepts.

And yes, it can certainly be a struggle to figure out which terms to look for!

Terri|||Hi,
Thanks again for the BOOKS ONLINE reference, I've used it a number of times already. Curiously, it hasn't been much help with my original problem. I did learn about detaching and reattaching databases, and that it one way to get the physical locations set for my user DB .mdf and .ldf files. I still haven't worked out how to get the model database to refelct these new assignments (thought there are a few more thngs to try).

While searching the BOOKS ONLINE, I did find the customizing MSDE install section. Unfortunatelly, it looks like there are no paramenters to the setup that will allow an installation with seperate drive locations for the data and log files. You can specify the location of the system data files, but the setup automatically adds the log file in the same location. I had considered reinstalling MSDE, or adding a new instance, but that doesn't seem to offer a solution.

I've run into a number of roadblocks when trying to relocate the assignments of the data or log files with the system BDs. Understandably, the system is quite restrictive about manipulating system files. If someone has established an instance of MSDE with sperate physical drive locations for the data and log files, I'd be interested in knowing how they did that.

Other than getting the model DB set up with the data files on one drive and log files on another (so that new USER DBs would follow that pattern), I'm not sure that there would be an advantage to this scheme anyway. I wouldn't think that the master DB data and log files get written to often enough to make a perfomance difference. If an application is creating SQL Server objects dynamically all the time, maybe; but not under normal use. I'd guess the same holds true for other system DBs.

Where it stands now, I can get all my user DBs set up the way I'd like; though I can't get the model DB to do this automattically for new user DBs. I can't get the system DB dat and log files on sperate drives, but that may not be important. I just wonder is MS set it up that way as a performance governor for MSDE?|||Brain,

You won't find much in Books Online about MSDE. MSDE, for all intents and purposes is just SQL Server without a number of the tools (Enterprise manager, etc), and 'hobbled' to certain database sizes etc.

You've got a few options to move your database files around:
1) detach and reattach with the files in the desired locations. This takes your DB offline for a few minutes while you make the change. Check out Books Online for sp_attach_db and sp_detach_db
(this one's a bit tricky/ugly).
2) ALTER the database to specify the creation of NEW files. Look into ALTER DATABASE (add and remove file options). This allows you do to everything while the db is up and running.. but can present some issues where you'll need to tidy things up.
3) Do a backup of your database, then restore it over itself with the files MOVED to where you want them. This might be the best option. And will look something like:

-- backup the db
BACKUP DATABASE myDatabaseName TO DISK = N'C:oink.bak'

-- create a device to 'wrap' your backup and details:
EXEC sp_addumpdevice 'disk', 'mydevice','C:\oink.BAK'

-- Now... just execute this:
RESTORE FILELISTONLY FROM mydevice

this shows you all of your logical file names as well as the actual paths to those files.

map those into something like the following:
RESTORE DATABASE
[myDatabase]
FROM
[mydevice]
WITH
RECOVERY, -- make it work when it comes back online
REPLACE, -- overwrite the existing database if it's there
STATS = 25,
MOVE 'logicalfilename' TO 'G:\SQLData\ProductCatalog_Data.MDF'
,MOVE 'logicalfilename' TO 'F:\SQLData\ProductCatalog_Log.LDF'

EXEC sp_dropdevice mydevice --,'delfile'

the logicalfilenames above correspond to the logical name returned in the resultset you get when you do RESTOREFILELISTONLY

hth