InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 05:35:57 GMT20http://www.sqlservercentral.com/Forums/Topic1003837-146-1.aspxAvoid maintenance plans!!!I HIGHLY recommend the incredible and FREE goodness at ola.hallengren.com for all maintenance activities. Learn to use the goodness there!Fri, 15 Oct 2010 07:27:11 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1003837-146-1.aspxThe maint plan has the "statistics only" option.Otherwise you might be using something based on:[code="sql"]select 'UPDATE STATISTICS ' + quotename(object_schema_name(object_id)) + '.' + quotename(object_name(object_id)) + ' ' + quotename(name) + ' with fullscan ;'from sys.statswhere objectpropertyex(object_id,'IsMSShipped') = 0 -- only select user objects-- statistics onlyand ( auto_created = 1 or user_created = 1 ) [/code]Thu, 14 Oct 2010 11:21:40 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic1003837-146-1.aspx[quote][b]ravz.sqldba (10/14/2010)[/b][hr]Now I have issue with Update Statistics is taking to long to completeas mentioned in my 1st post.[/quote]Since it's running after the index rebuild, make sure that (if it's a maint plan) you have the selection set for column statistics only, not all.[quote]@GilaMonster: How do I troubleshoot hitting IO bottlenecks?[/quote]I suggest you open your favourite search engine and do some reading. It's a large topic.[quote]What does the update Statistics job do internally?[/quote]Depends what options you're running it with. In essence, reads part or all of a table, calculates distributions of row values and updates the stored histogram.Thu, 14 Oct 2010 10:38:49 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1003837-146-1.aspxIn all our PRD servers. Once the Full Backup finishes, I call the Check DB Integrity job from the Full Backup job.Then the Check DB Integrity job runs and when it finishes it calls the User DB Reindex job.The User DB Reindex job will call the Update Statistics job. So I have one job calling the next job.Now I have issue with Update Statistics is taking to long to completeas mentioned in my 1st post.@GilaMonster: How do I troubleshoot hitting IO bottlenecks?and is there any other way to troubleshoot Update Statistics. What does the update Statistics job do internally?@MindOptimizer: thanks for your replay. But i am doing that by spliting the job and run it parallel. I want how to know trouble shoot do decrease the time. Thanks for your replaysThu, 14 Oct 2010 09:50:08 GMTRanzzhttp://www.sqlservercentral.com/Forums/Topic1003837-146-1.aspxWell there's no difference which way around providing you don't have the stats update update the index statistics.Thu, 14 Oct 2010 06:13:59 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1003837-146-1.aspx[quote][b]GilaMonster (10/14/2010)[/b][hr][quote][b]MindOptimizer (10/14/2010)[/b][hr][quote][b]GilaMonster (10/14/2010)[/b][hr]Since it's running after an index rebuild, you only need to update column statistics, not index statistics.[/quote] Will it get benefit, if we go for first "update statistics" then for "index rebuild/reorganize" ?[/quote]No.Either way (before or after) if the update stats does index stats as well as column stats it's wasting time as the rebuild indexes updates index stats.[/quote] So what sequence we should keep ? first update statistics or index rebuild/reorganize ?Thu, 14 Oct 2010 06:06:02 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1003837-146-1.aspx[quote][b]MindOptimizer (10/14/2010)[/b][hr][quote][b]GilaMonster (10/14/2010)[/b][hr]Since it's running after an index rebuild, you only need to update column statistics, not index statistics.[/quote] Will it get benefit, if we go for first "update statistics" then for "index rebuild/reorganize" ?[/quote]No.Either way (before or after) if the update stats does index stats as well as column stats it's wasting time as the rebuild indexes updates index stats.Thu, 14 Oct 2010 05:52:48 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1003837-146-1.aspx[quote][b]GilaMonster (10/14/2010)[/b][hr]Since it's running after an index rebuild, you only need to update column statistics, not index statistics.[/quote] Will it get benefit, if we go for first "update statistics" then for "index rebuild/reorganize" ?Thu, 14 Oct 2010 05:41:58 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1003837-146-1.aspxSince it's running after an index rebuild, you only need to update column statistics, not index statistics.Shouldn't be running that long, I've had TB size databases where the stats update takes maybe an hour. Are you hitting IO bottlenecks perhaps?Thu, 14 Oct 2010 05:36:25 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1003837-146-1.aspxyou can divide heavy transactional DB and less transaction Db and set their job accrodingly like for "heavy" DB daily and for "less Db" weekly OR you can choose full scan or sample scan option , it will also reduce the job execution time.Refer this link for "update statistics " details [url]http://technet.microsoft.com/en-us/library/ms187348%28SQL.90%29.aspx[/url] Thu, 14 Oct 2010 04:10:21 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1003837-146-1.aspxIn our one PRD server after User DB Reindex job is done Update Statistics job will startUpdate Statistics Maint.plan start around: 11:45PMending :11:00AM it is taking almost like 12 hrs to complete the job.I splited the Update Statistics Maint.plan into two.like Update Statistics Maint.plan1 and Update Statistics Maint.plan2which run parallely Update Statistics Maint.plan1 is taking like 6hrs 28 min 23 sec Update Statistics Maint.plan2 is taking like 8 hrs 20 min 35 sec.they are like 32 user databases: total size is 756.0184936523 gigabyteshow to troubleshoot to decrease the time. what does the Update Statistics Maint.plan do ?Thanks in advance.Wed, 13 Oct 2010 11:33:39 GMTRanzz