Question with filegroups

I'm trying to tweak our DB as much as I can. We have the full application
database sitting on a RAID 10. Logs, tempdb, etc.. are on separate arrays.
I read some articles about making separate filegroups. Of course, they all
say to move them to different arrays, which because of budget is not
possible right now. I also read articles that say to put large tables in a
filegroup then add another data file to the group. This allows SQL Server
to use multiple threads to access the tables. Therefore, to my question.
If I split our database up into multipele file groups on the same array,
could I see a performance gain. Would the database be able to issue
different threads for each group when a query is run and improve overall
performance.

Thank you,

Adam"Adam" <adam@.someone.com> wrote in message
news:4269993c$1_4@.alt.athenanews.com...
> I'm trying to tweak our DB as much as I can. We have the full application
> database sitting on a RAID 10. Logs, tempdb, etc.. are on separate
arrays.
> I read some articles about making separate filegroups. Of course, they
all
> say to move them to different arrays, which because of budget is not
> possible right now. I also read articles that say to put large tables in a
> filegroup then add another data file to the group. This allows SQL Server
> to use multiple threads to access the tables.

This was true up to (or thru, I always forget) SQL 7.0. It's no longer
true.

>Therefore, to my question.
> If I split our database up into multipele file groups on the same array,
> could I see a performance gain. Would the database be able to issue
> different threads for each group when a query is run and improve overall
> performance.

No. But, for VLDB, maintenance can sometimes be easier. (and SQL 2005 will
have some REAL nifty things in this regard.)

> Thank you,
> Adam|||Hi

You may want to look at:
http://www.sql-server-performance.c...performance.asp

John

"Adam" <adam@.someone.com> wrote in message
news:4269993c$1_4@.alt.athenanews.com...
> I'm trying to tweak our DB as much as I can. We have the full application
> database sitting on a RAID 10. Logs, tempdb, etc.. are on separate
> arrays. I read some articles about making separate filegroups. Of course,
> they all say to move them to different arrays, which because of budget is
> not possible right now. I also read articles that say to put large tables
> in a filegroup then add another data file to the group. This allows SQL
> Server to use multiple threads to access the tables. Therefore, to my
> question. If I split our database up into multipele file groups on the
> same array, could I see a performance gain. Would the database be able to
> issue different threads for each group when a query is run and improve
> overall performance.
> Thank you,
> Adam

Question on Tempdb mdf & ldf files

Thanks in advance.
I have heard much about putting tempdb on separate raid 1 spindles but
should I go further and put the tempdb data and tempdb logs on separate
spindles? Will that buy me anything? This will be a very large db server, a
cluster, and will have multiple dbs. I have two large raid 10 arrays for
data and multiple raid 1 arrays for logs and can separate out tempdb further
so the data has its own raid 1 array and the tempdb logs can have its own
raid 1 array as well. This is all on an emc san, cx400.
Thanks,
John
hard to say.
You'll likely have to test this with the SQL IO Stress tool or some other
test tool.
Greg Jackson
PDX, Oregon
|||Hi John,
Take a look at this article.
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default...b;en-us;328551
The article discusses some of the enhancement made in SP 4 and ways to
enhance tempdb performance.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
John - PDX wrote:
> Thanks in advance.
> I have heard much about putting tempdb on separate raid 1 spindles but
> should I go further and put the tempdb data and tempdb logs on separate
> spindles? Will that buy me anything? This will be a very large db server, a
> cluster, and will have multiple dbs. I have two large raid 10 arrays for
> data and multiple raid 1 arrays for logs and can separate out tempdb further
> so the data has its own raid 1 array and the tempdb logs can have its own
> raid 1 array as well. This is all on an emc san, cx400.
|||For a consolidated system, it is a good idea to seperate out at least the
TempDB data file(s). In our installation, we have also seperated out the
log files even though most of the activity resides in the data files.
Also, check out the following KB. You might want to consider planning this
now instead of waiting for it to happen to you.
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default...b;en-us;328551
This recommendation includes applying a hotfix, setting a trace flag, and
creating multiple fixed sized data files for tempdb.
Sincerely,
Anthony Thomas

