Hi,
We've installed a db with filegroups for data and indexes.
Each filegroup has two equaly sized files spread over two diskdevices.
When data is loaded it's spread equally over the files, if we do a reindex
over all tables and look (via Tasks/Shrink/Files) at the file useage
it has changed from equally spread 50-50% to 65-35%.
any ideas?
Derk Jan
Does it mean that your db has 2 filegroup - one for the data and one
for the indexes. And after you loaded the data in, they were
distributed equally. But after the reindex, the proportion changed and
no longer half half?
Index has two kind - clustered and non-clustered. Clustered is with
tables (data itself stored in an order of the index). Non-Clustered
can be stored somewhere else, which I believe you put it into another
filegroup. The size of non-clustered index varies depends on what
columns you have indexed on. The more columns you index, the bigger
the index.
After re-index, the size will change (unless no modification has been
made). So what you observe is normal to me or I must miss sth from
your post.
Mel
|||configuration as as you described it.
In our situation however the data was loaded -not altered- and then
re-indexed and then the proportion is no longer 50-50.
tanx,
Dick
"MSLam" wrote:
> Does it mean that your db has 2 filegroup - one for the data and one
> for the indexes. And after you loaded the data in, they were
> distributed equally. But after the reindex, the proportion changed and
> no longer half half?
> Index has two kind - clustered and non-clustered. Clustered is with
> tables (data itself stored in an order of the index). Non-Clustered
> can be stored somewhere else, which I believe you put it into another
> filegroup. The size of non-clustered index varies depends on what
> columns you have indexed on. The more columns you index, the bigger
> the index.
> After re-index, the size will change (unless no modification has been
> made). So what you observe is normal to me or I must miss sth from
> your post.
> Mel
>
|||Dick,
When you said data was loaded? Did you put index on before the data
was loaded? Did you turn it on afterwards? What tool did you use to
load the data in?
Mel
|||Hi Lam,
Data is loaded with regular SQL stored-procs.
All tables/index definitions where in place when data was loaded, no changes
in table definitions were made after the load.
After the load the dbcc reindex was executed.
cheers,
Dick
"MSLam" wrote:
> Dick,
> When you said data was loaded? Did you put index on before the data
> was loaded? Did you turn it on afterwards? What tool did you use to
> load the data in?
> Mel
>
|||The only thing I could think of is the fillfactor field. The
fillfactor may have be changed during the rebuild?
fillfactor
Is the percentage of space on each index page to be used for storing
data when the index is created. fillfactor replaces the original
fillfactor as the new default for the index and for any other
nonclustered indexes rebuilt because a clustered index is rebuilt. When
fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
when the index was created.
I assume you use DBCC REINDEX.
Mel
|||Hi,
Did an additional DBCC DBREINDEX ( 'table_name' [,'index_name' [ ,
fillfactor ] ]) on all tables with same fill factor as the tables were
created with, and still
experiencing and uneven spread of data (300MB files 15%-65% spreading), not
off indexes.
Dick
"MSLam" wrote:
> The only thing I could think of is the fillfactor field. The
> fillfactor may have be changed during the rebuild?
> fillfactor
> Is the percentage of space on each index page to be used for storing
> data when the index is created. fillfactor replaces the original
> fillfactor as the new default for the index and for any other
> nonclustered indexes rebuilt because a clustered index is rebuilt. When
> fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
> when the index was created.
> I assume you use DBCC REINDEX.
> Mel
>
Showing posts with label installed. Show all posts
Showing posts with label installed. Show all posts
Monday, March 19, 2012
filegroups / reindex
Hi,
We've installed a db with filegroups for data and indexes.
Each filegroup has two equaly sized files spread over two diskdevices.
When data is loaded it's spread equally over the files, if we do a reindex
over all tables and look (via Tasks/Shrink/Files) at the file useage
it has changed from equally spread 50-50% to 65-35%.
any ideas?
Derk JanDoes it mean that your db has 2 filegroup - one for the data and one
for the indexes. And after you loaded the data in, they were
distributed equally. But after the reindex, the proportion changed and
no longer half half?
Index has two kind - clustered and non-clustered. Clustered is with
tables (data itself stored in an order of the index). Non-Clustered
can be stored somewhere else, which I believe you put it into another
filegroup. The size of non-clustered index varies depends on what
columns you have indexed on. The more columns you index, the bigger
the index.
After re-index, the size will change (unless no modification has been
made). So what you observe is normal to me or I must miss sth from
your post.
Mel|||configuration as as you described it.
In our situation however the data was loaded -not altered- and then
re-indexed and then the proportion is no longer 50-50.
tanx,
Dick
"MSLam" wrote:
> Does it mean that your db has 2 filegroup - one for the data and one
> for the indexes. And after you loaded the data in, they were
> distributed equally. But after the reindex, the proportion changed and
> no longer half half?
> Index has two kind - clustered and non-clustered. Clustered is with
> tables (data itself stored in an order of the index). Non-Clustered
> can be stored somewhere else, which I believe you put it into another
> filegroup. The size of non-clustered index varies depends on what
> columns you have indexed on. The more columns you index, the bigger
> the index.
> After re-index, the size will change (unless no modification has been
> made). So what you observe is normal to me or I must miss sth from
> your post.
> Mel
>|||Dick,
When you said data was loaded? Did you put index on before the data
was loaded? Did you turn it on afterwards? What tool did you use to
load the data in?
Mel|||Hi Lam,
Data is loaded with regular SQL stored-procs.
All tables/index definitions where in place when data was loaded, no changes
in table definitions were made after the load.
After the load the dbcc reindex was executed.
cheers,
Dick
"MSLam" wrote:
> Dick,
> When you said data was loaded? Did you put index on before the data
> was loaded? Did you turn it on afterwards? What tool did you use to
> load the data in?
> Mel
>|||The only thing I could think of is the fillfactor field. The
fillfactor may have be changed during the rebuild?
fillfactor
Is the percentage of space on each index page to be used for storing
data when the index is created. fillfactor replaces the original
fillfactor as the new default for the index and for any other
nonclustered indexes rebuilt because a clustered index is rebuilt. When
fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
when the index was created.
I assume you use DBCC REINDEX.
Mel|||Hi,
Did an additional DBCC DBREINDEX ( 'table_name' [,'index_name' [ ,
fillfactor ] ]) on all tables with same fill factor as the tables were
created with, and still
experiencing and uneven spread of data (300MB files 15%-65% spreading), not
off indexes.
Dick
"MSLam" wrote:
> The only thing I could think of is the fillfactor field. The
> fillfactor may have be changed during the rebuild?
> fillfactor
> Is the percentage of space on each index page to be used for storing
> data when the index is created. fillfactor replaces the original
> fillfactor as the new default for the index and for any other
> nonclustered indexes rebuilt because a clustered index is rebuilt. When
> fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
> when the index was created.
> I assume you use DBCC REINDEX.
> Mel
>
We've installed a db with filegroups for data and indexes.
Each filegroup has two equaly sized files spread over two diskdevices.
When data is loaded it's spread equally over the files, if we do a reindex
over all tables and look (via Tasks/Shrink/Files) at the file useage
it has changed from equally spread 50-50% to 65-35%.
any ideas?
Derk JanDoes it mean that your db has 2 filegroup - one for the data and one
for the indexes. And after you loaded the data in, they were
distributed equally. But after the reindex, the proportion changed and
no longer half half?
Index has two kind - clustered and non-clustered. Clustered is with
tables (data itself stored in an order of the index). Non-Clustered
can be stored somewhere else, which I believe you put it into another
filegroup. The size of non-clustered index varies depends on what
columns you have indexed on. The more columns you index, the bigger
the index.
After re-index, the size will change (unless no modification has been
made). So what you observe is normal to me or I must miss sth from
your post.
Mel|||configuration as as you described it.
In our situation however the data was loaded -not altered- and then
re-indexed and then the proportion is no longer 50-50.
tanx,
Dick
"MSLam" wrote:
> Does it mean that your db has 2 filegroup - one for the data and one
> for the indexes. And after you loaded the data in, they were
> distributed equally. But after the reindex, the proportion changed and
> no longer half half?
> Index has two kind - clustered and non-clustered. Clustered is with
> tables (data itself stored in an order of the index). Non-Clustered
> can be stored somewhere else, which I believe you put it into another
> filegroup. The size of non-clustered index varies depends on what
> columns you have indexed on. The more columns you index, the bigger
> the index.
> After re-index, the size will change (unless no modification has been
> made). So what you observe is normal to me or I must miss sth from
> your post.
> Mel
>|||Dick,
When you said data was loaded? Did you put index on before the data
was loaded? Did you turn it on afterwards? What tool did you use to
load the data in?
Mel|||Hi Lam,
Data is loaded with regular SQL stored-procs.
All tables/index definitions where in place when data was loaded, no changes
in table definitions were made after the load.
After the load the dbcc reindex was executed.
cheers,
Dick
"MSLam" wrote:
> Dick,
> When you said data was loaded? Did you put index on before the data
> was loaded? Did you turn it on afterwards? What tool did you use to
> load the data in?
> Mel
>|||The only thing I could think of is the fillfactor field. The
fillfactor may have be changed during the rebuild?
fillfactor
Is the percentage of space on each index page to be used for storing
data when the index is created. fillfactor replaces the original
fillfactor as the new default for the index and for any other
nonclustered indexes rebuilt because a clustered index is rebuilt. When
fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
when the index was created.
I assume you use DBCC REINDEX.
Mel|||Hi,
Did an additional DBCC DBREINDEX ( 'table_name' [,'index_name' [ ,
fillfactor ] ]) on all tables with same fill factor as the tables were
created with, and still
experiencing and uneven spread of data (300MB files 15%-65% spreading), not
off indexes.
Dick
"MSLam" wrote:
> The only thing I could think of is the fillfactor field. The
> fillfactor may have be changed during the rebuild?
> fillfactor
> Is the percentage of space on each index page to be used for storing
> data when the index is created. fillfactor replaces the original
> fillfactor as the new default for the index and for any other
> nonclustered indexes rebuilt because a clustered index is rebuilt. When
> fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
> when the index was created.
> I assume you use DBCC REINDEX.
> Mel
>
filegroups / reindex
Hi,
We've installed a db with filegroups for data and indexes.
Each filegroup has two equaly sized files spread over two diskdevices.
When data is loaded it's spread equally over the files, if we do a reindex
over all tables and look (via Tasks/Shrink/Files) at the file useage
it has changed from equally spread 50-50% to 65-35%.
any ideas?
Derk JanDoes it mean that your db has 2 filegroup - one for the data and one
for the indexes. And after you loaded the data in, they were
distributed equally. But after the reindex, the proportion changed and
no longer half half?
Index has two kind - clustered and non-clustered. Clustered is with
tables (data itself stored in an order of the index). Non-Clustered
can be stored somewhere else, which I believe you put it into another
filegroup. The size of non-clustered index varies depends on what
columns you have indexed on. The more columns you index, the bigger
the index.
After re-index, the size will change (unless no modification has been
made). So what you observe is normal to me or I must miss sth from
your post.
Mel|||configuration as as you described it.
In our situation however the data was loaded -not altered- and then
re-indexed and then the proportion is no longer 50-50.
tanx,
Dick
"MSLam" wrote:
> Does it mean that your db has 2 filegroup - one for the data and one
> for the indexes. And after you loaded the data in, they were
> distributed equally. But after the reindex, the proportion changed and
> no longer half half?
> Index has two kind - clustered and non-clustered. Clustered is with
> tables (data itself stored in an order of the index). Non-Clustered
> can be stored somewhere else, which I believe you put it into another
> filegroup. The size of non-clustered index varies depends on what
> columns you have indexed on. The more columns you index, the bigger
> the index.
> After re-index, the size will change (unless no modification has been
> made). So what you observe is normal to me or I must miss sth from
> your post.
> Mel
>|||Dick,
When you said data was loaded? Did you put index on before the data
was loaded? Did you turn it on afterwards? What tool did you use to
load the data in?
Mel|||Hi Lam,
Data is loaded with regular SQL stored-procs.
All tables/index definitions where in place when data was loaded, no changes
in table definitions were made after the load.
After the load the dbcc reindex was executed.
cheers,
Dick
"MSLam" wrote:
> Dick,
> When you said data was loaded? Did you put index on before the data
> was loaded? Did you turn it on afterwards? What tool did you use to
> load the data in?
> Mel
>|||The only thing I could think of is the fillfactor field. The
fillfactor may have be changed during the rebuild?
fillfactor
Is the percentage of space on each index page to be used for storing
data when the index is created. fillfactor replaces the original
fillfactor as the new default for the index and for any other
nonclustered indexes rebuilt because a clustered index is rebuilt. When
fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
when the index was created.
I assume you use DBCC REINDEX.
Mel|||Hi,
Did an additional DBCC DBREINDEX ( 'table_name' [,'index_name' [ ,
fillfactor ] ]) on all tables with same fill factor as the tables were
created with, and still
experiencing and uneven spread of data (300MB files 15%-65% spreading), not
off indexes.
Dick
"MSLam" wrote:
> The only thing I could think of is the fillfactor field. The
> fillfactor may have be changed during the rebuild?
> fillfactor
> Is the percentage of space on each index page to be used for storing
> data when the index is created. fillfactor replaces the original
> fillfactor as the new default for the index and for any other
> nonclustered indexes rebuilt because a clustered index is rebuilt. When
> fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
> when the index was created.
> I assume you use DBCC REINDEX.
> Mel
>
We've installed a db with filegroups for data and indexes.
Each filegroup has two equaly sized files spread over two diskdevices.
When data is loaded it's spread equally over the files, if we do a reindex
over all tables and look (via Tasks/Shrink/Files) at the file useage
it has changed from equally spread 50-50% to 65-35%.
any ideas?
Derk JanDoes it mean that your db has 2 filegroup - one for the data and one
for the indexes. And after you loaded the data in, they were
distributed equally. But after the reindex, the proportion changed and
no longer half half?
Index has two kind - clustered and non-clustered. Clustered is with
tables (data itself stored in an order of the index). Non-Clustered
can be stored somewhere else, which I believe you put it into another
filegroup. The size of non-clustered index varies depends on what
columns you have indexed on. The more columns you index, the bigger
the index.
After re-index, the size will change (unless no modification has been
made). So what you observe is normal to me or I must miss sth from
your post.
Mel|||configuration as as you described it.
In our situation however the data was loaded -not altered- and then
re-indexed and then the proportion is no longer 50-50.
tanx,
Dick
"MSLam" wrote:
> Does it mean that your db has 2 filegroup - one for the data and one
> for the indexes. And after you loaded the data in, they were
> distributed equally. But after the reindex, the proportion changed and
> no longer half half?
> Index has two kind - clustered and non-clustered. Clustered is with
> tables (data itself stored in an order of the index). Non-Clustered
> can be stored somewhere else, which I believe you put it into another
> filegroup. The size of non-clustered index varies depends on what
> columns you have indexed on. The more columns you index, the bigger
> the index.
> After re-index, the size will change (unless no modification has been
> made). So what you observe is normal to me or I must miss sth from
> your post.
> Mel
>|||Dick,
When you said data was loaded? Did you put index on before the data
was loaded? Did you turn it on afterwards? What tool did you use to
load the data in?
Mel|||Hi Lam,
Data is loaded with regular SQL stored-procs.
All tables/index definitions where in place when data was loaded, no changes
in table definitions were made after the load.
After the load the dbcc reindex was executed.
cheers,
Dick
"MSLam" wrote:
> Dick,
> When you said data was loaded? Did you put index on before the data
> was loaded? Did you turn it on afterwards? What tool did you use to
> load the data in?
> Mel
>|||The only thing I could think of is the fillfactor field. The
fillfactor may have be changed during the rebuild?
fillfactor
Is the percentage of space on each index page to be used for storing
data when the index is created. fillfactor replaces the original
fillfactor as the new default for the index and for any other
nonclustered indexes rebuilt because a clustered index is rebuilt. When
fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
when the index was created.
I assume you use DBCC REINDEX.
Mel|||Hi,
Did an additional DBCC DBREINDEX ( 'table_name' [,'index_name' [ ,
fillfactor ] ]) on all tables with same fill factor as the tables were
created with, and still
experiencing and uneven spread of data (300MB files 15%-65% spreading), not
off indexes.
Dick
"MSLam" wrote:
> The only thing I could think of is the fillfactor field. The
> fillfactor may have be changed during the rebuild?
> fillfactor
> Is the percentage of space on each index page to be used for storing
> data when the index is created. fillfactor replaces the original
> fillfactor as the new default for the index and for any other
> nonclustered indexes rebuilt because a clustered index is rebuilt. When
> fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
> when the index was created.
> I assume you use DBCC REINDEX.
> Mel
>
Wednesday, March 7, 2012
File system rights
I have setup a test IIS box and would like to use that as my development platform. I have installed sql express 2005 for my database on the box as well.
I can setup new pages on the file server just fine.. But when I try and setup a login page and use the asp.net configuration web page to setup security which create a user DB it will not let me create it...
I get the following:
The following message may help in diagnosing the problem:An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 5110 and the SqlException message is: The file "J:\INETPUB\NBSRECAP\APP_DATA\ASPNETDB_TMP.MDF" is on a network path that is not supported for database files. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. Creating the ASPNETDB_4a9331c5903148ca92e3ffbee7d29976 database...
Can someone let me know.
Are you trying to create a database on a network path? This is not supported by default, however you can open a trace flag to allow this:dbcc traceon(1807,-1)
go
create database testUNC on (name='testUNC_data',
filename='\\iori\xxx\testUNC.mdf')
log on (name='testUNC_log',
filename='\\iori\xxx\testUNC.ldf')
Sunday, February 26, 2012
File placement on server with 2 instances of SQL
I have inherited a stand alone server with 2 instances of SQL installed.
There are 6 physical disks in the server which have been made into 3 mirrors.
The first mirror has the OS on it. Currently, the 2nd disk has all the
database and transaction log files from both instances of SQL.
I plan to make use of the 3rd disk. My question is: is it better to move the
database and transaction log files from the second instance to the new disk
so that all the files for the first instance are on disk 2 and all the files
for the 2nd instance are on disk 3 OR is it better to keep all the database
files from both instances on disk 2 and move all the log files for both
instances to disk 3?
I'm sure I have read somehwere that in this situation, the disks should be
separated by instance rather than seperating by file type.Seen from the databases' perspective, same or different instances doesn't really make a difference.
As usual, only testing with a realistic workload can give you a definitive answer. My guess, though,
is to have the tlog files separated.
Unless you have some pretty unusual workload. Like only reads during day and modifications during
night. And it doesn't matter that modifications take a bit longer. Then you might consider having db
1 (all db files) on one raid and db 2 (all files) on another. That would mean that the read
operations during day would not compete between db1 and db2.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>I have inherited a stand alone server with 2 instances of SQL installed.
> There are 6 physical disks in the server which have been made into 3 mirrors.
> The first mirror has the OS on it. Currently, the 2nd disk has all the
> database and transaction log files from both instances of SQL.
> I plan to make use of the 3rd disk. My question is: is it better to move the
> database and transaction log files from the second instance to the new disk
> so that all the files for the first instance are on disk 2 and all the files
> for the 2nd instance are on disk 3 OR is it better to keep all the database
> files from both instances on disk 2 and move all the log files for both
> instances to disk 3?
> I'm sure I have read somehwere that in this situation, the disks should be
> separated by instance rather than seperating by file type.
>|||+1 to both of Tibor's sentiments.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e7sqUlyXIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Seen from the databases' perspective, same or different instances doesn't
> really make a difference. As usual, only testing with a realistic workload
> can give you a definitive answer. My guess, though, is to have the tlog
> files separated.
> Unless you have some pretty unusual workload. Like only reads during day
> and modifications during night. And it doesn't matter that modifications
> take a bit longer. Then you might consider having db 1 (all db files) on
> one raid and db 2 (all files) on another. That would mean that the read
> operations during day would not compete between db1 and db2.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stu" <Stu@.discussions.microsoft.com> wrote in message
> news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>>I have inherited a stand alone server with 2 instances of SQL installed.
>> There are 6 physical disks in the server which have been made into 3
>> mirrors.
>> The first mirror has the OS on it. Currently, the 2nd disk has all the
>> database and transaction log files from both instances of SQL.
>> I plan to make use of the 3rd disk. My question is: is it better to move
>> the
>> database and transaction log files from the second instance to the new
>> disk
>> so that all the files for the first instance are on disk 2 and all the
>> files
>> for the 2nd instance are on disk 3 OR is it better to keep all the
>> database
>> files from both instances on disk 2 and move all the log files for both
>> instances to disk 3?
>> I'm sure I have read somehwere that in this situation, the disks should
>> be
>> separated by instance rather than seperating by file type.
>|||I also second Tibor's response. Especially the part about the only
difinitive answer will come from testing.
And don't forget there is a third option. That's to put the data from DB1
and the log from DB2 on Disk2 and the log from DB1 and the data from DB2 on
Disk3. That might help in a situation where you have primarily reads on
both databases during the day and updates during the night and almost all
the updates are done to one database.
Tom
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e7sqUlyXIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Seen from the databases' perspective, same or different instances doesn't
> really make a difference. As usual, only testing with a realistic workload
> can give you a definitive answer. My guess, though, is to have the tlog
> files separated.
> Unless you have some pretty unusual workload. Like only reads during day
> and modifications during night. And it doesn't matter that modifications
> take a bit longer. Then you might consider having db 1 (all db files) on
> one raid and db 2 (all files) on another. That would mean that the read
> operations during day would not compete between db1 and db2.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stu" <Stu@.discussions.microsoft.com> wrote in message
> news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>>I have inherited a stand alone server with 2 instances of SQL installed.
>> There are 6 physical disks in the server which have been made into 3
>> mirrors.
>> The first mirror has the OS on it. Currently, the 2nd disk has all the
>> database and transaction log files from both instances of SQL.
>> I plan to make use of the 3rd disk. My question is: is it better to move
>> the
>> database and transaction log files from the second instance to the new
>> disk
>> so that all the files for the first instance are on disk 2 and all the
>> files
>> for the 2nd instance are on disk 3 OR is it better to keep all the
>> database
>> files from both instances on disk 2 and move all the log files for both
>> instances to disk 3?
>> I'm sure I have read somehwere that in this situation, the disks should
>> be
>> separated by instance rather than seperating by file type.
>
There are 6 physical disks in the server which have been made into 3 mirrors.
The first mirror has the OS on it. Currently, the 2nd disk has all the
database and transaction log files from both instances of SQL.
I plan to make use of the 3rd disk. My question is: is it better to move the
database and transaction log files from the second instance to the new disk
so that all the files for the first instance are on disk 2 and all the files
for the 2nd instance are on disk 3 OR is it better to keep all the database
files from both instances on disk 2 and move all the log files for both
instances to disk 3?
I'm sure I have read somehwere that in this situation, the disks should be
separated by instance rather than seperating by file type.Seen from the databases' perspective, same or different instances doesn't really make a difference.
As usual, only testing with a realistic workload can give you a definitive answer. My guess, though,
is to have the tlog files separated.
Unless you have some pretty unusual workload. Like only reads during day and modifications during
night. And it doesn't matter that modifications take a bit longer. Then you might consider having db
1 (all db files) on one raid and db 2 (all files) on another. That would mean that the read
operations during day would not compete between db1 and db2.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>I have inherited a stand alone server with 2 instances of SQL installed.
> There are 6 physical disks in the server which have been made into 3 mirrors.
> The first mirror has the OS on it. Currently, the 2nd disk has all the
> database and transaction log files from both instances of SQL.
> I plan to make use of the 3rd disk. My question is: is it better to move the
> database and transaction log files from the second instance to the new disk
> so that all the files for the first instance are on disk 2 and all the files
> for the 2nd instance are on disk 3 OR is it better to keep all the database
> files from both instances on disk 2 and move all the log files for both
> instances to disk 3?
> I'm sure I have read somehwere that in this situation, the disks should be
> separated by instance rather than seperating by file type.
>|||+1 to both of Tibor's sentiments.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e7sqUlyXIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Seen from the databases' perspective, same or different instances doesn't
> really make a difference. As usual, only testing with a realistic workload
> can give you a definitive answer. My guess, though, is to have the tlog
> files separated.
> Unless you have some pretty unusual workload. Like only reads during day
> and modifications during night. And it doesn't matter that modifications
> take a bit longer. Then you might consider having db 1 (all db files) on
> one raid and db 2 (all files) on another. That would mean that the read
> operations during day would not compete between db1 and db2.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stu" <Stu@.discussions.microsoft.com> wrote in message
> news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>>I have inherited a stand alone server with 2 instances of SQL installed.
>> There are 6 physical disks in the server which have been made into 3
>> mirrors.
>> The first mirror has the OS on it. Currently, the 2nd disk has all the
>> database and transaction log files from both instances of SQL.
>> I plan to make use of the 3rd disk. My question is: is it better to move
>> the
>> database and transaction log files from the second instance to the new
>> disk
>> so that all the files for the first instance are on disk 2 and all the
>> files
>> for the 2nd instance are on disk 3 OR is it better to keep all the
>> database
>> files from both instances on disk 2 and move all the log files for both
>> instances to disk 3?
>> I'm sure I have read somehwere that in this situation, the disks should
>> be
>> separated by instance rather than seperating by file type.
>|||I also second Tibor's response. Especially the part about the only
difinitive answer will come from testing.
And don't forget there is a third option. That's to put the data from DB1
and the log from DB2 on Disk2 and the log from DB1 and the data from DB2 on
Disk3. That might help in a situation where you have primarily reads on
both databases during the day and updates during the night and almost all
the updates are done to one database.
Tom
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e7sqUlyXIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Seen from the databases' perspective, same or different instances doesn't
> really make a difference. As usual, only testing with a realistic workload
> can give you a definitive answer. My guess, though, is to have the tlog
> files separated.
> Unless you have some pretty unusual workload. Like only reads during day
> and modifications during night. And it doesn't matter that modifications
> take a bit longer. Then you might consider having db 1 (all db files) on
> one raid and db 2 (all files) on another. That would mean that the read
> operations during day would not compete between db1 and db2.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stu" <Stu@.discussions.microsoft.com> wrote in message
> news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>>I have inherited a stand alone server with 2 instances of SQL installed.
>> There are 6 physical disks in the server which have been made into 3
>> mirrors.
>> The first mirror has the OS on it. Currently, the 2nd disk has all the
>> database and transaction log files from both instances of SQL.
>> I plan to make use of the 3rd disk. My question is: is it better to move
>> the
>> database and transaction log files from the second instance to the new
>> disk
>> so that all the files for the first instance are on disk 2 and all the
>> files
>> for the 2nd instance are on disk 3 OR is it better to keep all the
>> database
>> files from both instances on disk 2 and move all the log files for both
>> instances to disk 3?
>> I'm sure I have read somehwere that in this situation, the disks should
>> be
>> separated by instance rather than seperating by file type.
>
Friday, February 24, 2012
File Not Found error.
I downloaded and installed sql express yesterday. After I rebooted I get a File not Found error.
Any Help would be great.
ThanksWe'd need more info to do anything with this. Please post again if you're still having a problem here.
File Not Found error.
I downloaded and installed sql express yesterday. After I rebooted I get a File not Found error.
Any Help would be great.
ThanksWe'd need more info to do anything with this. Please post again if you're still having a problem here.
Subscribe to:
Posts (Atom)