DB/SQL Server

※ ここに記載されてる内容は、SQL Server 2008の情報になります

オブジェクトメモ

カタログビュー確認できる情報
sys.all_objectsオブジェクト一覧。name列をlikeで検索して目的のオブジェクトを探す
sys.database_filesデータベースのデータファイル情報
sys.partition_functionsパーティション関数
sys.partition_schemesパーティション構成
sys.partition_range_valuesパーティション関数の範囲境界値
sys.destination_data_spacesパーティション構成のファイルグループ一覧
sys.server_principalsログインユーザー、サーバーロール
sys.database_principalsデータベースユーザー、データベースロール
sys.server_permissions権限
sys.server_file_audits監査ファイル
sys.server_audit_specificationsサーバー監査仕様
sys.database_audit_specificationsデータベース監査仕様
sys.resource_governor_resource_poolsリソースガバナのリソースプール
sys.resource_governor_workload_groupsリソースガバナのワークロード
sys.resource_governor_configurationリソースガバナの分類関数


動的管理オブジェクト説明
sys.dm_exec_connectionsサーバーに接続している現在の全てのセッションに関する情報
sys.dm_exec_requestsセッションごとに実行しているコマンドの処理要求に関する情報
sys.dm_exec_cached_plansプロシージャキャッシュに格納されたクエリプラン情報
sys.dm_exec_text_query_plan(plan_handle,0,-1)で 実行プラン取得上記で取得したquery_planを.sqlplanで保存し開くとプラン表示できる
sys.dm_exec_query_statsプロシージャキャッシュに格納されたクエリプランのパフォーマンス統計
sys.dm_exec_sql_textsys.dm_exec_sql_text(sql_handle)にてSQLのテキストを取得。
sys.dm_os_memory_poolsシステム内に割り当てられている各メモリオブジェクトの情報
sys.dm_os_wait_stats実行中のすべてのスレッドで検出された待機に関する統計
sys.dm_tran_locks現在システム内で許可または要求されているロックに関する情報
sys.dm_db_index_usage_statsINDEX使用頻度。user_xxx列で確認
sys.dm_db_index_physical_statsインデックスの断片化の情報
sys.dm_db_partition_statsパーティション毎にページ数と行数の情報を返す(データ及びインデックス)


INFORMATION_SCHEMA説明
COLUMNS現在のデータベースの現在のユーザーがアクセスできる列
INDEXES現在のデータベースに含まれるインデックス
KEY_COLUMN_USAGE現在のデータベースに含まれるキー
PROVIDER_TYPESSQL Server Compact 3.5 でサポートされるデータ型
TABLES現在のデータベースの現在のユーザーがアクセスできるテーブル
TABLE_CONSTRAINTS現在のデータベースに含まれるテーブル制約
REFERENTIAL_CONSTRAINTS現在のデータベースに含まれる外部制約


ストアドプロシージャ説明
sp_helpオブジェクトの全情報
sp_helpindexインデックスに関する情報
sp_helpconstraint制約情報に関する情報
sp_helpfileDBで使用しているファイル情報
sp_helpdevice論理デバイス情報
sp_helploginsログイン情報
sp_helpuserDBユーザー情報
sp_helpsrvrolemember固定サーバーロールが付与されているロールメンバ
sp_helprolemember固定データベースロールが付与されているロールメンバ
sp_configureサーバーの設定値取得・設定。サーバー構成オプション
sp_addsrvrolememberログインにロールを付与
sp_addumpdevice論理デバイス追加
sp_attach_dbDBをアタッチする
sp_detach_dbDBをデタッチする


組み込み関数取得する値
OBJECT_NAME(object_id)オブジェクト名
FILE_NAME(file_id)論理ファイル名
COL_NAME(object_id,column_id)カラム名取得
DB_NAME()データーベース名
ORIGINAL_LOGIN()ログインしたユーザー名
CURRENT_USER現在接続中のスキーマー

組み込み関数一覧

グローバル変数戻す値
@@VERSIONインストールされているSQL Serverのバージョン
@@CONNECTIONSSQL Server の起動後に、成功または失敗した接続試行数
@@ERROR直前に実行したクエリのエラー状態を保持
@@LOCK_TIMEOUTデットロックのタイムアウト時間(秒)
@@ROWCOUNT直前に実行したクエリの件数
@@SERVICENAMEインスタンス名


