Re: SQL2K Question

Snake
Create your stored procedure WITH RECOMPILE option. In SQL Server 2005 MS
has introduced a couple of new fetaures to deal with "parameter sniffing".
You may create and cachee "a good" execution plan , or just RECOMPILE on
statement level

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx










"Snake Eyes" <youngbar@xxxxxxxxxxxxx> wrote in message
news:ga6dna5cU_Rhy2_ZnZ2dnUVZ_t2dnZ2d@xxxxxxxxxxxxxxxx
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)