Automatic Update Statistics

[Brennan Hadden]

On a SQL 7 sp 2 server, I have a database with about about 77,000 records, with automatic update statistics on inserting 1000 records took 43 minutes. With automatic update off, it took 23 minutes to insert the same 1000 records. On the same machine, I inserted 1000 records into 2 other databases with the same database structure and automatic update statistics on. On the second database, there are about 174000 records and it took 35 minutes to insert 1000 records. On the third database, there are about 93000 records and it took 19 minutes to insert 1000 records.

Why is the time so high on the first database?

What can be done to fix the problem?

Thank you.

[roro]

Indexes usually slow down inserts...especially a clustered index.
Sometimes BCP and the bulk copy command runs faster than DTS.


------------
Brennan Hadden at 1/11/01 1:52:38 PM


On a SQL 7 sp 2 server, I have a database with about about 77,000 records, with automatic update statistics on inserting 1000 records took 43 minutes. With automatic update off, it took 23 minutes to insert the same 1000 records. On the same machine, I inserted 1000 records into 2 other databases with the same database structure and automatic update statistics on. On the second database, there are about 174000 records and it took 35 minutes to insert 1000 records. On the third database, there are about 93000 records and it took 19 minutes to insert 1000 records.

Why is the time so high on the first database?

What can be done to fix the problem?

Thank you.

Very interesting. There are lots of factors that can affect inserts; one of the biggest factors in my mind is how full your table and index pages were before you began. Also fragmentation can be an issue.

To completely reorg your database takes some time and effort. You would have to bcp out all of your data, recreate your database and size it big enough so that it doesn't have to grow, (if it is still growing specify a large growth rate so that you get large chunks of disk space at a time; you don't want a large database to grow 5M at a time because it's almost guaranteed to be very fragmented), recreate your objects and then bcp your data back in.

If you can narrow it down to a table or two and if you have enough disk space you can try to reorg your data by creating or recreating a clustered index - just make sure your database has enough space or grows in a big enough chunk to accomodate creating or recreating a clustered index (1.2X the size of the data) or you're going to end up fragmented again.

You might also experiment with recreating your indexes with different fillfactors.

Hope this helps.


------------
Brennan Hadden at 1/11/01 1:52:38 PM


On a SQL 7 sp 2 server, I have a database with about about 77,000 records, with automatic update statistics on inserting 1000 records took 43 minutes. With automatic update off, it took 23 minutes to insert the same 1000 records. On the same machine, I inserted 1000 records into 2 other databases with the same database structure and automatic update statistics on. On the second database, there are about 174000 records and it took 35 minutes to insert 1000 records. On the third database, there are about 93000 records and it took 19 minutes to insert 1000 records.

Why is the time so high on the first database?

What can be done to fix the problem?

Thank you.

[Ed Rudman]

Here are some questions/interesting scenarios relating to this topic someone might be able to answer for me:

We are doing a SQLDMO.BulkInsert into a table with about 30 million rows. Each time the BulkInsert runs, SQL "automatically" kicks off an "UPDATE STATISTICS", which--because of the size of the table, often causes DEADLOCKS, because users are running this process fairly often (several times an hour or more).

NOTHING we have found will turn off the "UPDATE STATISTICS" on that table. We have tried everything. The main point is that in SQL 6.5 you needed a user with DBO authority to run UPDATE STATISTICS. Back then, our app ran with a normal userid and when we trapped the error saying "You do not have permissions to run UPDATE STATISTICS", the app would just ignore the error. Now, however, contradictory to the text in the SQL 7 manual, a very generic userid has the ability to run UPDATE STISTICS. Is this a Microsoft bug?


------------
at 1/19/01 6:56:30 PM


Very interesting. There are lots of factors that can affect inserts; one of the biggest factors in my mind is how full your table and index pages were before you began. Also fragmentation can be an issue.

To completely reorg your database takes some time and effort. You would have to bcp out all of your data, recreate your database and size it big enough so that it doesn't have to grow, (if it is still growing specify a large growth rate so that you get large chunks of disk space at a time; you don't want a large database to grow 5M at a time because it's almost guaranteed to be very fragmented), recreate your objects and then bcp your data back in.

If you can narrow it down to a table or two and if you have enough disk space you can try to reorg your data by creating or recreating a clustered index - just make sure your database has enough space or grows in a big enough chunk to accomodate creating or recreating a clustered index (1.2X the size of the data) or you're going to end up fragmented again.

You might also experiment with recreating your indexes with different fillfactors.

Hope this helps.


------------
Brennan Hadden at 1/11/01 1:52:38 PM


On a SQL 7 sp 2 server, I have a database with about about 77,000 records, with automatic update statistics on inserting 1000 records took 43 minutes. With automatic update off, it took 23 minutes to insert the same 1000 records. On the same machine, I inserted 1000 records into 2 other databases with the same database structure and automatic update statistics on. On the second database, there are about 174000 records and it took 35 minutes to insert 1000 records. On the third database, there are about 93000 records and it took 19 minutes to insert 1000 records.

Why is the time so high on the first database?

What can be done to fix the problem?

Thank you.

[Mark Kemble]

Ed,

You can turn off the auto create and auto update statistics using the sp_dboption and setting their values to false. If that's not an option, you can set SQL Server to not maintain stats on a given column and indexes via the sp_autostats sproc. The syntax is: sp_autostats tablename, 'off' , index name (if you only want to turn off stats for a specific index). Check out BOL for more info on statistics and sp_autostats. Good luck . . .

Mark


------------
Ed Rudman at 1/23/01 8:13:32 AM

Here are some questions/interesting scenarios relating to this topic someone might be able to answer for me:

We are doing a SQLDMO.BulkInsert into a table with about 30 million rows. Each time the BulkInsert runs, SQL "automatically" kicks off an "UPDATE STATISTICS", which--because of the size of the table, often causes DEADLOCKS, because users are running this process fairly often (several times an hour or more).

NOTHING we have found will turn off the "UPDATE STATISTICS" on that table. We have tried everything. The main point is that in SQL 6.5 you needed a user with DBO authority to run UPDATE STATISTICS. Back then, our app ran with a normal userid and when we trapped the error saying "You do not have permissions to run UPDATE STATISTICS", the app would just ignore the error. Now, however, contradictory to the text in the SQL 7 manual, a very generic userid has the ability to run UPDATE STISTICS. Is this a Microsoft bug?


------------
at 1/19/01 6:56:30 PM


Very interesting. There are lots of factors that can affect inserts; one of the biggest factors in my mind is how full your table and index pages were before you began. Also fragmentation can be an issue.

To completely reorg your database takes some time and effort. You would have to bcp out all of your data, recreate your database and size it big enough so that it doesn't have to grow, (if it is still growing specify a large growth rate so that you get large chunks of disk space at a time; you don't want a large database to grow 5M at a time because it's almost guaranteed to be very fragmented), recreate your objects and then bcp your data back in.

If you can narrow it down to a table or two and if you have enough disk space you can try to reorg your data by creating or recreating a clustered index - just make sure your database has enough space or grows in a big enough chunk to accomodate creating or recreating a clustered index (1.2X the size of the data) or you're going to end up fragmented again.

You might also experiment with recreating your indexes with different fillfactors.

Hope this helps.


------------
Brennan Hadden at 1/11/01 1:52:38 PM


On a SQL 7 sp 2 server, I have a database with about about 77,000 records, with automatic update statistics on inserting 1000 records took 43 minutes. With automatic update off, it took 23 minutes to insert the same 1000 records. On the same machine, I inserted 1000 records into 2 other databases with the same database structure and automatic update statistics on. On the second database, there are about 174000 records and it took 35 minutes to insert 1000 records. On the third database, there are about 93000 records and it took 19 minutes to insert 1000 records.

Why is the time so high on the first database?

What can be done to fix the problem?

Thank you.

[Che J. Hsu]

You said the database structures are the same. Are they indexed the same way? Indexes are stored as a seperate B-tree, so any write operations can be a lot slower when indexes are involved. Of course, any read operations can be potentially much faster.


------------
Brennan Hadden at 1/11/01 1:52:38 PM


On a SQL 7 sp 2 server, I have a database with about about 77,000 records, with automatic update statistics on inserting 1000 records took 43 minutes. With automatic update off, it took 23 minutes to insert the same 1000 records. On the same machine, I inserted 1000 records into 2 other databases with the same database structure and automatic update statistics on. On the second database, there are about 174000 records and it took 35 minutes to insert 1000 records. On the third database, there are about 93000 records and it took 19 minutes to insert 1000 records.

Why is the time so high on the first database?

What can be done to fix the problem?

Thank you.

Just a thought:

Do DBCC SHOWCONTIG to see if the indexes are
heavily fragmented. If so, you can do DBCC DBREINDEX.

(This advice is worth every bit you paid for.)

Michael B. Johnson 2001/Jan/24 10:30

------------
Brennan Hadden at 1/11/01 1:52:38 PM

