SQL Server Performance

web site search

Discussion in 'Performance Tuning for DBAs' started by bfarr23, Aug 15, 2004.

  1. bfarr23 New Member

    we have a main web site search that averages 200ms-500ms. There are 4 db servers.

    every now and then the search will start to freak out and jump to 3 secs, 5 secs, etc...
    then same searchs are happening. nothing unusual about the searches when it "freaks" out.

    the "fix" is to capture a sp from profiler, run it in query analyzer 2-3 times. then the searches goes back down in the 200ms-500ms range.

    explain this.

    i am investigating the execution plan now cause i assume a nested loop is changing to merge or hash.

    but why does this happen? why is sql server doing this?

  2. Argyle New Member

    It could be that you have a cached query plan that is good for search value X but is not good for search value Y. A plan that is good for one value could lead to a major increase in bookmark lookups for another.

    Try to split the the search categories into different stored procedures if possible or do some test with the WITH RECOMPILE statment in the stored procedure.
  3. bfarr23 New Member

    there are about 15 different paramters passed into this main search sp.

    the search critera changes often and the site can be performing fine for days, then all of a sudden the search time can go from 500ms to 5 seconds. And this can continue on for all that day.......where the parameters are changing all day.

    this sp is finely tuned.

    It's sql server that starts to freak out here. How can it be fine for 5 days, then on the 6th day for ex. the sp starts to run high times.

  4. bfarr23 New Member

    as an fyi - this sp is called on average 2 million times a day.

  5. derrickleggett New Member

    It could still be the execution plans by different parameters. Genereally, on critical procedures you want to break them down into small pieces based on research of what people search for the most. You then have one catch all if it doesn't fit into any of them. Search queries are famous for just what you are talking about. They will run fine, then they get stuck with a bad query plan that SQL Server for some reason doesn't get rid of.

    If you run an sp_recompile, you are likely to see the same results from capturing the query in Profiler and running it several times. Give it a try and let us know.


    When life gives you a lemon, fire the DBA.
  6. satya Moderator

Share This Page