31 reputation
4
bio website
location
age
visits member for 2 years, 1 month
seen Jan 9 '13 at 17:48

Aug
15
awarded  Popular Question
Jan
9
comment Query performance degrades with time and use
Just wanted to report back that after running for 1 month we have not encountered the problem at all. It was a parameter sniffing issue, as Mike had suggested. An easy fix, of declaring all parameters as local variables in the SQL query fixed the problem. Big ups to Mike for all the help!
Nov
27
comment Query performance degrades with time and use
Update I - We modified the queries in question only to foil parameter sniffing. Local variables were declared for each parameter in the query (nothing more was changed). Since that time, everything has been running quite well for 1 week. Too early to declare victory, but I think this stands a good chance. I will update again after more time and info is available.
Nov
27
comment Query performance degrades with time and use
I have not tried the OPTION(RECOMPILE) yet, as we currently have a 1 week old test running (see update below), but I will keep it mind. The TOP(n) is simply used to limit huge data sets getting requested that would block other users. That value is always 20 for two of the queries and 1000 for the other two.
Nov
19
comment Query performance degrades with time and use
-- ODBC variable assigned query parameters DECLARE @n AS int; SELECT @n = varParam_1; WITH temp_org(t_ID, t_Parent, t_Iter) AS ( SELECT w.ID, w.Parent_ID, 0 FROM Org AS w WHERE w.ID = varParam_2 UNION ALL SELECT y.ID, x.t_Parent, x.t_Iter + 1 FROM temp_org AS x, Org AS y WHERE x.t_ID = y.Parent_ID ) SELECT TOP (@n) * FROM Table_1 WHERE ID > varParam_3 AND Type <> '9' AND IM_ID IN ( SELECT Table_2.ID FROM Table_2 LEFT JOIN Table_3 ON Table_2.ID = Table_3.IM_ID WHERE Table_3.Org_ID IN ( SELECT t_ID FROM temp_org ) ) ORDER BY Create_Time DESC
Nov
19
awarded  Commentator
Nov
19
comment Query performance degrades with time and use
1) The big table stores events with a time stamp and a couple of type fields, not too much else. It has 6 fields per row. 2) Yes, there were no inserts. This happened on my local machine which does not run the inserting applications. 3) The next comment below shows the query that causes the grief. 4) The query is called from a C++ app via ODBC that binds 3 variable input parameters.
Nov
17
comment Query performance degrades with time and use
Just a few minutes ago, it was running extremely slow again - non responsive in fact after 10 minutes from either of my apps. A couple of notes; 1) The app did get response from the login query. 2) Issuing the "slow" query from SSMS worked fine. 3) Ran sp_updatestats, restarted apps, still unresponsive. 4) Ran DBCC FREEPROCCACHE, restarted apps, still unresponsive. 5) Rebuilt one critical index in the big table, restarted, and apps worked fine again. A final note; no inserts were made from the time that the queries previously were fast.
Nov
17
comment Query performance degrades with time and use
We only have about 10 queries total, and I'd say it happens on most of them. Definitely on 4 of them.
Nov
16
awarded  Supporter
Nov
16
comment Query performance degrades with time and use
1) No we keep one ODBC connection open during the apps lifetime. The Statement cursors are closed after each query. Is there something I'm missing with this approach? I will consider any and all possibilities at this point. 2) I will look into missing indexes, thanks.
Nov
16
comment Query performance degrades with time and use
Thanks, this response is also very helpful. We too have a very large table that is being queried, 15 Million or so. I had not looked into allocating more RAM for SQL server, but will certainly try it now. Also, refreshing the statistics is something I will do the next time we get in the this state, and see if that resolves it.
Nov
16
comment Query performance degrades with time and use
1) 15 Million rows 2) 11k inserts per day 3) 24GB on server, Not dedicated to SQL - only 2GB set for max memory. 4) I don't have this info available. 5) I have tried that - it runs fine in SQL Mgr while the app is slow. Additionally, starting up a test app with the same query will run slowly. P.S - I am not a DBA, so please excuse my ignorance...
Nov
16
comment Query performance degrades with time and use
Thanks for the great suggestions below. Here is some more info.
Nov
16
comment Query performance degrades with time and use
Mike, Thanks for the extremely comprehensive answer. Several things for me to try, and I will post back as I have worked through them.
Nov
16
comment Query performance degrades with time and use
Yes - we have definitely considered updating to a modern version of SQL, but we likely won't be able to do it until we have a stable baseline first.
Nov
16
awarded  Student
Nov
15
asked Query performance degrades with time and use