Bookmark, Explain Plan understanding

When I display my execution plan, a node indicates an Index Seek, the object
listed is the name of the nonclustered indexe used, and the Output list has
[Bmk1002]. Normally in the Output list I see a list of columns when ther
e is
a Bookmark involved, but this is confusing to me cause it is an Index Seek
and the Output list is simply [Bmk1002]. Using "set showplan_all on" sho
ws
the same thing. In this instance do I look at the corresponding Bookmark nod
e
to determine which columns are being sought? Why does it give me a Bookmark
when I am having returned an Index Seek? This is a nasty multi-join query so
I am not including the statement or the tables and indexes. Any possible
explanations would be appreciated.
Message posted via http://www.droptable.comWell, it is because you are trying to get the column/s that do not covered
by index and SQL Server is using nonclusterd index to point to datapage to
return the data
Like
select col1, col30 from table where col1=5
col1 has an index and we will see index seek with bookmark because col30
does not have any index in that case
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:605b4028efd9b@.uwe...
> When I display my execution plan, a node indicates an Index Seek, the
> object
> listed is the name of the nonclustered indexe used, and the Output list
> has
> [Bmk1002]. Normally in the Output list I see a list of columns when th
ere
> is
> a Bookmark involved, but this is confusing to me cause it is an Index Seek
> and the Output list is simply [Bmk1002]. Using "set showplan_all on" s
hows
> the same thing. In this instance do I look at the corresponding Bookmark
> node
> to determine which columns are being sought? Why does it give me a
> Bookmark
> when I am having returned an Index Seek? This is a nasty multi-join query
> so
> I am not including the statement or the tables and indexes. Any possible
> explanations would be appreciated.
> --
> Message posted via http://www.droptable.com

Bookmark, Explain Plan understanding

When I display my execution plan, a node indicates an Index Seek, the object
listed is the name of the nonclustered indexe used, and the Output list has
[Bmk1002]. Normally in the Output list I see a list of columns when there is
a Bookmark involved, but this is confusing to me cause it is an Index Seek
and the Output list is simply [Bmk1002]. Using "set showplan_all on" shows
the same thing. In this instance do I look at the corresponding Bookmark node
to determine which columns are being sought? Why does it give me a Bookmark
when I am having returned an Index Seek? This is a nasty multi-join query so
I am not including the statement or the tables and indexes. Any possible
explanations would be appreciated.
--
Message posted via http://www.sqlmonster.comWell, it is because you are trying to get the column/s that do not covered
by index and SQL Server is using nonclusterd index to point to datapage to
return the data
Like
select col1, col30 from table where col1=5
col1 has an index and we will see index seek with bookmark because col30
does not have any index in that case
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:605b4028efd9b@.uwe...
> When I display my execution plan, a node indicates an Index Seek, the
> object
> listed is the name of the nonclustered indexe used, and the Output list
> has
> [Bmk1002]. Normally in the Output list I see a list of columns when there
> is
> a Bookmark involved, but this is confusing to me cause it is an Index Seek
> and the Output list is simply [Bmk1002]. Using "set showplan_all on" shows
> the same thing. In this instance do I look at the corresponding Bookmark
> node
> to determine which columns are being sought? Why does it give me a
> Bookmark
> when I am having returned an Index Seek? This is a nasty multi-join query
> so
> I am not including the statement or the tables and indexes. Any possible
> explanations would be appreciated.
> --
> Message posted via http://www.sqlmonster.comsql

Bookmark Lookup

Fellow Developers

i have a query that has joins of tables with huge data (more than 2G of records per table). the execution plan shows me the 80% of the execution is on a "Bookmark Lookup" on the biggest table. Does anyone have clue how can I optimize this query? other than using covering indexes...

best regards

Jeries Shahin wrote:

Other than using covering indexes...

Well, if you know the answer already....

Seriously, you need to understand how indexes in SQL Server work (you may already, so here is the short version.) The nonclustered index uses the key of the clustered index rather than keeping a pointer to the physical page. Of course, this is great almost all of the time, but can be a costly operation at times.

Are you using any hints? And what join operators are being used? That is a lot of data (assuming 2G = 2 GB and not 2 grand :) and I would have assumed it would do a hash join, unless this is doing a merge join. Posting the output of showplan_text would be a good place to start:

set showplan_text on
go

select ...
go

set showplan_text off
go

And what version of SQL Server are you on? The new INCLUDE clause on the CREATE INDEX statement could actually be the ticket. It gives you a covering index without the overhead of including data you aren't using for searching on in the B-Tree (only the leaf nodes are affected)

|||

Why don't you want to use a covering index. Thats like saying I want my car to go but I don't want to use gas. If you want performance from a relational DB system you need to use the right indexes.

I agree that the "include" option in SQL 2005 maybe an option.

|||

Using a covering index can be really costly if the index keys are really large, so it might not be a good idea in 2000 or earlier to cover an index. We don't know his usage pattern and that is a lot of data (again assuming that G means GB :) This query might only be executed once a day/week/month. There may be thousands of modifications a minute on the table.

It might even be that a reporting database/warehouse is in order.

|||

I agree but to some extent if the performance isn't what is required, then something has to be done and there are a number of options covering indexing being on of them, redesign being another.

One always has to balance out their performance needs. This gets much more complex when doing DSS stuff on an OLTP system. ideally they should be mutually exclusive.

Bizarre maintenance plan failure (db backup)

I am getting a failure on the db backup job of one of my maintenance
plans. It is coming back with the generic error message of,

"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step
failed."

I then checked the Database Maintenance Plan History page, but this
shows all the steps having run successfully. If I check the drives for
the actual backup files, they exist and look healthy too!

There is plenty of space on the drives, so it is not that.

I've checked the NT logs and all they say is,

"SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan for All User Databases''
(0xC06E15E2A9E1414087BE19541D167861) - Status: Failed - Invoked on:
2005-06-29 21:00:04 - Message: The job failed. The Job was invoked by
Schedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "

Which doesn't give me any clues.

Since the backups have actually run to success, I am going to take off
the option on the maintenance plan to "Verfiy the integrity of the
backup upon completion". Maybe it is this that is causing problems,
rather than the backup?

Anyone had anything similar?(nielsonj1976@.yahoo.co.uk) writes:
> I am getting a failure on the db backup job of one of my maintenance
> plans. It is coming back with the generic error message of,
> "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step
> failed."
> I then checked the Database Maintenance Plan History page, but this
> shows all the steps having run successfully. If I check the drives for
> the actual backup files, they exist and look healthy too!
> There is plenty of space on the drives, so it is not that.
> I've checked the NT logs and all they say is,
> "SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
> Maintenance Plan for All User Databases''
> (0xC06E15E2A9E1414087BE19541D167861) - Status: Failed - Invoked on:
> 2005-06-29 21:00:04 - Message: The job failed. The Job was invoked by
> Schedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "
> Which doesn't give me any clues.

So did you look at the Job History? Find the backup job, right-click
and select Show History. In this dialog check "Show step details". The
step details usually have some more meat for information.

> Since the backups have actually run to success, I am going to take off
> the option on the maintenance plan to "Verfiy the integrity of the
> backup upon completion". Maybe it is this that is causing problems,
> rather than the backup?

Ehum, if the problem is that the backups don't verify, that I would
not like to know that and not sweep it under the carpet.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> So did you look at the Job History? Find the backup job, right-click
> and select Show History. In this dialog check "Show step details". The
> step details usually have some more meat for information.

This doesn't really give me any clues either. If I click show step
details, I get it displaying 2 steps,

Step 1: Executed as user: OMNIA\SVC_SQL_Exec. sqlmaint.exe failed.
[SQLSTATE 42000] (Error 22029). The step failed.

Step 0: (Job Outcome) The job failed. The Job was invoked by Schedule
35 (Schedule 1). The last step to run was step 1 (Step 1).

> > Since the backups have actually run to success, I am going to take off
> > the option on the maintenance plan to "Verfiy the integrity of the
> > backup upon completion". Maybe it is this that is causing problems,
> > rather than the backup?

> Ehum, if the problem is that the backups don't verify, that I would
> not like to know that and not sweep it under the carpet.

Having checked this morning the backup status is still showing Failed
even with the verify option off. The only reason I thought it *could*
be this is that the backups are fairly large (25gb+) so SQL server
could be having problems verifying with the disk space available?|||nielsonj1976@.yahoo.co.uk (nielsonj1976@.yahoo.co.uk) writes:
> This doesn't really give me any clues either. If I click show step
> details, I get it displaying 2 steps,
> Step 1: Executed as user: OMNIA\SVC_SQL_Exec. sqlmaint.exe failed.
> [SQLSTATE 42000] (Error 22029). The step failed.
> Step 0: (Job Outcome) The job failed. The Job was invoked by Schedule
> 35 (Schedule 1). The last step to run was step 1 (Step 1).

I rarely set into maintenance job, so I don't really know where sqlmaint
might write its errors. But looking at the properties of a plan that I've
actually set up, I see that there is a Reporting tab. Have you checked
what's yours, and if that leads to a useful file?

>> > Since the backups have actually run to success, I am going to take off
>> > the option on the maintenance plan to "Verfiy the integrity of the
>> > backup upon completion". Maybe it is this that is causing problems,
>> > rather than the backup?
>> Ehum, if the problem is that the backups don't verify, that I would
>> not like to know that and not sweep it under the carpet.
> Having checked this morning the backup status is still showing Failed
> even with the verify option off. The only reason I thought it *could*
> be this is that the backups are fairly large (25gb+) so SQL server
> could be having problems verifying with the disk space available?

I will have to retract what I said above. I recalled a discussion among
my MVP colleagues who are into server administration. The consensus was
that verification of backups as offered by RESTORE VERIFYOLNLY is useless.
The only way to really verify a backup is to restore it. And run DBCC checks
on the restored copy.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Do your maint plans cover more than 1 database? If so check the recovery modes of each - this error seems to pop up if there are mixed modes within 1 plan.

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.