Sql server常用SQL语句01

背景

sql server数据库运行信息查看(有些语句是很有趣但不常用的)

查看当前用户

select system_user 

报告有关锁的信息，会显示实例里面的所有数据库的锁信息、堵塞信息

sp_lock

查看某个存储过程的内容

sp_helptext pro_name

假设查询到68被锁给堵塞了，查询被堵塞的SQL语句

#配合exec sp_who2
DBCC INPUTBUFFER (68)

查看某个数据库中是否存在活动事务，有活动事务就一定会写日志

DBCC OPENTRAN (dbname)

监视日志空间

DBCC SQLPERF (LOGSPACE)

#sqlserver查看实例级别的信息，使用SERVERPROPERTY函数
select SERVERPROPERTY ('IsClustered')
在故障转移群集中配置服务器实例。
1 = 群集。
0 = 非群集。
NULL = 输入无效或错误。
基本数据类型：int

select SERVERPROPERTY ('MachineName')
运行服务器实例的 Windows 计算机名称。
对于群集实例，即在 Microsoft 群集服务的虚拟服务器上运行的 SQL Server 实例，返回虚拟服务器的名称。
NULL = 输入无效或错误。
基本数据类型：nvarchar(128)

select SERVERPROPERTY ('ProcessID')
SQL Server 服务的进程 ID。ProcessID 对于标识属于该实例的 Sqlservr.exe 很有用。
NULL = 输入无效或出现错误。
基本数据类型：int

修复msdb数据库，比如ssms页面sql server agent丢失或看不了job view history等功能，说明msdb坏了，
需要修复(修复有风险)
dbcc checkdb (msdb);

查看数据库各种设置

select name,State,user_access,is_read_only,recovery_model from sys.databases

查看某个数据库中是否存在会话

select DB_NAME(dbid),* from sys.sysprocesses where dbid=db_id('dbname')

sys.dm_exec_requests返回SQL Server 中正在执行的每个请求的信息

SELECT session_id ,status ,blocking_session_id
,wait_type ,wait_time ,wait_resource
,transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended';

#配合waitfor delay测试
select * from MobileMessages waitfor delay '00:00:40'

查看哪些表被锁了，以及这些表被哪个进程锁了

#配合以下语句使用(tablockx)
窗口一：
begin tran
select * from tableName (tablockx) where id=1 ;
#commit
窗口二：
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT' ORDER BY request_session_id ASC

SELECT * FROM tableName
sp_lock

查询某个job是否被堵塞

select * from msdb.dbo.sysjobs where name='jobname'
select a.program_name,a.* from master..sysprocesses a where a.program_name 
like '%0D1CE57E8AC5%'
--把第一个语句查询到的job_id代入第二个语句的program_name

检查SQL Agent是否开启

IF EXISTS (
SELECT TOP 1 1
FROM sys.sysprocesses
WHERE program_name = 'SQLAgent - Generic Refresher'
)
SELECT 'Running'
ELSE
SELECT 'Not Running'

查看活动线程执行的sql语句,并生成批量杀掉的语句

select 'KILL '+CAST(a.spid AS NVARCHAR(100)) AS KillCmd,
REPLACE(hostname,' ','') as hostname ,replace(program_name,' ','') as program_name,
REPLACE(loginame, ' ', '') AS loginame, db_name(a.dbid) AS DBname,
spid,blocked,waittime/1000 as waittime,
a.status,Replace(b.text,'''','''') as sqlmessage,cpu
from sys.sysprocesses as a with(nolock)
cross apply sys.dm_exec_sql_text(sql_handle) as b
where a.status<>'sleeping' AND a.spid<>@@SPID
#配合以下语句测试
select * from MobileMessages waitfor delay '00:00:40' #status :suspended                                     
dbcc inputbuffer(52)

查看备份进度

SELECT DB_NAME(database_id) AS Exec_DB
,percent_complete
,CASE WHEN estimated_completion_time < 36000000
THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
,b.text as tsql
,*
FROM SYS.DM_EXEC_REQUESTS
cross apply sys.dm_exec_sql_text(sql_handle) as b
WHERE command LIKE 'Backup%' --and database_id=db_id('cardorder')
--OR command LIKE 'RESTORE%'
ORDER BY 2 DESC

查看恢复进度

SELECT DB_NAME(database_id) AS Exec_DB
,percent_complete
,CASE WHEN estimated_completion_time < 36000000
THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]
,b.text as tsql
,*
FROM SYS.DM_EXEC_REQUESTS
cross apply sys.dm_exec_sql_text(sql_handle) as b
WHERE command LIKE 'RESTORE%' --and database_id=db_id('cardorder')
--OR command LIKE 'RESTORE%'
ORDER BY 2 DESC

