I have been fighting a problem for several weeks now where the performance of my SQL Server queries degrade over a few days of use. In addition every few days, a query will simply not return from my application's ODBC SQLExecute() call.

Rebuilding the indexes manually (in SQL Server Management Studio) "fixes" both problems.

Here is some more information;

  • DB is ~13 Gig in size
  • Using ODBC ver 3.5.1 in the app
  • The apps are C++ with ODBC interface to the SQL DB
  • Test case is on a static DB - no inserts, thus no fragmentation should be possible!
  • Small queries are initially less than 1 sec, and degrade to over 50 seconds
  • Have seen the problem on all (3) machines it's run

I'm having a hard time understanding why the same query works fine for a while, but then starts slowing waaay down??

Any ideas would be greatly appreciated.

migrated from stackoverflow.com Nov 15 '12 at 11:53

This question came from our site for professional and enthusiast programmers.

  • I threw up an answer with a few directions to go down but adding a few details to the question would help: 1.) How many rows in the table(s) involved in the slow queries? 2.) How many inserts happen into the table(s)? 3.)How much memory is on this server and is it dedicated to SQL? 4.) What do your disks look like? Dedicated? What kind of specs? 5.) What happens if you run that same query in SQL Server Management Studio yourself when it is slow? – Mike Walsh Nov 15 '12 at 13:03
  • Thanks for the great suggestions below. Here is some more info. – user1825436 Nov 16 '12 at 17:09
  • 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... – user1825436 Nov 16 '12 at 17:41
  • So based on your answer to number 5 - runs fine in SSMS even when it runs slow in app tells me it is likely a combination of the answer I posted in the "Query Plan Issues" and "Too many adhoc plans" issue. Is this multiple queries this happens on or only a couple? – Mike Walsh Nov 17 '12 at 4:39
  • 1
    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. – user1825436 Nov 27 '12 at 16:58

Queries can start to slow down over time for a few reasons and you rebuilding the indexes can be fixing the problem a few ways. I'll share some of the more common reasons in my experience but there could be other causes as well. My guess is you are suffering from one of these issues.. I've also asked some questions as a comment to your question to see if we can get more details. But a few thoughts:

Statistics Getting Stale SQL Server maintains column and index statistics. These essentially tell the Query Optimizer how your data is distributed. This information is critical to the optimizer in choosing the right access method for data (Seek vs Scan) and then choosing the join method being used. If you have auto update statistics enabled (default setting in SQL.. At the database level) these get recomputed, but only when "enough" data changes. So if you have some inserts into your table but never manually update statistics and the inserts/updates are not enough to trigger an auto stats update you could be suffering from poor plans for your data distribution... Rebuilding your indexes also recomputes your index statistics I would create a job to manually update statistics on a regular basis, this is a best practice anyway - and the next time this happens try and just run sp_updatestats in your database and see if you notice a difference

Query plan issues You could be suffering from parameter sniffing - basically the first time a query runs one value is passed in - the query gets optimized for that value. When you next run it with a different value that would benefit from a different query plan, it suffers with the original query plan resulting in a slow query. When things run slow for the app - are they also slow if you run the same query in SQL Server Management Studio? If it is fast in SSMS but slow in the app - that can be a good sign pointing towards parameter sniffing. If it is consistently slow across the board over time for all queries and regardless of parameters, then I wouldn't look here. This article talks quite a bit about parameter sniffing.

Not enough memory/too many ad hoc plans It sounds like you are sending ad hoc SQL to SQL Server. This can bloat your plan cache sometimes, especially if you have a separate plan for each execution of a query. Depending on the memory on your server, this can also lead to the issue. How much memory is on your server? Check out this link on the problem with single use plans. You don't have a lot of great solutions in SQL Server 2005 for this problem, if you have it. If you can recreate this problem in a non-prod environment, I would suggest running DBCC FREEPROCCACHE in your non-prod environment if this happens again. Please note! This is an instance wide setting, if you do this on production - any stored query plans in cache for any database will no longer be there. It means you have to "pay" for compilations again. If you have high concurrency and a busy system, this could prove to cause issues. If this is the only real database and you are suffering from performance issues anyway, it doesn't hurt to try this in production.. If you have other Databases and just want to do it for this database, this blog post explains how to approach a clear for just one DB.

Index Fragmentation - It is possible that index fragmentation is the actual issue here, but I'm surprised it gets so bad so quick. If your tables are clustered on a key that causes fragmentation quickly and you have a lot of inserts, this could be the case. It would be made much worse if you were underpowered in terms of memory and disk IO. Setting up a job to rebuild/reorganize your indexes on a regular basis would be good. Based on your answers to some questions in the comments above there may be other things to do to minimize the impact of this.

  • 1
    +1 I'd also offer alternatives to try e.g. the server setting Optimize for ad hoc workloads and database setting parameterization forced. – Aaron Bertrand Nov 15 '12 at 16:48
  • I didn't suggest the Optimize for ad hoc workloads because they are on SQL Server 2005 and mentioned that in the answer. Good call on forced parameterization, though. – Mike Walsh Nov 15 '12 at 16:51
  • 1
    Ah yes, missed the sql-server-2005 tag. Still hopefully those options are useful to future readers who face this problem on modern versions of SQL Server. :-) – Aaron Bertrand Nov 15 '12 at 16:54
  • +1 I agree :-) And maybe that is bait to the OP to consider heading on up to 2008 or 2012 ;-) – Mike Walsh Nov 15 '12 at 16:55
  • 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. – user1825436 Nov 16 '12 at 16:33

We had the same issue with one of our production databases. Howerver a slightly different situation. The tables were read/write and containaed approx. 20+ Million records.

We rebuilt the indexes every week (weekends) which helped until Monday afternoon and then performance would degrade, because of the huge amount of data.

Depending on the size of your table it could be a memory issues where the server has only a certain amount of space available for a certain amount of record sets in cache. (Buffer Cache Hit Ratio lower than 90%). Solution: Adding more memory to the SQL Server instance might help. This might improve the Buffer Cache Hit Ratio to above 90%. The result is that the data is retrieved from memory and not from the physical drives.

If it is because the SQL database engine is "optimizing" the queries that are aimed at retrieving the data, then it could be an index statistics issue. (This was the issue with our huge table). The stastics became outdated. Solution: Refreshing the Index Statistics on a daily basis for this table might solve your problems.

Seeing as your problem is a short term issue, I would guess it is a Buffer Cache Hit Ratio (too less SQL Memory) problem. The SQL Query Optimizer will remove small record sets from meomry to accomodate for data that is more frequently retrieved.

  • 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. – user1825436 Nov 16 '12 at 19:00

A couple of things come to mind.

  1. Are the ODBC connection objects being closed and deleted at the end of each query? Otherwise the connection pool will continue to grow.

  2. Are there any missing indexes? The dynamic management views will tell you what's happening there.

  • 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. – user1825436 Nov 16 '12 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.