473,883 Members | 1,687 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

UPDATE STATISTICS necessary to improve performance (?)

Dear Sql Server experts:

First off, I am no sql server expert :)

A few months ago I put a database into a production environment.
Recently, It was brought to my attention that a particular query that
executed quite quickly in our dev environment was painfully slow in
production. I analyzed the the plan on the production server (it
looked good), and then tried quite a few tips that I'd gleaned from
reading newsgroups. Nothing worked. Then on a whim I performed an
UPDATE STATISTICS on a few of the tables that were being queried. The
query immediately went from executing in 61 seconds to under 1 second.
I checked to make sure that statistics were being "auto updated" and
they were.

Why did I need to run UPDATE STATISTICS? Will I need to again?

A little more background info:
The database started empty, and has grown quite rapidly in the last
few months. One particular table grows at a rate of about 300,000
records per month. I get fast query times due to a few well placed
indexes.

A quick question:
If I add an index, do statistics get automatically updated for this
new index immediately?

Thanks in advance for any help,

Felix
Jul 20 '05
17 14083
In article <Xn************ **********@127. 0.0.1>, so****@algonet. se
says...
Leythos (vo**@nowhere.c om) writes:
This same thought holds try for stats - I've seen hundreds of servers
that benefited from having tables manually reindexed, stored procs
recompiled, etc....


But reindexing is another thing. There is no autoreindex, so of course
running reindex manually may have some effect.


I can assure you that it won't have SOME effect, it will have a dramatic
effect if the table has a high insert ratio, or has been used for a long
time (months) without a reindex being done. Also, after the reindex you
need to mark the applicable stored procs for recompile.

If you've managed groups of 3 million record tables with 900GB of data
in a single database would have seen this many times.

Small databases (under 50GB) don't see this as much as larger ones, but
all of them benefit from proper table/index maintenance.

--
--
sp*********@rro hio.com
(Remove 999 to reply to me)
Jul 20 '05 #11
On 25 Dec 2003 01:34:03 -0800, Felix wrote:
A few months ago I put a database into a production environment.
Recently, It was brought to my attention that a particular query that
executed quite quickly in our dev environment was painfully slow in
production. I analyzed the the plan on the production server (it
looked good), and then tried quite a few tips that I'd gleaned from
reading newsgroups. Nothing worked. Then on a whim I performed an
UPDATE STATISTICS on a few of the tables that were being queried. The
query immediately went from executing in 61 seconds to under 1 second.
I checked to make sure that statistics were being "auto updated" and
they were.

Why did I need to run UPDATE STATISTICS? Will I need to again?
Autostatistics does a sample scan, as does UPDATE STATISTICS if you
don't specify FULLSCAN (which I see you didn't). Perhaps the sample
scans are skewed differently.

In fact, can anyone confirm how autostats works? Does it utilise
existing statistics to incrementally calculate new statistics based on
inserted / updated rows, or does it sample scan?

If the former, then perhaps the problem you are seeing is caused by
changes in the distribution of values over time, e.g. new rows are more
similar to each other than older rows were to each other.

It seems that the sample scan done by UPDATE STATISTICS has given you
good results, so it might be prudent to schedule a batch job to update
these periodically - e.g. nightly or weekly.