查看数据库的最近备份信息

SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date FROM 
msdb.dbo.backupset GROUP BY database_name,type ORDER BY database_name,type
备注：D 表示全备份，i 表示差异备份，L 表示日志备份

查看数据库的历史备份记录，并生成restore语句

SELECT
CONVERT(CHAR(100),SERVERPROPERTY('Servername'))AS Server,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.expiration_date,
CASE bs.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
bs.backup_size,
bmf.logical_device_name,
bmf.physical_device_name,
bs.name AS backupset_name,
bs.description,
'RESTORE DATABASE ['+bs.database_name+'] FROM DISK=N'''
+bmf.physical_device_name+ '''WITH NORECOVERY;'
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs
ON bmf.media_set_id=bs.media_set_id
WHERE bs.backup_start_date>DATEADD(DAY,-1,GETDATE()) #-1是最近一天，可自定义
ORDER BY bs.backup_finish_date
#最后一列为恢复restore语句,认真确认之后再执行(不清楚就图形界面操作)

查询XX库从YYYY-MM-DD日期开始的日志备份记录，并生成restore log的语句

SELECT TOP 1000 
      S.database_name [Database], 
      CASE [S].[type] 
            WHEN 'L' 
            THEN N'RESTORE LOG ' + QUOTENAME(S.database_name) 
            + N' FROM DISK = ''' + F.physical_device_name + N''' WITH NORECOVERY;' 
      END [LogRestore], 
      F.physical_device_name, 
      S.[Type], 
      S.backup_start_date, 
      S.backup_finish_date 
FROM msdb.dbo.backupmediafamily F 
INNER JOIN msdb.dbo.backupset S 
ON S.media_set_id = F.media_set_id 
WHERE S.database_name = 'XX' AND 
      S.type = 'L' AND S.backup_start_date > 'YYYY-MM-DD' ORDER BY 
      S.backup_start_date ASC  #注意替换XX库名和日期
#L表示日志备份，替换为D就是全量备份

** 查询always on状态是否正常**

select dc.database_name, d.synchronization_health_desc, d.synchronization_state_desc, 
d.database_state_desc from sys.dm_hadr_database_replica_states d join 
sys.availability_databases_cluster dc on d.group_database_id=dc.group_database_id 
and d.is_local=1

查看mirror镜像信息

SELECT
db_name(database_id),
mirroring_state_desc,
mirroring_role_desc,
mirroring_partner_name,
mirroring_partner_instance
FROM sys.database_mirroring

查看某个数据库的数据文件信息，就算是mirror从库的数据文件也可以查到，filestream目录也可以查到

SELECT db_name(database_id),* FROM master.sys.master_files 
WHERE database_id =DB_ID(N'TestDB');#TestDB为库名

** 查看某个数据文件信息**

select
b.name,a.type_desc,a.name,a.physical_name,a.size,a.max_size,a.is_percent_growth,
a.growth from sys.master_files a join sys.databases b on a.database_id=b.database_id 
and a.physical_name like '%D:\SQLData\%'
#%D:\SQLData\%为备份目录

** 查询实例上的每个数据库的大小**

SELECT
DB_NAME(db.database_id) DatabaseName,
(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files 
WHERE type = 0 GROUP BY database_id, type) mfrows ON 
mfrows.database_id = db.database_id
LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files 
WHERE type = 1 GROUP BY database_id, type) mflog ON 
mflog.database_id = db.database_id
LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files 
WHERE type = 2 GROUP BY database_id, type) mfstream ON 
mfstream.database_id = db.database_id
LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files 
WHERE type = 4 GROUP BY database_id, type) mftext ON 
mftext.database_id = db.database_id

查询总耗CPU最多的前3个SQL，且最近5天出现过

SELECT TOP 3
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1 and last_execution_time>dateadd(dd,-5,getdate())
ORDER BY total_worker_time DESC

#且最近5小时出现过
SELECT TOP 3
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)],
max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1 and last_execution_time>dateadd(hh,-5,getdate())
ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC

** 查看当前最耗资源的10个SQL及其spid**

SELECT TOP 10
session_id,request_id,start_time AS '开始时间',status AS '状态',
command AS '命令',d_sql.text AS 'sql语句', DB_NAME(database_id) AS '数据库名',
blocking_session_id AS '正在阻塞其他会话的会话ID',
wait_type AS '等待资源类型',wait_time AS '等待时间',wait_resource AS '等待的资源',
reads AS '物理读次数',writes AS '写次数',logical_reads AS '逻辑读次数',
row_count AS '返回结果行数'
FROM sys.dm_exec_requests AS d_request
CROSS APPLY
sys.dm_exec_sql_text(d_request.sql_handle) AS d_sql
WHERE session_id>50
ORDER BY cpu_time DESC
--前50号session_id一般是系统后台进程，sys.dm_exec_requests的status显示为background

查询某个存储过程被哪些job调用了

SELECT *
FROM msdb.dbo.sysjobs JOB WITH( NOLOCK)
INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK )
ON STP .job_id = JOB .job_id
WHERE STP .command LIKE N'%sp_name%'
--以上要查询某个job被哪个job调用了，把sp_name存储过程名字改成job_name作业名字即可

升级前，查询服务器名、实例名、版本号

select SERVERPROPERTY('machinename'),@@SERVERNAME,SERVERPROPERTY ('edition'),@@version

always on查看集群各节点的信息，包含节点成员的名称，类型，状态，拥有的投票仲裁数

SELECT * FROM  sys.dm_hadr_cluster_members;

查看WSFC(windows server故障转移群集)的信息，包含集群名称，仲裁类型，仲裁状态

SELECT * FROM SYS.dm_hadr_cluster;

查看集群各节点的子网信息，包含节点成员的名称，子网段，子网掩码

SELECT * FROM  sys.dm_hadr_cluster_networks;

查看侦听ip

select * from sys.availability_group_listeners;

查询所有job的状态是否running

SELECT sj.Name, 
    CASE 
        WHEN sja.start_execution_date IS NULL THEN 'Not running' 
        WHEN sja.start_execution_date IS NOT NULL AND 
        sja.stop_execution_date IS NULL THEN 'Running' 
        WHEN sja.start_execution_date IS NOT NULL AND 
        sja.stop_execution_date IS NOT NULL THEN 'Not running' 
    END AS 'RunStatus' 
FROM msdb.dbo.sysjobs sj 
JOIN msdb.dbo.sysjobactivity sja 
ON sj.job_id = sja.job_id 
WHERE session_id = ( 
    SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity) order by RunStatus desc;

锁表

TABLOCKX 
begin tran
SELECT * FROM tablename WITH (TABLOCKX)  
#查询过程中，其他会话无法查询、更新此表，直到查询过程结束

SELECT * FROM table WITH (HOLDLOCK)
#查询过程中，其他会话可以查询，但是无法更新此表，直到查询过程结束 

查询XX表的索引信息

SELECT a.name index_name,c.name table_name,d.name column_name 
FROM sysindexes a JOIN sysindexkeys b 
ON a.id=b.id AND a.indid=b.indid 
JOIN sysobjects c 
ON b.id=c.id 
JOIN syscolumns d 
ON b.id=d.id AND b.colid=d.colid 
WHERE a.indid NOT IN(0,255) AND c.name in ('MobileMessages')

生成sql语句的执行计划

SET SHOWPLAN_XML ON; 
GO 
select XXX   #语句
GO 
SET SHOWPLAN_XML OFF; 
GO

查询名称为XXX的job的最后一次运行成功的时间

SELECT TOP 1 CONVERT(DATETIME, RTRIM(run_date))+ ((run_time / 10000 * 3600) + 
((run_time % 10000) / 100 * 60) + (run_time % 10000) % 100) / (86399.9964) 
FROM msdb.dbo.sysjobhistory jobhis inner join msdb.dbo.sysjobs  jobs 
on jobhis.job_id = jobs.job_id AND jobhis.step_id = 0 AND jobhis.run_status = 1 
and jobs.name='XXX' 
ORDER BY 1 DESC

查询某张分区表的总行数和大小，比如表为crm.EmailLog

exec sp_spaceused 'crm.EmailLog';

查询某张分区表的信息，每个分区有多少行，比如表为crm.EmailLog

select convert(varchar(50), ps.name 
) as partition_scheme, 
p.partition_number, 
convert(varchar(10), ds2.name 
) as filegroup, 
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary, 
str(p.rows, 9) as rows 
from sys.indexes i 
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id 
join sys.destination_data_spaces dds 
on ps.data_space_id = dds.partition_scheme_id 
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id 
join sys.partitions p on dds.destination_id = p.partition_number 
and p.object_id = i.object_id and p.index_id = i.index_id 
join sys.partition_functions pf on ps.function_id = pf.function_id 
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id 
and v.boundary_id = p.partition_number - pf.boundary_value_on_right 
WHERE i.object_id = object_id('crm.EmailLog') 
and i.index_id in (0, 1) 
order by p.partition_number 

查询某张表里的索引的大小,如下示例表为dbo.table1

SELECT  i.name  AS IndexName,  SUM(page_count * 8) AS IndexSizeKB 
FROM sys.dm_db_index_physical_stats( 
    db_id(), object_id('dbo.table1'), NULL, NULL, 'DETAILED') AS s 
JOIN sys.indexes AS i 
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id 
GROUP BY i.name 
ORDER BY i.name

重建表上的所有索引

alter index all on table_name rebuild with (online=on)

重建表上的某个索引

#注意比较耗时的问题
alter index index_name on table_name rebuild with (online=on)

重新组织表上的所有索引

alter index all on table_name reorganize

重新组织表上的某个索引

alter index index_name on table_name reorganize

查看数据文件可收缩空间，结果见Availabesize_MB字段值

select name ,size*8/1024 as Totalsize_MB ,CAST(FILEPROPERTY(name,'SpaceUsed') 
AS int)*8/1024 as Usedsize_MB, 
size*8/1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)*8/1024 AS Availabesize_MB 
 from sys.master_files where database_id=db_id(N'DBNAME')

查询某个数据库下的表数据占用磁盘容量最大的10张表

select top 10 a.tablename,a.SCHEMANAME,sum(a.TotalSpaceMB) TotalSpaceMB,
sum(a.RowCounts) RowCounts 
from ( 
SELECT 
    t.NAME AS TableName, 
    s.Name AS SchemaName, 
    p.rows AS RowCounts, 
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) 
    AS TotalSpaceMB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) 
    AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) 
    AS NUMERIC(36, 2)) AS UnusedSpaceMB 
FROM 
    sys.tables t 
INNER JOIN       
    sys.indexes i ON t.OBJECT_ID = i.object_id 
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id 
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id 
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id 
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0 
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows) a 
GROUP BY  a.tablename,a.SCHEMANAME 
order by sum(a.TotalSpaceMB) desc 

根据id号查询某个数据库名

SELECT DB_NAME(5) 
SELECT OBJECT_NAME(1769220894)  #根据id号查询某个对象名 

原文链接link
本文说明，主要技术内容来自互联网技术大佬的分享，还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问，请留言，将确认之后，执行侵权必删​