network profile
| bio | website | |
|---|---|---|
| location | ||
| age | ||
| visits | member for | 6 months |
| seen | Jan 9 at 17:48 | |
| stats | profile views | 0 |
|
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 |