On a SQL 7 sp 2 server, database with about 77,000 records, automatic update statistics on: inserting 1000 records took 43 minutes. With automatic update off, it took 23 minutes for the same 1000 records.

Same machine, I inserted 1000 records into 2 other databases with the same database structure and automatic update statistics on. On the second database, about 174000 records, it took 35 minutes for 1000 records. On the third database, about 93000 records, it took 19 minutes for 1000 records.

Why is the time so high on the first database? What can be done to fix the problem?

On an allied theme, are there known problems with Auto Create Stats? If I have this switched on, I can guarantee that after the nightly re-org and optimise job has run, one or more tables will be inaccessible. The only way to get at the data in them is to remove all of the newly created statistics/indices.

Thanks
Simon, UK



------------
Mark Kemble at 1/23/01 9:23:26 AM

Ed,

You can turn off the auto create and auto update statistics using the sp_dboption and setting their values to false. If that's not an option, you can set SQL Server to not maintain stats on a given column and indexes via the sp_autostats sproc. The syntax is: sp_autostats tablename, 'off' , index name (if you only want to turn off stats for a specific index). Check out BOL for more info on statistics and sp_autostats. Good luck . . .

Mark


------------
Ed Rudman at 1/23/01 8:13:32 AM

Here are some questions/interesting scenarios relating to this topic someone might be able to answer for me:

We are doing a SQLDMO.BulkInsert into a table with about 30 million rows. Each time the BulkInsert runs, SQL "automatically" kicks off an "UPDATE STATISTICS", which--because of the size of the table, often causes DEADLOCKS, because users are running this process fairly often (several times an hour or more).

NOTHING we have found will turn off the "UPDATE STATISTICS" on that table. We have tried everything. The main point is that in SQL 6.5 you needed a user with DBO authority to run UPDATE STATISTICS. Back then, our app ran with a normal userid and when we trapped the error saying "You do not have permissions to run UPDATE STATISTICS", the app would just ignore the error. Now, however, contradictory to the text in the SQL 7 manual, a very generic userid has the ability to run UPDATE STISTICS. Is this a Microsoft bug?


------------
at 1/19/01 6:56:30 PM


Very interesting. There are lots of factors that can affect inserts; one of the biggest factors in my mind is how full your table and index pages were before you began. Also fragmentation can be an issue.

To completely reorg your database takes some time and effort. You would have to bcp out all of your data, recreate your database and size it big enough so that it doesn't have to grow, (if it is still growing specify a large growth rate so that you get large chunks of disk space at a time; you don't want a large database to grow 5M at a time because it's almost guaranteed to be very fragmented), recreate your objects and then bcp your data back in.

If you can narrow it down to a table or two and if you have enough disk space you can try to reorg your data by creating or recreating a clustered index - just make sure your database has enough space or grows in a big enough chunk to accomodate creating or recreating a clustered index (1.2X the size of the data) or you're going to end up fragmented again.

You might also experiment with recreating your indexes with different fillfactors.

Hope this helps.


------------
Brennan Hadden at 1/11/01 1:52:38 PM


On a SQL 7 sp 2 server, I have a database with about about 77,000 records, with automatic update statistics on inserting 1000 records took 43 minutes. With automatic update off, it took 23 minutes to insert the same 1000 records. On the same machine, I inserted 1000 records into 2 other databases with the same database structure and automatic update statistics on. On the second database, there are about 174000 records and it took 35 minutes to insert 1000 records. On the third database, there are about 93000 records and it took 19 minutes to insert 1000 records.

Why is the time so high on the first database?

What can be done to fix the problem?

Thank you.

If it takes 23 minutes to insert 1000 records, you must have a lot of indexes and/or triggers on your tables.

One thing worth considering is to remove the index(es) before inserting, and then rebuild the index(es) afterwards. When we used this trick, it resulted in data loads taking only 5% of the original time. Even with the time to re-create the indexes, it still took about 30% of the original time


------------
Brennan Hadden at 1/11/01 1:52:38 PM


On a SQL 7 sp 2 server, I have a database with about about 77,000 records, with automatic update statistics on inserting 1000 records took 43 minutes. With automatic update off, it took 23 minutes to insert the same 1000 records. On the same machine, I inserted 1000 records into 2 other databases with the same database structure and automatic update statistics on. On the second database, there are about 174000 records and it took 35 minutes to insert 1000 records. On the third database, there are about 93000 records and it took 19 minutes to insert 1000 records.

Why is the time so high on the first database?

What can be done to fix the problem?

Thank you.