SQL Server Performance

Help! - optimize speed of Stored Procedure(s)

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by sql_jr, May 21, 2008.

  1. sql_jr New Member

    I have been asked based on trace output to do analysis and performance optimization on some sql SP's that have the highest duration. The front-end is a high-traffic ecommerce web-site. Generally, my testing shows the data returns (in less than a second), sometimes 1-2 seconds - apparently this is NOT good enough for them. Not sure where to go next to try to increase this speed.
    When I turn on Exec Plan, I see that there is a temporary table variable that obviously does a table scan, but this is only 18% of 17% of the total query cost, and then 50% of 6% of the total query cost in the respective batches. Am I even looking at the right stats and is this the problem area? Everything else "looks" ok, for ex a 100% Clustered Index Seek - isn't this most desirable? Since I am not a Perf expert, please provide some insight and tips. (I guess they want result returned before they execute it [:p] - joke) THX IN ADVANCE!
  2. MichaelB Member

    I read that you were told to do some tracing, but I dont hear much about the results. How much tracing did you do? I would recommend going to http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm and implementing some server side tracing which is quite powerful for longer term tracing. I would suggest capturing the results and intserting them into a table when you close the trace. I like to run 10 min traces every hour and examine the results. Get quantity of those traces too to see what is called a lot. For example, if you have one query that takes 2 seconds, but it is called several hundered times a day, that would be a good one to look at compared to one that takes 1 second but runs once.
    Once you have this information, then run the query in Management studio and run it statement by statement in your testing environment. Make sure you clear the cache! I never go by the plan until I know what takes a long time in returning data. Sometimes one will show 45% query cost, but another line is the one that takes all the time. Figure out which part causes the issue and hone in on that one. Is it using the right indexes? Run DTA and find out what DTA recommends. I doubt that all your procs are <1 second but it could be. Only a complete trace over time will figure that out for sure [:D] I hope this helps, if not, lets keep going!

Share This Page