YourSQLDba低版本的一個Bug的淺析

幫人分析解決一個YourSQLDba備份報錯問題，個人覺得有點意思，順手記錄一下分析思路，大體解決思路如下：

首先，找到YourSQLDba作業YourSQLDba_FullBackups_And_Maintenance的報錯郵件或者作業的錯誤日誌信息，檢查YourSQLDba出錯的詳細信息。

YOURSQLDBA.MAINT.ShowHistoryErrors 96

發現YourSQLDba在更新統計信息是遇到錯誤，如下所示

<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>

檢查發現這個對象是queue，根本不是表，所以更新統計信息會出錯。

那麼YourSQLDba怎麼會更新queue對象的統計信息呢？我首先檢查了一下YourSQLDba的版本信息。當前資料庫伺服器上的YourSQLDba是相當老的一個版本了。然後我就去檢查YourSQLDba的代碼

Exec YourSQLDba.Install.PrintVersionInfo

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

YourSQLDba version: 5.0.2 2012-06-12

YourSQLDba更新統計信息是通過[yMaint].[UpdateStats]來更新統計，於是檢查代碼，發現獲取要更新統計信息的對象是通過下麵腳本獲取的。首先將要更新統計信息的對象放到臨時表#TableNames中去，然後生成更新統計信息的腳本。

-- 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>

'  

這個腳本會將queue類型的對象也放入臨時表，所以明顯是個Bug，不過YourSQLDba後續的版本已經Fix掉這個Bug了。如下所示，後續的版本就加上條件過濾了，只獲取表和視圖的數據。所以遇到這個問題，只需要升級YourSQLDba的版本就好了

-- 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>

'