SQL SERVER日常運維（一）

一、基礎命令

檢視當前資料庫的版本

SELECT @@VERSION;

檢視伺服器部分特殊資訊

select SERVERPROPERTY(N'edition') as Edition --資料版本，如企業版、開發版等
,SERVERPROPERTY(N'collation') as Collation --資料庫字符集
,SERVERPROPERTY(N'servername') as ServerName --服務名
,@@VERSION as Version --資料庫版本號
,@@LANGUAGE AS Language --資料庫使用的語言，如us_english等

獲取資料庫當前時間

SELECT GETDATE() AS CurrentDateTime;

檢視資料庫啟動的引數

sp_configure

檢視所有資料庫使用者登入資訊

sp_helplogins

檢視資料庫啟動時間（最近一次）

select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1;

檢視有多少個埠

SELECT * FROM sys.dm_tcp_listener_states;

檢視當前的連線數

SELECT COUNT(*) AS [Connection Count] FROM sys.dm_exec_connections;

檢視各個磁碟分割槽的剩餘空間

Exec master.dbo.xp_fixeddrives

檢視資料庫的磁碟使用情況

Exec sp_spaceused

檢視資料庫伺服器各資料庫日誌檔案的大小及利用率

DBCC SQLPERF(LOGSPACE)

檢視當前佔用 cpu 資源最高的會話和其中執行的語句

select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc;

檢視快取中重用次數少，佔用記憶體大的查詢語句（當前快取中未釋放的）

SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text]
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts,p.size_in_bytes desc;

看BUFFER POOL中，都快取了哪些表(當前資料庫)的資料

select OBJECT_NAME(object_id) 表名,COUNT(*) 頁數,COUNT(*)*8/1024.0 Mb
from sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c
where a.allocation_unit_id=b.allocation_unit_id
and b.container_id=c.hobt_id
and database_id=DB_ID()
group by OBJECT_NAME(object_id)
order by 2 desc;

檢視使用者的許可權

EXEC sp_helprotect;

檢視當前資料庫記憶體使用情況

select * from sys.dm_os_process_memory;

查詢當前資料庫快取的所有資料頁面，哪些資料表，快取的資料頁面數量

-- 查詢當前資料庫快取的所有資料頁面，哪些資料表，快取的資料頁面數量
-- 從這些資訊可以看出，系統經常要訪問的都是哪些表，有多大
select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*)
from sys.allocation_units a,
sys.dm_os_buffer_descriptors b,
sys.partitions p
where a.allocation_unit_id=b.allocation_unit_id
and a.container_id=p.hobt_id
and b.database_id=db_id()
group by p.object_id,p.index_id
order by buffer_pages desc;

查詢快取中具體的執行計劃，及對應的SQL

-- 查詢快取中具體的執行計劃，及對應的SQL
-- 將此結果按照資料表或SQL進行統計，可以作為基線，調整索引時考慮
-- 查詢結果會很大，注意將結果集輸出到表或檔案中
SELECT usecounts ,
refcounts ,
size_in_bytes ,
cacheobjtype ,
objtype ,
TEXT
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC ;

檢視具體某個使用者的許可權

SELECT p.class_desc, OBJECT_NAME(p.major_id) AS object_name, p.permission_name, p.state_desc, u.name AS user_name
FROM sys.database_permissions p
JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id
WHERE u.name = ‘test’;

檢視註冊時的例項名

SELECT * FROM sys.servers;

查詢使用者角色

select SrvRole = g.name, MemberName = u.name, MemberSID = u.sid
from sys.server_principals u, sys.server_principals g, sys.server_role_members m
where g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
order by 1, 2;

看伺服器角色

select 使用者名稱 = u.name,管理員許可權 = g.name,是否在用 = u.is_disabled,MemberSID = u.sid
from sys.server_principals u, sys.server_principals g, sys.server_role_members m
where g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
and g.name = 'sysadmin'
order by 1, 2;

查詢當前使用者所有使用者表

select name from sysobjects where xtype='u' order by name

檢視所有的資料庫

SELECT    Name FROM    Master..SysDatabases ORDER BY    Name;

檢視伺服器角色相關資訊

SP_HELPSRVROLE
SP_HELPSRVROLEMEMBER 伺服器角色
SP_HELPSRVROLE 伺服器角色

檢視資料庫角色相關資訊

