YourSQLDba低版本的一個Bug的淺析

<Exec>

  <ctx>yMaint.UpdateStats</ctx>

  <inf>update statistics selected</inf>

  <cmd>update statistics [model].[dbo].[ServiceBrokerQueue] WITH sample 100 PERCENT</cmd>

  <err>Error 2706, Severity 16, level 6 : Table 'ServiceBrokerQueue' does not exist.</err>

</Exec>

Exec YourSQLDba.Install.PrintVersionInfo

========================================

YourSQLDba version: 5.0.2 2012-06-12

-- makes query boilerplate with replacable parameter identified by

-- labels between '<' et '>'

-- this query select table for which to perform update statistics

truncate table #TableNames

set @sql =

'

 set nocount on

 ;With

   TableSizeStats as

 (

 select 

   object_schema_name(Ps.object_id, db_id('<DbName>')) as scn --collate <srvCol>

 , object_name(Ps.object_id, db_id('<DbName>')) as tb --collate <srvCol>

 , Sum(Ps.Page_count) as Pg

From

  sys.dm_db_index_physical_stats (db_id('<DbName>'), NULL, NULL, NULL, 'LIMITED') Ps

Group by 

  Ps.object_id  

)

Insert into #tableNames (scn, tb, seq, sampling)

Select 

  scn

, tb

, row_number() over (order by scn, tb) as seq

, Case 

    When pg > 200001 Then '10'

    When Pg between 50001 and 200000 Then '20'

    When Pg between 5001 and 50000 Then '30'

    else '100'

  End  

From 

  TableSizeStats

where (abs(checksum(tb)) % <SpreadUpdStatRun>) = <seqStatNow>

'  

-- makes query boilerplate with replacable parameter identified by

-- labels between "<" et ">"

-- this query select table for which to perform update statistics

truncate table #TableNames

set @sql =

'

 Use [<DbName>]

 set nocount on

 ;With

   TableSizeStats as

 (

 select 

   object_schema_name(Ps.object_id) as scn --collate <srvCol>

 , object_name(Ps.object_id) as tb --collate <srvCol>

 , Sum(Ps.Page_count) as Pg

From

  sys.dm_db_index_physical_stats (db_id("<DbName>"), NULL, NULL, NULL, "LIMITED") Ps

Where (   OBJECTPROPERTYEX ( Ps.object_id , "IsTable" ) = 1

       Or OBJECTPROPERTYEX ( Ps.object_id , "IsView" ) = 1)

Group by 

  Ps.object_id  

)

Insert into #tableNames (scn, tb, seq, sampling)

Select 

  scn

, tb

, row_number() over (order by scn, tb) as seq

, Case 

    When Pg > 5000001 Then "0"

    When Pg between 1000001 and 5000000 Then "1"

    When Pg between 500001 and 1000000 Then "5"

    When pg between 200001 and 500000 Then "10"

    When Pg between 50001 and 200000 Then "20"

    When Pg between 5001 and 50000 Then "30"

    else "100"

  End  

From 

  TableSizeStats

where scn is not null and tb is not null and (abs(checksum(tb)) % <SpreadUpdStatRun>) = <seqStatNow>

'