Hi All
I have an application with SQL Server 2000 SP4 as my RDBMS.
There is a table with around 1200000 records with all the necessary
indexes defined when i execute the query through query analyzer the no
of rows returned are around 300000, execution plan shows a Bookmark
Lookup. But the bookmark lookup taks lot of time?
Also if i remove the particular index it shows me a Table Scan in
Execution Plan.
Can anyone help me explain why Bookmark lookup takes the same time as
Table Scan?
Thanks & Regards
Vishal.I am not sure if I can but perhaps a covering index would be helpful here.
--
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:1159864815.076365.227450@.k70g2000cwa.googlegroups.com...
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>|||Vishal wrote:
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
A bookmark lookup occurs when the index that was used to satisfy the
query doesn't contain all of the columns that were requested from a
specific table. Below is a snippet from an earlier response that I
posted to a similar question:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Vishal,
to add another point to the mix, you might just want to check if it's
possible to use a covering index.
I mention this as you are referring to queries against one single table and
there is often a huge time saving if you can cover the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Can u tell me more about covering Indexes'
As in :
What do they mean'
How to define them?
Rgds
Vishal.
Paul Ibison wrote:
> Vishal,
> to add another point to the mix, you might just want to check if it's
> possible to use a covering index.
> I mention this as you are referring to queries against one single table and
> there is often a huge time saving if you can cover the query.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Tracy Thanks for ur info on Bookmark Lookup.
But does anyone have any Idea Why Bookmark Lookup is taking up same
time as a Table Scan does?
The table on which the query executes has about 80 columns defined.
Could this be the reason when I Execute a Select * on such a Table it
takes same time as a Table Scan?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> > Hi All
> > I have an application with SQL Server 2000 SP4 as my RDBMS.
> > There is a table with around 1200000 records with all the necessary
> > indexes defined when i execute the query through query analyzer the no
> > of rows returned are around 300000, execution plan shows a Bookmark
> > Lookup. But the bookmark lookup taks lot of time?
> > Also if i remove the particular index it shows me a Table Scan in
> > Execution Plan.
> > Can anyone help me explain why Bookmark lookup takes the same time as
> > Table Scan?
> > Thanks & Regards
> > Vishal.
> >
> A bookmark lookup occurs when the index that was used to satisfy the
> query doesn't contain all of the columns that were requested from a
> specific table. Below is a snippet from an earlier response that I
> posted to a similar question:
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Vishal,
here's an article that sums it up nicely:
http://www.informit.com/articles/article.asp?p=27015&seqNum=6&rl=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Vishal,
avoiding the * (all columns) and just getting the ones you need might make
it possible to use a covering index (as in other part of this thread).
But to answer your question with a question, why would you assume that
bookmark lookups are always faster than a tablescan? IE there's a logical
read ("set statistics io on" to see) cutoff point after which carrying out
the bookmark lookup will prove more expensive than a tablescan, eg in a
tablescan you'll read each page once while using a bookmark lookup you might
read each page 100 times.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Showing posts with label sp4. Show all posts
Showing posts with label sp4. Show all posts
Thursday, March 22, 2012
Bookmark lookup takes time in SQL server 2000
Hi All
I have an application with SQL Server 2000 SP4 as my RDBMS.
There is a table with around 1200000 records with all the necessary
indexes defined when i execute the query through query analyzer the no
of rows returned are around 300000, execution plan shows a Bookmark
Lookup. But the bookmark lookup taks lot of time?
Also if i remove the particular index it shows me a Table Scan in
Execution Plan.
Can anyone help me explain why Bookmark lookup takes the same time as
Table Scan?
Thanks & Regards
Vishal.
I am not sure if I can but perhaps a covering index would be helpful here.
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:1159864815.076365.227450@.k70g2000cwa.googlegr oups.com...
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
|||Vishal wrote:
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
A bookmark lookup occurs when the index that was used to satisfy the
query doesn't contain all of the columns that were requested from a
specific table. Below is a snippet from an earlier response that I
posted to a similar question:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Vishal,
to add another point to the mix, you might just want to check if it's
possible to use a covering index.
I mention this as you are referring to queries against one single table and
there is often a huge time saving if you can cover the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul
Can u tell me more about covering Indexes?
As in :
What do they mean?
How to define them?
Rgds
Vishal.
Paul Ibison wrote:
> Vishal,
> to add another point to the mix, you might just want to check if it's
> possible to use a covering index.
> I mention this as you are referring to queries against one single table and
> there is often a huge time saving if you can cover the query.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Tracy Thanks for ur info on Bookmark Lookup.
But does anyone have any Idea Why Bookmark Lookup is taking up same
time as a Table Scan does?
The table on which the query executes has about 80 columns defined.
Could this be the reason when I Execute a Select * on such a Table it
takes same time as a Table Scan?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> A bookmark lookup occurs when the index that was used to satisfy the
> query doesn't contain all of the columns that were requested from a
> specific table. Below is a snippet from an earlier response that I
> posted to a similar question:
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Vishal,
here's an article that sums it up nicely:
http://www.informit.com/articles/art...&seqNum=6&rl=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Vishal,
avoiding the * (all columns) and just getting the ones you need might make
it possible to use a covering index (as in other part of this thread).
But to answer your question with a question, why would you assume that
bookmark lookups are always faster than a tablescan? IE there's a logical
read ("set statistics io on" to see) cutoff point after which carrying out
the bookmark lookup will prove more expensive than a tablescan, eg in a
tablescan you'll read each page once while using a bookmark lookup you might
read each page 100 times.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I have an application with SQL Server 2000 SP4 as my RDBMS.
There is a table with around 1200000 records with all the necessary
indexes defined when i execute the query through query analyzer the no
of rows returned are around 300000, execution plan shows a Bookmark
Lookup. But the bookmark lookup taks lot of time?
Also if i remove the particular index it shows me a Table Scan in
Execution Plan.
Can anyone help me explain why Bookmark lookup takes the same time as
Table Scan?
Thanks & Regards
Vishal.
I am not sure if I can but perhaps a covering index would be helpful here.
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:1159864815.076365.227450@.k70g2000cwa.googlegr oups.com...
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
|||Vishal wrote:
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
A bookmark lookup occurs when the index that was used to satisfy the
query doesn't contain all of the columns that were requested from a
specific table. Below is a snippet from an earlier response that I
posted to a similar question:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Vishal,
to add another point to the mix, you might just want to check if it's
possible to use a covering index.
I mention this as you are referring to queries against one single table and
there is often a huge time saving if you can cover the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul
Can u tell me more about covering Indexes?
As in :
What do they mean?
How to define them?
Rgds
Vishal.
Paul Ibison wrote:
> Vishal,
> to add another point to the mix, you might just want to check if it's
> possible to use a covering index.
> I mention this as you are referring to queries against one single table and
> there is often a huge time saving if you can cover the query.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Tracy Thanks for ur info on Bookmark Lookup.
But does anyone have any Idea Why Bookmark Lookup is taking up same
time as a Table Scan does?
The table on which the query executes has about 80 columns defined.
Could this be the reason when I Execute a Select * on such a Table it
takes same time as a Table Scan?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> A bookmark lookup occurs when the index that was used to satisfy the
> query doesn't contain all of the columns that were requested from a
> specific table. Below is a snippet from an earlier response that I
> posted to a similar question:
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Vishal,
here's an article that sums it up nicely:
http://www.informit.com/articles/art...&seqNum=6&rl=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Vishal,
avoiding the * (all columns) and just getting the ones you need might make
it possible to use a covering index (as in other part of this thread).
But to answer your question with a question, why would you assume that
bookmark lookups are always faster than a tablescan? IE there's a logical
read ("set statistics io on" to see) cutoff point after which carrying out
the bookmark lookup will prove more expensive than a tablescan, eg in a
tablescan you'll read each page once while using a bookmark lookup you might
read each page 100 times.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Bookmark lookup takes time in SQL server 2000
Hi All
I have an application with SQL Server 2000 SP4 as my RDBMS.
There is a table with around 1200000 records with all the necessary
indexes defined when i execute the query through query analyzer the no
of rows returned are around 300000, execution plan shows a Bookmark
Lookup. But the bookmark lookup taks lot of time?
Also if i remove the particular index it shows me a Table Scan in
Execution Plan.
Can anyone help me explain why Bookmark lookup takes the same time as
Table Scan?
Thanks & Regards
Vishal.I am not sure if I can but perhaps a covering index would be helpful here.
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:1159864815.076365.227450@.k70g2000cwa.googlegroups.com...
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>|||Vishal wrote:
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
A bookmark lookup occurs when the index that was used to satisfy the
query doesn't contain all of the columns that were requested from a
specific table. Below is a snippet from an earlier response that I
posted to a similar question:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Vishal,
to add another point to the mix, you might just want to check if it's
possible to use a covering index.
I mention this as you are referring to queries against one single table and
there is often a huge time saving if you can cover the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Can u tell me more about covering Indexes'
As in :
What do they mean'
How to define them?
Rgds
Vishal.
Paul Ibison wrote:
> Vishal,
> to add another point to the mix, you might just want to check if it's
> possible to use a covering index.
> I mention this as you are referring to queries against one single table an
d
> there is often a huge time saving if you can cover the query.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Tracy Thanks for ur info on Bookmark Lookup.
But does anyone have any Idea Why Bookmark Lookup is taking up same
time as a Table Scan does?
The table on which the query executes has about 80 columns defined.
Could this be the reason when I Execute a Select * on such a Table it
takes same time as a Table Scan?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> A bookmark lookup occurs when the index that was used to satisfy the
> query doesn't contain all of the columns that were requested from a
> specific table. Below is a snippet from an earlier response that I
> posted to a similar question:
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Vishal,
here's an article that sums it up nicely:
http://www.informit.com/articles/ar...5&seqNum=6&rl=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Vishal,
avoiding the * (all columns) and just getting the ones you need might make
it possible to use a covering index (as in other part of this thread).
But to answer your question with a question, why would you assume that
bookmark lookups are always faster than a tablescan? IE there's a logical
read ("set statistics io on" to see) cutoff point after which carrying out
the bookmark lookup will prove more expensive than a tablescan, eg in a
tablescan you'll read each page once while using a bookmark lookup you might
read each page 100 times.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I have an application with SQL Server 2000 SP4 as my RDBMS.
There is a table with around 1200000 records with all the necessary
indexes defined when i execute the query through query analyzer the no
of rows returned are around 300000, execution plan shows a Bookmark
Lookup. But the bookmark lookup taks lot of time?
Also if i remove the particular index it shows me a Table Scan in
Execution Plan.
Can anyone help me explain why Bookmark lookup takes the same time as
Table Scan?
Thanks & Regards
Vishal.I am not sure if I can but perhaps a covering index would be helpful here.
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:1159864815.076365.227450@.k70g2000cwa.googlegroups.com...
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>|||Vishal wrote:
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
A bookmark lookup occurs when the index that was used to satisfy the
query doesn't contain all of the columns that were requested from a
specific table. Below is a snippet from an earlier response that I
posted to a similar question:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Vishal,
to add another point to the mix, you might just want to check if it's
possible to use a covering index.
I mention this as you are referring to queries against one single table and
there is often a huge time saving if you can cover the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Can u tell me more about covering Indexes'
As in :
What do they mean'
How to define them?
Rgds
Vishal.
Paul Ibison wrote:
> Vishal,
> to add another point to the mix, you might just want to check if it's
> possible to use a covering index.
> I mention this as you are referring to queries against one single table an
d
> there is often a huge time saving if you can cover the query.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Tracy Thanks for ur info on Bookmark Lookup.
But does anyone have any Idea Why Bookmark Lookup is taking up same
time as a Table Scan does?
The table on which the query executes has about 80 columns defined.
Could this be the reason when I Execute a Select * on such a Table it
takes same time as a Table Scan?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> A bookmark lookup occurs when the index that was used to satisfy the
> query doesn't contain all of the columns that were requested from a
> specific table. Below is a snippet from an earlier response that I
> posted to a similar question:
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Vishal,
here's an article that sums it up nicely:
http://www.informit.com/articles/ar...5&seqNum=6&rl=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Vishal,
avoiding the * (all columns) and just getting the ones you need might make
it possible to use a covering index (as in other part of this thread).
But to answer your question with a question, why would you assume that
bookmark lookups are always faster than a tablescan? IE there's a logical
read ("set statistics io on" to see) cutoff point after which carrying out
the bookmark lookup will prove more expensive than a tablescan, eg in a
tablescan you'll read each page once while using a bookmark lookup you might
read each page 100 times.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Thursday, March 8, 2012
blocking
I have seen spids blocking it self alot, after installing sql sp4.
As anyone notiice this?
Thanks,
Peter
yup
"peter" <peter@.discussions.microsoft.com> wrote in message
news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
>I have seen spids blocking it self alot, after installing sql sp4.
> As anyone notiice this?
> Thanks,
> Peter
|||Thanks for the response.
Is this a bug or does this cause performance issue?
"David J. Cartwright" wrote:
> yup
> "peter" <peter@.discussions.microsoft.com> wrote in message
> news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
>
>
|||dont know if bug and i have not seen any performance degridation...although
i do not have a big transactional db...more of a warehouse type situation
with bulk inserts, which his where i have seen this occur, but they have not
slowed or taken more time
what have you seen ?
"peter" <peter@.discussions.microsoft.com> wrote in message
news:61F37E3E-9152-47C9-A2CB-5E5C03F3CA59@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> Is this a bug or does this cause performance issue?
>
> "David J. Cartwright" wrote:
|||I have seen alot of blocking, which I did not see when I had sp3a.
"David J. Cartwright" wrote:
> dont know if bug and i have not seen any performance degridation...although
> i do not have a big transactional db...more of a warehouse type situation
> with bulk inserts, which his where i have seen this occur, but they have not
> slowed or taken more time
> what have you seen ?
> "peter" <peter@.discussions.microsoft.com> wrote in message
> news:61F37E3E-9152-47C9-A2CB-5E5C03F3CA59@.microsoft.com...
>
>
As anyone notiice this?
Thanks,
Peter
yup
"peter" <peter@.discussions.microsoft.com> wrote in message
news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
>I have seen spids blocking it self alot, after installing sql sp4.
> As anyone notiice this?
> Thanks,
> Peter
|||Thanks for the response.
Is this a bug or does this cause performance issue?
"David J. Cartwright" wrote:
> yup
> "peter" <peter@.discussions.microsoft.com> wrote in message
> news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
>
>
|||dont know if bug and i have not seen any performance degridation...although
i do not have a big transactional db...more of a warehouse type situation
with bulk inserts, which his where i have seen this occur, but they have not
slowed or taken more time
what have you seen ?
"peter" <peter@.discussions.microsoft.com> wrote in message
news:61F37E3E-9152-47C9-A2CB-5E5C03F3CA59@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> Is this a bug or does this cause performance issue?
>
> "David J. Cartwright" wrote:
|||I have seen alot of blocking, which I did not see when I had sp3a.
"David J. Cartwright" wrote:
> dont know if bug and i have not seen any performance degridation...although
> i do not have a big transactional db...more of a warehouse type situation
> with bulk inserts, which his where i have seen this occur, but they have not
> slowed or taken more time
> what have you seen ?
> "peter" <peter@.discussions.microsoft.com> wrote in message
> news:61F37E3E-9152-47C9-A2CB-5E5C03F3CA59@.microsoft.com...
>
>
Wednesday, March 7, 2012
blocking
I have seen spids blocking it self alot, after installing sql sp4.
As anyone notiice this?
Thanks,
Peteryup
"peter" <peter@.discussions.microsoft.com> wrote in message
news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
>I have seen spids blocking it self alot, after installing sql sp4.
> As anyone notiice this?
> Thanks,
> Peter|||Thanks for the response.
Is this a bug or does this cause performance issue?
"David J. Cartwright" wrote:
> yup
> "peter" <peter@.discussions.microsoft.com> wrote in message
> news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
>
>|||dont know if bug and i have not seen any performance degridation...although
i do not have a big transactional db...more of a warehouse type situation
with bulk inserts, which his where i have seen this occur, but they have not
slowed or taken more time
what have you seen ?
"peter" <peter@.discussions.microsoft.com> wrote in message
news:61F37E3E-9152-47C9-A2CB-5E5C03F3CA59@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> Is this a bug or does this cause performance issue?
>
> "David J. Cartwright" wrote:
>|||I have seen alot of blocking, which I did not see when I had sp3a.
"David J. Cartwright" wrote:
> dont know if bug and i have not seen any performance degridation...althou
gh
> i do not have a big transactional db...more of a warehouse type situation
> with bulk inserts, which his where i have seen this occur, but they have n
ot
> slowed or taken more time
> what have you seen ?
> "peter" <peter@.discussions.microsoft.com> wrote in message
> news:61F37E3E-9152-47C9-A2CB-5E5C03F3CA59@.microsoft.com...
>
>
As anyone notiice this?
Thanks,
Peteryup
"peter" <peter@.discussions.microsoft.com> wrote in message
news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
>I have seen spids blocking it self alot, after installing sql sp4.
> As anyone notiice this?
> Thanks,
> Peter|||Thanks for the response.
Is this a bug or does this cause performance issue?
"David J. Cartwright" wrote:
> yup
> "peter" <peter@.discussions.microsoft.com> wrote in message
> news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
>
>|||dont know if bug and i have not seen any performance degridation...although
i do not have a big transactional db...more of a warehouse type situation
with bulk inserts, which his where i have seen this occur, but they have not
slowed or taken more time
what have you seen ?
"peter" <peter@.discussions.microsoft.com> wrote in message
news:61F37E3E-9152-47C9-A2CB-5E5C03F3CA59@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> Is this a bug or does this cause performance issue?
>
> "David J. Cartwright" wrote:
>|||I have seen alot of blocking, which I did not see when I had sp3a.
"David J. Cartwright" wrote:
> dont know if bug and i have not seen any performance degridation...althou
gh
> i do not have a big transactional db...more of a warehouse type situation
> with bulk inserts, which his where i have seen this occur, but they have n
ot
> slowed or taken more time
> what have you seen ?
> "peter" <peter@.discussions.microsoft.com> wrote in message
> news:61F37E3E-9152-47C9-A2CB-5E5C03F3CA59@.microsoft.com...
>
>
blocking
I have seen spids blocking it self alot, after installing sql sp4.
As anyone notiice this?
Thanks,
Peteryup
"peter" <peter@.discussions.microsoft.com> wrote in message
news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
>I have seen spids blocking it self alot, after installing sql sp4.
> As anyone notiice this?
> Thanks,
> Peter|||Thanks for the response.
Is this a bug or does this cause performance issue?
"David J. Cartwright" wrote:
> yup
> "peter" <peter@.discussions.microsoft.com> wrote in message
> news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
> >I have seen spids blocking it self alot, after installing sql sp4.
> >
> > As anyone notiice this?
> >
> > Thanks,
> > Peter
>
>|||dont know if bug and i have not seen any performance degridation...although
i do not have a big transactional db...more of a warehouse type situation
with bulk inserts, which his where i have seen this occur, but they have not
slowed or taken more time
what have you seen ?
"peter" <peter@.discussions.microsoft.com> wrote in message
news:61F37E3E-9152-47C9-A2CB-5E5C03F3CA59@.microsoft.com...
> Thanks for the response.
> Is this a bug or does this cause performance issue?
>
> "David J. Cartwright" wrote:
>> yup
>> "peter" <peter@.discussions.microsoft.com> wrote in message
>> news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
>> >I have seen spids blocking it self alot, after installing sql sp4.
>> >
>> > As anyone notiice this?
>> >
>> > Thanks,
>> > Peter
>>|||I have seen alot of blocking, which I did not see when I had sp3a.
"David J. Cartwright" wrote:
> dont know if bug and i have not seen any performance degridation...although
> i do not have a big transactional db...more of a warehouse type situation
> with bulk inserts, which his where i have seen this occur, but they have not
> slowed or taken more time
> what have you seen ?
> "peter" <peter@.discussions.microsoft.com> wrote in message
> news:61F37E3E-9152-47C9-A2CB-5E5C03F3CA59@.microsoft.com...
> > Thanks for the response.
> >
> > Is this a bug or does this cause performance issue?
> >
> >
> > "David J. Cartwright" wrote:
> >
> >> yup
> >>
> >> "peter" <peter@.discussions.microsoft.com> wrote in message
> >> news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
> >> >I have seen spids blocking it self alot, after installing sql sp4.
> >> >
> >> > As anyone notiice this?
> >> >
> >> > Thanks,
> >> > Peter
> >>
> >>
> >>
>
>
As anyone notiice this?
Thanks,
Peteryup
"peter" <peter@.discussions.microsoft.com> wrote in message
news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
>I have seen spids blocking it self alot, after installing sql sp4.
> As anyone notiice this?
> Thanks,
> Peter|||Thanks for the response.
Is this a bug or does this cause performance issue?
"David J. Cartwright" wrote:
> yup
> "peter" <peter@.discussions.microsoft.com> wrote in message
> news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
> >I have seen spids blocking it self alot, after installing sql sp4.
> >
> > As anyone notiice this?
> >
> > Thanks,
> > Peter
>
>|||dont know if bug and i have not seen any performance degridation...although
i do not have a big transactional db...more of a warehouse type situation
with bulk inserts, which his where i have seen this occur, but they have not
slowed or taken more time
what have you seen ?
"peter" <peter@.discussions.microsoft.com> wrote in message
news:61F37E3E-9152-47C9-A2CB-5E5C03F3CA59@.microsoft.com...
> Thanks for the response.
> Is this a bug or does this cause performance issue?
>
> "David J. Cartwright" wrote:
>> yup
>> "peter" <peter@.discussions.microsoft.com> wrote in message
>> news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
>> >I have seen spids blocking it self alot, after installing sql sp4.
>> >
>> > As anyone notiice this?
>> >
>> > Thanks,
>> > Peter
>>|||I have seen alot of blocking, which I did not see when I had sp3a.
"David J. Cartwright" wrote:
> dont know if bug and i have not seen any performance degridation...although
> i do not have a big transactional db...more of a warehouse type situation
> with bulk inserts, which his where i have seen this occur, but they have not
> slowed or taken more time
> what have you seen ?
> "peter" <peter@.discussions.microsoft.com> wrote in message
> news:61F37E3E-9152-47C9-A2CB-5E5C03F3CA59@.microsoft.com...
> > Thanks for the response.
> >
> > Is this a bug or does this cause performance issue?
> >
> >
> > "David J. Cartwright" wrote:
> >
> >> yup
> >>
> >> "peter" <peter@.discussions.microsoft.com> wrote in message
> >> news:BCB6734A-0F85-46E8-B476-8F55C13A1132@.microsoft.com...
> >> >I have seen spids blocking it self alot, after installing sql sp4.
> >> >
> >> > As anyone notiice this?
> >> >
> >> > Thanks,
> >> > Peter
> >>
> >>
> >>
>
>
Tuesday, February 14, 2012
Blackberry Server MSDE and active directory authentication
To all, thanks in advance,
I have blackberry server running sql msde 2000 (sp4), which is
authenticated via windows integrated with an active directory user
account. When I changed the password on said user account, restart the
mssqlserver service, it fails to start with a 560 error. I had to
changed said user account back to the old password in order for the
mssqlserver service to start. I wanted know where in ms sql msde 2000,
I needed to make the change in order for this service to start. I also
tried starting the service using local system account and it would not
start either. All help with this is greatly appreciated.
JB
Hi
Control Panel > Services. The Service can be stopped and started from there,
and the account password can be changed there for the service.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<jon.jefferson@.gmail.com> wrote in message
news:1131849352.800496.311170@.g47g2000cwa.googlegr oups.com...
> To all, thanks in advance,
> I have blackberry server running sql msde 2000 (sp4), which is
> authenticated via windows integrated with an active directory user
> account. When I changed the password on said user account, restart the
> mssqlserver service, it fails to start with a 560 error. I had to
> changed said user account back to the old password in order for the
> mssqlserver service to start. I wanted know where in ms sql msde 2000,
> I needed to make the change in order for this service to start. I also
> tried starting the service using local system account and it would not
> start either. All help with this is greatly appreciated.
> JB
>
|||Mike
Thanks for your response, but I did that and the service would not
start under with the new password. The only idea I did not try was to
restart the Server.
Thanks,
JB
|||Mike,
Also, there are other services that start with this same service
account (i.e. all the blackberry services,) and those start fine,
except for errors in the Apps event log because the msssqlserver
service would not start with the same user account change for some
strange reason. I am changing passwords to in AD because I am taking
over duties after someone has left and need to change passwords for
security purposes. This is my only hold up.
JB
I have blackberry server running sql msde 2000 (sp4), which is
authenticated via windows integrated with an active directory user
account. When I changed the password on said user account, restart the
mssqlserver service, it fails to start with a 560 error. I had to
changed said user account back to the old password in order for the
mssqlserver service to start. I wanted know where in ms sql msde 2000,
I needed to make the change in order for this service to start. I also
tried starting the service using local system account and it would not
start either. All help with this is greatly appreciated.
JB
Hi
Control Panel > Services. The Service can be stopped and started from there,
and the account password can be changed there for the service.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<jon.jefferson@.gmail.com> wrote in message
news:1131849352.800496.311170@.g47g2000cwa.googlegr oups.com...
> To all, thanks in advance,
> I have blackberry server running sql msde 2000 (sp4), which is
> authenticated via windows integrated with an active directory user
> account. When I changed the password on said user account, restart the
> mssqlserver service, it fails to start with a 560 error. I had to
> changed said user account back to the old password in order for the
> mssqlserver service to start. I wanted know where in ms sql msde 2000,
> I needed to make the change in order for this service to start. I also
> tried starting the service using local system account and it would not
> start either. All help with this is greatly appreciated.
> JB
>
|||Mike
Thanks for your response, but I did that and the service would not
start under with the new password. The only idea I did not try was to
restart the Server.
Thanks,
JB
|||Mike,
Also, there are other services that start with this same service
account (i.e. all the blackberry services,) and those start fine,
except for errors in the Apps event log because the msssqlserver
service would not start with the same user account change for some
strange reason. I am changing passwords to in AD because I am taking
over duties after someone has left and need to change passwords for
security purposes. This is my only hold up.
JB
Sunday, February 12, 2012
Bizarre data loss running maintenance plan
I'm running MSDE 2000 SP4 on Win2KAS SP4 (fully patched). I have a
maintenance plan that backs up the database at 18:30 and backs up the
transaction log at midnight. Apparently the transaction log backups have
been failing because the database is set to the Simple recovery model. More
importantly, I'm losing updates to records that were updated prior to the
database backup! The inserted records are still there, but the updates
appear to have been rolled back (at least in one table where I've verifiied
the problem.) I'm running some more checks now and I've set the recovery
model to Full, but I'm interested in knowing if anyone else has seen anything
like this before.
hi,
Donald Welker wrote:
> I'm running MSDE 2000 SP4 on Win2KAS SP4 (fully patched). I have a
> maintenance plan that backs up the database at 18:30 and backs up the
> transaction log at midnight. Apparently the transaction log backups
> have been failing because the database is set to the Simple recovery
> model.
and this is a standard behavior :D
>More importantly, I'm losing updates to records that were
> updated prior to the database backup! The inserted records are still
> there, but the updates appear to have been rolled back (at least in
> one table where I've verifiied the problem.) I'm running some more
> checks now and I've set the recovery model to Full, but I'm
> interested in knowing if anyone else has seen anything like this
> before.
I do not think this has something to do with backup operation, even if
performed via Maintenance Plan (unfortunately a little buggy tool)... it's
actually not possible (and the simple recovery model is no guilty at all)
for a backup task to rollback update statements while committing insert...
the problem must be else where...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Overnight testing has isolated the occurence of the change to running the
transaction log backup at midnight. The data is fine at 23:55:01 and bad at
00:00:01. However it seems that I cannot reproduce this result even by
rescheduling the maintenance plan job steps to run in sequence during the
day. I've just dropped and replaced the maintenance plan (with a better
schedule) to see if that has any effect. Also, the new plan only backs up
this database rather than "All User Databases" (might also be related.)
"Andrea Montanari" wrote:
> hi,
> Donald Welker wrote:
> and this is a standard behavior :D
I've noticed.
> I do not think this has something to do with backup operation, even if
> performed via Maintenance Plan (unfortunately a little buggy tool)... it's
> actually not possible (and the simple recovery model is no guilty at all)
> for a backup task to rollback update statements while committing insert...
> the problem must be else where...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
It's happened three nights in a row so far, so it's certainly possible. It
still happened last night with Full recovery model, it just didn't record an
error anymore.
maintenance plan that backs up the database at 18:30 and backs up the
transaction log at midnight. Apparently the transaction log backups have
been failing because the database is set to the Simple recovery model. More
importantly, I'm losing updates to records that were updated prior to the
database backup! The inserted records are still there, but the updates
appear to have been rolled back (at least in one table where I've verifiied
the problem.) I'm running some more checks now and I've set the recovery
model to Full, but I'm interested in knowing if anyone else has seen anything
like this before.
hi,
Donald Welker wrote:
> I'm running MSDE 2000 SP4 on Win2KAS SP4 (fully patched). I have a
> maintenance plan that backs up the database at 18:30 and backs up the
> transaction log at midnight. Apparently the transaction log backups
> have been failing because the database is set to the Simple recovery
> model.
and this is a standard behavior :D
>More importantly, I'm losing updates to records that were
> updated prior to the database backup! The inserted records are still
> there, but the updates appear to have been rolled back (at least in
> one table where I've verifiied the problem.) I'm running some more
> checks now and I've set the recovery model to Full, but I'm
> interested in knowing if anyone else has seen anything like this
> before.
I do not think this has something to do with backup operation, even if
performed via Maintenance Plan (unfortunately a little buggy tool)... it's
actually not possible (and the simple recovery model is no guilty at all)
for a backup task to rollback update statements while committing insert...
the problem must be else where...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Overnight testing has isolated the occurence of the change to running the
transaction log backup at midnight. The data is fine at 23:55:01 and bad at
00:00:01. However it seems that I cannot reproduce this result even by
rescheduling the maintenance plan job steps to run in sequence during the
day. I've just dropped and replaced the maintenance plan (with a better
schedule) to see if that has any effect. Also, the new plan only backs up
this database rather than "All User Databases" (might also be related.)
"Andrea Montanari" wrote:
> hi,
> Donald Welker wrote:
> and this is a standard behavior :D
I've noticed.
> I do not think this has something to do with backup operation, even if
> performed via Maintenance Plan (unfortunately a little buggy tool)... it's
> actually not possible (and the simple recovery model is no guilty at all)
> for a backup task to rollback update statements while committing insert...
> the problem must be else where...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
It's happened three nights in a row so far, so it's certainly possible. It
still happened last night with Full recovery model, it just didn't record an
error anymore.
Friday, February 10, 2012
Bit or character data type?
SQL2K
SP4
Im creating a DB that has several Bit coulmns. The data is coming from a
mainframe. When one stored proc is run, the result set needs to have a "y",
an "n", or a " '' " (space) as thats the way current systems work. Another
proc needs to return "Eligible" or "Not eligible". I had originally thought
of using a Bit fields, but now I'm wondering why. It seems to me that if I
use Bit, I then need to convert it to display one of the things just listed.
Whereas if I use character coulmns and add check constraints to the to make
sure only certain values can be added, that conversion doesnt need to occur
when the data is being retrieved from the DB. Id have to imagine Im not the
first one to think of this as no app will probably ever return a 1 or a 0, so
Im wondering what others thoughts on the matter are?A couple of things to consider, a bit column by itself takes up one byte.
If eight of them are together in a table you can save some space. To put
"Not eligible" takes 12 or 14 bytes depending on char or varchar. Next bit
has a special behavior that says any integer other than 0 is 1. In the end
I like to format results in the reporting tool so I would generally choose a
tinyint with a check constraint. But in the case of 'y', 'n', or space
char(1) would be pretty effective.
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:57F596FC-365B-4EF5-907E-041498A0D11B@.microsoft.com...
> SQL2K
> SP4
> Im creating a DB that has several Bit coulmns. The data is coming from a
> mainframe. When one stored proc is run, the result set needs to have a
> "y",
> an "n", or a " '' " (space) as thats the way current systems work. Another
> proc needs to return "Eligible" or "Not eligible". I had originally
> thought
> of using a Bit fields, but now I'm wondering why. It seems to me that if I
> use Bit, I then need to convert it to display one of the things just
> listed.
> Whereas if I use character coulmns and add check constraints to the to
> make
> sure only certain values can be added, that conversion doesnt need to
> occur
> when the data is being retrieved from the DB. Id have to imagine Im not
> the
> first one to think of this as no app will probably ever return a 1 or a 0,
> so
> Im wondering what others thoughts on the matter are?|||Chris,
Eight or less bit columns willl occupy only one byte, therefore the bit type
should be the prefered choice if you have multiple such columns in the same
table. From SQL Server books on-line:
"Microsoft® SQL ServerT optimizes the storage used for bit columns. If there
are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If
there are from 9 through 16 bit columns, they are stored as 2 bytes, and so
on."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_2it0.asp
Leo
"Danny" <nomailbox@.nowhere.com> wrote in message
news:Uiqfg.13100$lN5.1551@.trnddc04...
>A couple of things to consider, a bit column by itself takes up one byte.
>If eight of them are together in a table you can save some space. To put
>"Not eligible" takes 12 or 14 bytes depending on char or varchar. Next bit
>has a special behavior that says any integer other than 0 is 1. In the end
>I like to format results in the reporting tool so I would generally choose
>a tinyint with a check constraint. But in the case of 'y', 'n', or space
>char(1) would be pretty effective.
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:57F596FC-365B-4EF5-907E-041498A0D11B@.microsoft.com...
>> SQL2K
>> SP4
>> Im creating a DB that has several Bit coulmns. The data is coming from a
>> mainframe. When one stored proc is run, the result set needs to have a
>> "y",
>> an "n", or a " '' " (space) as thats the way current systems work.
>> Another
>> proc needs to return "Eligible" or "Not eligible". I had originally
>> thought
>> of using a Bit fields, but now I'm wondering why. It seems to me that if
>> I
>> use Bit, I then need to convert it to display one of the things just
>> listed.
>> Whereas if I use character coulmns and add check constraints to the to
>> make
>> sure only certain values can be added, that conversion doesnt need to
>> occur
>> when the data is being retrieved from the DB. Id have to imagine Im not
>> the
>> first one to think of this as no app will probably ever return a 1 or a
>> 0, so
>> Im wondering what others thoughts on the matter are?
>
SP4
Im creating a DB that has several Bit coulmns. The data is coming from a
mainframe. When one stored proc is run, the result set needs to have a "y",
an "n", or a " '' " (space) as thats the way current systems work. Another
proc needs to return "Eligible" or "Not eligible". I had originally thought
of using a Bit fields, but now I'm wondering why. It seems to me that if I
use Bit, I then need to convert it to display one of the things just listed.
Whereas if I use character coulmns and add check constraints to the to make
sure only certain values can be added, that conversion doesnt need to occur
when the data is being retrieved from the DB. Id have to imagine Im not the
first one to think of this as no app will probably ever return a 1 or a 0, so
Im wondering what others thoughts on the matter are?A couple of things to consider, a bit column by itself takes up one byte.
If eight of them are together in a table you can save some space. To put
"Not eligible" takes 12 or 14 bytes depending on char or varchar. Next bit
has a special behavior that says any integer other than 0 is 1. In the end
I like to format results in the reporting tool so I would generally choose a
tinyint with a check constraint. But in the case of 'y', 'n', or space
char(1) would be pretty effective.
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:57F596FC-365B-4EF5-907E-041498A0D11B@.microsoft.com...
> SQL2K
> SP4
> Im creating a DB that has several Bit coulmns. The data is coming from a
> mainframe. When one stored proc is run, the result set needs to have a
> "y",
> an "n", or a " '' " (space) as thats the way current systems work. Another
> proc needs to return "Eligible" or "Not eligible". I had originally
> thought
> of using a Bit fields, but now I'm wondering why. It seems to me that if I
> use Bit, I then need to convert it to display one of the things just
> listed.
> Whereas if I use character coulmns and add check constraints to the to
> make
> sure only certain values can be added, that conversion doesnt need to
> occur
> when the data is being retrieved from the DB. Id have to imagine Im not
> the
> first one to think of this as no app will probably ever return a 1 or a 0,
> so
> Im wondering what others thoughts on the matter are?|||Chris,
Eight or less bit columns willl occupy only one byte, therefore the bit type
should be the prefered choice if you have multiple such columns in the same
table. From SQL Server books on-line:
"Microsoft® SQL ServerT optimizes the storage used for bit columns. If there
are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If
there are from 9 through 16 bit columns, they are stored as 2 bytes, and so
on."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_2it0.asp
Leo
"Danny" <nomailbox@.nowhere.com> wrote in message
news:Uiqfg.13100$lN5.1551@.trnddc04...
>A couple of things to consider, a bit column by itself takes up one byte.
>If eight of them are together in a table you can save some space. To put
>"Not eligible" takes 12 or 14 bytes depending on char or varchar. Next bit
>has a special behavior that says any integer other than 0 is 1. In the end
>I like to format results in the reporting tool so I would generally choose
>a tinyint with a check constraint. But in the case of 'y', 'n', or space
>char(1) would be pretty effective.
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:57F596FC-365B-4EF5-907E-041498A0D11B@.microsoft.com...
>> SQL2K
>> SP4
>> Im creating a DB that has several Bit coulmns. The data is coming from a
>> mainframe. When one stored proc is run, the result set needs to have a
>> "y",
>> an "n", or a " '' " (space) as thats the way current systems work.
>> Another
>> proc needs to return "Eligible" or "Not eligible". I had originally
>> thought
>> of using a Bit fields, but now I'm wondering why. It seems to me that if
>> I
>> use Bit, I then need to convert it to display one of the things just
>> listed.
>> Whereas if I use character coulmns and add check constraints to the to
>> make
>> sure only certain values can be added, that conversion doesnt need to
>> occur
>> when the data is being retrieved from the DB. Id have to imagine Im not
>> the
>> first one to think of this as no app will probably ever return a 1 or a
>> 0, so
>> Im wondering what others thoughts on the matter are?
>
Subscribe to:
Posts (Atom)