SP_HELPROLE
SP_HELPROLEMEMBER 資料庫角色
SP_HELPROLE 資料庫角色

檢視使用者相關資訊

SP_HELPUSER
SP_HELPUSER 資料庫使用者名稱

檢視上次啟動以來嘗試的連線數

select @@connections //返回 SQL Server 自上次啟動以來嘗試的連線數，無論連線是成功還是失敗

當前例項允許同時進行的最大使用者連線數

select @@max_connections
--返回 SQL Server 例項允許同時進行的最大使用者連線數。返回的數值不一定是當前配置的數值

查詢當前最大的連線數

SELECT value_in_use
FROM sys.configurations c
WHERE c.name = 'user connections'; --0表示無限制

設定修改連線數

exec sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
exec sp_configure 'user connections', 300
RECONFIGURE WITH OVERRIDE

查詢當前會話超時時間

select @@lock_timeout --返回當前會話的當前鎖定超時設定（毫秒）。

查詢每個使用者的連線數

select loginame,count(1) as Nums
from sys.sysprocesses
group by loginame
order by 2 desc
select spid,ecid,status,loginame,hostname,cmd,request_id
from sys.sysprocesses where loginame='' and hostname='';

檢視當前活動的例項

SELECT CURRENT_USER AS [Current User], SESSION_USER AS [Session User];

檢視當前活動程序

SELECT * FROM sys.dm_exec_requests;

檢視所有資料庫的大小

SELECT
DB_NAME(database_id) AS DatabaseName,
SUM(size/128.0) AS SizeInMB,
SUM(size/128.0)/1024 AS SizeInGB
FROM
sys.master_files
GROUP BY
database_id
ORDER BY
SizeInMB DESC;

檢視某個資料庫的大小

SELECT sys.databases.name AS [Database Name],
CAST(SUM(size * 8 / 1024.0) AS DECIMAL(10,2)) AS [Size (MB)]
FROM sys.master_files
INNER JOIN sys.databases ON sys.master_files.database_id = sys.databases.database_id
WHERE sys.databases.name = 'master'
GROUP BY sys.databases.name;
--也可以用EXEC sp_spaceused @updateusage = N'TRUE';

檢視當前資料庫的日誌大小

SELECT sys.databases.name AS [Database Name],
CAST(size * 8 / 1024.0 AS DECIMAL(10,2)) AS [Log File Size (MB)]
FROM sys.master_files
INNER JOIN sys.databases ON sys.master_files.database_id = sys.databases.database_id
WHERE sys.databases.name = 'master'
AND sys.master_files.type = 1;

查詢當前資料庫的表和檢視

SELECT TABLE_NAME AS [Table/View Name], TABLE_TYPE AS [Type]
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE IN ('BASE TABLE', 'VIEW');

查詢表結構資訊

sp_help 'test';

二、運維小技巧

一次性清除資料庫所有表的資料（高危操作，謹慎）

CREATE PROCEDURE sp_DeleteAllData
AS
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'
GO;

資料備份與恢復

備份

BACKUP DATABASE test TO DISK = 'C:\backup\MyDatabase.bak';

恢復

RESTORE DATABASE MyDatabase FROM DISK = 'C:\backup\MyDatabase.bak';

完整資料庫備份

BACKUP DATABASE test
TO DISK = 'C:\Backup\MyDatabase.bak'
WITH FORMAT, MEDIANAME = 'MyDatabase_Full', NAME = 'Full Backup';

差異備份

BACKUP DATABASE test
TO DISK = 'C:\Backup\MyDatabase_diff.bak'
WITH DIFFERENTIAL, FORMAT, MEDIANAME = 'MyDatabase_Diff', NAME = 'Differential Backup';

事務日誌備份

BACKUP LOG test
TO DISK = 'C:\Backup\MyDatabase_log.trn'
WITH NOFORMAT, NOINIT, NAME = N'MyDatabase_LogBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

還原資料庫

RESTORE DATABASE test
FROM DISK = 'C:\Backup\MyDatabase.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5;

建立賬戶及資料庫使用者

--建立賬戶
CREATE LOGIN test WITH PASSWORD = '123123';
--建立資料庫使用者並對映到登入名
CREATE USER test FOR LOGIN test;
-- 給予讀許可權
ALTER ROLE db_datareader ADD MEMBER test; 
-- 給予寫許可權
ALTER ROLE db_datawriter ADD MEMBER test;

SQL最佳化相關、執行時間

SELECT creation_time N'語句編譯時間'
,last_execution_time N'上次執行時間'
,total_physical_reads N'物理讀取總次數'
,total_logical_reads/execution_count N'每次邏輯讀次數'
,total_logical_reads N'邏輯讀取總次數'
,total_logical_writes N'邏輯寫入總次數'
,execution_count N'執行次數'
,total_worker_time/1000 N'所用的CPU總時間ms'
,total_elapsed_time/1000 N'總花費時間ms'
,(total_elapsed_time / execution_count)/1000 N'平均時間ms'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'執行語句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) NOT LIKE '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;

檢視job執行持續時間

SELECT
[T1].[job_id]
,[T1].[name] AS [job_name]
,[T2].[run_status]
,[T2].[run_date]
,[T2].[run_time]
,[dbo].[agent_datetime]([T2].[run_date], [T2].[run_time]) AS [run_datetime]
,[T2].[run_duration]
,DATEDIFF(SECOND, '1900-01-01', DATEADD(SECOND, 31, [dbo].[agent_datetime](19000101, [run_duration]))) AS [run_duration_s]
FROM
[dbo].[sysjobs] AS T1
INNER JOIN [dbo].[sysjobhistory] AS T2
ON [T2].[job_id] = [T1].[job_id]
WHERE
[T1].[enabled] = 1
AND [T2].[step_id] = 0
AND [T2].[run_duration] >= 1
and [T1].[name]='PIMS_CreatePaperCraftParameterAnalysisData'
ORDER BY
[T2].[job_id] ASC
,[T2].[run_date] ASC;

從所有快取中釋放所有未使用的快取條目

DBCC FREESYSTEMCACHE('ALL');

查詢、解除死鎖

----查詢表死鎖資訊
select object_name(resource_associated_entity_id) as tableName, request_session_id as pid from sys.dm_tran_locks
where resource_type = 'OBJECT'
dbcc opentran;
--將上方查詢到的死鎖PID填入，檢視死鎖的詳細資訊、執行的sql語句
exec sp_who2 53 
DBCC inputbuffer (53);
--輸入鎖死程序PID解除死鎖
kill 53;

查詢SQL Server根據CPU消耗列出前5個最差效能的查詢

SELECT TOP 5
st.text,
qp.query_plan,
qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC;

查詢資料庫中各資料表大小

-- =============================================

-- 描 述：更新查詢資料庫中各表的大小，結果儲存到資料表中

-- =============================================
 
--查詢是否存在結果儲存表
IF NOT EXISTS (SELECT * FROM sysobjects where id = OBJECT_ID(N'temp_tableSpaceInfo') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
--不存在則建立
CREATE TABLE temp_tableSpaceInfo
(name NVARCHAR(128),
rows char(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18))
END
--清空資料表
DELETE FROM temp_tableSpaceInfo

--定義臨時變數在遍歷時儲存表名稱
DECLARE @tablename VARCHAR(255)
--使用遊標讀取資料庫內所有表表名
DECLARE table_list_cursor CURSOR FOR
SELECT name FROM sysobjects
WHERE OBJECTPROPERTY(id, N'IsTable') = 1 AND name NOT LIKE N'#%%' ORDER BY name
--開啟遊標
OPEN table_list_cursor
--讀取第一條資料
FETCH NEXT FROM table_list_cursor INTO @tablename
--遍歷查詢到的表名
WHILE @@FETCH_STATUS = 0
BEGIN
--檢查當前表是否為使用者表
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
--當前表則讀取其資訊插入到表格中
EXECUTE sp_executesql N'INSERT INTO temp_tableSpaceInfo EXEC sp_spaceused @tbname', N'@tbname varchar(255)', @tbname = @tablename
END
--讀取下一條資料
FETCH NEXT FROM table_list_cursor INTO @tablename
END 
--釋放遊標
CLOSE table_list_cursor
DEALLOCATE table_list_cursor
SELECT *,replace(reserved,'KB','')/1024 資料表大小M FROM temp_tableSpaceInfo order by replace(reserved,'KB','')/1024 desc
drop table temp_tableSpaceInfo;

顯示如何依據I/O消耗，來找出效能最差的查詢

-- 顯示如何依據I/O消耗,來找出效能最差的查詢
SELECT TOP 5
    st.text,
    qp.query_plan,
    qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_logical_reads DESC;

壓縮資料庫、檔案、日誌

DBCC ShrinkFile(‘資料庫名’, targetsize); /* 收縮資料庫檔案 */
DBCC ShrinkFile(‘資料庫名_log’, targetsize); /* 收縮日誌檔案 */
Targetsize：單位為兆，必須為整數，DBCC SHRINKFILE 嘗試將檔案收縮到指定大小。
DBCC SHRINKFILE 不會將檔案收縮到小於“實際使用的空間”大小，例如“分配空間”為10M，“實際使用空間”為6M，當制定targetsize為1時，則將該檔案收縮到6M，不會將檔案收縮到1M。
--收縮資料庫
DBCC SHRINKDATABASE(資料庫名，百分比)
百分比：即“收縮後檔案中的最大可用空間”，取值範圍“大於等於0， 小於100%”，實際使用中設為0即可。

查詢資料庫表欄位各項屬性資訊，便於直接複製匯出excel表

SELECT
表名 = Case When A.colorder=1 Then D.name Else '' End,
表說明 = Case When A.colorder=1 Then isnull(F.value,'') Else '' End,
欄位序號 = A.colorder,
欄位名 = A.name,
欄位說明 = isnull(G.[value],''),
標識 = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,
主鍵 = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end,
型別 = B.name,
佔用位元組數 = A.Length,
長度 = COLUMNPROPERTY(A.id,A.name,'PRECISION'),
小數位數 = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),
允許空 = Case When A.isnullable=1 Then '√'Else '' End,
預設值 = isnull(E.Text,'')
FROM
syscolumns A
Left Join
systypes B
On
A.xusertype=B.xusertype
Inner Join
sysobjects D
On
A.id=D.id and D.xtype='U' and D.name<>'dtproperties'
Left Join
syscomments E
on
A.cdefault=E.id
Left Join
sys.extended_properties G
on
A.id=G.major_id and A.colid=G.minor_id
Left Join
sys.extended_properties F
On
D.id=F.major_id and F.minor_id=0
--where d.name='OrderInfo' --如果只查詢指定表,加上此條件
Order By
A.id,A.colorder;

資料庫快取清理

CREATE PROCEDURE [dbo].ClearMemory
AS
BEGIN
--清除所有快取
DBCC DROPCLEANBUFFERS
--開啟高階配置
exec sp_configure 'show advanced options', 1
--設定最大記憶體值，清除現有快取空間
exec sp_configure 'max server memory', 25600
EXEC ('RECONFIGURE')
--設定等待時間
WAITFOR DELAY '00:00:01'
--重新設定最大記憶體值
EXEC sp_configure 'max server memory',40960
EXEC ('RECONFIGURE')
--關閉高階配置
exec sp_configure 'show advanced options',0
END

三、日常運維操作

資料庫使用者、許可權操作

USE [master]
GO
--待確認賬號密碼
CREATE LOGIN [NDIT] WITH PASSWORD=N'1', DEFAULT_DATABASE=[PIMS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE PIMS
go
CREATE USER [NDIT] FOR LOGIN [NDIT]
GO
--大許可權， 如果是指定的部分表，不執行這個，如果是所有內容都可以讀，用此指令碼
--EXEC sp_addrolemember N'db_datareader', N'NDIT'
--GO
--指定特定表名賦予新增/更新/查詢
DECLARE @Sql NVARCHAR(max)
SET @Sql=''
--table
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.tables AS a WHERE name IN ('Tab1','Tab2');
--view
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.views AS a WHERE name IN ('view1','view2');
--procedure
--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.procedures AS a WHERE name IN ('proc1','proc2');
PRINT @Sql 
EXEC(@Sql)
go 
--禁用登陸帳戶
alter login NDIT disable
--啟用登陸帳戶
alter login NDIT enable 
--登陸帳戶改名
alter login NDIT with name=dba_tom
--登陸帳戶改密碼：
alter login NDIT with password='aabb@ccdd' 
--資料庫使用者改名：
alter user NDIT with name=dba_tom 
--更改資料庫使用者 defult_schema：
alter user NDIT with default_schema=sales
--刪除資料庫使用者：
drop user NDIT
--刪除 SQL Server登陸帳戶：
drop login NDIT

該文章轉自微信公眾號：小周的資料庫進階之路