Does placing the transaction log on dedicated RAID volume make sense with Simple Recovery

Hi,
I'm trying to get up to speed on SQL Server and data storage solutions.
I've read many posts which indicate that the transaction log should be
placed on it's own dedicated volume. I think that I understand the
rationale: Writes to the log are sequential in nature and it's
counterproductive to have random I/O to the database interfere with these
sequential log writes.
Does this logic still hold true when using the Simple recovery model?
I've read a bit about this model and the documentation states that when
operating under the rules of this model, SQL Server will truncate the log
after each transaction. Doesn't this imply that writing to the log would NOT
be sequential in nature since each write begins at position x, the write
takes place, and then the drive must return to position x again for the next
write? Or is this protocol still considered a sequential write and should
therefore be isolated on a dedicated volume?
Thanks,
DavidLarry,
It holds for simple recovery mode as well. SQL Server doesn't truncate the log after each
transaction. It truncates after each time it performs a checkpoint (read about checkpoint in Books
Online).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Larry David" <invalid@.bogus.bum> wrote in message news:CsudnU16LPcHoqrfRVn-hA@.giganews.com...
> Hi,
> I'm trying to get up to speed on SQL Server and data storage solutions.
> I've read many posts which indicate that the transaction log should be
> placed on it's own dedicated volume. I think that I understand the
> rationale: Writes to the log are sequential in nature and it's
> counterproductive to have random I/O to the database interfere with these
> sequential log writes.
> Does this logic still hold true when using the Simple recovery model?
> I've read a bit about this model and the documentation states that when
> operating under the rules of this model, SQL Server will truncate the log
> after each transaction. Doesn't this imply that writing to the log would NOT
> be sequential in nature since each write begins at position x, the write
> takes place, and then the drive must return to position x again for the next
> write? Or is this protocol still considered a sequential write and should
> therefore be isolated on a dedicated volume?
> Thanks,
> David
>
>sql

Does placing the transaction log on dedicated RAID volume make sense with Simple Recovery

Hi,
I'm trying to get up to speed on SQL Server and data storage solutions.
I've read many posts which indicate that the transaction log should be
placed on it's own dedicated volume. I think that I understand the
rationale: Writes to the log are sequential in nature and it's
counterproductive to have random I/O to the database interfere with these
sequential log writes.
Does this logic still hold true when using the Simple recovery model?
I've read a bit about this model and the documentation states that when
operating under the rules of this model, SQL Server will truncate the log
after each transaction. Doesn't this imply that writing to the log would NOT
be sequential in nature since each write begins at position x, the write
takes place, and then the drive must return to position x again for the next
write? Or is this protocol still considered a sequential write and should
therefore be isolated on a dedicated volume?
Thanks,
David
Larry,
It holds for simple recovery mode as well. SQL Server doesn't truncate the log after each
transaction. It truncates after each time it performs a checkpoint (read about checkpoint in Books
Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Larry David" <invalid@.bogus.bum> wrote in message news:CsudnU16LPcHoqrfRVn-hA@.giganews.com...
> Hi,
> I'm trying to get up to speed on SQL Server and data storage solutions.
> I've read many posts which indicate that the transaction log should be
> placed on it's own dedicated volume. I think that I understand the
> rationale: Writes to the log are sequential in nature and it's
> counterproductive to have random I/O to the database interfere with these
> sequential log writes.
> Does this logic still hold true when using the Simple recovery model?
> I've read a bit about this model and the documentation states that when
> operating under the rules of this model, SQL Server will truncate the log
> after each transaction. Doesn't this imply that writing to the log would NOT
> be sequential in nature since each write begins at position x, the write
> takes place, and then the drive must return to position x again for the next
> write? Or is this protocol still considered a sequential write and should
> therefore be isolated on a dedicated volume?
> Thanks,
> David
>
>

Does placing the transaction log on dedicated RAID volume make sense with Simple Recov

Hi,
I'm trying to get up to speed on SQL Server and data storage solutions.
I've read many posts which indicate that the transaction log should be
placed on it's own dedicated volume. I think that I understand the
rationale: Writes to the log are sequential in nature and it's
counterproductive to have random I/O to the database interfere with these
sequential log writes.
Does this logic still hold true when using the Simple recovery model?
I've read a bit about this model and the documentation states that when
operating under the rules of this model, SQL Server will truncate the log
after each transaction. Doesn't this imply that writing to the log would NOT
be sequential in nature since each write begins at position x, the write
takes place, and then the drive must return to position x again for the next
write? Or is this protocol still considered a sequential write and should
therefore be isolated on a dedicated volume?
Thanks,
DavidLarry,
It holds for simple recovery mode as well. SQL Server doesn't truncate the l
og after each
transaction. It truncates after each time it performs a checkpoint (read abo
ut checkpoint in Books
Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Larry David" <invalid@.bogus.bum> wrote in message news:CsudnU16LPcHoqrfRVn-hA@.giganews.com.
.
> Hi,
> I'm trying to get up to speed on SQL Server and data storage solutions.
> I've read many posts which indicate that the transaction log should be
> placed on it's own dedicated volume. I think that I understand the
> rationale: Writes to the log are sequential in nature and it's
> counterproductive to have random I/O to the database interfere with these
> sequential log writes.
> Does this logic still hold true when using the Simple recovery model?
> I've read a bit about this model and the documentation states that when
> operating under the rules of this model, SQL Server will truncate the log
> after each transaction. Doesn't this imply that writing to the log would N
OT
> be sequential in nature since each write begins at position x, the write
> takes place, and then the drive must return to position x again for the ne
xt
> write? Or is this protocol still considered a sequential write and should
> therefore be isolated on a dedicated volume?
> Thanks,
> David
>
>

Does 'Group By' affect the query speed?

i have a table such sa below:

Name1, Name2, Name3, Nam4, C1, C2,.., C100

and in this table, i have found index for Name1-Nam4,

i don't why sql below is very slow?

select
Name1, sum(C1), ...., Sum(C100)
from
(
select
Name1, Name2, sum(C1) as C1, ...., Sum(C100) as C100
from
(
select
Name1, Name2, Name3, sum(C1) as C1, ...., Sum(C100) as C100
from
(
select
Name1, Name2, Name3, Name4, C1, ...., C100
from
My_Table
group by Name1, Name2, Name3, Name4
) as T
group by Name1, Name2, Nam3
) as T
group by Name1, Name2
) as T
group by Name1

Does 'Group By' affect the speed of query?

Yes... It depends with your number of data...

I found your query is strange...

Why not, can you try the following query..

select
Name1, sum(C1), ...., Sum(C100)
from
My_Table
group by Name1

Bcs.. finally you are going to get only the Name1 data...

If you need kind of Rolling up data... use ROLLUP instead of multiple Subqueries...

Does a View of an Indexed Table has a View?

Hi,
I have a View, which is actually a Union of some joins of some Tables. All
these Tables have indexes.
Will these indexes speed up a query when doing a Select on the View? Or will
the view behave as a non-indexed table? Should I take in account a lack of
performance when using the view?
any answers, explanations, workarounds for this are welcome!
Thanks a lot in advance,
Pieter
Pieter wrote:
> Hi,
> I have a View, which is actually a Union of some joins of some
> Tables. All these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View?
> Or will the view behave as a non-indexed table? Should I take in
> account a lack of performance when using the view?
Indexes are used if appropriate for your query criteria. You can see this
by looking at the execution plan with QA.
Kind regards
robert
|||Hi Pieter
Views are virtual tables and they are not actual tables. When you try to
query a view, u will actually be executing a query.
The query will use the same indexes that a table is using.
However u can create an index on views, creating an index on a view will
effect that query only and not the main table
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
"Pieter" wrote:

> Hi,
> I have a View, which is actually a Union of some joins of some Tables. All
> these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View? Or will
> the view behave as a non-indexed table? Should I take in account a lack of
> performance when using the view?
> any answers, explanations, workarounds for this are welcome!
> Thanks a lot in advance,
> Pieter
>
>
|||Pieter,

> Will these indexes speed up a query when doing a Select on the View?
The answer is "it depends". i.e., what columns are you asking for? what
rows? do the index(es) match the row criteria? are the columns highly
selective? etc.. etc...
If the optimizer determines that an index(es) can assist in accessing the
data from the underlying tables when the view is queried, then the optimizer
will likely use the index(es). Use the Display Estimated Execution Plan
(Ctrl+L) to determine the predicted access method.
Creating an index on a view (clustered) will make a copy of the data. This
approach is useful when the underlying data does not change very often and
aggregates are involved.
HTH
Jerry
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:Olj4WGX2FHA.2436@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a View, which is actually a Union of some joins of some Tables. All
> these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View? Or
> will the view behave as a non-indexed table? Should I take in account a
> lack of performance when using the view?
> any answers, explanations, workarounds for this are welcome!
> Thanks a lot in advance,
> Pieter
>
|||Ok thanks a lot for the info!
One more question: In the enterprise Manager the option "All Tasks" ->
"Manage Indexes" is disabled when right-clicking on the View. Is the only
way to manage indexes on a View via the query analyzer?
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:EFC3D7B8-44AE-4721-9875-9EFF46F72190@.microsoft.com...[vbcol=seagreen]
> Hi Pieter
> Views are virtual tables and they are not actual tables. When you try to
> query a view, u will actually be executing a query.
> The query will use the same indexes that a table is using.
> However u can create an index on views, creating an index on a view will
> effect that query only and not the main table
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
>
> "Pieter" wrote:

Does a View of an Indexed Table has a View?

Hi,
I have a View, which is actually a Union of some joins of some Tables. All
these Tables have indexes.
Will these indexes speed up a query when doing a Select on the View? Or will
the view behave as a non-indexed table? Should I take in account a lack of
performance when using the view?
any answers, explanations, workarounds for this are welcome!
Thanks a lot in advance,
PieterPieter wrote:
> Hi,
> I have a View, which is actually a Union of some joins of some
> Tables. All these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View?
> Or will the view behave as a non-indexed table? Should I take in
> account a lack of performance when using the view?
Indexes are used if appropriate for your query criteria. You can see this
by looking at the execution plan with QA.
Kind regards
robert|||Hi Pieter
Views are virtual tables and they are not actual tables. When you try to
query a view, u will actually be executing a query.
The query will use the same indexes that a table is using.
However u can create an index on views, creating an index on a view will
effect that query only and not the main table
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Pieter" wrote:
> Hi,
> I have a View, which is actually a Union of some joins of some Tables. All
> these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View? Or will
> the view behave as a non-indexed table? Should I take in account a lack of
> performance when using the view?
> any answers, explanations, workarounds for this are welcome!
> Thanks a lot in advance,
> Pieter
>
>|||Pieter,
> Will these indexes speed up a query when doing a Select on the View?
The answer is "it depends". i.e., what columns are you asking for? what
rows? do the index(es) match the row criteria? are the columns highly
selective? etc.. etc...
If the optimizer determines that an index(es) can assist in accessing the
data from the underlying tables when the view is queried, then the optimizer
will likely use the index(es). Use the Display Estimated Execution Plan
(Ctrl+L) to determine the predicted access method.
Creating an index on a view (clustered) will make a copy of the data. This
approach is useful when the underlying data does not change very often and
aggregates are involved.
HTH
Jerry
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:Olj4WGX2FHA.2436@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a View, which is actually a Union of some joins of some Tables. All
> these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View? Or
> will the view behave as a non-indexed table? Should I take in account a
> lack of performance when using the view?
> any answers, explanations, workarounds for this are welcome!
> Thanks a lot in advance,
> Pieter
>|||Ok thanks a lot for the info!
One more question: In the enterprise Manager the option "All Tasks" ->
"Manage Indexes" is disabled when right-clicking on the View. Is the only
way to manage indexes on a View via the query analyzer?
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:EFC3D7B8-44AE-4721-9875-9EFF46F72190@.microsoft.com...
> Hi Pieter
> Views are virtual tables and they are not actual tables. When you try to
> query a view, u will actually be executing a query.
> The query will use the same indexes that a table is using.
> However u can create an index on views, creating an index on a view will
> effect that query only and not the main table
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Pieter" wrote:
>> Hi,
>> I have a View, which is actually a Union of some joins of some Tables.
>> All
>> these Tables have indexes.
>> Will these indexes speed up a query when doing a Select on the View? Or
>> will
>> the view behave as a non-indexed table? Should I take in account a lack
>> of
>> performance when using the view?
>> any answers, explanations, workarounds for this are welcome!
>> Thanks a lot in advance,
>> Pieter
>>

Does a View of an Indexed Table has a View?

Hi,
I have a View, which is actually a Union of some joins of some Tables. All
these Tables have indexes.
Will these indexes speed up a query when doing a Select on the View? Or will
the view behave as a non-indexed table? Should I take in account a lack of
performance when using the view?
any answers, explanations, workarounds for this are welcome!
Thanks a lot in advance,
PieterPieter wrote:
> Hi,
> I have a View, which is actually a Union of some joins of some
> Tables. All these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View?
> Or will the view behave as a non-indexed table? Should I take in
> account a lack of performance when using the view?
Indexes are used if appropriate for your query criteria. You can see this
by looking at the execution plan with QA.
Kind regards
robert|||Hi Pieter
Views are virtual tables and they are not actual tables. When you try to
query a view, u will actually be executing a query.
The query will use the same indexes that a table is using.
However u can create an index on views, creating an index on a view will
effect that query only and not the main table
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Pieter" wrote:

> Hi,
> I have a View, which is actually a Union of some joins of some Tables. All
> these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View? Or wi
ll
> the view behave as a non-indexed table? Should I take in account a lack of
> performance when using the view?
> any answers, explanations, workarounds for this are welcome!
> Thanks a lot in advance,
> Pieter
>
>|||Pieter,

> Will these indexes speed up a query when doing a Select on the View?
The answer is "it depends". i.e., what columns are you asking for? what
rows? do the index(es) match the row criteria? are the columns highly
selective? etc.. etc...
If the optimizer determines that an index(es) can assist in accessing the
data from the underlying tables when the view is queried, then the optimizer
will likely use the index(es). Use the Display Estimated Execution Plan
(Ctrl+L) to determine the predicted access method.
Creating an index on a view (clustered) will make a copy of the data. This
approach is useful when the underlying data does not change very often and
aggregates are involved.
HTH
Jerry
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:Olj4WGX2FHA.2436@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a View, which is actually a Union of some joins of some Tables. All
> these Tables have indexes.
> Will these indexes speed up a query when doing a Select on the View? Or
> will the view behave as a non-indexed table? Should I take in account a
> lack of performance when using the view?
> any answers, explanations, workarounds for this are welcome!
> Thanks a lot in advance,
> Pieter
>|||Ok thanks a lot for the info!
One more question: In the enterprise Manager the option "All Tasks" ->
"Manage Indexes" is disabled when right-clicking on the View. Is the only
way to manage indexes on a View via the query analyzer?
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:EFC3D7B8-44AE-4721-9875-9EFF46F72190@.microsoft.com...[vbcol=seagreen]
> Hi Pieter
> Views are virtual tables and they are not actual tables. When you try to
> query a view, u will actually be executing a query.
> The query will use the same indexes that a table is using.
> However u can create an index on views, creating an index on a view will
> effect that query only and not the main table
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Pieter" wrote:
>