その他コマンド説明
use DB名DBの切り替え
SET STATISTICS IOSQL実行時にページのI/Oを確認
SET STATISTICS TIMESQL実行時にページの経過時間等が確認できる
SET SHOWPLAN_ALL実行プランの表示。ALLの替わりにXML/TEXTもある
DBCC DROPCLEANBUFFERSプロシージャキャッシュをクリア
DBCC IND(DB名,TBL名,INDID)テーブルが使用しているページIDの確認をする
DBCC PAGE(DB名,FileID,PageID,出力オプション)ページに格納されているデータの内容を出力する


 

メモリープール

サーバパラメータ max server memory で指定した値を利用しOSメモリが逼迫しない限り解放されない。
メモリープールは以下の種類がある。

  • System Data Structures
  • Lock
  • Procedure Cache
  • Buffer Cache
  • Log Cache
  • Users Connection Context

パフォーマンスカウンタで、現在のメモリ使用量が確認できる

Memory Maanger説明
Target Server Memory (KB)max server memoryの値
Total Server Memory (KB)SQL Serverがコミットしているメモリ合計容量

SQL Server: Memory Manager オブジェクト

Buffer Cache

データベース上のデータページとインデックスページのデータが入る。
検索結果が同じ結果の場合I/Oを発生させず、メモリ内にあるキャッシュからデータを返すことでパフォーマンスを向上。

Procedure Cache

種類カラム名
オブジェクトプランストアドプロシージャや関数、トリガーといった、再利用可能なクエリをキャッシュ
SQLプランクエリの実行プランでアドホッククエリ(使い捨て)や「パラメータ化クエリ」の実行プランを蓄積。
パラメータ化クエリとは、クエリの中から変動する数値の部分を変数のようにして、コンパイルしたクエリを指す。
※実行するたびに、ユーザーが指定した数値を当てはめて実行するが再コンパイルは必要ない


メモリ容量の搭載要領によってサイズが変動する。

搭載メモリ(GB)確保容量
0〜40〜4GB領域の75%
4〜644〜64GB領域の10%
64以上64GB以上の領域の5%

※96GB搭載している場合は、(4 x 0.75) + (60 x 0.1) + (32 x 0.05) = 3 + 6 + 1.6 = 10.6GB

パフォーマンスモニターで確認すべきカウンター

オブジェクトカウンタ値説明
SQL Server:Buffer ManagerBuffer cache hit ratioキャッシュヒット率
SQL Server: Memory ManagerTarget Server Memory (KB)max server memoryの値
Total Server Memory (KB)SQLServerが使用しているメモリの使用量(tasklist等で確認できる容量より多い筈)

※ 64bit OS に 32bit SQLServerをインストールしている場合は、32bit の perfmon を実行しないとカウンタが表示されない(ファイル名指定して実行だと64bit版のperfmonになる)

 

オブジェクトの変更

sp_rename '変更前','変更後','タイプ'
タイプ説明
COLUMN名前を変更する列
DATABASEユーザー定義のデータベース
INDEXユーザー定義のインデックス
OBJECTsys.objects に記録される型の項目
CREATETYPE または sp_addtype を実行すると追加される別名データ型または CLR ユーザー定義型

SQL文による列データの変更操作

説明
追加ALTER TABLE テーブル名 ADD 列名 列定義
削除ALTER TABLE テーブル名 DROP COLUMN 列名
変更ALTER TABLE テーブル名 ALTER COLUMN 列名 列定義


 

sqlcmdによるSQLインスタンスへの接続

sqlcmd -S インスタンス名

インスタンス名は[コンピュータ名\インスタンス]

オプション説明
-UログインIDを指定(省略した場合、Windows認証になる)
-Pパスワードを指定(省略した場合、Windows認証になる)
-S接続先のインスタンスを指定(省略時は既定のインスタンス)
-Hホスト名(IPアドレス)を指定
-d接続先のデータベースを指定(省略した場合はmaster)
-i入力ファイル名を指定。SQLファイルを実行する場合などに使用


 

ストアドプロシージャ

圧倒的、時間を見つけて作成せざるを得ない。
判定分や、四則演算や、何か色々。

all_objectsは使用頻度高いので、ストアドプロシージャにした方が良い。

CREATE PROCEDURE [dbo].[t] @objname varchar(50)
AS
select name from sys.all_objects
where name like @objname


 

調査用SQL

  • LOCK状態確認(2000のビュー使ってるので将来的に使えなくなる)

     

メモ

インデックス INDEX Seek と INDEX Scanの違い。

Seekは、特定の部分検索。Scanは全参照。
複合インデックスで、一番左に指定したカラム以外で絞るとINDEXのScanになる。
クラスター化インデックスの場合、実データを持っているのでTABLEのフルスキャンと全く同じになる。

 

外部リンク


トップ   編集 凍結解除 差分 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2015-12-01 (火) 22:08:28 (687d)