If new rows are skewing the statistics dramatically, then you might even
benefit from disabling autostats for *selected tables*, but make sure
that you run regular updates (and I'd go for the FULLSCAN option).
Disabling autostats is generally not recommended, so don't go for this
option lightly!

The trick is to determine which tables the autostats are not performing
well for, so that you can keep manual updates to a minimum. Profiler can
tell you when statistics are missing (set SHOWPLAN_ALL on and show
warnings in profile, you will see NO STATS messages), but out of date
statistics are harder.

You need to check the query plans for your slow running queries, and
look at the estimates for returned rows, then check that with actual
returned rows if you break the query up in the same way that the query
plan has. Major differences (e.g. 5,000 vs 100,000) should indicate
where the stats are out wildly.

I picked this up from the book "SQL Server Query Performance Tuning
Distilled" by Sajal Dam. Good book for covering many of the aspects of
performance in SQL Server.
A quick question:
If I add an index, do statistics get automatically updated for this
new index immediately?


Indexes store and update statistics automatically. As with non-index
statistics, they can get out of date (i.e. unrepresentativ e), so they
may need updating also. You can do this either by UPDATE STATISTICS or
by rebuilding the index. I guess which one is the best pick depends on
how fragmented your index is - not much, and it might be cheaper to just
update the stats than rebuild the entire index.

cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander
Jul 20 '05 #12
Bas

"Leythos" <vo**@nowhere.c om> wrote in message
news:MP******** *************** *@news-server.columbus .rr.com...
In article <Xn************ **********@127. 0.0.1>, so****@algonet. se

says...
Leythos (vo**@nowhere.c om) writes:
This same thought holds try for stats - I've seen hundreds of servers
that benefited from having tables manually reindexed, stored procs
recompiled, etc....


But reindexing is another thing. There is no autoreindex, so of course
running reindex manually may have some effect.


I can assure you that it won't have SOME effect, it will have a dramatic
effect if the table has a high insert ratio, or has been used for a long
time (months) without a reindex being done. Also, after the reindex you
need to mark the applicable stored procs for recompile.


I regularly do a reindex for every table and then DBCC freeproccache the
database. After following this thread I'm wondering if this is the best
approach? Would it be wise to schedule this or more with either my app or
schedule it with SQL server? What interval would be good, each day, week,
month?
I understand ofcourse this depends on how the DB is used and how big it is.

Bas
Jul 20 '05 #13
In article <3f************ *********@dread er9.news.xs4all .nl>, "Bas"
<nomailplease > says...

"Leythos" <vo**@nowhere.c om> wrote in message
news:MP******** *************** *@news-server.columbus .rr.com...
In article <Xn************ **********@127. 0.0.1>, so****@algonet. se

says...
Leythos (vo**@nowhere.c om) writes:
> This same thought holds try for stats - I've seen hundreds of servers
> that benefited from having tables manually reindexed, stored procs
> recompiled, etc....

But reindexing is another thing. There is no autoreindex, so of course
running reindex manually may have some effect.


I can assure you that it won't have SOME effect, it will have a dramatic
effect if the table has a high insert ratio, or has been used for a long
time (months) without a reindex being done. Also, after the reindex you
need to mark the applicable stored procs for recompile.


I regularly do a reindex for every table and then DBCC freeproccache the
database. After following this thread I'm wondering if this is the best
approach? Would it be wise to schedule this or more with either my app or
schedule it with SQL server? What interval would be good, each day, week,
month?


I have one client that has more than 3 million registration identities -
this tracks financial information. They used my sprocs to perform
maintenance on the tables on a weekly basis. Their system consists of a
large import daily and a large export daily - import being lots of
inserts and updates. If you look at the tables, determine which ones
have significant data additions/deletions/changes each day you can tweak
your maintenance plan to just those tables on a frequent basis and the
less used ones on another schedule.

DBCC freeproccache doesn't really help, except when creating
plans/indexes by hand. I only use it when working with reports and
optimizing indexes.
--
--
sp*********@rro hio.com
(Remove 999 to reply to me)
Jul 20 '05 #14
Hi Ross,

There is an KB article that explains how autostats works.

http://support.microsoft.com/default...b;en-us;195565
INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work

Yih-Yoon Lee
Ross McKay <ro***@zeta.NOT .THIS.BIT.org.a u> wrote in message news:<mu******* *************** **********@4ax. com>...
On 25 Dec 2003 01:34:03 -0800, Felix wrote:
A few months ago I put a database into a production environment.
Recently, It was brought to my attention that a particular query that
executed quite quickly in our dev environment was painfully slow in
production. I analyzed the the plan on the production server (it
looked good), and then tried quite a few tips that I'd gleaned from
reading newsgroups. Nothing worked. Then on a whim I performed an
UPDATE STATISTICS on a few of the tables that were being queried. The
query immediately went from executing in 61 seconds to under 1 second.
I checked to make sure that statistics were being "auto updated" and
they were.

Why did I need to run UPDATE STATISTICS? Will I need to again?


Autostatistics does a sample scan, as does UPDATE STATISTICS if you
don't specify FULLSCAN (which I see you didn't). Perhaps the sample
scans are skewed differently.

