Post: Updating Statistics in SQL Server: Maintenance Questions & Answers

Migrating comments from wordpress

Thiago April 18, 2016 9:11 am Really cool live hangout! Sorry about my lack of questions, but the “on browser” version of the hangout doesn’t show nothing but the video stream.. Does not have a text area or something to type in…

Reply Kendra Little April 18, 2016 10:41 am Thanks for letting me know! I’ll figure out the “Secret Handshake Link” that allows Q&A for future hangouts on air 🙂

Reply Kevin Fries April 18, 2016 3:35 pm Got shut out at work as your stream was flagged for adult content. Duh. With luck, there’s a replay but I still have this killer reference page.

Reply Kendra Little April 20, 2016 11:23 am LOL, adult content. My drawings are more childish than anything else 😀

The videos are now up on my YouTube channel if those are allowed through your work firewall– hopefully that works. https://www.youtube.com/KendraLittle

Loading... Reply Statistics FAQ – Curated SQL April 19, 2016 6:46 am […] Kendra Little has a great FAQ on statistics, from the standpoint of developers as well as administra…: […]

Loading... Reply Kendra Little April 25, 2016 9:03 am Post updated April 25 — added section “Should I Turn on Trace Flag 7471 for Concurrent Statistics Update?”

Loading... Reply Simon April 25, 2016 6:43 pm Hi Kendra, Is Trace Flag 2371 still applicable on SQL 2014? From kb https://support.microsoft.com/en-us/kb/2754171, it seem this TF stops at SQL 2012.

Thanks.

Loading... Reply Kendra Little April 26, 2016 7:44 am It’s still applicable on SQL Server 2014, they just haven’t updated the versions list in the KB.

It’s easy to test yourself (try it!), or you can just look at Erik Darling’s test results against 2014 here (he compared it against 2016): https://www.brentozar.com/archive/2016/03/changes-to-auto-update-stats-thresholds-in-sql-server-2016/

Loading... Reply Simon April 28, 2016 4:34 pm Thanks Kendra. Good to know this info. Its a pretty important TF for VLDB

Reply Karthik Yella May 2, 2016 5:21 am Great write up Kendra!!

This line of yours “A lot of data is changing, but it’s below the 25%+500 rows limit of where auto-update kicks in because the table has hundreds of millions of rows”

Should not it be 20%+500 ?

Loading... Reply Kendra Little May 2, 2016 12:26 pm Great catch, and thank you for your comment. Yes, it should be 20%+500 there, I just corrected it.

Loading... Reply Alan September 13, 2016 10:15 am Hi Kendra, thank you for a great summary! I have a question: when I run a full scan for both indexes and columns stats for a large table (around 50 gb), where does it occur? is it a logged activity, if I have a full recovery mode? does it use a buffer cache? Thank you! Alan

Loading... Reply Kendra Little September 19, 2016 8:33 am Hi Alan,

Breaking out the questions:

1) Can update statistics read data from memory if it’s in cache? Yes. You can see this yourself by running a simple profiler or extended events trace containing logical and physical reads, and updating statistics with fullscan for an object twice. As long as the data you’re reading fits in memory, you should not see physical reads on the second update of stats. (If the data is already in cache, you might not see physical reads even on the first run.)

2) Are statistics updates logged? Yes, but the reads aren’t logged. Statistics objects themselves (histogram, header, etc) are quite small. These are logged operations, which is good– when you restore a database the statistics are restored with it.

Loading... Reply Suresh Thiravidamani October 7, 2016 2:36 am Hi,

Lot of things about statistics has been described …Thanks lot …. I am leaning lot of very deep information about SQL related… Thanks…

Loading... Reply Kendra Little October 7, 2016 7:51 am My pleasure! Thanks for reading.

Loading... Reply sandip pani December 5, 2016 12:43 pm before update stats query was taking 1 sec but after update stats it is taking 15 seconds. Of course plan got changed, logical read reduced no change in physical read. what could be the reason? I enforced the old plan to test but it is taking more time now.

Loading... Reply Kendra Little December 5, 2016 2:09 pm First things that come to mind:

The query could have different waits now. For example, any physical reads might have a lot more latency now, or it might have lock waits, or wait time to get on a processor.
If the query is parameterized, the parameters running now that take 15 seconds might not work well with the plans that it is getting (old or new).

Loading... Reply Does Truncate Table Reset Statistics? - by Kendra Little December 8, 2016 8:22 am […] Want to learn more about statistics in SQL Server? Start here. […]

Loading... Reply Gundeep January 18, 2017 11:39 am Hello : Really nice article. Good information. I have a question around Rebuild Indexes and Update Statistics ..We are on SQL 2012 . Basically our ETL team needs to incorporate that ability as part of there ETL script they use , so that once they are done with the load they should be able too Rebuild Indexes and Update Statistics without scheduling any job on SQL server . What king of Privileges can I give to there SQL ID’s they normally use which can kick off the Rebuild etc . Want to avoid the Maintain ace Plan and Schedule the job as it is only required when they do the load and we do not know when it will finish …So trying to kick off the Rebuild process once load is done but using there SQL ID’s

Thanks

Loading... Reply Kendra Little January 18, 2017 11:47 am From Books Online, “To execute ALTER INDEX, at a minimum, ALTER permission on the table or view is required.” So that’s a lot of permissions.

My memory is that ownership chaining doesn’t cover things like alter index — so just granting permissions to a stored procedure that did the work wouldn’t be enough to work around this if you want to give more limited/targeted permissions. However, I think you can get around this by using a certificate with a procedure. Read more here and test: http://www.sommarskog.se/grantperm.html#Certificates

Again, just going off memory– haven’t looked at this for a while. Hope it helps.

Loading... Reply Gundeep January 18, 2017 12:07 pm Thanks…

