October 23, 2008 at 4:48 am
Hi all,
I have a sql server 2005 database which has auto update statistics set to on. However, in the log every day there are messages stating that this is switching on, then off, then on again etc. There are hundreds of these messages. Even when I turn auto update stats off it still happens! It looks as though the process causing this relates to ReportServer and I know reporting services is installed on this machine. Can anyone give me any clue as to why this happens in this circumstance and how I stop it? It is a fairly busy database and I would expect it to update stats regularly but I don't undertand why it is coming on and off every minute of every day.
Many thanks,
Paula
October 23, 2008 at 8:24 am
Some process or job is changing the database setting and enabling and disabling the feature. SQL won't by itself, change a database's settings.
You can run profiler for a while to catch all the commands run against the server and see where the changing of the settings is coming from.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2008 at 8:41 am
Thanks for this. It looks as though it may be an application which is opening and closing connections to the database constantly. When that happens it runs sp_reset_connection, which then turns auto update statistics off and back on again. It is apparently common with .NET Apps.
Thanks for your help.
Paula.
October 23, 2008 at 8:50 am
Paula (10/23/2008)
When that happens it runs sp_reset_connection, which then turns auto update statistics off and back on again. It is apparently common with .NET Apps.
Reset connection is common to .net apps. I believe it has to do with connection pooling. However it does not change the setting of auto-update stats when it runs.
To change the database setting requires fairly high permissions, either DB owner or alter database permission. General users (that would be using .net) shouldn't have that right.
If the .net connections are changing that setting, it's because someone has written code into the app to do so.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2008 at 12:47 pm
check for any 3rd party apps, i have seen something similar to this before
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 27, 2008 at 3:15 am
Yes, it is a 3rd party app, so I can't tell what access rights the .NET prograsm will have. It doesn't seem to be causing any problems as such at the moment, but I'm still looking into it. I'm pretty sure it is the app causing the problem. No-one / nothing else accesses the database at that level.
Thanks.
October 28, 2008 at 9:26 am
I wonder if it might not be a problem, even if it gets left on as the last statement by each spid connected. You will lose the ability for needed stats to be created DURING query execution while this is off. I don't know if there aren't other side effects or unintended consequences, but there may well be. I would get with the 3rd party vendor and inquire why they do this and see if it can be configured out of their system.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply