info about file or filegroup last change date?

Hi, all:
Is there any way we can use to find the time when the last change happened
to the file or filegroup?
Thanks in advance
There is nothing built in to SQL Server that tracks changes to files, but
you can build, or buy, a solution that might fit your needs. What
specifically are you wanting to track?
"SQLNewbie" <SQLNewbie@.hotmail.com> wrote in message
news:eKpcK$5KEHA.240@.TK2MSFTNGP10.phx.gbl...
> Hi, all:
> Is there any way we can use to find the time when the last change happened
> to the file or filegroup?
> Thanks in advance
>
|||I just want to make sure if the file or filegroup is not changed since I
last backed up the file, if it's not, I don't need to recover it if there is
a problem with database. Instead, I will recover other file(s) or
filegroup(s).
Thanks
"Don Peterson" <no1@.nunya.com> wrote in message
news:#lENwQ7KEHA.2716@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> There is nothing built in to SQL Server that tracks changes to files, but
> you can build, or buy, a solution that might fit your needs. What
> specifically are you wanting to track?
> "SQLNewbie" <SQLNewbie@.hotmail.com> wrote in message
> news:eKpcK$5KEHA.240@.TK2MSFTNGP10.phx.gbl...
happened
>

info about file or filegroup last change date?

Hi, all:
Is there any way we can use to find the time when the last change happened
to the file or filegroup?
Thanks in advanceThere is nothing built in to SQL Server that tracks changes to files, but
you can build, or buy, a solution that might fit your needs. What
specifically are you wanting to track?
"SQLNewbie" <SQLNewbie@.hotmail.com> wrote in message
news:eKpcK$5KEHA.240@.TK2MSFTNGP10.phx.gbl...
> Hi, all:
> Is there any way we can use to find the time when the last change happened
> to the file or filegroup?
> Thanks in advance
>|||I just want to make sure if the file or filegroup is not changed since I
last backed up the file, if it's not, I don't need to recover it if there is
a problem with database. Instead, I will recover other file(s) or
filegroup(s).
Thanks
"Don Peterson" <no1@.nunya.com> wrote in message
news:#lENwQ7KEHA.2716@.tk2msftngp13.phx.gbl...
> There is nothing built in to SQL Server that tracks changes to files, but
> you can build, or buy, a solution that might fit your needs. What
> specifically are you wanting to track?
> "SQLNewbie" <SQLNewbie@.hotmail.com> wrote in message
> news:eKpcK$5KEHA.240@.TK2MSFTNGP10.phx.gbl...
> > Hi, all:
> >
> > Is there any way we can use to find the time when the last change
happened
> > to the file or filegroup?
> >
> > Thanks in advance
> >
> >
>

info about file or filegroup last change date?

Hi, all:
Is there any way we can use to find the time when the last change happened
to the file or filegroup?
Thanks in advanceThere is nothing built in to SQL Server that tracks changes to files, but
you can build, or buy, a solution that might fit your needs. What
specifically are you wanting to track?
"SQLNewbie" <SQLNewbie@.hotmail.com> wrote in message
news:eKpcK$5KEHA.240@.TK2MSFTNGP10.phx.gbl...
> Hi, all:
> Is there any way we can use to find the time when the last change happened
> to the file or filegroup?
> Thanks in advance
>|||I just want to make sure if the file or filegroup is not changed since I
last backed up the file, if it's not, I don't need to recover it if there is
a problem with database. Instead, I will recover other file(s) or
filegroup(s).
Thanks
"Don Peterson" <no1@.nunya.com> wrote in message
news:#lENwQ7KEHA.2716@.tk2msftngp13.phx.gbl...
> There is nothing built in to SQL Server that tracks changes to files, but
> you can build, or buy, a solution that might fit your needs. What
> specifically are you wanting to track?
> "SQLNewbie" <SQLNewbie@.hotmail.com> wrote in message
> news:eKpcK$5KEHA.240@.TK2MSFTNGP10.phx.gbl...
happened[vbcol=seagreen]
>

Infinite time to query

I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
it here.
Please let me know the correct group if there is any.
I am trying to query the Indexing Engine. Added a linked server
"FileSystem" and then created a view.
View definition is given below:
CREATE VIEW FileView
AS
SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
Characterization FROM SCOPE() ')
Any query to this view takes an infinite time. The query never stops.
Even "Count(*)" never returns. SQL profiler (when used in conjunction
with a long query containing join on this view) shows the query going
into an infinite recursion.
Any pointers to solve this issue would be very helpful.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com
Hi,
Does the simple SELECT using four part naming convention work?
For Ex:
SELECT * FROM <linked_server_name>.<DB_Name>.<User>.<Table_Name>
Btw, What Is SCOPE()?
- - - - - - - - -
Thanks
Yogish
"MasterGaurav" wrote:

> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>
|||MasterGaurav
What is SCOPE() ? Is that function?
SELECT <column list> FROM FileSystem.Database.dbo.Table/Function
"MasterGaurav" <gaurav.vaish@.gmail.com> wrote in message
news:1120729619.181576.238440@.g47g2000cwa.googlegr oups.com...
> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>
|||Yogish/Uri:
SCOPE() defines the scope on the file system. The query is, as I
said, related to Indexing Engine. The query is executed on the Indexing
Engine in the defined catalog.
SCOPE() defines all directories and subdirectories and files.
SCOPE can be something like SCOPE("D:\") and it will search for files
in only D-drive.
You may want to have a look at:
http://msdn.microsoft.com/library/de...filedatats.asp
Uri:
I'm refering to the query to the catalog in indexing engine. No
table/function here as in direct SQL server.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com

Infinite time to query

I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
it here.
Please let me know the correct group if there is any.
I am trying to query the Indexing Engine. Added a linked server
"FileSystem" and then created a view.
View definition is given below:
CREATE VIEW FileView
AS
SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
Characterization FROM SCOPE() ')
Any query to this view takes an infinite time. The query never stops.
Even "Count(*)" never returns. SQL profiler (when used in conjunction
with a long query containing join on this view) shows the query going
into an infinite recursion.
Any pointers to solve this issue would be very helpful.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com
--Hi,
Does the simple SELECT using four part naming convention work?
For Ex:
SELECT * FROM <linked_server_name>.<DB_Name>.<User>.<Table_Name>
Btw, What Is SCOPE()?
--
- - - - - - - - -
Thanks
Yogish
"MasterGaurav" wrote:

> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>|||MasterGaurav
What is SCOPE() ? Is that function?
SELECT <column list> FROM FileSystem.Database.dbo.Table/Function
"MasterGaurav" <gaurav.vaish@.gmail.com> wrote in message
news:1120729619.181576.238440@.g47g2000cwa.googlegroups.com...
> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>|||Yogish/Uri:
SCOPE() defines the scope on the file system. The query is, as I
said, related to Indexing Engine. The query is executed on the Indexing
Engine in the defined catalog.
SCOPE() defines all directories and subdirectories and files.
SCOPE can be something like SCOPE("D:\") and it will search for files
in only D-drive.
You may want to have a look at:
http://msdn.microsoft.com/library/d...r />
atats.asp
Uri:
I'm refering to the query to the catalog in indexing engine. No
table/function here as in direct SQL server.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com
--

Infinite time to query

I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
it here.
Please let me know the correct group if there is any.
I am trying to query the Indexing Engine. Added a linked server
"FileSystem" and then created a view.
View definition is given below:
CREATE VIEW FileView
AS
SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
Characterization FROM SCOPE() ')
Any query to this view takes an infinite time. The query never stops.
Even "Count(*)" never returns. SQL profiler (when used in conjunction
with a long query containing join on this view) shows the query going
into an infinite recursion.
Any pointers to solve this issue would be very helpful.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com
--Hi,
Does the simple SELECT using four part naming convention work?
For Ex:
SELECT * FROM <linked_server_name>.<DB_Name>.<User>.<Table_Name>
Btw, What Is SCOPE()?
--
- - - - - - - - -
Thanks
Yogish
"MasterGaurav" wrote:
> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>|||MasterGaurav
What is SCOPE() ? Is that function?
SELECT <column list> FROM FileSystem.Database.dbo.Table/Function
"MasterGaurav" <gaurav.vaish@.gmail.com> wrote in message
news:1120729619.181576.238440@.g47g2000cwa.googlegroups.com...
> I couldn't find any newsgroup for MS Indexing engine.. so, I am posting
> it here.
> Please let me know the correct group if there is any.
> I am trying to query the Indexing Engine. Added a linked server
> "FileSystem" and then created a view.
> View definition is given below:
> CREATE VIEW FileView
> AS
> SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
> Characterization FROM SCOPE() ')
>
> Any query to this view takes an infinite time. The query never stops.
> Even "Count(*)" never returns. SQL profiler (when used in conjunction
> with a long query containing join on this view) shows the query going
> into an infinite recursion.
>
> Any pointers to solve this issue would be very helpful.
>
>
> Cheers,
> Gaurav Vaish
> http://mastergaurav.org
> http://mastergaurav.blogspot.com
> --
>|||Yogish/Uri:
SCOPE() defines the scope on the file system. The query is, as I
said, related to Indexing Engine. The query is executed on the Indexing
Engine in the defined catalog.
SCOPE() defines all directories and subdirectories and files.
SCOPE can be something like SCOPE("D:\") and it will search for files
in only D-drive.
You may want to have a look at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/filedatats.asp
Uri:
I'm refering to the query to the catalog in indexing engine. No
table/function here as in direct SQL server.
Cheers,
Gaurav Vaish
http://mastergaurav.org
http://mastergaurav.blogspot.com
--

Indexing and partitioning of the tables.

We have 6 lac row records which will be increasing with time. So for
optimized performance please provide solution related to partitioning
and indexing of tables.
Is partitioning necessary for optimization of performance, keeping in
mind , future growth of records.
Karan
karanlobana@.gmail.com wrote:
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
Disclaimer: haven't use partitioning in SQL 2005 myself - only on Oracle
instances.
It depends on the volume and the queries you do. If volume is rather high
and queries always include a specific narrow time range then it's likely
that your app will benefit from partitioning.
robert
|||Forgive me but I don't know what "lac" means but please note that
partitioning in 2005 is primarily for data management not performance. That
does not mean that you can not get increased performance from a proper
partitioning scheme but that is usually not the primary goal. Partitioning
allow you to manage very large amounts of data much more easily and
efficient than if they were all in a single table or partition.
Andrew J. Kelly SQL MVP
<karanlobana@.gmail.com> wrote in message
news:1137392219.125403.291600@.g14g2000cwa.googlegr oups.com...
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
> Karan
>
|||Andrew J. Kelly wrote:
> Forgive me but I don't know what "lac" means but please note that
> partitioning in 2005 is primarily for data management not
> performance. That does not mean that you can not get increased
> performance from a proper partitioning scheme but that is usually not
> the primary goal. Partitioning allow you to manage very large amounts
> of data much more easily and efficient than if they were all in a
> single table or partition.
Oracle's optimizer restricts access to only some partitions if he can
determine from the table, partitioning set up and indexing that this is
more efficient. Does the optimizer in SQL 2005 not do this? I browsed
BOL but couldn't find anything definitive on the matter.
Kind regards
robert
|||Absolutely. If the WHERE clause is such that it can determine based on the
partitioning scheme which partition(s) the data resides and the indexing is
aligned with the partitions it will only search those partitions. That is
and of itself does not guarantee better performance. It may or it may not.
It is just as fast to seek an individual row from a single partition out of
many as it is from one very large table, assuming it was properly indexed to
begin with. So it depends on exactly what they are doing and how.
Andrew J. Kelly SQL MVP
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eAQ0kzqGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Andrew J. Kelly wrote:
> Oracle's optimizer restricts access to only some partitions if he can
> determine from the table, partitioning set up and indexing that this is
> more efficient. Does the optimizer in SQL 2005 not do this? I browsed
> BOL but couldn't find anything definitive on the matter.
> Kind regards
> robert
>
|||Andrew J. Kelly wrote:
> Absolutely. If the WHERE clause is such that it can determine based
> on the partitioning scheme which partition(s) the data resides and
> the indexing is aligned with the partitions it will only search those
> partitions. That is and of itself does not guarantee better
> performance. It may or it may not. It is just as fast to seek an
> individual row from a single partition out of many as it is from one
> very large table, assuming it was properly indexed to begin with. So
> it depends on exactly what they are doing and how.
Thanks for the explanation and verifying that my expectations are met! Of
course performance in the end still depends on what query is actually
executed.
Cheers
robert

Indexing and partitioning of the tables.

We have 6 lac row records which will be increasing with time. So for
optimized performance please provide solution related to partitioning
and indexing of tables.
Is partitioning necessary for optimization of performance, keeping in
mind , future growth of records.
Karankaranlobana@.gmail.com wrote:
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
Disclaimer: haven't use partitioning in SQL 2005 myself - only on Oracle
instances.
It depends on the volume and the queries you do. If volume is rather high
and queries always include a specific narrow time range then it's likely
that your app will benefit from partitioning.
robert|||Forgive me but I don't know what "lac" means but please note that
partitioning in 2005 is primarily for data management not performance. That
does not mean that you can not get increased performance from a proper
partitioning scheme but that is usually not the primary goal. Partitioning
allow you to manage very large amounts of data much more easily and
efficient than if they were all in a single table or partition.
Andrew J. Kelly SQL MVP
<karanlobana@.gmail.com> wrote in message
news:1137392219.125403.291600@.g14g2000cwa.googlegroups.com...
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
> Karan
>|||Andrew J. Kelly wrote:
> Forgive me but I don't know what "lac" means but please note that
> partitioning in 2005 is primarily for data management not
> performance. That does not mean that you can not get increased
> performance from a proper partitioning scheme but that is usually not
> the primary goal. Partitioning allow you to manage very large amounts
> of data much more easily and efficient than if they were all in a
> single table or partition.
Oracle's optimizer restricts access to only some partitions if he can
determine from the table, partitioning set up and indexing that this is
more efficient. Does the optimizer in SQL 2005 not do this? I browsed
BOL but couldn't find anything definitive on the matter.
Kind regards
robert|||Absolutely. If the WHERE clause is such that it can determine based on the
partitioning scheme which partition(s) the data resides and the indexing is
aligned with the partitions it will only search those partitions. That is
and of itself does not guarantee better performance. It may or it may not.
It is just as fast to seek an individual row from a single partition out of
many as it is from one very large table, assuming it was properly indexed to
begin with. So it depends on exactly what they are doing and how.
Andrew J. Kelly SQL MVP
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eAQ0kzqGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Andrew J. Kelly wrote:
> Oracle's optimizer restricts access to only some partitions if he can
> determine from the table, partitioning set up and indexing that this is
> more efficient. Does the optimizer in SQL 2005 not do this? I browsed
> BOL but couldn't find anything definitive on the matter.
> Kind regards
> robert
>|||Andrew J. Kelly wrote:
> Absolutely. If the WHERE clause is such that it can determine based
> on the partitioning scheme which partition(s) the data resides and
> the indexing is aligned with the partitions it will only search those
> partitions. That is and of itself does not guarantee better
> performance. It may or it may not. It is just as fast to seek an
> individual row from a single partition out of many as it is from one
> very large table, assuming it was properly indexed to begin with. So
> it depends on exactly what they are doing and how.
Thanks for the explanation and verifying that my expectations are met! Of
course performance in the end still depends on what query is actually
executed.
Cheers
robert

Indexing and partitioning of the tables.

We have 6 lac row records which will be increasing with time. So for
optimized performance please provide solution related to partitioning
and indexing of tables.
Is partitioning necessary for optimization of performance, keeping in
mind , future growth of records.
Karankaranlobana@.gmail.com wrote:
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
Disclaimer: haven't use partitioning in SQL 2005 myself - only on Oracle
instances.
It depends on the volume and the queries you do. If volume is rather high
and queries always include a specific narrow time range then it's likely
that your app will benefit from partitioning.
robert|||Forgive me but I don't know what "lac" means but please note that
partitioning in 2005 is primarily for data management not performance. That
does not mean that you can not get increased performance from a proper
partitioning scheme but that is usually not the primary goal. Partitioning
allow you to manage very large amounts of data much more easily and
efficient than if they were all in a single table or partition.
--
Andrew J. Kelly SQL MVP
<karanlobana@.gmail.com> wrote in message
news:1137392219.125403.291600@.g14g2000cwa.googlegroups.com...
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
> Karan
>|||Andrew J. Kelly wrote:
> Forgive me but I don't know what "lac" means but please note that
> partitioning in 2005 is primarily for data management not
> performance. That does not mean that you can not get increased
> performance from a proper partitioning scheme but that is usually not
> the primary goal. Partitioning allow you to manage very large amounts
> of data much more easily and efficient than if they were all in a
> single table or partition.
Oracle's optimizer restricts access to only some partitions if he can
determine from the table, partitioning set up and indexing that this is
more efficient. Does the optimizer in SQL 2005 not do this? I browsed
BOL but couldn't find anything definitive on the matter.
Kind regards
robert|||Absolutely. If the WHERE clause is such that it can determine based on the
partitioning scheme which partition(s) the data resides and the indexing is
aligned with the partitions it will only search those partitions. That is
and of itself does not guarantee better performance. It may or it may not.
It is just as fast to seek an individual row from a single partition out of
many as it is from one very large table, assuming it was properly indexed to
begin with. So it depends on exactly what they are doing and how.
--
Andrew J. Kelly SQL MVP
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eAQ0kzqGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Andrew J. Kelly wrote:
>> Forgive me but I don't know what "lac" means but please note that
>> partitioning in 2005 is primarily for data management not
>> performance. That does not mean that you can not get increased
>> performance from a proper partitioning scheme but that is usually not
>> the primary goal. Partitioning allow you to manage very large amounts
>> of data much more easily and efficient than if they were all in a
>> single table or partition.
> Oracle's optimizer restricts access to only some partitions if he can
> determine from the table, partitioning set up and indexing that this is
> more efficient. Does the optimizer in SQL 2005 not do this? I browsed
> BOL but couldn't find anything definitive on the matter.
> Kind regards
> robert
>|||Andrew J. Kelly wrote:
> Absolutely. If the WHERE clause is such that it can determine based
> on the partitioning scheme which partition(s) the data resides and
> the indexing is aligned with the partitions it will only search those
> partitions. That is and of itself does not guarantee better
> performance. It may or it may not. It is just as fast to seek an
> individual row from a single partition out of many as it is from one
> very large table, assuming it was properly indexed to begin with. So
> it depends on exactly what they are doing and how.
Thanks for the explanation and verifying that my expectations are met! Of
course performance in the end still depends on what query is actually
executed.
Cheers
robert

Indexes update when table is modified

Hello again,

Two Short questions this time,

I have a table with several indexes, currently most of them are very narrow (one column), and the question is, when I modify the table by updating a record, does all the indexes are calculated again?? Even if the modified field isn't indexed? Or the server is smart and knows what indexes to calculate if any.

Second question, can I give to a query a low priority(In dynamic SQL), for example when I don't want my query to exploit too many system resources so it won't interfere the main system ?

Inon.update only affects the modified data

second one I have no idea what a priority setting is...|||You can't normally set the scheduler priority within SQL Server, because the timeslice manager affects so many other things if it gets even a little bit confused. A given spid can elect for lower priority treatment by setting its DEADLOCK_PRIORITY (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_8ynt.asp) to LOW.

-PatP|||update only affects the modified data

Ok, just allow me to be sure, if I have a table with 10 indexes and I modify a field in some record which is not indexed, none of the indexed are recalculated right?

I'm asking because I read something that made me unsure about this process, see this link: http://www.sql-server-performance.com/q&a59.asp
Look at the part where he writes: "For every data modification you have, each index in your table needs to be updated".

Thanks,

Inon.|||OK, somebody else can correct me if I'm wrong, but SQL Server does not strictly "update" a record. It copies the record with the modifications and then deletes the original. And it has to copy the entire page of records. That entails suffling of the clustered index, and for non-clustered indexes all the references have to be updated.|||To be honest I have to get Kalen's book

BUT copying the entire page for an update?

Sounds like a lot of IO and overhead...|||There is no deletion-insertion going on. An update of "circus" to "circle" only overwrites the last 2 bytes ("us" to "le"). The whole process can be easily seen when analyzing a transaction log with Log Explorer.|||There is no deletion-insertion going on. An update of "circus" to "circle" only overwrites the last 2 bytes ("us" to "le"). The whole process can be easily seen when analyzing a transaction log with Log Explorer.In terms of changes to the data page, yes, but the log page still gets written in its entirety. As a second issue, if you change "circus" to "circuses" then things may (or may not) get complex if the page is full.

-PatP|||Of course complexity increases when circus is in town! But in respect to log page being written, - you answered your own question, - it does get written!|||I can't find squat on Microsoft's site that deals with this. Thanks, MS.

But here is some info from Kalen:

http://www.winnetmag.com/SQLServer/Article/ArticleID/8031/8031.html

In SQL Server 7.0 (and presumably 2000...blindman), updates can happen in place or as a delete followed by an insert. An in-place update is one where SQL Server changes the bytes in the row with no movement of data necessary.

The leaf level of nonclustered indexes contains a row locator for every row in the table. If the table has a clustered index, the row locator in every nonclustered index is the clustering key for that row. So ifand only ifthe clustered index key is updated, modifications are required in every nonclustered index.|||That's correct. But as Pat mention (not fully, but kinda hinted) that if the data modification of non-indexed fields affects physical location (page) of indexed fields, and no clustered index is defined, - that "may" (!!!) require an update of non-clustered index pages.|||Ok, just allow me to be sure, if I have a table with 10 indexes and I modify a field in some record which is not indexed, none of the indexed are recalculated right?

I'm asking because I read something that made me unsure about this process, see this link: http://www.sql-server-performance.com/q&a59.asp
Look at the part where he writes: "For every data modification you have, each index in your table needs to be updated".

Thanks,

Inon.

Below is the reply to my question from the author of the article you were referring to:

If you modify a non-indexed column, then other indexes are not affected.

Brad

_____________________________________________
From: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Sent: Tuesday, June 08, 2004 11:34 AM
To: webmaster@.sql-server-performance.com
Subject: SQL Tuning

In your Q&A posting (http://www.sql-server-performance.com/q&a59.asp) you're stating that "every data modification you have, each index in your table needs to be updated". Does it hold true even if you modify non-indexed fields?

Thanks in advance.|||Great, Thanks a lot for the help!

I thought I knew the answer, but after I accidentally bumped into that article I wasn't sure and had to be sure.

Inon.

Indexes not rebuilding on SQL 2K DB MAINT PLAN

I see errors in my DB Maint log that indicates indexes are not being rebuilt.
We are having production problems - time outs. Does the optimization plan
for DB Maintenence drop bad indexes - simply put, what is my best way to
handle back indexes on a production server?
--
Regards,
JamieError is:
Backup can not be performed on this database. This sub task is ignored
--
Regards,
Jamie
"thejamie" wrote:
> I see errors in my DB Maint log that indicates indexes are not being rebuilt.
> We are having production problems - time outs. Does the optimization plan
> for DB Maintenence drop bad indexes - simply put, what is my best way to
> handle back indexes on a production server?
> --
> Regards,
> Jamie|||thejamie wrote:
> Error is:
> Backup can not be performed on this database. This sub task is ignored
That has nothing to do with indexes... This error is telling you that
you are attempting to do a transaction log backup against a database
that is in Simple recovery mode.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||That particular database is a production database. Why would it be in simple
recovery mode?
--
Regards,
Jamie
"Tracy McKibben" wrote:
> thejamie wrote:
> > Error is:
> > Backup can not be performed on this database. This sub task is ignored
> That has nothing to do with indexes... This error is telling you that
> you are attempting to do a transaction log backup against a database
> that is in Simple recovery mode.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||thejamie wrote:
> That particular database is a production database. Why would it be in simple
> recovery mode?
Only you can answer that question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Can you give me a hint on this... it appears to be in normal mode - not
recovery mode.
--
Regards,
Jamie
"Tracy McKibben" wrote:
> thejamie wrote:
> > That particular database is a production database. Why would it be in simple
> > recovery mode?
> Only you can answer that question...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Open the database property window, select the option tab and check what
recovery mode your database on
vinu
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
> Can you give me a hint on this... it appears to be in normal mode - not
> recovery mode.
> --
> Regards,
> Jamie
>
> "Tracy McKibben" wrote:
>> thejamie wrote:
>> > That particular database is a production database. Why would it be in
>> > simple
>> > recovery mode?
>> Only you can answer that question...
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||Does your maintenance plan include master databases, if so remove it from ur
maintenance plan darabase list..
vinu
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
> Can you give me a hint on this... it appears to be in normal mode - not
> recovery mode.
> --
> Regards,
> Jamie
>
> "Tracy McKibben" wrote:
>> thejamie wrote:
>> > That particular database is a production database. Why would it be in
>> > simple
>> > recovery mode?
>> Only you can answer that question...
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||Thanks. I am in simple recovery mode. I would like to know I can run a
point in time restore. Do I need to switch to FULL recovery mode for this?
--
Regards,
Jamie
"vt" wrote:
> Does your maintenance plan include master databases, if so remove it from ur
> maintenance plan darabase list..
> vinu
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
> > Can you give me a hint on this... it appears to be in normal mode - not
> > recovery mode.
> > --
> > Regards,
> > Jamie
> >
> >
> > "Tracy McKibben" wrote:
> >
> >> thejamie wrote:
> >> > That particular database is a production database. Why would it be in
> >> > simple
> >> > recovery mode?
> >>
> >> Only you can answer that question...
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com
> >>
>
>|||With simple recovery mode you will not be able to do point in time restore
read BOL (Book online) for more detail..
vinu
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
> Thanks. I am in simple recovery mode. I would like to know I can run a
> point in time restore. Do I need to switch to FULL recovery mode for
> this?
> --
> Regards,
> Jamie
>
> "vt" wrote:
>> Does your maintenance plan include master databases, if so remove it from
>> ur
>> maintenance plan darabase list..
>> vinu
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
>> > Can you give me a hint on this... it appears to be in normal mode - not
>> > recovery mode.
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "Tracy McKibben" wrote:
>> >
>> >> thejamie wrote:
>> >> > That particular database is a production database. Why would it be
>> >> > in
>> >> > simple
>> >> > recovery mode?
>> >>
>> >> Only you can answer that question...
>> >>
>> >>
>> >> --
>> >> Tracy McKibben
>> >> MCDBA
>> >> http://www.realsqlguy.com
>> >>
>>|||Yes. That's right. Will the server need anything more than a reboot to
enter full recovery mode?
--
Regards,
Jamie
"vt" wrote:
> With simple recovery mode you will not be able to do point in time restore
> read BOL (Book online) for more detail..
> vinu
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
> > Thanks. I am in simple recovery mode. I would like to know I can run a
> > point in time restore. Do I need to switch to FULL recovery mode for
> > this?
> > --
> > Regards,
> > Jamie
> >
> >
> > "vt" wrote:
> >
> >> Does your maintenance plan include master databases, if so remove it from
> >> ur
> >> maintenance plan darabase list..
> >> vinu
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
> >> > Can you give me a hint on this... it appears to be in normal mode - not
> >> > recovery mode.
> >> > --
> >> > Regards,
> >> > Jamie
> >> >
> >> >
> >> > "Tracy McKibben" wrote:
> >> >
> >> >> thejamie wrote:
> >> >> > That particular database is a production database. Why would it be
> >> >> > in
> >> >> > simple
> >> >> > recovery mode?
> >> >>
> >> >> Only you can answer that question...
> >> >>
> >> >>
> >> >> --
> >> >> Tracy McKibben
> >> >> MCDBA
> >> >> http://www.realsqlguy.com
> >> >>
> >>
> >>
> >>
>
>|||Hi Jamie
You don't even need to reboot. Just change to FULL, then make a full db
backup.
From that point on, you will be able to make log backups, and use them to
restore to a specific point in time, when necessary.
Please read the documentation on log backups and recovery models.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
> Yes. That's right. Will the server need anything more than a reboot to
> enter full recovery mode?
> --
> Regards,
> Jamie
>
> "vt" wrote:
>> With simple recovery mode you will not be able to do point in time
>> restore
>> read BOL (Book online) for more detail..
>> vinu
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
>> > Thanks. I am in simple recovery mode. I would like to know I can
>> > run a
>> > point in time restore. Do I need to switch to FULL recovery mode for
>> > this?
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "vt" wrote:
>> >
>> >> Does your maintenance plan include master databases, if so remove it
>> >> from
>> >> ur
>> >> maintenance plan darabase list..
>> >> vinu
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
>> >> > Can you give me a hint on this... it appears to be in normal mode -
>> >> > not
>> >> > recovery mode.
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >> >
>> >> >
>> >> > "Tracy McKibben" wrote:
>> >> >
>> >> >> thejamie wrote:
>> >> >> > That particular database is a production database. Why would it
>> >> >> > be
>> >> >> > in
>> >> >> > simple
>> >> >> > recovery mode?
>> >> >>
>> >> >> Only you can answer that question...
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Tracy McKibben
>> >> >> MCDBA
>> >> >> http://www.realsqlguy.com
>> >> >>
>> >>
>> >>
>> >>
>>|||I don't think server will ask you to reboot
go ahead
vt
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
> Yes. That's right. Will the server need anything more than a reboot to
> enter full recovery mode?
> --
> Regards,
> Jamie
>
> "vt" wrote:
>> With simple recovery mode you will not be able to do point in time
>> restore
>> read BOL (Book online) for more detail..
>> vinu
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
>> > Thanks. I am in simple recovery mode. I would like to know I can
>> > run a
>> > point in time restore. Do I need to switch to FULL recovery mode for
>> > this?
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "vt" wrote:
>> >
>> >> Does your maintenance plan include master databases, if so remove it
>> >> from
>> >> ur
>> >> maintenance plan darabase list..
>> >> vinu
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
>> >> > Can you give me a hint on this... it appears to be in normal mode -
>> >> > not
>> >> > recovery mode.
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >> >
>> >> >
>> >> > "Tracy McKibben" wrote:
>> >> >
>> >> >> thejamie wrote:
>> >> >> > That particular database is a production database. Why would it
>> >> >> > be
>> >> >> > in
>> >> >> > simple
>> >> >> > recovery mode?
>> >> >>
>> >> >> Only you can answer that question...
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Tracy McKibben
>> >> >> MCDBA
>> >> >> http://www.realsqlguy.com
>> >> >>
>> >>
>> >>
>> >>
>>|||Thanks to all of you. This is a big load off my mind.
--
Regards,
Jamie
"vt" wrote:
> I don't think server will ask you to reboot
> go ahead
> vt
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
> > Yes. That's right. Will the server need anything more than a reboot to
> > enter full recovery mode?
> > --
> > Regards,
> > Jamie
> >
> >
> > "vt" wrote:
> >
> >> With simple recovery mode you will not be able to do point in time
> >> restore
> >> read BOL (Book online) for more detail..
> >>
> >> vinu
> >>
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
> >> > Thanks. I am in simple recovery mode. I would like to know I can
> >> > run a
> >> > point in time restore. Do I need to switch to FULL recovery mode for
> >> > this?
> >> > --
> >> > Regards,
> >> > Jamie
> >> >
> >> >
> >> > "vt" wrote:
> >> >
> >> >> Does your maintenance plan include master databases, if so remove it
> >> >> from
> >> >> ur
> >> >> maintenance plan darabase list..
> >> >> vinu
> >> >>
> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
> >> >> > Can you give me a hint on this... it appears to be in normal mode -
> >> >> > not
> >> >> > recovery mode.
> >> >> > --
> >> >> > Regards,
> >> >> > Jamie
> >> >> >
> >> >> >
> >> >> > "Tracy McKibben" wrote:
> >> >> >
> >> >> >> thejamie wrote:
> >> >> >> > That particular database is a production database. Why would it
> >> >> >> > be
> >> >> >> > in
> >> >> >> > simple
> >> >> >> > recovery mode?
> >> >> >>
> >> >> >> Only you can answer that question...
> >> >> >>
> >> >> >>
> >> >> >> --
> >> >> >> Tracy McKibben
> >> >> >> MCDBA
> >> >> >> http://www.realsqlguy.com
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Run this in query analyzer:
alter database dbname
set recovery Full
No reboot required
thejamie wrote:
> Thanks to all of you. This is a big load off my mind.
> --
> Regards,
> Jamie
>
> "vt" wrote:
> > I don't think server will ask you to reboot
> > go ahead
> >
> > vt
> >
> >
> >
> > "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> > news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
> > > Yes. That's right. Will the server need anything more than a reboot to
> > > enter full recovery mode?
> > > --
> > > Regards,
> > > Jamie
> > >
> > >
> > > "vt" wrote:
> > >
> > >> With simple recovery mode you will not be able to do point in time
> > >> restore
> > >> read BOL (Book online) for more detail..
> > >>
> > >> vinu
> > >>
> > >>
> > >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> > >> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
> > >> > Thanks. I am in simple recovery mode. I would like to know I can
> > >> > run a
> > >> > point in time restore. Do I need to switch to FULL recovery mode for
> > >> > this?
> > >> > --
> > >> > Regards,
> > >> > Jamie
> > >> >
> > >> >
> > >> > "vt" wrote:
> > >> >
> > >> >> Does your maintenance plan include master databases, if so remove it
> > >> >> from
> > >> >> ur
> > >> >> maintenance plan darabase list..
> > >> >> vinu
> > >> >>
> > >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> > >> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
> > >> >> > Can you give me a hint on this... it appears to be in normal mode -
> > >> >> > not
> > >> >> > recovery mode.
> > >> >> > --
> > >> >> > Regards,
> > >> >> > Jamie
> > >> >> >
> > >> >> >
> > >> >> > "Tracy McKibben" wrote:
> > >> >> >
> > >> >> >> thejamie wrote:
> > >> >> >> > That particular database is a production database. Why would it
> > >> >> >> > be
> > >> >> >> > in
> > >> >> >> > simple
> > >> >> >> > recovery mode?
> > >> >> >>
> > >> >> >> Only you can answer that question...
> > >> >> >>
> > >> >> >>
> > >> >> >> --
> > >> >> >> Tracy McKibben
> > >> >> >> MCDBA
> > >> >> >> http://www.realsqlguy.com
> > >> >> >>
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >|||"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:D8A1EEBD-95A0-4DB4-B40A-AD3AD0EA4E22@.microsoft.com...
> Thanks to all of you. This is a big load off my mind.
Umm, not quite.
Make sure you do transaction log backups.
And keep in mind a disaster recovery plan is far more than simply "turning
on backups".
It's managing them among other things.
How many often will you take a full backup? How long will you keep it?
How often will you take transaction backups. How long will you keep them?
Etc.
> --
> Regards,
> Jamie
>
> "vt" wrote:
>> I don't think server will ask you to reboot
>> go ahead
>> vt
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
>> > Yes. That's right. Will the server need anything more than a reboot
>> > to
>> > enter full recovery mode?
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "vt" wrote:
>> >
>> >> With simple recovery mode you will not be able to do point in time
>> >> restore
>> >> read BOL (Book online) for more detail..
>> >>
>> >> vinu
>> >>
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
>> >> > Thanks. I am in simple recovery mode. I would like to know I can
>> >> > run a
>> >> > point in time restore. Do I need to switch to FULL recovery mode
>> >> > for
>> >> > this?
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >> >
>> >> >
>> >> > "vt" wrote:
>> >> >
>> >> >> Does your maintenance plan include master databases, if so remove
>> >> >> it
>> >> >> from
>> >> >> ur
>> >> >> maintenance plan darabase list..
>> >> >> vinu
>> >> >>
>> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
>> >> >> > Can you give me a hint on this... it appears to be in normal
>> >> >> > mode -
>> >> >> > not
>> >> >> > recovery mode.
>> >> >> > --
>> >> >> > Regards,
>> >> >> > Jamie
>> >> >> >
>> >> >> >
>> >> >> > "Tracy McKibben" wrote:
>> >> >> >
>> >> >> >> thejamie wrote:
>> >> >> >> > That particular database is a production database. Why would
>> >> >> >> > it
>> >> >> >> > be
>> >> >> >> > in
>> >> >> >> > simple
>> >> >> >> > recovery mode?
>> >> >> >>
>> >> >> >> Only you can answer that question...
>> >> >> >>
>> >> >> >>
>> >> >> >> --
>> >> >> >> Tracy McKibben
>> >> >> >> MCDBA
>> >> >> >> http://www.realsqlguy.com
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||And what kind of testing will you do to make sure you can smoothly recover
from your backups when the need arises?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23zodPZgAHHA.3836@.TK2MSFTNGP02.phx.gbl...
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:D8A1EEBD-95A0-4DB4-B40A-AD3AD0EA4E22@.microsoft.com...
>> Thanks to all of you. This is a big load off my mind.
> Umm, not quite.
> Make sure you do transaction log backups.
> And keep in mind a disaster recovery plan is far more than simply "turning
> on backups".
> It's managing them among other things.
> How many often will you take a full backup? How long will you keep it?
> How often will you take transaction backups. How long will you keep them?
> Etc.
>
>> --
>> Regards,
>> Jamie
>>
>> "vt" wrote:
>> I don't think server will ask you to reboot
>> go ahead
>> vt
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
>> > Yes. That's right. Will the server need anything more than a reboot
>> > to
>> > enter full recovery mode?
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "vt" wrote:
>> >
>> >> With simple recovery mode you will not be able to do point in time
>> >> restore
>> >> read BOL (Book online) for more detail..
>> >>
>> >> vinu
>> >>
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
>> >> > Thanks. I am in simple recovery mode. I would like to know I
>> >> > can
>> >> > run a
>> >> > point in time restore. Do I need to switch to FULL recovery mode
>> >> > for
>> >> > this?
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >> >
>> >> >
>> >> > "vt" wrote:
>> >> >
>> >> >> Does your maintenance plan include master databases, if so remove
>> >> >> it
>> >> >> from
>> >> >> ur
>> >> >> maintenance plan darabase list..
>> >> >> vinu
>> >> >>
>> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
>> >> >> > Can you give me a hint on this... it appears to be in normal
>> >> >> > mode -
>> >> >> > not
>> >> >> > recovery mode.
>> >> >> > --
>> >> >> > Regards,
>> >> >> > Jamie
>> >> >> >
>> >> >> >
>> >> >> > "Tracy McKibben" wrote:
>> >> >> >
>> >> >> >> thejamie wrote:
>> >> >> >> > That particular database is a production database. Why would
>> >> >> >> > it
>> >> >> >> > be
>> >> >> >> > in
>> >> >> >> > simple
>> >> >> >> > recovery mode?
>> >> >> >>
>> >> >> >> Only you can answer that question...
>> >> >> >>
>> >> >> >>
>> >> >> >> --
>> >> >> >> Tracy McKibben
>> >> >> >> MCDBA
>> >> >> >> http://www.realsqlguy.com
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>|||We're running simulations on virtual servers. Thanks again everyone for your
help.
--
Regards,
Jamie
"Kalen Delaney" wrote:
> And what kind of testing will you do to make sure you can smoothly recover
> from your backups when the need arises?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
> news:%23zodPZgAHHA.3836@.TK2MSFTNGP02.phx.gbl...
> >
> > "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> > news:D8A1EEBD-95A0-4DB4-B40A-AD3AD0EA4E22@.microsoft.com...
> >> Thanks to all of you. This is a big load off my mind.
> >
> > Umm, not quite.
> >
> > Make sure you do transaction log backups.
> >
> > And keep in mind a disaster recovery plan is far more than simply "turning
> > on backups".
> >
> > It's managing them among other things.
> >
> > How many often will you take a full backup? How long will you keep it?
> >
> > How often will you take transaction backups. How long will you keep them?
> >
> > Etc.
> >
> >
> >> --
> >> Regards,
> >> Jamie
> >>
> >>
> >> "vt" wrote:
> >>
> >> I don't think server will ask you to reboot
> >> go ahead
> >>
> >> vt
> >>
> >>
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
> >> > Yes. That's right. Will the server need anything more than a reboot
> >> > to
> >> > enter full recovery mode?
> >> > --
> >> > Regards,
> >> > Jamie
> >> >
> >> >
> >> > "vt" wrote:
> >> >
> >> >> With simple recovery mode you will not be able to do point in time
> >> >> restore
> >> >> read BOL (Book online) for more detail..
> >> >>
> >> >> vinu
> >> >>
> >> >>
> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> >> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
> >> >> > Thanks. I am in simple recovery mode. I would like to know I
> >> >> > can
> >> >> > run a
> >> >> > point in time restore. Do I need to switch to FULL recovery mode
> >> >> > for
> >> >> > this?
> >> >> > --
> >> >> > Regards,
> >> >> > Jamie
> >> >> >
> >> >> >
> >> >> > "vt" wrote:
> >> >> >
> >> >> >> Does your maintenance plan include master databases, if so remove
> >> >> >> it
> >> >> >> from
> >> >> >> ur
> >> >> >> maintenance plan darabase list..
> >> >> >> vinu
> >> >> >>
> >> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> >> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
> >> >> >> > Can you give me a hint on this... it appears to be in normal
> >> >> >> > mode -
> >> >> >> > not
> >> >> >> > recovery mode.
> >> >> >> > --
> >> >> >> > Regards,
> >> >> >> > Jamie
> >> >> >> >
> >> >> >> >
> >> >> >> > "Tracy McKibben" wrote:
> >> >> >> >
> >> >> >> >> thejamie wrote:
> >> >> >> >> > That particular database is a production database. Why would
> >> >> >> >> > it
> >> >> >> >> > be
> >> >> >> >> > in
> >> >> >> >> > simple
> >> >> >> >> > recovery mode?
> >> >> >> >>
> >> >> >> >> Only you can answer that question...
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Tracy McKibben
> >> >> >> >> MCDBA
> >> >> >> >> http://www.realsqlguy.com
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >
> >
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eEBNUqgAHHA.1220@.TK2MSFTNGP04.phx.gbl...
> And what kind of testing will you do to make sure you can smoothly recover
> from your backups when the need arises?
>
Oh yeah. Good point. :-)
(We do home rolled log-shipping of most of our databases, which is a real
good check that backups are working etc. ;-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message news:%23zodPZgAHHA.3836@.TK2MSFTNGP02.phx.gbl...
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:D8A1EEBD-95A0-4DB4-B40A-AD3AD0EA4E22@.microsoft.com...
>> Thanks to all of you. This is a big load off my mind.
>> Umm, not quite.
>> Make sure you do transaction log backups.
>> And keep in mind a disaster recovery plan is far more than simply
>> "turning on backups".
>> It's managing them among other things.
>> How many often will you take a full backup? How long will you keep it?
>> How often will you take transaction backups. How long will you keep
>> them?
>> Etc.
>>
>> --
>> Regards,
>> Jamie
>>
>> "vt" wrote:
>> I don't think server will ask you to reboot
>> go ahead
>> vt
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:7A6C958E-1DBD-4E41-8361-75FA5EE7996E@.microsoft.com...
>> > Yes. That's right. Will the server need anything more than a reboot
>> > to
>> > enter full recovery mode?
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "vt" wrote:
>> >
>> >> With simple recovery mode you will not be able to do point in time
>> >> restore
>> >> read BOL (Book online) for more detail..
>> >>
>> >> vinu
>> >>
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...
>> >> > Thanks. I am in simple recovery mode. I would like to know I
>> >> > can
>> >> > run a
>> >> > point in time restore. Do I need to switch to FULL recovery mode
>> >> > for
>> >> > this?
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >> >
>> >> >
>> >> > "vt" wrote:
>> >> >
>> >> >> Does your maintenance plan include master databases, if so remove
>> >> >> it
>> >> >> from
>> >> >> ur
>> >> >> maintenance plan darabase list..
>> >> >> vinu
>> >> >>
>> >> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> >> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
>> >> >> > Can you give me a hint on this... it appears to be in normal
>> >> >> > mode -
>> >> >> > not
>> >> >> > recovery mode.
>> >> >> > --
>> >> >> > Regards,
>> >> >> > Jamie
>> >> >> >
>> >> >> >
>> >> >> > "Tracy McKibben" wrote:
>> >> >> >
>> >> >> >> thejamie wrote:
>> >> >> >> > That particular database is a production database. Why
>> >> >> >> > would it
>> >> >> >> > be
>> >> >> >> > in
>> >> >> >> > simple
>> >> >> >> > recovery mode?
>> >> >> >>
>> >> >> >> Only you can answer that question...
>> >> >> >>
>> >> >> >>
>> >> >> >> --
>> >> >> >> Tracy McKibben
>> >> >> >> MCDBA
>> >> >> >> http://www.realsqlguy.com
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>

Indexes not rebuilding on SQL 2K DB MAINT PLAN

I see errors in my DB Maint log that indicates indexes are not being rebuilt.
We are having production problems - time outs. Does the optimization plan
for DB Maintenence drop bad indexes - simply put, what is my best way to
handle back indexes on a production server?
Regards,
Jamie
Error is:
Backup can not be performed on this database. This sub task is ignored
Regards,
Jamie
"thejamie" wrote:

> I see errors in my DB Maint log that indicates indexes are not being rebuilt.
> We are having production problems - time outs. Does the optimization plan
> for DB Maintenence drop bad indexes - simply put, what is my best way to
> handle back indexes on a production server?
> --
> Regards,
> Jamie
|||thejamie wrote:
> Error is:
> Backup can not be performed on this database. This sub task is ignored
That has nothing to do with indexes... This error is telling you that
you are attempting to do a transaction log backup against a database
that is in Simple recovery mode.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||That particular database is a production database. Why would it be in simple
recovery mode?
Regards,
Jamie
"Tracy McKibben" wrote:

> thejamie wrote:
> That has nothing to do with indexes... This error is telling you that
> you are attempting to do a transaction log backup against a database
> that is in Simple recovery mode.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||thejamie wrote:
> That particular database is a production database. Why would it be in simple
> recovery mode?
Only you can answer that question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Can you give me a hint on this... it appears to be in normal mode - not
recovery mode.
Regards,
Jamie
"Tracy McKibben" wrote:

> thejamie wrote:
> Only you can answer that question...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||Open the database property window, select the option tab and check what
recovery mode your database on
vinu
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...[vbcol=seagreen]
> Can you give me a hint on this... it appears to be in normal mode - not
> recovery mode.
> --
> Regards,
> Jamie
>
> "Tracy McKibben" wrote:
|||Does your maintenance plan include master databases, if so remove it from ur
maintenance plan darabase list..
vinu
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...[vbcol=seagreen]
> Can you give me a hint on this... it appears to be in normal mode - not
> recovery mode.
> --
> Regards,
> Jamie
>
> "Tracy McKibben" wrote:
|||Thanks. I am in simple recovery mode. I would like to know I can run a
point in time restore. Do I need to switch to FULL recovery mode for this?
Regards,
Jamie
"vt" wrote:

> Does your maintenance plan include master databases, if so remove it from ur
> maintenance plan darabase list..
> vinu
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:2D4155FC-D55C-4C9C-9630-1327B0E66D0B@.microsoft.com...
>
>
|||With simple recovery mode you will not be able to do point in time restore
read BOL (Book online) for more detail..
vinu
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:F1B75A04-D4AF-4F82-BBBE-681891483C4B@.microsoft.com...[vbcol=seagreen]
> Thanks. I am in simple recovery mode. I would like to know I can run a
> point in time restore. Do I need to switch to FULL recovery mode for
> this?
> --
> Regards,
> Jamie
>
> "vt" wrote:

Indexes ineffective in MSSQL Server 2000

Indexes defined on my tables seems to be ineffective.
Eventhough execution plans shows a Index Seek or Index Scan it takes up
the same time as the query would take when it shows a Table scan in
Execution plan.
This behaviour seems to occur when the number of rows returned by the
query are > than 1/10 th of the total no of rows in the table.
If the number of rows is less than 1/10th of the total rows, the time
difference between IndexSeek and Table Scan seems to be alright.
Is this behaviour (To take up same time for Index Seek and Table Scan
when number of rows returned are more) shown by SQL server expected?
Also the Recompute statistics on my indexes are on still i would like
to know how i can recompute statistics of individual indexes defined on
the table?
Thanks
Vishal.can you run update statistics tablename and see if this solves the problem.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegroups.com...
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
> Also the Recompute statistics on my indexes are on still i would like
> to know how i can recompute statistics of individual indexes defined on
> the table?
> Thanks
> Vishal.
>|||Hi
In additon, please take a look at
http://www.sql-server-performance.c...ate_indexes.asp
http://www.sql-server-performance.c..._statistics.asp
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegroups.com...
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
> Also the Recompute statistics on my indexes are on still i would like
> to know how i can recompute statistics of individual indexes defined on
> the table?
> Thanks
> Vishal.
>|||On 03.10.2006 10:53, Vishal wrote:
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
If you pull more than a certain percentage of rows from a table a table
scan is more efficient. The amount of that percentage differs depending
on DB product, schema, query etc. So, yes it is expected and typically
optimizers take this into account when creating the execution plan.
Kind regards
robert|||Thanx for this info Robert...
Robert Klemme wrote:
> On 03.10.2006 10:53, Vishal wrote:
> If you pull more than a certain percentage of rows from a table a table
> scan is more efficient. The amount of that percentage differs depending
> on DB product, schema, query etc. So, yes it is expected and typically
> optimizers take this into account when creating the execution plan.
> Kind regards
> robert

Indexes ineffective in MSSQL Server 2000

Indexes defined on my tables seems to be ineffective.
Eventhough execution plans shows a Index Seek or Index Scan it takes up
the same time as the query would take when it shows a Table scan in
Execution plan.
This behaviour seems to occur when the number of rows returned by the
query are > than 1/10 th of the total no of rows in the table.
If the number of rows is less than 1/10th of the total rows, the time
difference between IndexSeek and Table Scan seems to be alright.
Is this behaviour (To take up same time for Index Seek and Table Scan
when number of rows returned are more) shown by SQL server expected?
Also the Recompute statistics on my indexes are on still i would like
to know how i can recompute statistics of individual indexes defined on
the table?
Thanks
Vishal.can you run update statistics tablename and see if this solves the problem.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegroups.com...
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
> Also the Recompute statistics on my indexes are on still i would like
> to know how i can recompute statistics of individual indexes defined on
> the table?
> Thanks
> Vishal.
>|||Hi
In additon, please take a look at
http://www.sql-server-performance.com/ma_finding_duplicate_indexes.asp
http://www.sql-server-performance.com/nb_execution_plan_statistics.asp
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegroups.com...
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
> Also the Recompute statistics on my indexes are on still i would like
> to know how i can recompute statistics of individual indexes defined on
> the table?
> Thanks
> Vishal.
>|||On 03.10.2006 10:53, Vishal wrote:
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
If you pull more than a certain percentage of rows from a table a table
scan is more efficient. The amount of that percentage differs depending
on DB product, schema, query etc. So, yes it is expected and typically
optimizers take this into account when creating the execution plan.
Kind regards
robert|||Thanx for this info Robert...
Robert Klemme wrote:
> On 03.10.2006 10:53, Vishal wrote:
> > Indexes defined on my tables seems to be ineffective.
> > Eventhough execution plans shows a Index Seek or Index Scan it takes up
> > the same time as the query would take when it shows a Table scan in
> > Execution plan.
> > This behaviour seems to occur when the number of rows returned by the
> > query are > than 1/10 th of the total no of rows in the table.
> > If the number of rows is less than 1/10th of the total rows, the time
> > difference between IndexSeek and Table Scan seems to be alright.
> > Is this behaviour (To take up same time for Index Seek and Table Scan
> > when number of rows returned are more) shown by SQL server expected?
> If you pull more than a certain percentage of rows from a table a table
> scan is more efficient. The amount of that percentage differs depending
> on DB product, schema, query etc. So, yes it is expected and typically
> optimizers take this into account when creating the execution plan.
> Kind regards
> robert

Indexes ineffective in MSSQL Server 2000

Indexes defined on my tables seems to be ineffective.
Eventhough execution plans shows a Index Seek or Index Scan it takes up
the same time as the query would take when it shows a Table scan in
Execution plan.
This behaviour seems to occur when the number of rows returned by the
query are > than 1/10 th of the total no of rows in the table.
If the number of rows is less than 1/10th of the total rows, the time
difference between IndexSeek and Table Scan seems to be alright.
Is this behaviour (To take up same time for Index Seek and Table Scan
when number of rows returned are more) shown by SQL server expected?
Also the Recompute statistics on my indexes are on still i would like
to know how i can recompute statistics of individual indexes defined on
the table?
Thanks
Vishal.
can you run update statistics tablename and see if this solves the problem.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegrou ps.com...
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
> Also the Recompute statistics on my indexes are on still i would like
> to know how i can recompute statistics of individual indexes defined on
> the table?
> Thanks
> Vishal.
>
|||Hi
In additon, please take a look at
http://www.sql-server-performance.co...te_indexes.asp
http://www.sql-server-performance.co...statistics.asp
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegrou ps.com...
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
> Also the Recompute statistics on my indexes are on still i would like
> to know how i can recompute statistics of individual indexes defined on
> the table?
> Thanks
> Vishal.
>
|||On 03.10.2006 10:53, Vishal wrote:
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
If you pull more than a certain percentage of rows from a table a table
scan is more efficient. The amount of that percentage differs depending
on DB product, schema, query etc. So, yes it is expected and typically
optimizers take this into account when creating the execution plan.
Kind regards
robert
|||Thanx for this info Robert...
Robert Klemme wrote:
> On 03.10.2006 10:53, Vishal wrote:
> If you pull more than a certain percentage of rows from a table a table
> scan is more efficient. The amount of that percentage differs depending
> on DB product, schema, query etc. So, yes it is expected and typically
> optimizers take this into account when creating the execution plan.
> Kind regards
> robert

indexes are slowing down my query

>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
Has anyone else EVER encountered this before. I know I
never have. I built individual indexes on the same 4
columns that the ITW wanted to build a covered index on.
It slowed down my query by 40%. This is bizarre behavior.
I've only been a DBA for a few years, but I've NEVER had
this happen or even heard of it. Has anyone else? Is this
a bug?

>--Original Message--
>Hi,
>Here is a bit of information I found in BOL:
>"
>Covered queries can improve performance. Covered queries
>are queries where all the columns specified in the query
>are contained within the same index. For example, a query
>retrieving columns a and b from a table that has a
>composite index created on columns a, b, and c is
>considered covered. Creating indexes that cover a query
>can improve performance because all the data for the
query
>is contained within the index itself; only the index
>pages, not the data pages, of the table must be
referenced
>to retrieve the data, thereby reducing overall I/O.
>Although adding columns to an index to cover queries can
>improve performance, maintaining the extra columns in the
>index incurs update and storage costs.
>"
>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
>hth
>DeeJay
>transdtl
>SERVICE','DTV*DIRECTV
on[vbcol=seagreen]
is[vbcol=seagreen]
this[vbcol=seagreen]
>in
>performance.com/composite_indexes.asp
to[vbcol=seagreen]
>table
>soooooo
>the
index.[vbcol=seagreen]
query[vbcol=seagreen]
>.
>On Thu, 24 Jun 2004 08:39:28 -0700, ChrisR wrote:

>This is bizarre behavior.
Hi Chris,
No, it is not. Both Greg and DeeJay already pointed out that one index on
all columns required for the query means that SQL Server has to access
this index only. Individual indexes are not covering; SQL Server might
choose to read all indexes and merge the results, or use one index and
fetch the data pages through that index. In both cases, more disk access
is required than when one covering index on all columns can be used.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

indexes are slowing down my query

>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
Has anyone else EVER encountered this before. I know I
never have. I built individual indexes on the same 4
columns that the ITW wanted to build a covered index on.
It slowed down my query by 40%. This is bizarre behavior.
I've only been a DBA for a few years, but I've NEVER had
this happen or even heard of it. Has anyone else? Is this
a bug?

>--Original Message--
>Hi,
>Here is a bit of information I found in BOL:
>"
>Covered queries can improve performance. Covered queries
>are queries where all the columns specified in the query
>are contained within the same index. For example, a query
>retrieving columns a and b from a table that has a
>composite index created on columns a, b, and c is
>considered covered. Creating indexes that cover a query
>can improve performance because all the data for the
query
>is contained within the index itself; only the index
>pages, not the data pages, of the table must be
referenced[vbcol=seagreen]
>to retrieve the data, thereby reducing overall I/O.
>Although adding columns to an index to cover queries can
>improve performance, maintaining the extra columns in the
>index incurs update and storage costs.
>"
>It seems that your individual indexes DO NOT cover your
>query. Each time you run your query, the optimizer is
>using 4 different index (one at a time) and it has
>increased disk i/o. Increased disk i/o means slow query
>response.
>hth
>DeeJay
>transdtl
>SERVICE','DTV*DIRECTV
on[vbcol=seagreen]
is[vbcol=seagreen]
this[vbcol=seagreen]
>in
>performance.com/composite_indexes.asp
to[vbcol=seagreen]
>table
>soooooo
>the
index.[vbcol=seagreen]
query
>.
>
On Thu, 24 Jun 2004 08:39:28 -0700, ChrisR wrote:

>This is bizarre behavior.
Hi Chris,
No, it is not. Both Greg and DeeJay already pointed out that one index on
all columns required for the query means that SQL Server has to access
this index only. Individual indexes are not covering; SQL Server might
choose to read all indexes and merge the results, or use one index and
fetch the data pages through that index. In both cases, more disk access
is required than when one covering index on all columns can be used.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)