Loading... Reply Kyle Dooley May 25, 2017 7:18 am i have a database that once we upgraded from 2008R2 to 2014 we started experiencing severe issues with about 5 procedures. I wont go into every step i have taken but it even includes opening a support call with Microsoft who gave us very general recommendations. We did roll back the comparability to use the old optimizer. The only thing that seems to help is if I update stats every 30 minutes on a group of about 7 tables. I really do not see the amount of change in these to warrant this. My feeling is there is too much business logic in these procedures and something about 2014 doesn’t like it. I do see the plans are often incomplete and I think the Optimizer is just guessing wrong,. I have asked for them to be redone Other than that I do not know where we are going with it. We still have the case open with Microsoft.

Loading... Reply Kendra Little May 25, 2017 8:27 am One simple test that might help you make progress is to see if running sp_recompile against the stored procedure instead of updating stats also helps.

If it does, then that indicates you’re likely looking at an issue relating to parameter sniffing. If it does NOT then it may be related more to the statistics. Knowing this can help guide the direction of the treatment.

It’s easy to confuse this with a statistics update issue because a side effect of updating statistics is causing recompiles.

Loading... Reply Kyle Dooley May 25, 2017 8:38 am Hi Kendra, Have done that as well as trying the local variable swap trick for parameter sniffing. I did’t know about the trace flag 2371 until I read this posting by you. Thanks very much. I am trying that one right now and keeping a close eye on things. Also using the DMV now to see how many rows are changing. I am seeing more that 6000 in 15 minutes on some of those tables.

Loading... Reply knn9413 March 2, 2018 5:57 pm We took over a SQL server 2008 application. (Sarcasm font on) Unfortunately the DBA’s are so good, that I have to read articles to have them try out something to improve the performance of queries (Sarcasm font off). This article was an eye opener. The DBA’s have a process that do a REBUILD INDEX everyday for all the high volume tables, and then they run the UPDATE STATS WITH FULLSCAN during the evening which runs for so long somedays( imagine over 6 hours) that most of the days we have to end up cancelling them before the start of our nightly batch cycle. I think I will have to refer them to this article and make them try a script based UPDATE stats than the one with a FULL SCAN (like the one you suggested in the Brent Ozar blog). I could probably go on and on with some of the critical problems we face that could probably be a teaching session for most folks.

Loading... Reply CT August 28, 2018 8:38 am Hi Kendra,

Thank you so much for the excellent article. We are running the databases in AG in a Asynchronous mode. Primary and one secondary Database. When we collect stats on the tables in primary it puts the secondary lot behind.Collecting stats on a table took 10 minutes in primary, it took 35 minutes for secondary to catch up. Is it an anticipated behavior? can you throw some light on what is happening behind the scenes? Thank you and appreciate your time. Version SQL Server 2017

Loading... Reply Kendra Little August 31, 2018 1:26 pm Just want to confirm, when you say “collecting stats”, do you mean updating statistics? Or could there be some index rebuild commands in there as well?

If you are only updating statistics, running it with fullscan against a large table could certainly take 10 minutes. However, this is almost entirely a read operation and a stats update does not write much information to the database. It could be that simply doing the scans is overwhelming the IO system and causing the secondary to get behind, and if that is the case I’d be very concerned because it seems like a VERY fragile setup since so little has changed.

If the job does contain something like running an index rebuild against a very large table, then it might be more expected for the secondary to get behind if there is a large amount of network latency — still concerning, but it would make more sense.

Loading... Reply 升級SQL 2014，統計資訊(Statistics)的重要性？ – DBA 黑白講 September 6, 2018 8:40 am […] https://littlekendra.com/2016/04/18/updating-statistics-in-sql-server-maintenance-answers/ […]

Loading... Reply Nick February 21, 2019 2:39 pm any idea why SQL server doesn’t update index stats automatically after reorganizing an index? Is it a design flow or some technical limitation.

Loading... Reply Kendra Little February 26, 2019 12:02 pm In the case of a reorganize, it works through small groups of pages at a time. This has some benefits: small memory requirements, low impact on performance, and if you stop reorganize at any point there is no transaction to roll back. A reorganize operation doesn’t need to scan all of the data at once like an index rebuild does, so it doesn’t have all the information it would need to update statistics.

Loading... Reply akhilesh narayanan April 28, 2019 9:32 am What would happen if we run the update statistics in the following order;

UPDATE STATISTICS TABLE WITH FULLSCAN — Sunday
UPDATE STATISTICS TABLE — Monday, Will the FULL statistics over written by sample statistics?

Loading... Reply Kendra Little April 29, 2019 4:38 am Hi there,

You can control the behavior in step 2 by choosing whether or not to specify RESAMPLE. Read up on RESAMPLE here to learn more. You may also be interested in the PERSIST_SAMPLE_PERCENT setting, but note that it was introduced in 2016, so not available everywhere.

Kendra

Loading... Reply akhilesh narayanan May 3, 2019 2:56 am Hello, My query different; will the full statistics replaced by sampling!

Loading... Reply Kendra Little May 3, 2019 4:28 am If you are not specifying RESAMPLE or PERSIST_SAMPLE_PERCENT anywhere, the answer is ‘maybe’. It’s possible that SQL Server might choose to do a fullscan to update the stats, it will do that against smaller tables. But yes, it might use a sampling against a larger table.

Loading... Reply Eugene Larkin October 4, 2019 6:29 am Thank you for the concise information. I appreciate the candor and the examples. I also appreciate the complete presentation of the information in one place. The only thing I didn’t observe you talking about much is the difference between dynamic sql and stored procedure sql and how this is affected. That is something I have had to deal with on large tables.

LitKnd / littlekendracomments

Post: Updating Statistics in SQL Server: Maintenance Questions & Answers #19