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.

Biztalk read and write access to the master database.

My fellow administrator tells me that his biztalk account needs read and
write permissions to the master database of out MSSQL 2000 Enterprice
edition, running on a windows 2000 advanced server.
Our biztalk developer does not remember that there has been an issue, so he
is not doing anything specific on the masters database, in the jobs he is
running.
Is it for real that biztalk needs this access?
--
Best regards
MikaelMikael
You have to ask him why does he want to access? What process/s does he run ?
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> My fellow administrator tells me that his biztalk account needs read and
> write permissions to the master database of out MSSQL 2000 Enterprice
> edition, running on a windows 2000 advanced server.
> Our biztalk developer does not remember that there has been an issue, so
> he
> is not doing anything specific on the masters database, in the jobs he is
> running.
> Is it for real that biztalk needs this access?
> --
> Best regards
> Mikael|||Yes, it has been a while since they did the installation and he just
remembers that it needs the permissions, but not why. My question is just if
the biztalk application itself needs this access to run?
--
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> You have to ask him why does he want to access? What process/s does he run ?
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> > My fellow administrator tells me that his biztalk account needs read and
> > write permissions to the master database of out MSSQL 2000 Enterprice
> > edition, running on a windows 2000 advanced server.
> >
> > Our biztalk developer does not remember that there has been an issue, so
> > he
> > is not doing anything specific on the masters database, in the jobs he is
> > running.
> >
> > Is it for real that biztalk needs this access?
> >
> > --
> > Best regards
> >
> > Mikael
>
>|||Mikael
To connect to SQL Server you will have to create a LOGIN. To access to the
database you will have to create an USER mapped to the LOGIN
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
> Yes, it has been a while since they did the installation and he just
> remembers that it needs the permissions, but not why. My question is just
> if
> the biztalk application itself needs this access to run?
> --
> Best regards
> Mikael
>
> "Uri Dimant" wrote:
>> Mikael
>> You have to ask him why does he want to access? What process/s does he
>> run ?
>>
>>
>> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
>> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
>> > My fellow administrator tells me that his biztalk account needs read
>> > and
>> > write permissions to the master database of out MSSQL 2000 Enterprice
>> > edition, running on a windows 2000 advanced server.
>> >
>> > Our biztalk developer does not remember that there has been an issue,
>> > so
>> > he
>> > is not doing anything specific on the masters database, in the jobs he
>> > is
>> > running.
>> >
>> > Is it for real that biztalk needs this access?
>> >
>> > --
>> > Best regards
>> >
>> > Mikael
>>|||My colleague did this and the application is running nicely, but now it is
time for the IT revision to inspect the SQL server and I need to know if I
can remove the privileges to the master database from the biztalk account
without affecting the production environment or know why I canâ't.
--
Best regards
Mikael
"Uri Dimant" wrote:
> Mikael
> To connect to SQL Server you will have to create a LOGIN. To access to the
> database you will have to create an USER mapped to the LOGIN
>
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
> > Yes, it has been a while since they did the installation and he just
> > remembers that it needs the permissions, but not why. My question is just
> > if
> > the biztalk application itself needs this access to run?
> >
> > --
> > Best regards
> >
> > Mikael
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Mikael
> >>
> >> You have to ask him why does he want to access? What process/s does he
> >> run ?
> >>
> >>
> >>
> >>
> >> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> >> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> >> > My fellow administrator tells me that his biztalk account needs read
> >> > and
> >> > write permissions to the master database of out MSSQL 2000 Enterprice
> >> > edition, running on a windows 2000 advanced server.
> >> >
> >> > Our biztalk developer does not remember that there has been an issue,
> >> > so
> >> > he
> >> > is not doing anything specific on the masters database, in the jobs he
> >> > is
> >> > running.
> >> >
> >> > Is it for real that biztalk needs this access?
> >> >
> >> > --
> >> > Best regards
> >> >
> >> > Mikael
> >>
> >>
> >>
>
>

Biztalk read and write access to the master database.

