SQLServer_Tips †
Resourceデータベース †
システムオブジェクト †
システムオブジェクトの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
データベースをシングルユーザモードにする †
- AUTO_UPDATE_STATISTICSオプションの統計の更新設定が同期か非同期かを確認する
SELECT is_auto_update_stats_async_on FROM sys.databases
- 結果が1(同期)なら統計の更新設定を非同期に設定する
ALTER DATABASE データベース名 SET AUTO_UPDATE_STATISTICS_ASYNC OFF
- アクティブな非同期の統計ジョブがあるかどうかを確認する
SELECT * FROM sys.dm_exec_background_job_queue
- 統計ジョブがある場合はKILLする
KILL STATS JOB
- シングルユーザモードに設定する。(接続していたユーザのトランザクションは指定秒数でロールバックさせる)
ALTER DATABASE データベース名 SET SINGLE_USER WITH ROLLBACK AFTER 秒数
- シングルモードで実施する作業を行う
- 統計の更新設定を同期から非同期へ変更していた場合は元に戻す
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'
データベーススナップショットを作成する †
CREATE DATABASE スナップショット名
ON (NAME='データベースファイル論理名', FILENAME='データベースのファイルパス\スナップショットファイル名')
AS SNAPSHOT OF データベース名
データベーススナップショットを削除する †
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
- 計算結果を列に格納して利用できるので利用方法によっては利便性が向上する。
- 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 = 圧縮種別)
圧縮解除 †
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