Automatic statistics update

Hi. I have automatic statistic update turned on for all my databases. Is
this an overhead I can do without? Could I update them overnight when the
database is hardly in use?

Thanks

--
Chris WestonChris Weston (chrisweston[losethislot]@.ntlworld.com) writes:
> Hi. I have automatic statistic update turned on for all my databases. Is
> this an overhead I can do without? Could I update them overnight when the
> database is hardly in use?

Unless you can deduct that auto-stats is causing you performance problems,
I would not consider doing this.

What may be important, though, is if you have large table with monotonically
growing keys, is that you run UPDATE STATISTICS on these tables with some
frequency. This is because auto-stats only sets in when 20% of the rows
have changed. For a 10 million-row table, that means that you need another
two million before autostats set in. Since the key grows monotonically, this
means that the statistics for the newly inserted rows is grossly inaccurate.

If you run a defragmentation job regularly, you don't need to do UPDATE
STATISTICS as well, as when you rebuild the index, the statistics are
updatead automatically. What we ran into was that a colleague set up a
maintenance job that would only defragment indexes with a certainly
level of fragmentation. Tables with a clustered key that grows monotically,
do not get fragmented easily, so we ran into problems with stale statistics.
Our maintenance job now performs UPDATE STATISTICS WITH FULLSCAN INDEX
on tables that don't get defragmented. (We're restricting the stats update
to indexes, because else it took too long time.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Auto-increment my primary key: why 2 instead of 1?

Hi all,

I have a table where I have my ProdPK set up as Primary key, turned on "Is Identity" and set the Identity increment to 1. But each time I add a new item, the number incremented by 2... I have couple of other tables and they are all fine, just this particular table increased twice as it should. I check the setting against other tables and everything seems to be the same.

By the way, this is adding the data to the table inside MS SQL Server Management Studio manually. I haven't done anything in the ASP.NET page yet.

Thank you very much,

Kenny.

That's weird. I would double check the identity column settings, and maybe check for triggers on the table?

|||

Thanks. I did checked the table's setting and the auto-increment was set to "1". Other settings are identical to my other tables. Any suggestions?

Thanks again,

Kenny.

|||

Can you post the table script and the results you observe by executing "dbcc checkident ( YourTableName )" ? Have you made sure that there are no triggers defined on the table ?

|||

Here is the table's script:

1USE [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.2\MSSQL\DATA\MTRENZ.MDF]2GO3/****** Object: Table [dbo].[T_PRODUCTS] Script Date: 12/22/2007 11:58:52 ******/4SET ANSI_NULLS ON5GO6SET QUOTED_IDENTIFIER ON7GO8SET ANSI_PADDING ON9GO10CREATE TABLE [dbo].[T_PRODUCTS](11[PROD_ID] [int] IDENTITY(1,1) NOT NULL,12[ACC_TYPE_ID_FK] [int] NOT NULL,13[PROD_NAME] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,14[PROD_DESCR] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,15[PROD_MODEL] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,16[PROD_STATUS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,17[PROD_PRICE] [smallmoney] NULL,18[PROD_DATE_ADDED] [datetime] NOT NULL CONSTRAINT [DF_T_PRODUCTS_PROD_DATE_ADDED] DEFAULT (getdate()),19[PROD_ADDED_BY] [uniqueidentifier] NULL,20[PROD_NO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,21[PROD_NOTES] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,22[PROD_PIC_S] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,23[PROD_PIC_L] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,24[PROD_NEW] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,25 CONSTRAINT [PK_T_PRODUCTS] PRIMARY KEY CLUSTERED26(27[PROD_ID] ASC28)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]29) ON [PRIMARY]3031GO32SET ANSI_PADDING OFF33GO34USE [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.2\MSSQL\DATA\MTRENZ.MDF]35GO36ALTER TABLE [dbo].[T_PRODUCTS] WITH NOCHECK ADD CONSTRAINT [ACT_TYPE_ID_FK] FOREIGN KEY([ACC_TYPE_ID_FK])37REFERENCES [dbo].[T_ACC_TYPES] ([ACC_TYPE_ID])38NOT FOR REPLICATION

This is what it return when I run the dbcc:

Checking identity information: current identity value '4', current column value '4'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Currently there are only two rows in my database, with the primary key values 2 and 4. It skipped 1 and 3.

Thank you,

Kenny.

|||

I don't know what exactly is the problem, but my database primary key started at 2, then 4, then 8... After 8, everything then become normal, meaning they increased correctly by 1!!!

I still don't know why, but it solved the problem itself I guess!

Thanks all,

Kenny.

AUTOCLOSE

It has been suggested to me by one of my vendors that I
need to have AUTOCLOSE turned on for my SQL Server 2000
backups to be reliable. I have never heard this before.
Does anyone feel AUTOCLOSE needs to be set to True for
Enterprise Manager/Agent/Jobs backups to be reliable?
Please explain.
ThanksThat is complete rubbish. :-)
Your vendor apparently think that you do backup through nicking the database
files at the file system level...
Even if you did, autoclose is not, AFAIK, documented to work for this. I nev
er recommend to do backup of the
database files, but if someone would do that, you need to shutdown SQL Serve
r.
(And convincing me of the opposite requires a BOL topic or a KB article ;-).
Such may very well exists, and I
may just have missed it, of course.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Johnny Kennedy" <jkennedy@.pcmh.com> wrote in message news:12a7a01c411b6$f56bf6b0$a401280a@.
phx.gbl...
> It has been suggested to me by one of my vendors that I
> need to have AUTOCLOSE turned on for my SQL Server 2000
> backups to be reliable. I have never heard this before.
> Does anyone feel AUTOCLOSE needs to be set to True for
> Enterprise Manager/Agent/Jobs backups to be reliable?
> Please explain.
> Thanks

Auto update statistics

We have 'auto update statistics' turned on for all our databases.
We also have a job that updates statistics with full scan every morning of
every day.
I am seeing some high cpu on one of our servers and in running Profiler I see
a number of "SELECT StatMan([SCO])..." statements.
From what I understand this could be due to procedures recompiling due to a
temp table, or auto update statistics.
If by the chance it is due to having 'auto update statistics' turned on, and
I am updating statistics daily, can I do away with one or the other? Is doing
both rather over kill?
--
Message posted via http://www.sqlmonster.comIt is 'probably' ok to turn off autoupdate in this case. One situation
where it isn't is tables that are heavily modified - you could have queries
developing bad query plans after sufficient modifications altered the value
distributions. If this is the case you could simply enable specific tables
using the sp_autostats sproc.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:798b0fc1296d4@.uwe...
> We have 'auto update statistics' turned on for all our databases.
> We also have a job that updates statistics with full scan every morning of
> every day.
> I am seeing some high cpu on one of our servers and in running Profiler I
> see
> a number of "SELECT StatMan([SCO])..." statements.
> From what I understand this could be due to procedures recompiling due to
> a
> temp table, or auto update statistics.
> If by the chance it is due to having 'auto update statistics' turned on,
> and
> I am updating statistics daily, can I do away with one or the other? Is
> doing
> both rather over kill?
> --
> Message posted via http://www.sqlmonster.com
>|||Hi there,
There's a new database SET option, AUTO_UPDATE_STATISTICS_ASYNC, that you
can enable to improve the predictability of query response times. When you
enable this option, out-of-date statistics are put on a queue for updating by
a background worker thread, and the query that initiated the statistics
update compiles immediately rather than waiting for the statistics to be
updated.
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
Register (Free):
http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
"TheSQLGuru" wrote:
> It is 'probably' ok to turn off autoupdate in this case. One situation
> where it isn't is tables that are heavily modified - you could have queries
> developing bad query plans after sufficient modifications altered the value
> distributions. If this is the case you could simply enable specific tables
> using the sp_autostats sproc.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
> news:798b0fc1296d4@.uwe...
> > We have 'auto update statistics' turned on for all our databases.
> >
> > We also have a job that updates statistics with full scan every morning of
> > every day.
> >
> > I am seeing some high cpu on one of our servers and in running Profiler I
> > see
> > a number of "SELECT StatMan([SCO])..." statements.
> >
> > From what I understand this could be due to procedures recompiling due to
> > a
> > temp table, or auto update statistics.
> >
> > If by the chance it is due to having 'auto update statistics' turned on,
> > and
> > I am updating statistics daily, can I do away with one or the other? Is
> > doing
> > both rather over kill?
> >
> > --
> > Message posted via http://www.sqlmonster.com
> >
>
>|||The auto update kicks in when a certain thresshold of changes has
occurred since the last time the statistics were determined. So if you
update statistics every morning, then the auto update statistics will
not activate unless it thinks it is necessary. This could be on heavily
used tables, or temp tables.
IOW, I would keep it turned on. It also prevents problems if you missed
a table in your daily job, or if (for some reason) the job hasn't run
(completely).
--
Gert-Jan
"cbrichards via SQLMonster.com" wrote:
> We have 'auto update statistics' turned on for all our databases.
> We also have a job that updates statistics with full scan every morning of
> every day.
> I am seeing some high cpu on one of our servers and in running Profiler I see
> a number of "SELECT StatMan([SCO])..." statements.
> From what I understand this could be due to procedures recompiling due to a
> temp table, or auto update statistics.
> If by the chance it is due to having 'auto update statistics' turned on, and
> I am updating statistics daily, can I do away with one or the other? Is doing
> both rather over kill?
> --
> Message posted via http://www.sqlmonster.com

Auto List Members

In SSMS I have "Auto List Members" and "Parameter Information" turned on for the text editor/all lanuguages but it does not work in the text editor when connected to a SQL Server 2000 DB. Any suggestion would be appreciates.SSMS does not support intellisense for the SQL Editor/TSQL. (as of this time) :)