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?
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.
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.
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. MeanOldDBA derrickleggett@hotmail.com When life gives you a lemon, fire the DBA.
http://www.sql-server-performance.com/optimizing_indexes.asp http://www.sql-server-performance.com/asp_sql_server.asp .. articles for your reference on fine tune of SP & website search. HTH Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
