I’d always find a way to get the testing one to the latest upgraded statistics command used otherwise would use if not specifying any Decide to try
Gaby – okay, please grab the book Troubleshooting SQL Machine by the Kehayias & Kreuger, or see all of our Developer’s Self-help guide to SQL Host Abilities video category at
Discover [so].[name] As the [TableName], –[sch].[name] + ‘.’ + [so].[name] Once the [TableName], [ss].[name] Just like the [Statistic], [sp].[last_updated] Once the [StatsLastUpdated], [sp].[rows] Due to the fact [RowsInTable], [sp].[rows_sampled] Since [RowsSampled], [sp].[modification_counter] While the [RowModifications], CAST(100 * [sp].[modification_counter] / [sp].[rows] Given that Quantitative(18,2)) As the [PercentChange] , ROUND(CAST(one hundred.0 * sp.modification_prevent / sp.rows Since Decimal(18,2)),2) As percentTwo On #StatsToCheck2 From [sys].[stats] [ss] Sign up [sys].[objects] [so] To your [ss].[object_id] = [so].[object_id] Signup [sys].[schemas] [sch] On [so].[schema_id] = [sch].[schema_id] Outer Pertain [sys].[dm_db_stats_properties] ([so].[object_id], [ss].[stats_id]) sp In which [so].[type] = ‘U’ And you can [so].[name] Maybe not from inside the (‘TableNotNeedingMaintenance’) And you may ss.Name Maybe not Within the (Find statname Off administrator.dbo.statstoskip) –And you can SP.last_current.5 –And sp.rows>5000000 Acquisition From the Bullet(CAST(100.0 * sp.modification_restrict / sp.rows Since Quantitative(18,2)),2) DESC,last_upgraded
– get a hold of * from #statstocheck2 Play with learn go declare int set = 600 — ten instances = 600 moments maximum for whole procedure State DATETIME; Put = GETDATE(); e’ age Declare VARCHAR(200) elizabeth,Fact from #StatsToCheck2 Open c Get 2nd From c Towards the , Whenever you are ( = 0 and you may DATEDIFF(Time, , GETDATE()) 0 ROLLBACK – Increase a mistake with the information on the new different Declare NVARCHAR(4000) , INT
men and women dining tables where i have to inform the data features analysis chantes (Inform, insert , delete) we try not to must eliminate the automobile do stats: because the other Arrangements might be affected. my personal last resource try do something so you’re able to posting the fresh new statistics everty 24 hours. Is there most other choice?
Select [so].[name] Since the [TableName], –[sch].[name] + ‘.’ + [so].[name] Because the [TableName], [ss].[name] As [Statistic], [sp].[last_updated] Since [StatsLastUpdated], [sp].[rows] Because the [RowsInTable], [sp].[rows_sampled] Since the [RowsSampled], [sp].[modification_counter] Since the [RowModifications], CAST(a hundred * [sp].[modification_counter] / [sp].[rows] Because the Quantitative(18,2)) As the [PercentChange] , ROUND(CAST(a hundred.0 * sp.modification_restrict / sp.rows While the Decimal(18,2)),2) Because percentTwo To your #StatsToCheck From [sys].[stats] [ss] Join [sys].[objects] [so] To your [ss].[object_id] = [so].[object_id] Subscribe [sys].[schemas] [sch] Towards [so].[schema_id] = [sch].[schema_id] Outside Apply [sys].[dm_db_stats_properties] ([so].[object_id], [ss].[stats_id]) sp Where [so].[type] = ‘U’ And you will [so].[name] Perhaps not into the (‘USER_ACTIVITY_LOG’,’OTTS_HISTORY_NEW’,’XML_SERIALIZED_INSTANCE’,’FILE_RECORD’) And you may ss.Title Maybe not In (See connexion kortingscode statname Off administrator.dbo.statstoskip) –And Shed(one hundred * [sp].[modification_counter] / [sp].[rows] As the Quantitative(18,2)) >= step 1.00 And you may Round(CAST(100.0 * sp.modification_prevent / sp.rows Because Quantitative(18,2)),2)>.5 And you may sp.rows>50000 Order Because of the Round(CAST(a hundred.0 * sp.modification_restrict / sp.rows While the Decimal(18,2)),2) DESC,last_upgraded Have fun with grasp wade state int place = 600 — 10 days = 600 Declare DATETIME; Put = GETDATE(); Elizabeth Put = ‘MyDatabase’ age Declare VARCHAR(200) elizabeth,Fact from #StatsToCheck purchase because of the [PercentChange] desc, [StatsLastUpdated] Unlock c Get Next Away from c Towards the , If you’re ( = 0 and DATEDIFF(Time, , GETDATE()) 0 ROLLBACK – Raise a mistake on the specifics of the exclusion Claim NVARCHAR(4000) , INT
I want to up-date my personal statistics every for some dining tables, because the execution ask requires doing fifty moments alternatively ten Sec there are quick tables, how to stop this situation?
Remaining it out once more. Really on cursor loop it does so it. We’ll find out if this new comment demons strip it out once more:
Carry out master.dbo.IndexOptimize = , = NULL, = NULL, = NULL, = ‘ALL’, = , = ‘Y’, = 100, = 7200, = 8, = ‘Y’, = ‘Y’, =step one
I have tried Ola’s texts, but could never ever cause them to benefit myself. You individuals are much s. Becoming a part go out dBA doesn’t help me to receive any finest.
High post, I’m trying understand the TABLESAMPLE part. Have you got a great deal more sense on this? How come, I am inquiring is the fact I realized that the new Hallengren script, is determined to run because the “update analytics TableName indexName… and you will I’m evaluating it using my individual software… Inside my software with regards to the level of rows the new desk features, I ple, If i had a dining table you to 120M rows, I’d ple 3M rows. My personal ways takes additional time than just allowing SQL pick the sampling as the Hallengren really does. I tried to run the new query which you have regarding TABLESAMPLE point but did not have it to get results… Any suggestion?
