DB/SQL Server/調査用SQL

TABLEのINDEX付与状態確認

select OBJECT_NAME(A.object_id) As [オブジェクト名]
    ,B.name
    ,C.name
    ,A.index_id
    ,B.type_desc
from sys.index_columns A
inner join sys.indexes B on A.index_id=B.index_id
inner join sys.columns C on A.column_id=C.column_id
    where  A.object_id=B.object_id
    and    A.object_id=C.object_id
--    and    A.object_id=OBJECT_ID('TABLE名')
order by OBJECT_NAME(A.object_id),B.index_id,A.index_column_id

ファイルグループとデータファイルの紐付け

select D.name
   ,F.name
   ,F.physical_name
   ,F.size
from sys.database_files F
inner join sys.data_spaces D on F.data_space_id=D.data_space_id

統計取得情報

SELECT OBJECT_NAME(OBJECT_ID) AS オブジェクト
    ,name AS 統計名
    ,STATS_DATE(object_id, stats_id) AS 更新日
FROM sys.stats
order by OBJECT_ID,name

パーティション関数とファイルグループの紐付け

SELECT
    ps.name As [パーティション構成名]
    ,ds.name As [ファイルグループ名]
    ,dds.destination_id As [パーティション番号]
FROM sys.destination_data_spaces dds
INNER JOIN sys.partition_schemes ps
    ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.data_spaces ds
    ON dds.data_space_id = ds.data_space_id
ORDER BY partition_scheme_id

パーティション関数と境界値の紐付け

SELECT f.name, r.value
FROM sys.partition_range_values r
INNER JOIN sys.partition_functions f
    ON r.function_id = f.function_id

バックアップ情報

select
     S.name
    ,S.backup_set_id
    ,S.media_set_id
    ,S.database_name
    ,S.type
    ,S.position
    ,M.logical_device_name
    ,M.physical_device_name
    ,S.compressed_backup_size/1024/1024 as [容量(MB)]
    ,S.backup_start_date
    ,S.backup_finish_date
from msdb.dbo.backupset S inner
join msdb.dbo.backupmediafamily M
on   S.media_set_id=M.media_set_id
order by S.backup_set_id

LOCK状態確認

SELECT CONVERT (SMALLINT, req_spid) AS spid,
    db_name(rsc_dbid) AS dbid,
    object_name(rsc_objid) AS ObjId,
    rsc_indid AS IndId,
    SUBSTRING (v.name, 1, 4) AS Type,
    SUBSTRING (rsc_text, 1, 16) AS Resource,
    SUBSTRING (u.name, 1, 8) AS Mode,
    SUBSTRING (x.name, 1, 5) AS Status
FROM master.dbo.syslockinfo,
    master.dbo.spt_values v,
    master.dbo.spt_values x,
    master.dbo.spt_values u
WHERE master.dbo.syslockinfo.rsc_type = v.number
   and v.type = 'LR'
   and master.dbo.syslockinfo.req_status = x.number
   and x.type = 'LS'
   and master.dbo.syslockinfo.req_mode + 1 = u.number
   and u.type = 'L'

トップ   編集 凍結解除 差分 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2015-07-19 (日) 22:44:03 (1931d)