"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23wCVc%23uaFHA.2536@.TK2MSFTNGP10.phx.gbl...
hard to say.
You'll likely have to test this with the SQL IO Stress tool or some other
test tool.
Greg Jackson
PDX, Oregon
sql

Question on Tempdb mdf & ldf files

Thanks in advance.
I have heard much about putting tempdb on separate raid 1 spindles but
should I go further and put the tempdb data and tempdb logs on separate
spindles? Will that buy me anything? This will be a very large db server, a
cluster, and will have multiple dbs. I have two large raid 10 arrays for
data and multiple raid 1 arrays for logs and can separate out tempdb further
so the data has its own raid 1 array and the tempdb logs can have its own
raid 1 array as well. This is all on an emc san, cx400.
--
Thanks,
Johnhard to say.
You'll likely have to test this with the SQL IO Stress tool or some other
test tool.
Greg Jackson
PDX, Oregon|||Hi John,
Take a look at this article.
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/defaul...kb;en-us;328551
The article discusses some of the enhancement made in SP 4 and ways to
enhance tempdb performance.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
John - PDX wrote:
> Thanks in advance.
> I have heard much about putting tempdb on separate raid 1 spindles but
> should I go further and put the tempdb data and tempdb logs on separate
> spindles? Will that buy me anything? This will be a very large db server,
a
> cluster, and will have multiple dbs. I have two large raid 10 arrays for
> data and multiple raid 1 arrays for logs and can separate out tempdb furth
er
> so the data has its own raid 1 array and the tempdb logs can have its own
> raid 1 array as well. This is all on an emc san, cx400.|||For a consolidated system, it is a good idea to seperate out at least the
TempDB data file(s). In our installation, we have also seperated out the
log files even though most of the activity resides in the data files.
Also, check out the following KB. You might want to consider planning this
now instead of waiting for it to happen to you.
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/defaul...kb;en-us;328551
This recommendation includes applying a hotfix, setting a trace flag, and
creating multiple fixed sized data files for tempdb.
Sincerely,
Anthony Thomas
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23wCVc%23uaFHA.2536@.TK2MSFTNGP10.phx.gbl...
hard to say.
You'll likely have to test this with the SQL IO Stress tool or some other
test tool.
Greg Jackson
PDX, Oregon

Question on Tempdb mdf & ldf files

Thanks in advance.
I have heard much about putting tempdb on separate raid 1 spindles but
should I go further and put the tempdb data and tempdb logs on separate
spindles? Will that buy me anything? This will be a very large db server, a
cluster, and will have multiple dbs. I have two large raid 10 arrays for
data and multiple raid 1 arrays for logs and can separate out tempdb further
so the data has its own raid 1 array and the tempdb logs can have its own
raid 1 array as well. This is all on an emc san, cx400.
--
Thanks,
Johnhard to say.
You'll likely have to test this with the SQL IO Stress tool or some other
test tool.
Greg Jackson
PDX, Oregon|||Hi John,
Take a look at this article.
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
The article discusses some of the enhancement made in SP 4 and ways to
enhance tempdb performance.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
John - PDX wrote:
> Thanks in advance.
> I have heard much about putting tempdb on separate raid 1 spindles but
> should I go further and put the tempdb data and tempdb logs on separate
> spindles? Will that buy me anything? This will be a very large db server, a
> cluster, and will have multiple dbs. I have two large raid 10 arrays for
> data and multiple raid 1 arrays for logs and can separate out tempdb further
> so the data has its own raid 1 array and the tempdb logs can have its own
> raid 1 array as well. This is all on an emc san, cx400.|||For a consolidated system, it is a good idea to seperate out at least the
TempDB data file(s). In our installation, we have also seperated out the
log files even though most of the activity resides in the data files.
Also, check out the following KB. You might want to consider planning this
now instead of waiting for it to happen to you.
FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
This recommendation includes applying a hotfix, setting a trace flag, and
creating multiple fixed sized data files for tempdb.
Sincerely,
Anthony Thomas
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23wCVc%23uaFHA.2536@.TK2MSFTNGP10.phx.gbl...
hard to say.
You'll likely have to test this with the SQL IO Stress tool or some other
test tool.
Greg Jackson
PDX, Oregon