In fact, can anyone confirm how autostats works? Does it utilise
existing statistics to incrementally calculate new statistics based on
inserted / updated rows, or does it sample scan?

If the former, then perhaps the problem you are seeing is caused by
changes in the distribution of values over time, e.g. new rows are more
similar to each other than older rows were to each other.

It seems that the sample scan done by UPDATE STATISTICS has given you
good results, so it might be prudent to schedule a batch job to update
these periodically - e.g. nightly or weekly.

If new rows are skewing the statistics dramatically, then you might even
benefit from disabling autostats for *selected tables*, but make sure
that you run regular updates (and I'd go for the FULLSCAN option).
Disabling autostats is generally not recommended, so don't go for this
option lightly!

The trick is to determine which tables the autostats are not performing
well for, so that you can keep manual updates to a minimum. Profiler can
tell you when statistics are missing (set SHOWPLAN_ALL on and show
warnings in profile, you will see NO STATS messages), but out of date
statistics are harder.

You need to check the query plans for your slow running queries, and
look at the estimates for returned rows, then check that with actual
returned rows if you break the query up in the same way that the query
plan has. Major differences (e.g. 5,000 vs 100,000) should indicate
where the stats are out wildly.

I picked this up from the book "SQL Server Query Performance Tuning
Distilled" by Sajal Dam. Good book for covering many of the aspects of
performance in SQL Server.
A quick question:
If I add an index, do statistics get automatically updated for this
new index immediately?


Indexes store and update statistics automatically. As with non-index
statistics, they can get out of date (i.e. unrepresentativ e), so they
may need updating also. You can do this either by UPDATE STATISTICS or
by rebuilding the index. I guess which one is the best pick depends on
how fragmented your index is - not much, and it might be cheaper to just
update the stats than rebuild the entire index.

cheers,
Ross.

Jul 20 '05 #15
I asked:
In fact, can anyone confirm how autostats works? Does it utilise
existing statistics to incrementally calculate new statistics based on
inserted / updated rows, or does it sample scan?

(Yih-Yoon Lee) wrote:There is an KB article that explains how autostats works.

http://support.microsoft.com/default...b;en-us;195565
INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work


Thanks Yih-Yoon, from this article once can infer that autostats does a
sample scan, since it can affect performance on heavily loaded systems
(and I presume that using existing stats to incrementally calculate new
stats would use bugger all resources).

So I guess the question is, why did Felix's autostats perform a
different scan to his manual scan?

cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"The lawn could stand another mowing; funny, I don't even care"
- Elvis Costello
Jul 20 '05 #16
Bas

"Leythos" <vo**@nowhere.c om> wrote in message
news:MP******** *************** *@news-server.columbus .rr.com...
I have one client that has more than 3 million registration identities -
this tracks financial information. They used my sprocs to perform
maintenance on the tables on a weekly basis. Their system consists of a
large import daily and a large export daily - import being lots of
inserts and updates. If you look at the tables, determine which ones
have significant data additions/deletions/changes each day you can tweak
your maintenance plan to just those tables on a frequent basis and the
less used ones on another schedule.

DBCC freeproccache doesn't really help, except when creating
plans/indexes by hand. I only use it when working with reports and
optimizing indexes.


Hi Leythos,

Could you shed a little light on what your sprocs do then? Reindexes?

Thanx,

Bas

Jul 20 '05 #17
In article <3f************ **********@drea der10.news.xs4a ll.nl>, "Bas"
<nomailplease > says...

"Leythos" <vo**@nowhere.c om> wrote in message
news:MP******** *************** *@news-server.columbus .rr.com...
I have one client that has more than 3 million registration identities -
this tracks financial information. They used my sprocs to perform
maintenance on the tables on a weekly basis. Their system consists of a
large import daily and a large export daily - import being lots of
inserts and updates. If you look at the tables, determine which ones
have significant data additions/deletions/changes each day you can tweak
your maintenance plan to just those tables on a frequent basis and the
less used ones on another schedule.

DBCC freeproccache doesn't really help, except when creating
plans/indexes by hand. I only use it when working with reports and
optimizing indexes.


Hi Leythos,

Could you shed a little light on what your sprocs do then? Reindexes?


The scan all the tables, gather the table names, reindex the tables
(only 20% of them per night), mark the stored procs for recompile, and
run nightly. Sometime, when I cant run nightly I do it on Sunday.

--
--
sp*********@rro hio.com
(Remove 999 to reply to me)
Jul 20 '05 #18

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
10456
by: Mark A Framness | last post by:
Greetings, I am working on a project and we need to write a conversion script to initialize a new field on a table. The number of records on this table is on the order of millions so routine selection and update takes a long time. I am tasked with writing a pl/sql proc that utilizes array processing to update the column.
1
6181
by: Gent | last post by:
am using FOR UPDATE triggers to audit a table that has 67 fields. My problem is that this slows down the system significantly. I have narrowed down the problem to the size (Lines of code) that need to be compiled after the trigger has been fired. There is about 67 IF Update(fieldName) inside the trigger and a not very complex select statement inside the if followed by an insert to the audit table. When I leave only a few IF-s in the...
2
2527
by: serge | last post by:
/* This is a long post. You can paste the whole message in the SQL Query Analyzer. I have a scenario where there are records with values pointing to wrong records and I need to fix them using an Update statement. I have a sample code to reproduce my problem. To simplify the scenario I am trying to use Order related tables to explain a little better the tables i have to work with.
1
2530
by: hrhoe | last post by:
Hi, I created a C# program that update SQL Server table row by row. I referenced third party dll file to do the necessary modification for each row. And I used foreach loop to update data in the table. The problem is the poor performance. If I use the third party program, I can process 10,000 records within a minute, but if I try with my program,
1
2318
by: Chris Weston | last post by:
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 Weston
17
5114
by: romixnews | last post by:
Hi, I'm facing the problem of analyzing a memory allocation dynamic and object creation dynamics of a very big C++ application with a goal of optimizing its performance and eventually also identifying memory leaks. The application in question is the Mozilla Web Browser. I also have had similar tasks before in the compiler construction area. And it is easy to come up with many more examples, where such kind of statistics can be very...
4
1665
by: CMOS | last post by:
hi, does any one know a place to get performance statistics for stl containers? CMOS
11
6082
by: SAL | last post by:
Hello, I have a Gridview control (.net 2.0) that I'm having trouble getting the Update button to fire any kind of event or preforming the update. The datatable is based on a join so I don't know if that's what's causing the behavior or not. It seems like the Update button should at least do something. When the Edit button is clicked, the grid goes into Edit mode and the Cancel button takes the grid out of Edit mode. So, I don't get what...
3
3986
by: Michel Esber | last post by:
Hi all, DB2 V8 LUW FP 15 There is a table T (ID varchar (24), ABC timestamp). ID is PK. Our application needs to frequently update T with a new value for ABC. update T set ABC=? where ID = ?
0
9781
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10734
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10836
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10407
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9564
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7960
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5793
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5982
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4210
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.