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.

blocking caused from SQL with (nolock) hint

I am seeing blocking in a database that is caused by a SQL statement that
joins two tables with (nolock) hints. How is that possible? I thought
nolock would perform a dirty read and would not block readers. Is that not
the case?
Thanks,
JayJay,
No more information (environment, SQL code, etc) than the question, means
this question is hard to answer.
(1) If your SQL is doing an update, then (of course) it will lock the
resources being updated.
(2) I have also seen a repeated instance of an older version of Access
causing a lock (a SCH-M lock) even though it had no rights to make any
schema change.
Post some more details if you have them.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> I am seeing blocking in a database that is caused by a SQL statement that
> joins two tables with (nolock) hints. How is that possible? I thought
> nolock would perform a dirty read and would not block readers. Is that
not
> the case?
> Thanks,
> Jay|||Jay P wrote:
> I am seeing blocking in a database that is caused by a SQL statement
> that joins two tables with (nolock) hints. How is that possible? I
> thought nolock would perform a dirty read and would not block
> readers. Is that not the case?
> Thanks,
> Jay
I've seen undesirable results with NOLOCK on temp tables. Is this the
case? Post your SQL please.
--
David Gugick
Imceda Software
www.imceda.com|||The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b WHERE
a.col1='x' and b.col2='y' ' I am using a script to check for blocking that
generates a SQL Profiler trace and also using the sp_pss80 script to show
locks and input buffer contents but I'm having problems interpreting the
output. I do know that when I run this statement, I get some blocking going
on and I'm confused by the fact that it's just a SELECT (dirty read) type
operation albeit on a rather large table of appx. 13 million rows and is
doing a index range scan... Thanks for the reply.
"Russell Fields" wrote:
> Jay,
> No more information (environment, SQL code, etc) than the question, means
> this question is hard to answer.
> (1) If your SQL is doing an update, then (of course) it will lock the
> resources being updated.
> (2) I have also seen a repeated instance of an older version of Access
> causing a lock (a SCH-M lock) even though it had no rights to make any
> schema change.
> Post some more details if you have them.
> RLF
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> > I am seeing blocking in a database that is caused by a SQL statement that
> > joins two tables with (nolock) hints. How is that possible? I thought
> > nolock would perform a dirty read and would not block readers. Is that
> not
> > the case?
> >
> > Thanks,
> > Jay
>
>|||Jay,
Hmmmm....
If the join and select is big enough, SQL Server may need to create
worktables in order to handle the whole operation. It is possible that (if
worktables are being created) that you are doing some blocking on tempdb
system tables. Is that possible?
Beyond that, I have no brilliant ideas, because (as you say) you should be
getting dirty reads without locking.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
> The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b
WHERE
> a.col1='x' and b.col2='y' ' I am using a script to check for blocking
that
> generates a SQL Profiler trace and also using the sp_pss80 script to show
> locks and input buffer contents but I'm having problems interpreting the
> output. I do know that when I run this statement, I get some blocking
going
> on and I'm confused by the fact that it's just a SELECT (dirty read) type
> operation albeit on a rather large table of appx. 13 million rows and is
> doing a index range scan... Thanks for the reply.
> "Russell Fields" wrote:
> > Jay,
> >
> > No more information (environment, SQL code, etc) than the question,
means
> > this question is hard to answer.
> >
> > (1) If your SQL is doing an update, then (of course) it will lock the
> > resources being updated.
> >
> > (2) I have also seen a repeated instance of an older version of Access
> > causing a lock (a SCH-M lock) even though it had no rights to make any
> > schema change.
> >
> > Post some more details if you have them.
> >
> > RLF
> > "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> > news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> > > I am seeing blocking in a database that is caused by a SQL statement
that
> > > joins two tables with (nolock) hints. How is that possible? I
thought
> > > nolock would perform a dirty read and would not block readers. Is
that
> > not
> > > the case?
> > >
> > > Thanks,
> > > Jay
> >
> >
> >|||When worktables are created there should not be blocking.
You could use sp_lock to find out if blocking is caused by any lock
resource, and use select * from sysprocesses to find out the wait type and
wait time. The Books Online has more details on how to use the two.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23ErXqoW3EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Jay,
> Hmmmm....
> If the join and select is big enough, SQL Server may need to create
> worktables in order to handle the whole operation. It is possible that
> (if
> worktables are being created) that you are doing some blocking on tempdb
> system tables. Is that possible?
> Beyond that, I have no brilliant ideas, because (as you say) you should be
> getting dirty reads without locking.
> RLF
>
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
>> The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b
> WHERE
>> a.col1='x' and b.col2='y' ' I am using a script to check for blocking
> that
>> generates a SQL Profiler trace and also using the sp_pss80 script to show
>> locks and input buffer contents but I'm having problems interpreting the
>> output. I do know that when I run this statement, I get some blocking
> going
>> on and I'm confused by the fact that it's just a SELECT (dirty read) type
>> operation albeit on a rather large table of appx. 13 million rows and is
>> doing a index range scan... Thanks for the reply.
>> "Russell Fields" wrote:
>> > Jay,
>> >
>> > No more information (environment, SQL code, etc) than the question,
> means
>> > this question is hard to answer.
>> >
>> > (1) If your SQL is doing an update, then (of course) it will lock the
>> > resources being updated.
>> >
>> > (2) I have also seen a repeated instance of an older version of Access
>> > causing a lock (a SCH-M lock) even though it had no rights to make any
>> > schema change.
>> >
>> > Post some more details if you have them.
>> >
>> > RLF
>> > "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
>> > news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
>> > > I am seeing blocking in a database that is caused by a SQL statement
> that
>> > > joins two tables with (nolock) hints. How is that possible? I
> thought
>> > > nolock would perform a dirty read and would not block readers. Is
> that
>> > not
>> > > the case?
>> > >
>> > > Thanks,
>> > > Jay
>> >
>> >
>> >
>

blank cells cause trouble on join

I have a few tables that I am joining together and one of the joins is on a
field that almost always has a code in it. Sometimes it is a blank (not a
null) if the clerk didn't type anything into it. The table that I am joining
to this one, has many cells in the join field thhat are blank and those rows
are used for commenting the other records in the table (I know it is stupid
but I didn't design it).
If I do a regular join whenever it comes upon one of these blanks it joins
to all of the comment records in the 2nd table.
What would be the easiest way to get around this problem?
Below is the statement as far as I got befor the last join messed me up.
cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
blank in all rows where they are being used as comments.
SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
codentry
from osmccsdb.cdocket
left outer join osmccsdb.cparty
on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
left outer join osmccsdb.ccharge
on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
left outer join osmccsdb.ccodes
on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
order by osmccsdb.cdocket.cdevtdt descLinda,
A LEFT JOIN will return all of the records from the table on left side of
the JOIN keyword and any matching records on the right. I don't have any
sample data to work with but you might look at the NULLIF function and see
if it might help you in your situation.
HTH
Jerry
"Linda Ibarra" <LindaIbarra@.discussions.microsoft.com> wrote in message
news:8953B911-7B98-4A20-BD74-C9FFA335B9B2@.microsoft.com...
>I have a few tables that I am joining together and one of the joins is on a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am
> joining
> to this one, has many cells in the join field thhat are blank and those
> rows
> are used for commenting the other records in the table (I know it is
> stupid
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||I see this with some tools and it is kind of a pain. First thing I would
note is that it is usually a bad idea to have user inputted values being
what you are joining on (but I know you said you didn't design it)
I would probaby just add a condition to your join that says something like:
AND ccodes.codevent <> '' AND the other column too
This will eliminate them from the join. Or if you want the '' rows from one
side, but not the other, then change the '' in the join:
case when ccodes.codevent = '' then 'NOT POSSIBLE' else ccodes.codevent end
Or you could use NULL instead of 'NOT POSSIBLE'
It might not be great for performance, so you might have to do some
trickiness if you have scads of data, but that is just the price you pay :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Linda Ibarra" <LindaIbarra@.discussions.microsoft.com> wrote in message
news:8953B911-7B98-4A20-BD74-C9FFA335B9B2@.microsoft.com...
>I have a few tables that I am joining together and one of the joins is on a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am
> joining
> to this one, has many cells in the join field thhat are blank and those
> rows
> are used for commenting the other records in the table (I know it is
> stupid
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||I would prefer updating those columns with some value,say string and
eliminate that in the where clause. and make it default so that problem wil
l
not come again
--
Regards
R.D
--Knowledge gets doubled when shared
"Linda Ibarra" wrote:

> I have a few tables that I am joining together and one of the joins is on
a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am joini
ng
> to this one, has many cells in the join field thhat are blank and those ro
ws
> are used for commenting the other records in the table (I know it is stupi
d
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||You are a freaking genius!!! I am not sure that I will be allowed to do tha
t
but if I can what a relief! These are court records and I am not sure how
weird they will be about me changing data.
"R.D" wrote:
> I would prefer updating those columns with some value,say string and
> eliminate that in the where clause. and make it default so that problem w
ill
> not come again
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Linda Ibarra" wrote:
>|||It was also pointed out to me that if I just created a view of this data
where the records with blank fields were not included and used that for the
join, that that would also eliminate this problem.
"Linda Ibarra" wrote:

> I have a few tables that I am joining together and one of the joins is on
a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am joini
ng
> to this one, has many cells in the join field thhat are blank and those ro
ws
> are used for commenting the other records in the table (I know it is stupi
d
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||yup, you can always have a view. yet what if you want to use data in another
sproc or function or in another view. To solve this permanently have
amechanism where either you dont allow such values(?) or such blanks are
automatically replace with something like 'No Data'
--
Regards
R.D
--Knowledge gets doubled when shared
"Linda Ibarra" wrote:
> It was also pointed out to me that if I just created a view of this data
> where the records with blank fields were not included and used that for th
e
> join, that that would also eliminate this problem.
> "Linda Ibarra" wrote:
>