My fellow administrator tells me that his biztalk account needs read and
write permissions to the master database of out MSSQL 2000 Enterprice
edition, running on a Windows 2000 advanced server.
Our biztalk developer does not remember that there has been an issue, so he
is not doing anything specific on the masters database, in the jobs he is
running.
Is it for real that biztalk needs this access?
Best regards
MikaelMikael
You have to ask him why does he want to access? What process/s does he run ?
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> My fellow administrator tells me that his biztalk account needs read and
> write permissions to the master database of out MSSQL 2000 Enterprice
> edition, running on a Windows 2000 advanced server.
> Our biztalk developer does not remember that there has been an issue, so
> he
> is not doing anything specific on the masters database, in the jobs he is
> running.
> Is it for real that biztalk needs this access?
> --
> Best regards
> Mikael|||Yes, it has been a while since they did the installation and he just
remembers that it needs the permissions, but not why. My question is just i
f
the biztalk application itself needs this access to run?
Best regards
Mikael
"Uri Dimant" wrote:

> Mikael
> You have to ask him why does he want to access? What process/s does he run
?
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
>
>|||Mikael
To connect to SQL Server you will have to create a LOGIN. To access to the
database you will have to create an USER mapped to the LOGIN
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...[vbcol=seagreen]
> Yes, it has been a while since they did the installation and he just
> remembers that it needs the permissions, but not why. My question is just
> if
> the biztalk application itself needs this access to run?
> --
> Best regards
> Mikael
>
> "Uri Dimant" wrote:
>|||My colleague did this and the application is running nicely, but now it is
time for the IT revision to inspect the SQL server and I need to know if I
can remove the privileges to the master database from the biztalk account
without affecting the production environment or know why I can’t.
--
Best regards
Mikael
"Uri Dimant" wrote:

> Mikael
> To connect to SQL Server you will have to create a LOGIN. To access to the
> database you will have to create an USER mapped to the LOGIN
>
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
>
>

Biztalk read and write access to the master database.

My fellow administrator tells me that his biztalk account needs read and
write permissions to the master database of out MSSQL 2000 Enterprice
edition, running on a windows 2000 advanced server.
Our biztalk developer does not remember that there has been an issue, so he
is not doing anything specific on the masters database, in the jobs he is
running.
Is it for real that biztalk needs this access?
Best regards
Mikael
Mikael
You have to ask him why does he want to access? What process/s does he run ?
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
> My fellow administrator tells me that his biztalk account needs read and
> write permissions to the master database of out MSSQL 2000 Enterprice
> edition, running on a windows 2000 advanced server.
> Our biztalk developer does not remember that there has been an issue, so
> he
> is not doing anything specific on the masters database, in the jobs he is
> running.
> Is it for real that biztalk needs this access?
> --
> Best regards
> Mikael
|||Yes, it has been a while since they did the installation and he just
remembers that it needs the permissions, but not why. My question is just if
the biztalk application itself needs this access to run?
Best regards
Mikael
"Uri Dimant" wrote:

> Mikael
> You have to ask him why does he want to access? What process/s does he run ?
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:E91840AC-3A2D-4730-9A1E-1F3FBAC9B04D@.microsoft.com...
>
>
|||Mikael
To connect to SQL Server you will have to create a LOGIN. To access to the
database you will have to create an USER mapped to the LOGIN
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...[vbcol=seagreen]
> Yes, it has been a while since they did the installation and he just
> remembers that it needs the permissions, but not why. My question is just
> if
> the biztalk application itself needs this access to run?
> --
> Best regards
> Mikael
>
> "Uri Dimant" wrote:
|||My colleague did this and the application is running nicely, but now it is
time for the IT revision to inspect the SQL server and I need to know if I
can remove the privileges to the master database from the biztalk account
without affecting the production environment or know why I can’t.
Best regards
Mikael
"Uri Dimant" wrote:

> Mikael
> To connect to SQL Server you will have to create a LOGIN. To access to the
> database you will have to create an USER mapped to the LOGIN
>
>
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:F98CCA5E-3BC2-4B3E-8A95-FEF2EEFEA0E6@.microsoft.com...
>
>