![]() On the one hand, I've corrected all the "automatically cluster by identity" on large tables, so I don't have specific issue. We've got tables with 100Ms to 1.4B rows, or so, and thus doing a full scan of them to update stats is not a trivial decision. But the I/O required to fully scan large tables definitely is. <<Īgreed, CPU is not a concern for me either. > Yes, it takes some CPU (fortunately, I'm not hurting there). This reply was modified 2 years, 4 months ago by Jackie Lowery.WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1ĪND (s.auto_created = 0 ) - OR s.user_created = 1)Īnd OBJECT_NAME(s.) like 'ws%' = obj.ĬROSS APPLY sys.dm_db_stats_properties(sc., s.stats_id) AS dsp STATS_DATE(s., s.stats_id) AS LastUpdated,ĭATEDIFF(d,STATS_DATE(s., s.stats_id),getdate()) DaysOld, Does that logic sound right? It also looks like i could use the modification_counter column to see which stats are changing the most. That should tell me which tables i need to do a manual full daily stats update on. I did a full scan on the warehouse tables yesterday (they get a lot of updates) and my plan is to check daily to see which tables receive a new auto stats update within a days time. I found a query that shows the date statistics were updated and days since last update. I didn't know of the new persisting sample percent feature. But it has helped and it's one more thing that I don't have to worry about or put up with if something is missed. Yes, it takes some CPU (fortunately, I'm not hurting there). While many will take exception to it, because of that fact, I've adopted the "if it moves, shoot it" philosophy on nightly statistics rebuilds and rebuild statistics on anything that's had a change for row modifications (which includes inserts). The indexes that are based on SEQUENCE might be a little tough to find because they don't necessarily use anything that's easily detectable, such as a default with a sequence.Ī good deal of the tables we have at our shop are necessarily based on columns that have the IDENTITY property and Clustered Indexes to match (we're working on that but that's a different subject that I'd rather not get into on this thread). I agree with Scott on persisting the sample percent.Īs to your other inquiry about how to determine which tables need full scans, I'd say any indexes that are based on an "ever-increasing" key, such as IDENTITY, SEQUENCE, something temporal, or NEWSEQUENTIALID(), generally fit the bill unless they're static. What's the best method to figure out which tables i need to manually do full scans of stats on a daily basis so SQL server doesn't need to do an auto update of stats? ![]() The issue is the auto update of stats doesn't do a full scan, so it causes slowness and bad query plans. ![]() What's the best way to know which index statistics need to be updated on a daily basis? I currently update stats on Friday, but I've noticed SQL is updating some of my stats automatically. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |