Slow query that times-out my application, any advice? I have a query that it seems taking about 50 seconds to execute from SQL Management Studio, but when a user tries to run it as a report from a front end application (that was built in VB), the application seems to through an error of "timing-out expired (after 20 seconds),The timeout period elapsed prior to completion of the operation or the server is not responding"
So it's not waiting for SQL to finish executing the stored procedure.

It was running fine up until today, where one of the developers had to update (several 100's) rows for a column in one of the joined tables for that stored procedure, could that affected indexes, statistics..? I'm not sure where to start checking, please advice.

This report only acting up for one division (Central America), so when I log in as a West division user or any other division, it executes within 2 seconds for the same amount of rows (about 200 rows)...but I remember that it ran successfully couple of times this morning when I log back in from Central America. I checked it back again after peak hours, errors still the same! 

I'm using SQL Server 2008 r2, Standard edition, not using any tools unfortunately.
The most likely case is either parameter sniffing or, less likely, bad statistics, with a very unlikely side of index split pages (thousands of rows is pretty small for that).
To prove parameter sniffing:

Turn on profiler or a trace for your particular Session_ID; watch Reads, Writes, CPU, Duration for events SQL:BatchCompleted and RPC:Completed

Invalidate the query cache for your particular query, which you can do by either DBCC FREEPROCCACHE  sql_handle
a. Note that if you do DBCC FREEPROCCACHE without any arguments, it frees the entire procedure cache, which has serious though short lived bad performance impacts on production boxes, since EVERY query plan now needs to be rebuilt.

Run your query for the most common set of parameters

Run your query for the most common set of parameters (again; ignore the results from #2, since they may include caching data)

Run your query for the least common, most unusual (index statistics wise) set of parameters.

Run your query for the least common, most unusual (index statistics wise) set of parameters (again; ignore the results from #4, since they may include caching data)

Do steps 1-5 again, EXCEPT do 1,2,5,6,3,4 - i.e. do the least common first, and the most common second

If your results on the second run of each set are significantly different on reads, writes, and/or CPU, you've proved parameter sniffing!  If not, you may still have parameter sniffing issues on other combinations, or you may not.


If it takes arguments that vary from division to division, it's probably parameter sniffing.  Read Erland Sommarskog's article Dynamic Search Conditions in T‑SQL which covers several ways of handling the various execution plans required.  WITH RECOMPILE is the quick band-aid, multiple stored procedures is the cadillac solution, and very careful dynamic SQL is another option (NEVER concatenating parameters into the SQL statement, only using them as actual parameters and as part of IF statements that put blocks of code in).
You can ALTER INDEX ALL on  REBUILD and it'll also update statistics, and see if that makes a difference.  Use ONLINE=ON if you're on Enterprise edition and need to, otherwise it's going to lock those tables.
Oh - make sure all tables have high cardinality clustered indexes first!