| Author |
Update statistics setting
|
|
|
| I have a database that has 'auto update statistics' set to ON. A weekly
maintenance job is also run over the weekend that updates statistics based on
a 20% sample in the database. My problem is that at the start of every week
users are getting timeout issues which seem to go away after this first day.
On turning profiler on i have discovered that SQL is issuing recompiles on a
number of procedure (SELECT Statman commands straight after recompile
statement). Why would SQL be doing this as the statistics should already be
up to date? Do i also need to have the weekly maintenance task performing the
update statistics when the database already has it specifically turned on?
Thanks in advance.
| |
| Uri Dimant 2005-09-06, 9:23 am |
| Billu
SQL Server decides to recomplie a stopred procedure when you create a DML
(temporary table at the beginnig of the SP) and amount of data that was
inserted into has changed or you created a SP with recompile option and
statistics have updated as well.
http://support.microsoft.com/defaul...kb;en-us;243586
"billu" <billu@discussions.microsoft.com> wrote in message
news:EA3F403C-66DD-4F5E-A644- 66FA81B03158@microso
ft.com...
>I have a database that has 'auto update statistics' set to ON. A weekly
> maintenance job is also run over the weekend that updates statistics based
> on
> a 20% sample in the database. My problem is that at the start of every
> week
> users are getting timeout issues which seem to go away after this first
> day.
> On turning profiler on i have discovered that SQL is issuing recompiles on
> a
> number of procedure (SELECT Statman commands straight after recompile
> statement). Why would SQL be doing this as the statistics should already
> be
> up to date? Do i also need to have the weekly maintenance task performing
> the
> update statistics when the database already has it specifically turned on?
> Thanks in advance.
| |
| Hari Prasad 2005-09-06, 9:23 am |
| Hi,
If you have update statistics turned on database level then you do not want
to perform it via Maintenance plan.
See the below in the procedure which has recompiles:-
1. Look for Dynamic SQL
2. Look for the usage of Temp tables
3. Look for With recompile option in CREATE PROCEDURE statement
1. if all the above are ok then for each FROM clause use the owner name as
table qualifier
table_owner.table_name
2. Change all the stored procedure names to lower case.
Try all the above steps are see the status
Thanks
Hari
SQL Server MVP
"billu" <billu@discussions.microsoft.com> wrote in message
news:EA3F403C-66DD-4F5E-A644- 66FA81B03158@microso
ft.com...
>I have a database that has 'auto update statistics' set to ON. A weekly
> maintenance job is also run over the weekend that updates statistics based
> on
> a 20% sample in the database. My problem is that at the start of every
> week
> users are getting timeout issues which seem to go away after this first
> day.
> On turning profiler on i have discovered that SQL is issuing recompiles on
> a
> number of procedure (SELECT Statman commands straight after recompile
> statement). Why would SQL be doing this as the statistics should already
> be
> up to date? Do i also need to have the weekly maintenance task performing
> the
> update statistics when the database already has it specifically turned on?
> Thanks in advance.
| |
|
| temp tables are used in the procedures but why would this affect the running
time on only the first day of the week and not after? Could you explain what
effect running stats has via maintenance plan. Could this be causing the
stats to end up in a bad state so that it takes around a day for the stats to
be updatred correctly ?
"Hari Prasad" wrote:
> Hi,
>
> If you have update statistics turned on database level then you do not want
> to perform it via Maintenance plan.
>
> See the below in the procedure which has recompiles:-
>
> 1. Look for Dynamic SQL
> 2. Look for the usage of Temp tables
> 3. Look for With recompile option in CREATE PROCEDURE statement
>
> 1. if all the above are ok then for each FROM clause use the owner name as
> table qualifier
>
> table_owner.table_name
>
> 2. Change all the stored procedure names to lower case.
>
>
> Try all the above steps are see the status
>
> Thanks
> Hari
> SQL Server MVP
>
> "billu" <billu@discussions.microsoft.com> wrote in message
> news:EA3F403C-66DD-4F5E-A644- 66FA81B03158@microso
ft.com...
>
>
>
|
|
|
|