Our clients are divided onto about 50 different databases. The application is running fine for 49 of the 50 databases. But a certain part of the application keeps running poorly every few days for 1 of the 50 databases. If we update statistics on 3 of the tables in the database it starts running fast again for awhile. There are no apparent missing indexes and no table scans in the plan. Not sure how to troubleshoot this one. The number of rows in the tables doesn't change that much (at least not any more than the other databases). Can't figure out why the problem is limited to this one database.
are you taking backups on this one db?
We take backups on all of them. Don't think there is anything different about this one.
What are the automatic stats settings on that db?:
is_auto_create_stats_on
is_auto_update_stats_on
is_auto_update_stats_async_on
You say the 'Application' is running slow. In our company about 50% of the time someone says the Database is slow, it ends up being the Application. I'd try to get some timing data to verify the Database is in fact the problem. Maybe a query like this could help:
SELECT
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id) END AS DBName,
OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME],
last_execution_time,
cast(last_elapsed_time /1000000.0 as decimal(10, 3)) Last_Elapsed_Seconds,
cast(total_elapsed_time / execution_count/1000000.0 as decimal(10, 3)) AS AVG_ELAPSED,
*
FROM
sys.dm_exec_procedure_stats
ORDER BY
AVG_ELAPSED DESC
All set to on Scott.
For the db that is slow, set is_auto_update_stats_async_on to off. Leave auto stats on, but force SQL to do it synchronously, since it seems to be affecting the running query(ies) a lot.
When you update statistics - are you using sp_updatestats or are you performing an update statistics with full scan?
Are you certain it is actually out of date statistics that is the problem? When you update the statistics the query plan will also recompile. What happens when you recompile just the stored procedures that are running slow? Since you state that the issue gets resolved after updating statistics on a few tables you could use that to help identify which stored procedures or queries are the slow ones. Maybe use the Activity Monitor in SSMS to look for Recent Expensive Queries that have a long execution time or you could also use SQL Profiler to trap any queries that take a long time there too.
If it turns out to be just a query or two then I'd start by recompiling just the slow queries to see if everything speeds up. If it does then I'd start thinking about it being a parameter sniffing issue. There is a ton of stuff online on how to diagnose parameter sniffing and it can be a bit tricky to figure out how to solve it.
But first things first. Is the slowdown limited to just a few queries or is everything slow? Does recompiling just the queries that are slow solve the problem for at least a little while?
Jeff
Hi Kayaking Jeff,
It is just the one query that is super slow. It is only slow on the one database (not the 40 other databases). Since refreshing the statistics temporarily makes it fast again, I assumed it was the statistics.