| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2011-11-08 : 10:08:00
|
| Hi,In my stored procs, I am using "update statistics tableName" after each large insert or update on the tablesIs this ok?Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-08 : 10:14:33
|
| no need after each insert/update. you can do it once after all DML statements are over...that too only if its a rather large data getting affected------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-11-08 : 10:18:50
|
| do you mean like the following ?:update tbl1...insert tbl1...insert tbl2...insert tbl3.........update statistics tbl1update statistics tbl2update statistics tbl3...... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-08 : 10:29:36
|
| not even like that. we usually do sp_updatestats to update statistics of entire db few times a day depending on how frequently we're having large DML operations (usually its once per day in case of warehouses after data population jobs)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-11-08 : 10:31:52
|
| How about this ;-Have a job which does "sp_updatestats" over night.And, should it run under master database or one particular database?Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-08 : 10:41:06
|
quote:
Originally posted by arkiboys
How about this ;-Have a job which does "sp_updatestats" over night.And, should it run under master database or one particular database?Thanks
it can run in databases where you want to update statistics ie whichever dbs have large DML operations------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-11-08 : 10:49:00
|
| In one of the databases, there are tables with several milliion records...I run the sp_updatestats on this database and completes within a few seconds.Is this normal? should it take longer to complete?Ran it once again but it is taking a long time now. is this correct behavious?Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-08 : 10:52:45
|
quote:
Originally posted by arkiboys
In one of the databases, there are tables with several milliion records...I run the sp_updatestats on this database and completes within a few seconds.Is this normal? should it take longer to complete?Ran it once again but it is taking a long time now. is this correct behavious?Thanks
depends on factors like number of indexes, how far statistics are out of date etc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-11-08 : 11:04:02
|
| solved.Thank you |
|
|
|
|
update stats