SQLServer備忘2

●このエラーは、Visual Studio デバッガーがユーザーをデバッグ用データベース サーバーに登録できないときに発生します。 最も可能性の高い原因は、SQL Server のインスタンスに関して必要なアクセス許可がないことです。 sysadmin 固定サーバー ロールを持つログインを使用して Transact-SQL エディターをサーバーに接続するだけでは不十分です。Visual Studio ユーザーの Windows ログインも、SQL Server のそのインスタンス上で sysadmin 固定サーバー ロールのメンバーである必要があります。
https://msdn.microsoft.com/ja-jp/library/vstudio/ms241738(v=vs.100).aspx

●Transact-SQL のデバッグのセットアップ エラーとトラブルシューティング

●シノニムの生成

create table Owner.b (a int)
create synonym b for Owner.b;
drop synonym b

select * from b;

select * from b
select * from Owner.b

insert into b (a) values (1);

PUBLIC SYNONYMは、権限付与で実現？

●DB内の全テーブルの件数一覧

select distinct a.name, b.row_count
from sys.tables a
left join sys.dm_db_partition_stats b
on a.object_id = b.object_id
order by 2 desc

●ROWNUMのような値を取得するには。

SELECT column1 FROM
(SELECT ROW_NUMBER() OVER (ORDER BY id) AS rownum,*
FROM logsize) AS t

●ログの正味量を見る

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

●ログの圧縮 (10MBを目標にしたログ圧縮)

DBCC SHRINKFILE(hogehoge_DB_Log,10);

●権限指定

USE <DB名>
GO
GRANT SELECT ON OBJECT::[DB名].[スキーマ名].[テーブル名] TO <ユーザ名>;
GO"

●ストアドの再作成

名前解決時に同じ名前のオブジェクトが存在していれば再作成不要

●SSMSでプロシジャデバッグする設定

（サーバー側）
・TCP ポート 135 を許可
・プログラム sqlservr.exe を許可
※IPSec 経由でネットワーク通信を行う場合、UDP ポート 4500 と UDP ポート 500 も許可する。

（クライアント側）
・TCP ポート 135 を許可
・プログラム ssms.exe を許可に追加

・FWありローカルDBで試行時には、ポートが開いていないと管理者アカウントでないとエラー。

SSMSを管理者として実行で解消。

・SQL Server ユーザにsysadmin 権限が必要。
・更にクライアントのWindowsログインユーザーにもsysadmin権限必要。

●主キーのあるテーブル、ないテーブル、全テーブル数の表示

select count(*) from sys.tables
where object_id IN (select parent_object_id from sys.objects
where type_desc like 'PRIMARY_KEY_CONSTRAINT')
UNION ALL
select count(*) from sys.tables
where object_id NOT IN (select parent_object_id from sys.objects
where type_desc like 'PRIMARY_KEY_CONSTRAINT')
UNION ALL
select count(*) from sys.tables

●パブリケーションの設定情報

USE <DB名>
EXEC sp_helppublication @publication = 'パブリケーション名'
GO

●アーティクルの設定情報

USE [AdventureWorks2008R2]
EXEC sp_helparticle
@publication = @publication;
GO

●SQLCMDで処理結果件数表示を出力しない

以下を発行する。
set nocount on

●セッションKILL

select * from sys.dm_exec_sessions ⇒セッション情報
select * from sys.dm_exec_requests

sp_who ⇒ これだと利用しているDB名が表示される。

コマンドプロンプトで、
kill プロセスID(セッションID)

--SSMSだけでkillするには--------------------------------------
sp_who

Execute('Kill ' + '78')
--'78'はspidを表す。
--------------------------------------------------------------

●SQL Server Profilerを利用するには、以下を発行する。

use master
grant alter trace to ユーザ名;

●リンクサーバ

use master
exec master.dbo.sp_addlinkedserver
@server = N'SERVER\INSTANCE',
@srvproduct = N'SQL Server'
go
exec master.dbo.sp_addlinkedsrvlogin 'DB',false,NULL,'sa','Password'

exec master.dbo.sp_droplinkedsrvlogin 'DB','SERVER\Administrator'
exec master.dbo.sp_dropserver
@server=N'DB'

select * from [DB].master.sys.sysservers;
select * from [DB].master.sys.sysdatabases;
select * from master.sys.sysservers;
select * from master.sys.sysdatabases;

◎以下で設定

use master
exec master.dbo.sp_addlinkedserver
@server = N'SERVER\INSTANCE',
@srvproduct = N'SQL Server'
go
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname ='SERVER\INSTANCE',
@useself='FALSE',@locallogin =NULL,@rmtuser = 'OwnerUser',@rmtpassword = 'OwnerUser'

◎以下で確認

select * from [DB].master.sys.sysservers;
select * from [DB].master.sys.sysdatabases;
select * from master.sys.sysservers;
select * from master.sys.sysdatabases;

◎DROP時に確認する内容

sp_helpremotelogin
sp_helpserver

●対象DBのみオフラインにし、データファイルを移動した後、オンラインにする。

-- 以下で事前確認

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'OwnerMasterDB');

use master
alter database OwnerMasterDB set offline;

ALTER DATABASE OwnerMasterDB MODIFY FILE ( NAME = OwnerMasterDB_Data, FILENAME = 'D:\Database\UserDB\OwnerMasterDB.Mdf' );

ALTER DATABASE OwnerMasterDB MODIFY FILE ( NAME = OwnerMasterDB_Log, FILENAME = 'D:\Database\UserDB\OwnerMasterDB.Ldf' );

use master
alter database OwnerMasterDB set online;

●サーバ名の変更。インスタンス名の変更はできないと思った方がよい。

SELECT @@SERVERNAME AS 'Server Name';
select * from sys.servers;

sp_dropserver 'WIN-MI2D9GIA9E9';
GO
sp_addserver 'SQLServer', local;
GO

SELECT @@SERVERNAME AS 'Server Name';
select * from sys.servers;

●レプリケーションで生成した際に裏で自動生成されるリンクサーバの データアクセスのプロパティをTRUEにすると、リンクサーバとして分散クエリが利用できる。

●デフォルト値があるカラムの変更

ALTER TABLE テーブル名 DROP CONSTRAINT DF_xxx_xxx ; --制約削除
ALTER TABLE テーブル名 ALTER COLUMN [フィールド名]INT NOT NULL ; --カラム変更
ALTER TABLE テーブル名 ADD DEFAULT 0 FOR [フィールド名] ; --デフォルト値設定

●カラムの追加、変更、削除例

カラムの追加：

ALTER TABLE <テーブル名> ADD <カラム名> <データ型> <NULL/NOT NULL> <DEFAULT> …;
例) ALTER TABLE Owner.MASTER ADD TEST_COLUMN NVARCHAR(10) NOT NULL DEFAULT 0;

カラムの変更：

ALTER TABLE <テーブル名> DROP CONSTRAINT <制約名：この場合はDEFAULT>;
ALTER TABLE <テーブル名> ALTER COLUMN <カラム名> <データ型> <NULL/NOT NULL> ;
ALTER TABLE <テーブル名> ADD DEFAULT <デフォルト値> FOR <カラム名>;
例) ALTER TABLE Owner.MASTER DROP CONSTRAINT DF__WORK___TEST___25A5F845;
ALTER TABLE Owner.MASTER ALTER COLUMN TEST_COLUMN CHAR(10) ;
ALTER TABLE Owner.MASTER ADD DEFAULT 0 FOR TEST_COLUMN;

カラムの削除： 動作に支障がなければ、利用しない方向で調整することを推奨。

ALTER TABLE <テーブル名> DROP CONSTRAINT <制約名：この場合はDEFAULT>;
ALTER TABLE <テーブル名> DROP COLUMN <カラム名> ;
例) ALTER TABLE Owner.MASTER DROP CONSTRAINT DF__WORK___TEST___2B5ED19B;
ALTER TABLE Owner.MASTER DROP COLUMN TEST_COLUMN;

●同じパスワード／SID のログインアカウントの作成： sp_help_revlogin
sp_change_users_loginによるログインアカウントの再マッピングは、データベース ユーザーの数が多い場合には、大変な作業になります。そこで、同じパスワード／SIDのログインアカウントを一括で作成できる方法があります。これは、sp_help_revlogin という名前のストアドプロシージャで、マイクロソフトのサポート技術情報（KB：Knowledge Base）の文書番号 918992 で提供されています。
KB918992：ログインとパスワードを SQL Server 2005 のインスタンスの間、転送する方法 この文書は、SQL Server 2005 用ですが、SQL Server 2008 にもそのまま適用可能です。この文書で提供されるスクリプトを丸ごとコピーして、Management Studioのクエリ エディタに貼り付けて実行すれば、sp_help_revlogin ストアドプロシージャを作成することができます。 作成後、次のように実行すれば、同一のパスワード／SIDのログインアカウントを作成するためのスクリプトが生成されます。
USE master EXEC sp_help_revlogin

●主キー変更例

alter table dbo.ACCOUNTING drop constraint ACCOUNTING_PKC;
alter table dbo.ACCOUNTING alter column YEAR char(4) NOT NULL;
alter table dbo.ACCOUNTING add constraint ACCOUNTING_PKC
PRIMARY KEY CLUSTERED (CD,SLIP_NO,SLIP_LINE_NO,YEAR);

● オブジェクトの統計情報確認。_WAは列ごとの統計

SELECT OBJECT_NAME(s.object_id) AS object_name,
COL_NAME(sc.object_id, sc.column_id) AS column_name,
s.name AS statistics_name
FROM sys.stats AS s Join sys.stats_columns AS sc
ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;

● 統計の自動更新の設定確認

SELECT name AS "Name",
is_auto_create_stats_on AS "Auto Create Stats",
is_auto_update_stats_on AS "Auto Update Stats",
is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;

● 統計の非同期自動更新の設定確認

SELECT name AS "Name",
is_auto_update_stats_async_on AS "Asynchronous Update"
FROM sys.databases;

●オブジェクトごとの統計情報取得

DBCC SHOW_STATISTICS

●Current Worker と Acitive Worker

Current Worker の値から、各スケジューラーに割り当てられている、ワーカースレッド数を確認した。
ワーカースレッドの状態はいくつかあるのだが、今回は Active Worker についてみていく。

Current Worker はスケジューラーに割り当てられているワーカースレッドの数になるのだが、これは割り当てられている状態を示しており、実際に使われているワーカースレッドということではない。

SELECT
(SELECT SUM(current_workers_count) FROM sys.dm_os_schedulers) AS [CurrentWorker]
, (SELECT SUM(active_workers_count) FROM sys.dm_os_schedulers) AS [Active Worker]
, (SELECT COUNT(*) FROM sys.dm_os_threads) AS [OS Thread (Include Non SQL Server)]
, (SELECT COUNT(*) FROM sys.dm_os_threads
WHERE started_by_sqlservr = 1 AND scheduler_address IS NOT NULL)
AS [OS Thread (Exclude Non SQL Server)]

●データコレクションはEE？でないと使えない。⇒ウソ。SEでも使える

●オブジェクト権限を確認する

USE データベース名
SELECT
OBJECT_NAME(major_id) AS オブジェクト名
,USER_NAME(grantee_principal_id) AS ユーザー名
,permission_name, state_desc
FROM sys.database_permissions
WHERE major_id = OBJECT_ID('オブジェクト名')

●2008ではALTER ROLE、ALTER SERVER ROLEでメンバーを追加できない。

sp_addrolemember、sp_addsrvrolemember を利用する。

●システム定義のメッセージを確認

select * from sys.messages

●データコレクタ系ログ

syscollector_collection_items
syscollector_collection_sets
syscollector_collector_types
syscollector_config_store
syscollector_execution_log
syscollector_execution_log_full
syscollector_execution_stats

●データファイルとログファイルの容量

SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files ;
GO

●テーブル毎の容量

-- 領域情報の更新
EXEC sp_spaceused @updateusage = N'TRUE';
GO
--
DECLARE @table sysname
DECLARE @T1 Table (
name sysname
,rows bigint
,reserved varchar(10)
,data varchar(10)
,index_size varchar(10)
,unused varchar(10))

--カーソルの定義
DECLARE C CURSOR LOCAL FOR select s.name+'.'+t.name
from sys.schemas s,sys.tables t
where s.schema_id = t.schema_id
OPEN C
FETCH NEXT FROM C INTO @table --１件読む
WHILE (@@FETCH_STATUS=0)
BEGIN
--テーブル情報のストアド結果を一時テーブルに放り込む
INSERT INTO @T1 EXEC sp_spaceused @table
FETCH NEXT FROM C INTO @table --１件読む
END
CLOSE C
DEALLOCATE C

--結果を表示する
SELECT * FROM @T1
ORDER BY name

●断片化状況を確認する。

SELECT name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'DBname'),
NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO

●アローケーションユニットごとの情報

select * from sys.allocation_units

●Windows認証で別ユーザでログインする場合
コマンドプロンプトを別のユーザで実行した上で以下を発行

sqlcmd -S SERVER -E DOMAIN\user

●SQL Serverユーザでログインする場合

sqlcmd -S SERVER -U User -P Password

sys.dm_exec_sessions v$session?
sys.dm_exec_requests
sys.dm_exec_connections
sys.dm_exec_query_stats
sys.dm_exec_procedure_stats

sys.dm_db_file_space_usage ファイルに関する使用領域(tempDBのみ)
sys.dm_db_session_space_usage セッション毎のページ割り当て(tempDBのみ)
sys.dm_db_partition_stats データベースのパーティションごとに、ページ数と行数の情報
sys.dm_io_pending_io_requests SQL Server で保留中の I/O 要求ごとに 1 行のデータを返します。
sys.dm_io_virtual_file_stats(NULL,NULL) データとログ ファイルの I/O 統計を返します。

●平均 CPU 時間に基づく上位 5 つのクエリに関する情報を取得する

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
*1 * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;

●A. サーバーに接続しているユーザーを検索する
次の例では、サーバーに接続しているユーザーを検索し、各ユーザーのセッション数を返します。

SELECT login_name ,COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;

●B. 長時間実行されているカーソルを検索する
次の例では、特定の期間よりも長く開いているカーソル、カーソルの作成者、およびカーソルが配置されているセッションを検索。

USE master;
GO
SELECT creation_time ,cursor_id
,name ,c.session_id ,login_name
FROM sys.dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
WHERE DATEDIFF(mi, c.creation_time, GETDATE()) > 5;

●C. トランザクションが開いているアイドル状態のセッションを検索する
次の例では、トランザクションが開いているアイドル状態のセッションを検索します。アイドル状態のセッションとは、現在要求が実行されていないセッションです。

SELECT s.*
FROM sys.dm_exec_sessions AS s
WHERE EXISTS
(
SELECT *
FROM sys.dm_tran_session_transactions AS t
WHERE t.session_id = s.session_id
)
AND NOT EXISTS
(
SELECT *
FROM sys.dm_exec_requests AS r
WHERE r.session_id = s.session_id
);

*1:CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;