Question on moving logs

Running SQL Server 2000. I have been asked to move the transaction logs
to another drive. I was able to do that for the user databases using
detach/attach.

However I don't see that as an option for moving the logs for the
system databases.
Is there any way I can move the system database logs? I'm guessing it
might be possible through a restore.

Any thoughts ?

Thanks in advance.

GerryShutdown the service, and change thew appropiate registry settings for
the temp and the master database. The other databases can be moved with
attach / detach.

HTH, Jens K. Suessmeyer.

--
http://www.sqlserver2005.de
--|||Jens wrote:

Quote:

Originally Posted by

Shutdown the service, and change thew appropiate registry settings for
the temp and the master database. The other databases can be moved with
attach / detach.
>
HTH, Jens K. Suessmeyer.
>
--
http://www.sqlserver2005.de
--

Thanks Jens

Also came across this

http://support.microsoft.com/kb/224071/

Question on moving logs

Running SQL Server 2000. I have been asked to move the transaction logs to another drive. I was able to do that for the user databases using detach/attach.

However I don't see that as an option for moving the logs for the system databases.
Is there any way I can move the system database logs? I'm guessing it might be possible through a restore.

Any thoughts ?

Thanks in advance.

GerryGiven the number of transactions that happen in master, model and msdb on a daily basis, I have never found it necessary to move the logs of these three databases. Tempdb on the other hand can be moved with a dirty trick. Leave the existing log with a maxsize limit of say 5MB, and create a second logfile on the new device at the actual size you need. Logfiles are used in sequence, as opposed to datafiles which are used in a more distributed way. You will still have the original disk used for 5MB of logs every once in a while, but the vast majority of the usage should be on the other drive.|||Thanks

Also came across this article

http://support.microsoft.com/kb/224071/

Question on changing startup parameters

I'm new to SQL Server...running SQL Server 2000. Had to move the system database logs to a different drive. Used EM, Server properties, Startup parameters. Changed the transaction log designation. Closed that window. Stopped the server.

Server won't start as I messed up and did not include the file name in the new designated path...just down to directory level.

From what I've seen, I have to be able to start the server service to change the parameters and the server won't start.

Is there a file that contains these parameters I may edit or should I try using the command line options.

Many thanks in advance.Was able to do a reg edit on the parameters and have it running.|||You can also start it from a command line with parameters.

sqlservr [-sinstance_name] [-c] [-dmaster_path] [-f]
[-eerror_log_path] [-lmaster_log_path] [-m]
[-n] [-Ttrace#] [-v] [-x] [-g number] [-O] [-y number]

Look at SQL BOL for the complete reference. It's a handy thing to know at 2:00am.

Regards,

hmscott|||I'm new to SQL Server...running SQL Server 2000. Had to move the system database logs to a different drive. Used EM, Server properties, Startup parameters. Changed the transaction log designation. Closed that window. Stopped the server.

Server won't start as I messed up and did not include the file name in the new designated path...just down to directory level.

From what I've seen, I have to be able to start the server service to change the parameters and the server won't start.

Is there a file that contains these parameters I may edit or should I try using the command line options.

Many thanks in advance.
Just a note: sometimes the paramters, when used in conjunction with other parameters, will act in an unpredicatable way. use google for more info|||just another reason to script everything.|||just another reason to script everything.
a real reason to script everything? all these sex ads (http://www.napstergirl.com) floating around the internets. I once thought that it was cool, but think of all the space this is wasting? anyway, this one ad kinda made sense to show cause look at the website: it is so advanced and could be used for other things