December 30, 2011 at 7:36 am
We have an application that is built in Access as an ADE that uses SQL Server 2005 as the backend database. The original version was 2003 and currently is run as 2007. We have noticed an issue that periodically certain components (or all) of the application will slow down on a random basis. I will check the SQL Server and there are no performance issues, infact the other 100+ databases and applications on the server all respond very well. So it is specific to this application or database. The issue is resolved by running DBCC FREEPROCACHE on the server.
However, I don't believe it to really be a SQL Server issue. The reason is that if you run profiler and catch the transactions and run the commands via SQL Management Studio or similar method response time is immediate. But if you run those commands via the Access application it is slow. So something is happening that the app starts to respond poorly. Not being an Access person I'm not sure where to go to troubleshoot the issue further.
Has anyone seen such a thing or have suggestions at what to review or look at?
January 3, 2012 at 7:24 am
I'm not so sure I'd be too quick to dismiss the issue as SQL Server. It sounds like it could be bad parameter sniffing. Especially since clearing the procedure cache means that it recompiled all queries, likely with a different set of parameters.
The thing you need to do is check the execution of the Access processes as they're occurring. Use the sys.dm_exec_requests to see them as they come out. Then you can get the execution plans and the query stats from cache to identify what's going wrong. If you just flush the cache though, all this information goes away, so I wouldn't recommend that until you get the chance to troubleshoot further.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 4, 2012 at 9:38 am
I can look at doing that check, but how would that differ from what I have already done? In essence since I ran the application with profiler to catch the code segments that caused the slowness issue. As stated if I run them via other interfaces like SSMS there is no performance issues. If it was param sniffing or similar issue should I not see the same results of slow performance from SSMS? The fact that it runs quick I figured to indicate it was not param sniffing and something else.
January 4, 2012 at 10:03 am
If you have different ANSI settings on the connections, you'll have different plans. If you're calling the query one way from the app (parameterized query, stored proc) and a different way from SSMS (query run directly, ad hoc, etc.) you can get different plans. I just don't think based on what you've said that you have all the information and I still suspect SQL Server could be at the heart of it. But, hey, I'm guessing. I'm not there and I don't see what you see.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 4, 2012 at 10:06 am
Use that to find avg run times for all your sps and then catch the ones that are far out of whack.
Rerun that code in SSMS and save the actual execution plan and post it here (upload)
You can save the plan in the trace but that takes a crapload of ressources. Do ONLY with you on site and able to kill the trace in seconds.
January 5, 2012 at 1:07 pm
Grant...
I think your point is valid and I may have looked past that. If running it via SSMS and the ADO connection can yield different execution plan results then yes it could still be a SQL Param or other issue.
I guess I would be interested to know how to resolve it then. If it is something like param sniffing that occurs only in the ADO connection can it be resolved for that connection only or is a DBCC command the only option (outside of index fix or something obvious)?
January 5, 2012 at 1:13 pm
No, you'd need to use one of the standard methods, OPTIMIZE FOR, local variables, stats updates... which ever one worked best for your situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply