Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bsethi24
Starting Member
25 Posts |
 Posted - 2013-12-17 : 08:56:57
|
| Hi Team, We have a table with a 1 column Primary Key on and an UNIQUE Non-Clustered Index with 16 columns.This table has more than 20700000 Rows and on every day new records inserted between 30000 and 35000.After insertion process, we have many other processes those refer this table.On every day, before proceeding further, after insert we do an Update Statistics with 30 Percent Sample on this table.After this when we do rest processes then we are not facing problem and everything goes smoothly.Now the problem is if we do Update Statistics then it is taking more than 15 Minutes.We need a solution: - To make this "Update Statistics" faster OR Can we perform a parallel Update Stats on this table with other processes OR Instead of "Update Statistics" can we go for REBUILD INDEXES (need to know it will update all Statistics or not)? OR Any other solution by which we can save this 15 minutes.Please advice. |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-12-17 : 15:08:51
|
| [code]ALTER DATABASE [<your_db_name>] SET AUTO_UPDATE_STATISTICS ON, AUTO_UPDATE_STATISTICS_ASYNC ON[/code] |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-17 : 15:12:36
|
On top of auto update stats plus async, you should consider trace flag 2371.We have auto update stats plus async ON, but we also have an update stats job. Here is the sampling that we do, which is based upon number of rows in the table: SET @RowsSampled = CASE WHEN @RowCount < 500000 THEN '100 PERCENT' WHEN @RowCount < 1000000 THEN '50 PERCENT' WHEN @RowCount < 5000000 THEN '25 PERCENT' WHEN @RowCount < 10000000 THEN '10 PERCENT' WHEN @RowCount < 50000000 THEN '5 PERCENT' WHEN @RowCount < 100000000 THEN '2 PERCENT' WHEN @RowCount < 1000000000 THEN '1 PERCENT' ELSE '20000000 ROWS' END Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2013-12-18 : 13:26:50
|
| Can the insert, and the Update Statistics, run unattended overnight - such that the Update Statistics has finished before people arrive for work? |
|
|
|
|
|
|
How to make "Update Statistics" faster?