SQLServer_Tips

SQLServer_Tips †

Resourceデータベース †

• Resourceデータベースはmasterデータベースと同じ場所に配置しなければならない
• Resourceデータベースのバージョン確認

  SELECT SERVERPROPERTY('ResourceVersion')

• Resourceデータベースの最終更新日時確認

  SELECT SERVERPROPERTY('ResourceLastUpdateDateTime')

システムオブジェクト †

システムオブジェクトのSQL定義を確認する(3種類) †

SELECT OBJECT_DEFINITION(OBJECT_ID('システムオブジェクト'))

EXEC sp_helptext 'システムオブジェクト', NULL

SELECT m.definition
FROM sys.systems_sql_modules m
INNER JOIN sys.sysobjects s ON m.object_id = s.id
WHERE s.name = 'オブジェクト名'    <- sys.all_objectsの場合は、'all_objects'でよい

データベースの状態確認 †

SELECT DATABASEPROPERTYEX('データベース名', 'Status')

ユーザデータベースの移動 †

• データベースを計画的に再配置する場合

  ALTER DATABASE データベース名 SET OFFLINE
  ファイルの移動
  ALTER DATABASE データベース名 MODIFY FILE (NAME=データベース論理名, FILENAME='データベースファイルの物理パス(ファイル名含む)')
  ALTER DATABASE データベース名 SET ONLINE

• サーバのメンテナンス時に再配置する場合

  ALTER DATABASE データベース名 MODIFY FILE (NAME=データベース論理名, FILENAME='データベースファイルの物理パス(ファイル名含む)')
  サーバ(インスタンス)の停止
  ファイルの移動
  サーバ(インスタンス)の起動

• 障害時の再配置

  サーバ(インスタンス)の停止
  masterデータベースのみ復旧でのサーバ(インスタンス)起動  NET START MSSQLSERVER /f /t3608
  ALTER DATABASE データベース名 MODIFY FILE (NAME=データベース論理名, FILENAME='データベースファイルの物理パス(ファイル名含む)')
  サーバ(インスタンス)の停止
  ファイルの移動
  サーバ(インスタンス)の起動

フルテキストカタログの移動 †

• ALTER DATABASEでの移動

  ALTER DATABASE データベース名 SET OFFLINE
  ファイルの移動
  ALTER DATABASE データベース名 MODIFY FILE (NAME=カタログ論理名, FILENAME='カタログファイルの物理パス(ファイル名除く)')
  ALTER DATABASE データベース名 SET ONLINE

• アタッチでの移動

  sp_detach_db データベース名
  ファイルの移動
  CREATE DATABASE データベース名 ON (FILENAME='.mdfのパス'), (FILENAME='.ldfのパス'), (FILENAME='カタログのパス') FOR ATTACH

データベースをシングルユーザモードにする †

1. AUTO_UPDATE_STATISTICSオプションの統計の更新設定が同期か非同期かを確認する

   SELECT is_auto_update_stats_async_on FROM sys.databases

2. 結果が1(同期)なら統計の更新設定を非同期に設定する

   ALTER DATABASE データベース名 SET AUTO_UPDATE_STATISTICS_ASYNC OFF

3. アクティブな非同期の統計ジョブがあるかどうかを確認する

   SELECT * FROM sys.dm_exec_background_job_queue

4. 統計ジョブがある場合はKILLする

   KILL STATS JOB

5. シングルユーザモードに設定する。(接続していたユーザのトランザクションは指定秒数でロールバックさせる)

   ALTER DATABASE データベース名 SET SINGLE_USER WITH ROLLBACK AFTER 秒数

6. シングルモードで実施する作業を行う
7. 統計の更新設定を同期から非同期へ変更していた場合は元に戻す

   ALTER DATABASE データベース名 SET AUTO_UPDATE_STATISTICS_ASYNC ON

データベースをマルチユーザモード(通常モード)にする †

ALTER DATABASE SET MULTI_USER

データベース名を変更する †

ALTER DATABASE データベース名 MODIFY NAME = 新しいデータベース名

• データベース名を変更する場合は、そのデータベースをシングルユーザモードに設定しておく必要がある

DAC(専用管理者接続)が使用中かどうかを確認する †

SELECT COUNT(t1.name) FROM sys.tcp_endpoints AS t1
INNER JOIN sys.dm_exec_sessions AS t2 ON t1.endpoint_id = t2.endpoint_id
WHERE t1.name = 'Dedicated Admin Connections'

• 結果が1ならDAC使用中、0なら使用していない

データベーススナップショットを作成する †

CREATE DATABASE スナップショット名
ON (NAME='データベースファイル論理名', FILENAME='データベースのファイルパス\スナップショットファイル名')
AS SNAPSHOT OF データベース名

• 例

  CREATE DATABASE AdventureWorks_snapshot
  ON (
        NAME='AdventureWorks_Data'
      , FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks_snapshot.ss'
  )
  AS SNAPSHOT OF AdventureWorks

データベーススナップショットを削除する †

DROP DATABASE スナップショット名

計算列 †

• 同じテーブルの他の列の計算結果を列として格納できる

  CREATE TABLE TestTable1(
        [price] [money]
      , [tax] [money]
      , [total] AS ([price] + [price] * [tax] / 100)
  )
  
  INSERT INTO TestTable1 VALUES(100, 5)
  SELECT * FROM TestTable1
  price                 tax                   total
  --------------------- --------------------- ---------------------
  100.00                5.00                  105.00

  • 計算結果を列に格納して利用できるので利用方法によっては利便性が向上する。
    • 条件はあるが、インデックスやキーとして利用できる。
    • 計算列で作成できるのは、UNIQUEとPRIMARY KEYの制約のみ。
    • CHECK, FOREIGN KEY, NOT NULLの制約のある列で計算列を利用する場合は、PERSISTED宣言をする必要がある。

      , [total] AS ([price] + [price] * [tax] / 100) NOT NULL              <- ×
      , [total] AS ([price] + [price] * [tax] / 100) PERSISTED NOT NULL    <- ○

  • SELECTする度に計算して値を求めるので、計算が複雑になれば速度は落ちる。

データベースの互換性レベル †

互換性レベルを確認する †

EXEC sp_dbcmptlevel 'データベース名'

互換性レベルを変更する †

EXEC sp_dbcmptlevel 'データベース名', 互換性レベル

テーブルの圧縮 †

現在圧縮を実行しているかどうかの確認 †

SELECT data_compression, dataq_compression_desc FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('テーブル名')

• data_compressionの意味

  data_compression	data_compression_desc	意味
  0	NONE	非圧縮
  1	ROW	行圧縮済み
  2	PAGE	ページ圧縮済み

圧縮後のサイズ見積もり †

EXEC sp_estimate_data_compression_savings 'スキーマ名', 'テーブル名', NULL, NULL, '圧縮種別'

• 例

  USE AdventureWorks
  EXEC sp_estimate_data_compression_savings 'Person', 'Address', NULL, NULL, 'ROW'    <- 行圧縮

  列名	意味
  size_with_current_compression_settings[KB]	現在のサイズ
  size_with_requested_compression_setting[KB]	圧縮後のサイズ

圧縮 †

ALTER TABLE スキーマ名.テーブル名 REBUILD WITH (DATA_COMPRESSION = 圧縮種別)

• 例

  USE AdventureWorks
  ALTER TABLE Persion.Address REBUILD WITH (DATA_COMPRESSION = ROW)

圧縮解除 †

ALTER TABLE スキーマ名.テーブル名 REBUILD WITH (DATA_COMPRESSION = NONE)

DBCC -> DML †

DBCC SQLPERF('logspace') †

SELECT RTRIM(pc1.instance_name) AS [Database Name]
    , pc1.cntr_value / 1024.0 AS [Log Size (MB)]
    , CAST(pc2.cntr_value * 100.0 / pc1.cntr_value AS decimal(5,2)) AS [Log Space Used (%)]
FROM sys.dm_os_performance_counters as pc1
INNER JOIN sys.dm_os_performance_counters as pc2 ON pc1.instance_name = pc2.instance_name
WHERE pc1.object_name LIKE '%Databases%'
    AND pc2.object_name LIKE '%Databases%'
    AND pc1.counter_name = 'Log File(s) Size (KB)'
    AND pc2.counter_name = 'Log File(s) Used Size (KB)'
    AND pc1.instance_name NOT IN ('_Total', 'mssqlsystemrecource')
    AND pc1.cntr_value > 0

日時 †

現在日時の取得 †

SELECT GETDATE()

SELECT CURRENT_TIMESTAMP