Bookmark Lookups...

I hope this doesn't show up twice. I didn't see it post. This is better
formatted :)

Anyway, here is my problem:

I have a Stored Procedure that is taking an unusual amount of time
(gradually getting slower) to run. I have analyzed the execution plan via
Query Analyzer and I have identified what portion of the Stored Procedure is
causing the problem. I have analyzed the indexes and added other index
fields in the query with no luck. The stored procedure accepts parameters,
and I use those in the where clause. Breaking down the query, I have tried
changing the data types of the @parameter variables to exactly match the
columns in the table. But still sluggish performance.

One thing I noticed, and this has happened to me before, is that if I hard
code some values in the same Select statement, the stored procedure seems to
run much faster. If I hard code a date ranges in the proc ie between
'01/01/2006' and '01/25/2006' , it runs much faster (as there are no
bookmark lookups), but if I use the same values assigning them to varchar,
char, datetime, or smalldate
time (tried most logical ones, to equal the table column) it triggers a
bookmark lookup. With the hard coded date values the execution plan shows
that the query is now utilizing the indexes and it reduces the bookmark
lookups in some cases from 49% to 15% or less.

Any ideas??

Thanks


.

Relevant Pages

  • SQL2K Question
    ... I have a Stored Procedure that is taking an unusual amount of time ... I have analyzed the execution plan via Query Analyzer and I have identified ... analyzed the indexes and added other index fields in the query with no luck. ...
    (microsoft.public.sqlserver.programming)
  • Incomplete result set returned by record select - Crystal XI, MS SQL stored procedure
    ... I've got a stored procedure written in T-SQL. ... When I use Query ... query analyzer. ... That functions fine, the prompts seem to work OK, I ...
    (microsoft.public.vb.crystal)
  • Re: Help with where clause in Stored proc and Qry Analyzer getting different rec. counts
    ... type of fkarInvFlagRefID is charor some nullable char ... you run this query. ... analyzer) and stored procedure and procedure call. ... If you are calling the stored procedure from somewhere other than query ...
    (microsoft.public.sqlserver.programming)
  • SP **
    ... I've a Stored procedure in SQL server 2000 and when I run it in Query ... executing it throght a command and ofcourse I make the connection timout ... when I alter procedure with it's script again in Query analyzer whithout any ...
    (microsoft.public.sqlserver.programming)
  • Re: noise words, @@ERROR, and stop and resume indexing
    ... > Noiseword varcharNot Null ... > the data from the noise file to the noise_words table. ... >> A clause of the query contained only ignored words. ... >> into query analyzer before starting the stored procedure. ...
    (microsoft.public.sqlserver.fulltext)