October 8, 2003 at 1:53 am
Hi Experts,
I read in BOL the following -
Creating statistics manually allows you to create statistics that contain multiple column densities (average number of duplicates for the combination of columns). For example, a query contains the clause:
WHERE a = 7 and b = 9
Creating manual statistics on both columns together (a, b) can allow SQL Server to make a better estimate for the query because the statistics also contain the average number of distinct values for the combination of columns a and b.
My question is -
1. Will it be more efficient to create/update statistics manually ?
2. If Ans of pt. 1 is Yes then how can we get the percentage of improvement in performance while using creating /updating statistics manually ?
Thanks,
Sheilesh
October 8, 2003 at 3:56 am
quote:
My question is -1. Will it be more efficient to create/update statistics manually ?
2. If Ans of pt. 1 is Yes then how can we get the percentage of improvement in performance while using creating /updating statistics manually ?
not really sure on this, consider this just a remark in general.
You must factor in the 'human factor'. If you don't leave this to SQL Server, you are responsible for updating statistics. Maybe due to laziness you're in danger to have outdated statistics which can fool the query optimizer to choose not the least cost efficient execution plans, which in turn can negatively affect performance.
Just my $0.02
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 8, 2003 at 4:25 am
Always run update statistics automatically, with auto update or a maintenance plan for the reason Frank gave. It will update all created statistics. I prefer the maintenance plan as that gives more control on when and how the update takes place.
Create manual statistics on two columns is usefull if the densities are very dependent. My one test with it did not show any advantage for that case.
You can also do it for a column without an index, if you search on that column. (But maybe adding an index would be more usefull.)
October 8, 2003 at 4:48 am
Thanks for the replies. Actually i am planning to keep Auto create/update statistics ON (which is already there) .Apart from that I am planning to to run -
exec sp_updatestats
for the database manually thru sql server agent as a nightly task. Will it give the advantage of updating statistics for the all the rows of a table instead of sampling of table rows.I mean will it give more accurate statistics ?
October 8, 2003 at 4:59 am
Sorry instead of sp_updatestats i will use -
UPDATE STATISTICS statement wiht FULLSCAN clause , specifies that all of the data in the table is scanned to gather statistics, thru sql server agent as a nightly task.Wil it help ?
October 9, 2003 at 7:41 am
I appreciate if i get comment on my strategy stated in last mail.
October 9, 2003 at 8:21 am
Now this is simple statistic.
If you sample contains the whole population you'll get an accurate result.
However, if your sample is large enough, normally >30 observations, your result will be sufficiently precise.
Depending on the number of rows to consider updating statistics surely take longer. If you have the time, well, why not. But I bet the results you get are not significantly 'better' than letting SQL Server take his sample.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply