可以通过一下关键字搜索相关内容：系统、性能、堵塞、表、数据库相关信息、事务锁、远程
# sqlserver 数据库
# 简介
sqlserver 数据库
# sqlserver 常用脚本
- sql server 开启 clr 权限：（系统）
exec sp_configure 'clr enabled', 1 | |
GO | |
RECONFIGURE | |
GO | |
ALTER DATABASE HWMESTC SET TRUSTWORTHY ON | |
ALTER AUTHORIZATION ON Database::HWMESTC TO sa; |
- 查询数据库大小（系统）
Exec sp_spaceused | |
select name, convert(float,size) * (8192.0/1024.0)/1024. from dbo.sysfiles |
- 数据库日志压缩（系统）
-- 选择需要使用的数据库 | |
USE PIMS | |
-- 将数据库模式设置为 SIMPLE | |
ALTER DATABASE PIMS SET RECOVERY SIMPLE | |
-- 将日志文件收缩到 1M | |
DBCC SHRINKFILE ('PIMS_log', 1) | |
-- 还原数据库 | |
ALTER DATABASE PIMS SET RECOVERY FULL |
- 查看数据库连接用户 (性能、堵塞)
SELECT top 10 | |
(total_elapsed_time / execution_count)/1000 N'平均时间ms' | |
,total_elapsed_time/1000 N'总花费时间ms' | |
,total_worker_time/1000 N'所用的CPU总时间ms' | |
,total_physical_reads N'物理读取总次数' | |
,total_logical_reads/execution_count N'每次逻辑读次数' | |
,total_logical_reads N'逻辑读取总次数' | |
,total_logical_writes N'逻辑写入总次数' | |
,execution_count N'执行次数' | |
,creation_time N'语句编译时间' | |
,last_execution_time N'上次执行时间' | |
,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'执行语句' | |
,qp.query_plan | |
FROM sys.dm_exec_query_stats AS qs | |
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st | |
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp | |
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; |
- 查看执行时间最长的 sql (性能、堵塞)
SELECT top 10 | |
(total_elapsed_time / execution_count)/1000 N'平均时间ms' | |
,total_elapsed_time/1000 N'总花费时间ms' | |
,total_worker_time/1000 N'所用的CPU总时间ms' | |
,total_physical_reads N'物理读取总次数' | |
,total_logical_reads/execution_count N'每次逻辑读次数' | |
,total_logical_reads N'逻辑读取总次数' | |
,total_logical_writes N'逻辑写入总次数' | |
,execution_count N'执行次数' | |
,creation_time N'语句编译时间' | |
,last_execution_time N'上次执行时间' | |
,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'执行语句' | |
,qp.query_plan | |
FROM sys.dm_exec_query_stats AS qs | |
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st | |
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp | |
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; |
- 查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）-- 全局（系统）
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 |
- 查询 SQLSERVER 内存使用情况（系统）
select * from sys.dm_os_process_memory |
- 查询 SqlServer 总体的内存使用情况（系统）
select type, | |
sum(virtual_memory_reserved_kb)*0.1*10/1024/1024 as vm_Reserved_gb,-- 保留的内存 | |
sum(virtual_memory_committed_kb)*0.1*10/1024/1024 as vm_Committed_gb,-- 提交的内存 | |
sum(awe_allocated_kb)*0.1*10/1024/1024 as awe_Allocated_gb,-- 开启 AWE 后使用的内存 | |
sum(shared_memory_reserved_kb)*0.1*10/1024/1024 as sm_Reserved_gb,-- 共享的保留内存 | |
sum(shared_memory_committed_kb)*0.1*10/1024/1024 as sm_Committed_gb-- 共享的提交内存 | |
from sys.dm_os_memory_clerks | |
group by type | |
order by type |
- 查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量 (性能、堵塞)
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 |
- 查询缓存的各类执行计划，及分别占了多少内存 （系统）
select cacheobjtype | |
, objtype | |
, sum(cast(size_in_bytes as bigint))/1024 as size_in_kb | |
, count(bucketid) as cache_count | |
from sys.dm_exec_cached_plans | |
group by cacheobjtype, objtype | |
order by cacheobjtype, objtype |
- 查询缓存中具体的执行计划，及对应的 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 ; |
- 查询 sql server 内存整体使用情况 (性能、堵塞)
SELECT object_name, cntr_value*0.1*10/1024/1024 ,cntr_value,cntr_type,t.counter_name,t.instance_name | |
FROM sys.dm_os_performance_counters t | |
WHERE counter_name = 'Total Server Memory (KB)'; |
- 一次性清除数据库所有表的数据（表、数据库相关信息）
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 |
- 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; |
- truncate 外键表存储过程（表、数据库相关信息）
USE PIMS | |
GO | |
CREATE PROCEDURE [dbo].[usp_Truncate_Table] | |
@TableToTruncate VARCHAR(64) | |
AS | |
BEGIN | |
SET NOCOUNT ON | |
--== 变量定义 | |
DECLARE @i int | |
DECLARE @Debug bit | |
DECLARE @Recycle bit | |
DECLARE @Verbose bit | |
DECLARE @TableName varchar(80) | |
DECLARE @ColumnName varchar(80) | |
DECLARE @ReferencedTableName varchar(80) | |
DECLARE @ReferencedColumnName varchar(80) | |
DECLARE @ConstraintName varchar(250) | |
DECLARE @CreateStatement varchar(max) | |
DECLARE @DropStatement varchar(max) | |
DECLARE @TruncateStatement varchar(max) | |
DECLARE @CreateStatementTemp varchar(max) | |
DECLARE @DropStatementTemp varchar(max) | |
DECLARE @TruncateStatementTemp varchar(max) | |
DECLARE @Statement varchar(max) | |
SET @Debug = 0--(0: 将执行相关语句 | 1: 不执行语句) | |
SET @Recycle = 0--(0: 不创建 / 不清除存储表 | 1: 将创建 / 清理存储表) | |
set @Verbose = 1--(1: 每步执行均打印消息 | 0: 不打印消息) | |
SET @i = 1 | |
SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])' | |
SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]' | |
SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]' | |
-- 创建外键临时表 | |
IF OBJECT_ID('tempdb..#FKs') IS NOT NULL | |
DROP TABLE #FKs | |
-- 获取外键 | |
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID, | |
OBJECT_NAME(constraint_object_id) as ConstraintName, | |
OBJECT_NAME(parent_object_id) as TableName, | |
clm1.name as ColumnName, | |
OBJECT_NAME(referenced_object_id) as ReferencedTableName, | |
clm2.name as ReferencedColumnName | |
INTO #FKs | |
FROM sys.foreign_key_columns fk | |
JOIN sys.columns clm1 ON fk.parent_column_id = clm1.column_id AND fk.parent_object_id = clm1.object_id | |
JOIN sys.columns clm2 ON fk.referenced_column_id = clm2.column_id AND fk.referenced_object_id= clm2.object_id | |
--WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated') | |
WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate | |
ORDER BY OBJECT_NAME(parent_object_id) | |
-- 外键操作 (删除 | 重建) 表 | |
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage') | |
BEGIN | |
IF @Verbose = 1 | |
PRINT '1. 正在创建表(Internal_FK_Definition_Storage)...' | |
CREATE TABLE [Internal_FK_Definition_Storage] | |
( | |
ID int not null identity(1,1) primary key, | |
FK_Name varchar(250) not null, | |
FK_CreationStatement varchar(max) not null, | |
FK_DestructionStatement varchar(max) not null, | |
Table_TruncationStatement varchar(max) not null | |
) | |
END | |
ELSE | |
BEGIN | |
IF @Recycle = 0 | |
BEGIN | |
IF @Verbose = 1 | |
PRINT '1. 正在清理表(Internal_FK_Definition_Storage)...' | |
TRUNCATE TABLE [Internal_FK_Definition_Storage] | |
END | |
ELSE | |
PRINT '1. 正在清理表(Internal_FK_Definition_Storage)...' | |
END | |
IF @Recycle = 0 | |
BEGIN | |
IF @Verbose = 1 | |
PRINT '2. 正在备份外键定义...' | |
WHILE (@i <= (SELECT MAX(ID) FROM #FKs)) | |
BEGIN | |
SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i) | |
SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i) | |
SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i) | |
SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i) | |
SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i) | |
SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName) | |
SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName) | |
SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName) | |
INSERT INTO [Internal_FK_Definition_Storage] | |
SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp | |
SET @i = @i + 1 | |
IF @Verbose = 1 | |
PRINT ' > 已备份外键:[' + @ConstraintName + '] 所属表: [' + @TableName + ']' | |
END | |
END | |
ELSE | |
PRINT '2. 正在备份外键定义...' | |
IF @Verbose = 1 | |
PRINT '3. 正在删除外键...' | |
BEGIN TRAN | |
BEGIN TRY | |
SET @i = 1 | |
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage])) | |
BEGIN | |
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i) | |
SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i) | |
IF @Debug = 1 | |
PRINT @Statement | |
ELSE | |
EXEC(@Statement) | |
SET @i = @i + 1 | |
IF @Verbose = 1 | |
PRINT ' > 已删除外键:[' + @ConstraintName + ']' | |
END | |
IF @Verbose = 1 | |
PRINT '4. 正在清理数据表...' | |
-- 先清除该外键所在表 (由于外键所在表仍可能又被其他外键所引用，因此需要循环递归处理)(注：本处理未实现) | |
-- 请不要使用下面注释代码 | |
/* | |
SET @i = 1 | |
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage])) | |
BEGIN | |
SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i) | |
IF @Debug = 1 | |
PRINT @Statement | |
ELSE | |
EXEC(@Statement) | |
SET @i = @i + 1 | |
IF @Verbose = 1 | |
PRINT ' > ' + @Statement | |
END | |
*/ | |
IF @Debug = 1 | |
PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']' | |
ELSE | |
EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']') | |
IF @Verbose = 1 | |
PRINT ' > 已清理数据表[' + @TableToTruncate + ']' | |
IF @Verbose = 1 | |
PRINT '5. 正在重建外键...' | |
SET @i = 1 | |
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage])) | |
BEGIN | |
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i) | |
SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i) | |
IF @Debug = 1 | |
PRINT @Statement | |
ELSE | |
EXEC(@Statement) | |
SET @i = @i + 1 | |
IF @Verbose = 1 | |
PRINT ' > 已重建外键:[' + @ConstraintName + ']' | |
END | |
COMMIT | |
END TRY | |
BEGIN CATCH | |
ROLLBACK | |
PRINT '出错信息:'+ERROR_MESSAGE() | |
END CATCH | |
IF @Verbose = 1 | |
PRINT '6. 处理完成！' | |
END |
- 查看 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 | |
GO |
- 从所有缓存中释放所有未使用的缓存条目 (性能、堵塞)
DBCC FREESYSTEMCACHE('ALL'); |
- 查询、解除死锁（事务锁)
-- 创建查询谁被谁锁住的存储过程 | |
use master | |
go | |
create procedure sp_who_lock(@in_spid int) | |
as | |
begin | |
declare @spid int,@bl int, | |
@intTransactionCountOnEntry int, | |
@intRowcount int, | |
@intCountProperties int, | |
@intCounter int | |
create table #tmp_lock_who ( | |
id int identity(1,1), | |
spid smallint, | |
bl smallint) | |
IF @@ERROR<>0 RETURN @@ERROR | |
insert into #tmp_lock_who(spid,bl) select 0 ,blocked | |
from (select * from sysprocesses where blocked>0 ) a | |
where not exists(select * from (select * from sysprocesses | |
where blocked>0 ) b | |
where a.blocked=spid) | |
union select spid,blocked from sysprocesses where blocked>0 | |
IF @@ERROR<>0 RETURN @@ERROR | |
-- 找到临时表的记录数 | |
select @intCountProperties = Count(*),@intCounter = 1 | |
from #tmp_lock_who | |
IF @@ERROR<>0 RETURN @@ERROR | |
if @intCountProperties=0 | |
select '现在没有阻塞和死锁信息' as message | |
-- 循环开始 | |
while @intCounter <= @intCountProperties | |
begin | |
-- 取第一条记录 | |
select @spid = case when @in_spid = 0 then spid else @in_spid end,@bl = bl | |
from #tmp_lock_who where Id = @intCounter | |
begin | |
if @spid =0 | |
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) | |
+ '进程号,其执行的SQL语法如下' | |
else | |
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' | |
+ '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' | |
DBCC INPUTBUFFER (@bl ) | |
end | |
-- 循环指针下移 | |
set @intCounter = @intCounter + 1 | |
end | |
drop table #tmp_lock_who | |
return 0 | |
end | |
GO | |
-- 查询表死锁信息 | |
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_who_lock 53 | |
-- 查询所有死锁的详细信息、执行的 sql 语句 | |
exec sp_who_lock 0 | |
DBCC inputbuffer (53) | |
-- 解除死锁 | |
kill 53 |
- 查询 SQL Server 根据 CPU 消耗列出前 5 个最差性能的查询 (性能、堵塞)
-- Worst performing CPU bound queries | |
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 | |
GO |
- 显示如何依据 I/O 消耗来找出你性能最差的查询 (性能、堵塞)
-- Worst performing I/O bound queries | |
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 | |
GO |
- 查询服务器部分特殊信息（系统）
select SERVERPROPERTY(N'edition') as Edition -- 数据版本，如企业版、开发版等 | |
,SERVERPROPERTY(N'collation') as Collation -- 数据库字符集 | |
,SERVERPROPERTY(N'servername') as ServerName -- 服务名 | |
,@@VERSION as Version -- 数据库版本号 | |
,@@LANGUAGE AS Language -- 数据库使用的语言，如 us_english 等 |
- 查询数据库中各数据表大小（表、数据库相关信息）
-- ============================================= | |
-- 描 述：更新查询数据库中各表的大小，结果存储到数据表中 | |
-- ============================================= | |
-- 查询是否存在结果存储表 | |
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 |
- 压缩数据库、文件、日志（系统）
DBCC ShrinkFile(‘数据库名’, targetsize); /* 收缩数据库文件 */ | |
DBCC ShrinkFile(‘数据库名_log’, targetsize); /* 收缩日志文件 */ | |
Targetsize：单位为兆，必须为整数，DBCC SHRINKFILE 尝试将文件收缩到指定大小。 | |
DBCC SHRINKFILE 不会将文件收缩到小于“实际使用的空间”大小，例如“分配空间”为10M，“实际使用空间”为6M，当制定targetsize为1时，则将该文件收缩到6M，不会将文件收缩到1M。 | |
-- 收缩数据库 | |
DBCC SHRINKDATABASE(数据库名，百分比) | |
百分比：即“收缩后文件中的最大可用空间”，取值范围“大于等于0， 小于100%”，实际使用中设为0即可。 |
- 数据库对象信息检索（表、数据库相关信息）
-- 查看对象的说明信息 | |
exec sp_help 'T_papermachine' | |
-- 显示视图、存储过程、函数、触发器的定义脚本。 | |
exec sp_helptext 'proc_report_getmeasuredata' | |
-- 显示表的行数和占用空间。 | |
exec sp_spaceused 'T_papermachine' | |
-- 显示表或视图的前 100 行，选定 “tablename,1000” 按 Ctrl+F1 可显示表的前 1000 行。 | |
exec sp_executesql N'IF OBJECT_ID(@tablename) IS NOT NULL EXEC(N''SELECT TOP(''+@n+N'') * FROM ''+@tablename)',N'@tablename nvarchar(100)=''t_papermachine'',@n int=100' | |
-- 显示表中每个索引占用的空间。 | |
exec sp_executesql N'SELECT index_name = ind.name, ddps.used_page_count, ddps.reserved_page_count, ddps.row_count FROM sys.indexes ind INNER JOIN sys.dm_db_partition_stats ddps ON ind.object_id = ddps.object_id AND ind.index_id = ddps.index_id WHERE ind.object_id = OBJECT_ID(@tablename)',N'@tablename nvarchar(100)=''t_papermachine''' | |
-- 显示表或视图的字段名，以逗号分隔。 | |
exec sp_executesql N'SELECT columns = STUFF((SELECT '', ''+name FROM sys.columns WHERE object_id = OBJECT_ID(@tablename) FOR XML PATH('''')),1,2,'''')',N'@tablename nvarchar(100)=''T_Papermachine''' | |
-- 根据选定关键词在当前数据库中查找表、视图、存储过程、函数 | |
exec sp_executesql N'SELECT * FROM sys.objects WHERE type IN (''U'',''V'',''P'',''FN'') AND name LIKE ''%''+@keyword+''%'' ORDER BY type,name',N'@keyword nvarchar(50)=''machine''' | |
-- 查询数据库中包含指定关键词的表、视图、存储过程、函数 | |
select routine_name,routine_definition,routine_type | |
from information_schema.routines | |
where routine_definition like '%AssessmentSpeed%' | |
order by routine_type | |
-- 模糊查询存储过程 sql 中包含某个文本 | |
SELECT obj.Name 存储过程名, sc.TEXT 存储过程内容 | |
FROM syscomments sc | |
INNER JOIN sysobjects obj ON sc.Id = obj.ID | |
WHERE sc.TEXT LIKE '%存储过程内容%' |
- 数据库用户、权限操作（系统）
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 |
- 使用 Checksum 结合 NewID 获得随机数（表、数据库相关信息）
Create FUNCTION Scalar_CheckSumNEWID | |
( | |
@From int, | |
@To int, | |
@Keep int, | |
@newid varchar(50) | |
) | |
RETURNS float | |
BEGIN | |
DECLARE @ResultVar float | |
SELECT @ResultVar=CONVERT(BIGINT,RIGHT(ABS(CHECKSUM(@newid)),9))*0.1/100000000 | |
RETURN @From+round((@To-@From)*@ResultVar,@Keep) | |
END | |
GO |
- 查询数据库表字段各项属性信息，便于直接复制导出 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 |
- 判断是否存在数据库、表、列、视图（表、数据库相关信息）
--1 判断数据库是否存在 | |
if exists (select * from sys.databases where name = '数据库名') | |
drop database [数据库名] | |
--2 判断表是否存在 | |
if exists (select * from sysobjects where id = object_id(N'[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) | |
drop table [表名] | |
--3 判断存储过程是否存在 | |
if exists (select * from sysobjects where id = object_id(N'[存储过程名]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) | |
drop procedure [存储过程名] | |
--4 判断临时表是否存在 | |
if object_id('tempdb..#临时表名') is not null | |
drop table #临时表名 | |
--5 判断视图是否存在 | |
-- 判断是否存在 'MyView52' 这个试图 | |
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'MyView52') | |
PRINT '存在' | |
else | |
PRINT '不存在' | |
--6 判断函数是否存在 | |
-- 判断要创建的函数名是否存在 | |
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[函数名]') and xtype in (N'FN', N'IF', N'TF')) | |
drop function [dbo].[函数名] | |
--7 获取用户创建的对象信息 | |
SELECT [name],[id],crdate FROM sysobjects where xtype='U' | |
--8 判断列是否存在 | |
if exists(select * from syscolumns where id=object_id('表名') and name='列名') | |
alter table 表名 drop column 列名 | |
--9 判断列是否自增列 | |
if columnproperty(object_id('table'),'col','IsIdentity')=1 | |
print '自增列' | |
else | |
print '不是自增列' | |
SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('表名') AND is_identity=1 | |
--10 判断表中是否存在索引 | |
if exists(select * from sysindexes where id=object_id('表名') and name='索引名') | |
print '存在' | |
else | |
print '不存在' | |
删除索引 drop index 表名.索引名 | |
或: drop index 索引名 on 表名(貌似2000不行) | |
--11 查看数据库中对象 | |
SELECT * FROM sys.sysobjects WHERE name='对象名' SELECT * FROM sys.sysobjects WHERE name='对象名' |
- CTE 查询的存储过程执行时间明显超出 T-Sql 查询。 可以通过添加 “WITH RECOMPILE” 参数，强制存储过程每次执行时重编译，实现快速查询。 (性能、堵塞)
大神的帖子： Parameter Sniffing, Embedding, and the RECOMPILE Options | |
https://www.cnblogs.com/wy123/p/6262800.html |
- 解决 insert exec 嵌套问题，解决办法是建立一个指向自己的数据库，增加链接服务器。 (性能、堵塞)
--1. 首先，增加链接服务器： | |
exec sp_addlinkedserver 'srv1','','SQLOLEDB','(local)' | |
exec sp_addlinkedsrvlogin 'srv1','false',null,'sa','sa' | |
--2. 其次找到该链接服务器，右键属性，开启 RPC： | |
服务器对象->链接服务器->右键->属性->服务器选项->RPC、RPC Out 都设置为True | |
--3. 启动 MSDTC 服务： | |
服务名称为：MSDTC（显示名称为Distributed Transaction Coordinator） | |
如果没启动会报错如下：MSDTC on server 'servername' is unavailable | |
--4. 调整存储过程访问，使用 srv1 调用存储过程 | |
insert #Temp exec srv1.DBName.dbo.Proc_Test @param | |
--5. 成功！结束！ |
- 查询数据库连接数、用户等（表、数据库相关信息）
-- 查看连接到数据库 "DB" 的连接 | |
SELECT * from master.dbo.sysprocesses WHERE dbid = DB_ID('DB') | |
-- 查询某个数据库用户的连接情况 | |
sp_who 'sa' | |
-- 以主机名分组显示 (与连接池配置的一样) | |
select hostname 主机名,count(*) 连接数量 from master.dbo.sysprocesses group by hostname order by count(*) desc | |
-- 根据主机和登陆名查看连接 | |
select * from master.dbo.sysprocesses where hostname = 'hmgx' and loginame = 'abc' | |
-- 字段 Blocked<>0 代表当前的 SQL Server 会话 ID 被锁定，锁定当前会话 ID 的 SQL Server 会话 ID 就是 Blocked 中的值。 | |
select * from master.sys.sysprocesses where blocked <> 0 | |
-- 查询某个会话所执行的 SQL 语句 | |
dbcc inputbuffer(spid) | |
-- 查看数据库允许的最大连接 | |
select @@MAX_CONNECTIONS | |
-- 查看数据库自上次启动以来的连接次数 | |
SELECT @@CONNECTIONS | |
-- 关闭连接，上面的查询可以得到 spid，根据 spid，关闭进程就可以了。 | |
kill 54 |
- 当前正在执行的语句 (性能、堵塞)
select session_id,transaction_id,wait_type,last_wait_type,wait_resource,start_time,status,command | |
,estimated_completion_time,cpu_time,logical_reads,text,open_transaction_count,open_resultset_count,percent_complete | |
from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle) s | |
where session_id>50 and session_id<>@@spid | |
go |
- 堵塞语句 (性能、堵塞)
-- 查询语句堵塞情况 | |
select spid,blocked,waittime,lastwaittype,waitresource,open_tran,status,p.dbid,cpu,physical_io,memusage,login_time,last_batch | |
,hostname,[program_name],hostprocess,cmd,nt_domain,nt_username,net_address,net_library,loginame,sql_handle,text | |
from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) s | |
where blocked >0 or spid in(select sp.blocked from master.dbo.sysprocesses sp where sp.blocked>0) | |
go | |
-- 查询语句堵塞情况 | |
SELECT | |
DB_NAME(der.[database_id]) AS '数据库名', | |
der.[start_time] AS '开始时间', | |
dest.[text] AS 'sql语句', | |
der.[session_id],der.[blocking_session_id], | |
sp.lastwaittype,sp.hostname,sp.program_name,sp.loginame, | |
der.[status] AS '状态', | |
der.[wait_type] AS '等待资源类型', | |
der.[wait_time] AS '等待时间', | |
der.[wait_resource] AS '等待的资源', | |
der.[logical_reads] AS '逻辑读次数' | |
FROM sys.[dm_exec_requests] AS der | |
INNER JOIN master.dbo.sysprocesses AS sp ON der.session_id=sp.spid | |
CROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) AS dest | |
--WHERE [session_id]>50 AND session_id<>@@SPID | |
ORDER BY '开始时间','数据库名' , der.[session_id] | |
GO |
- 是否有未提交事务（事务锁)
select spid,blocked,waittime,waittype,waitresource,p.dbid,cpu,physical_io,memusage,open_tran | |
,status,login_time,last_batch,hostname,program_name,hostprocess,loginame,cmd,text | |
from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) s | |
where open_tran <> 0 | |
go |
- 各数据库连接数（表、数据库相关信息）
SELECT @@ServerName AS server,NAME AS dbname,COUNT(STATUS) AS number_of_connections,GETDATE() AS timestamp | |
FROM sys.databases sd LEFT JOIN sys.sysprocesses sp ON sd.database_id = sp.dbid | |
WHERE database_id NOT BETWEEN 1 AND 4 | |
GROUP BY NAME | |
GO |
- 死锁跟踪，启用： dbcc traceon (1222,-1)（事务锁)
IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE ID=OBJECT_ID('TEMPDB..#deadlock')) | |
Drop TABLE #deadlock | |
CREATE TABLE #deadlock(LogDate DATETIME,ProcessInfo VARCHAR(20),Text VARCHAR(2000)) | |
go | |
INSERT INTO #deadlock EXEC xp_readerrorlog 0,1,N'deadlock victim',N'','2018-03-01','2018-12-30','DESC' | |
go | |
SELECT DISTINCT 'exec xp_readerrorlog 0,1,NULL,NULL,'''+CONVERT(VARCHAR(19),LogDate,120)+''','''+CONVERT(VARCHAR(19),DATEADD(S,1,LogDate),120)+''',''ASC''' | |
FROM #deadlock | |
go |
- 查看最近失败的 SqlServer 作业（系统）
select top 10 run_date,run_time,run_duration,step_name,message | |
from msdb..sysjobhistory where run_status = 0 | |
order by run_date desc,run_time desc | |
go |
- 各 DB 最近备份情况（系统）
select top 10 run_date,run_time,run_duration,step_name,message | |
from msdb..sysjobhistory where run_status = 0 | |
order by run_date desc,run_time desc | |
go |
- 谁对对象进行了 DDL 操作 (exec sp_configure 'default trace enabled')（表、数据库相关信息）
DECLARE @path NVARCHAR(1000) | |
SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) +'\log.trc' | |
FROM sys.traces WHERE id = 1 | |
SELECT DatabaseID,NTDomainName,NTUserName,HostName, ClientProcessID,ApplicationName | |
,LoginName,StartTime,DatabaseName,ObjectName,SessionLoginName | |
,(CASE WHEN EventClass=46 THEN 'Object:Created' WHEN EventClass=47 THEN 'Object:Deleted' WHEN EventClass=164 THEN 'Object:Altered' END)EventClass | |
FROM ::fn_trace_gettable(@path, 0) | |
WHERE EventClass in(46,47,164) and DatabaseName<>'tempdb' and ObjectName is not null | |
GO |
- 平均耗时最大的 SQL 语句 (性能、堵塞)
SELECT TOP 30 execution_count,total_worker_time,total_logical_reads,total_logical_writes | |
,total_worker_time/total_logical_reads as avgRead,s.text | |
FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) s | |
where execution_count>=100 and total_logical_reads<>0 | |
order by avgRead desc | |
go |
- 平均罗辑读最大的 SQL 语句 (性能、堵塞)
SELECT TOP 30 execution_count,total_worker_time,total_logical_reads,total_logical_writes | |
,total_worker_time/total_logical_reads as avgRead,s.text | |
FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) s | |
where execution_count<=10 and total_logical_reads<>0 | |
order by avgRead desc | |
go |
- 系统主要等待类型（系统）
SELECT TOP 10 | |
wait_type,waiting_tasks_count ,wait_time_ms,signal_wait_time_ms | |
,wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms | |
,100.0 * wait_time_ms / SUM (wait_time_ms ) OVER( )AS percent_total_waits | |
,100.0 * signal_wait_time_ms / SUM (signal_wait_time_ms) OVER( )AS percent_total_signal_waits | |
,100.0 * ( wait_time_ms - signal_wait_time_ms )/SUM (wait_time_ms ) OVER( ) AS percent_total_resource_waits | |
FROM sys .dm_os_wait_stats | |
WHERE wait_time_ms > 0 | |
go |
- 当前锁请求脚本 （事务锁)
select req_spid | |
,case req_status when 1 then '已授予' when 2 then '正在转换' when 3 then '正在等待' end as req_status | |
,case rsc_type when 1 then 'NULL 资源（未使用）' when 2 then '数据库' when 3 then '文件' | |
when 4 then '索引' when 5 then '表' when 6 then '页' when 7 then '键' | |
when 8 then '扩展盘区' when 9 then 'RID（行 ID)' when 10 then '应用程序' else '' end rsc_type | |
,coalesce(OBJECT_NAME(rsc_objid),db_name(rsc_dbid)) as [object] | |
,case req_mode when 1 then 'NULL' when 1 then 'Sch-S' when 2 then 'Sch-M' when 3 then 'S' | |
when 4 then 'U' when 5 then 'X' when 6 then 'IS' when 7 then 'IU' when 8 then 'IX' when 9 then 'SIU' | |
when 10 then 'SIX' when 11 then 'UIX' when 12 then 'BU' when 13 then 'RangeS_S' when 14 then 'RangeS_U' | |
when 15 then 'RangeI_N' when 16 then 'RangeI_S' when 17 then 'RangeI_U' when 18 then 'RangeI_X' | |
when 19 then 'RangeX_S' when 20 then 'RangeX_U' when 21 then 'RangeX_X' else '' end req_mode | |
,rsc_indid as index_id,rsc_text,req_refcnt | |
,case req_ownertype when 1 then '事务' when 2 then '游标' when 3 then '会话' when 4 then 'ExSession' else'' end req_ownertype | |
from sys.syslockinfo WHERE rsc_type<>2 | |
GO |
- 查看日志大小（系统）
EXEC xp_enumerrorlogs 1 -- 查看 sqlserver 错误日志大小 | |
EXEC xp_enumerrorlogs 2 -- 查看 代理日志大小 | |
go | |
exec msdb.dbo.sp_cycle_errorlog -- "Sql Server 日志" 切换 | |
exec msdb.dbo.sp_cycle_agent_errorlog -- "代理错误日志" 切换 | |
go |
- 各数据库日志大小及使用百分比（系统）
dbcc sqlperf(logspace) | |
go |
- 当前 DB 虚拟日志数量 （系统）
DBCC loginfo | |
go |
- 数据库活动游标（系统）
DBCC activecursors | |
go |
- 查看操作系统逻辑磁盘可用空间（系统）
EXEC master.dbo.xp_fixeddrives | |
go |
- 数据库大小（表、数据库相关信息）
select name,sum(size)*8/1024 from sys.database_files where type=0 group by name order by name | |
go | |
exec master.dbo.proc_getdbspaceused | |
go |
- 数据库表大小及行数（部分不算太准确，但可作为参考） （表、数据库相关信息）
SELECT OBJECT_NAME(id) as tab,rows,(reserved*8)/1024 as size_MB | |
FROM SYS.sysindexes WHERE indid IN(0,1) and id in(select object_id from sys.tables ) | |
order by size_MB desc | |
go |
- 数据库文件默认设置情况（系统）
select DB_NAME(database_id) as dbName,file_id,(size*8/1024) as [size(mb)] | |
,case when is_percent_growth = 1 then '10%' else CONVERT(varchar(10),growth*8/1024)+'M' end as growth | |
,type_desc,physical_name | |
from sys.master_files | |
where state = 0 | |
go |
- 各数据库 buffer pool 的分配情况 （系统）
SELECT | |
CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END AS Database_name | |
,count(*) AS cached_pages_count | |
,count(*)*8/1024 AS cached_space_in_mb | |
,sum(convert(bigint,free_space_in_bytes))/1024/1024 AS free_space_in_mb | |
FROM sys.dm_os_buffer_descriptors(nolock) | |
GROUP BY db_name(database_id) ,database_id | |
ORDER BY cached_pages_count DESC; | |
GO |
- 当前内存脏页数量及大小（系统）
SELECT db_name(database_id) AS 'Database' | |
,count(page_id) AS 'Dirty Pages' | |
,count(page_id)*8/1024 AS 'Dirty Pages(MB)' | |
FROM sys.dm_os_buffer_descriptors(nolock) | |
WHERE is_modified =1 | |
GROUP BY db_name(database_id) | |
ORDER BY 'Dirty Pages' DESC | |
GO |
- 缓存类型数量大小（系统）
select cacheobjtype as [Cached Type] | |
,COUNT(*) [Number of Plans] | |
,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)] | |
from sys.dm_exec_cached_plans | |
group by cacheobjtype | |
order by [Plan Cache Size(MB)] desc | |
GO |
- 缓存对象数量大小 （系统）
select objtype as [Cached Object Type] | |
,COUNT(*) as [Number of Plans] | |
,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)] | |
from sys.dm_exec_cached_plans | |
group by objtype | |
order by [Plan Cache Size(MB)] desc | |
GO |
- 前 N 行则表示最近的 N 分钟内 CPU 使用情况 （系统）
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) | |
FROM sys.dm_os_sys_info WITH (NOLOCK)); | |
SELECT TOP(60) | |
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] | |
,SQLProcessUtilization AS [SQL Server Process CPU Utilization] | |
,SystemIdle AS [System Idle Process] | |
,(100 - SystemIdle - SQLProcessUtilization) AS [Other Process CPU Utilization] | |
FROM ( | |
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id | |
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS[SystemIdle] | |
,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') AS [SQLProcessUtilization], [timestamp] | |
FROM ( | |
SELECT [timestamp], CONVERT(xml, record) AS [record] | |
FROM sys.dm_os_ring_buffers WITH (NOLOCK) | |
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' | |
AND record LIKE N'%<SystemHealth>%' | |
) AS x | |
) AS y | |
ORDER BY record_id DESC OPTION (RECOMPILE); | |
GO |
- 复制相关 （系统）
-- 事务复制：未分发命令数 (分发服务器执行) | |
SELECT 'EXEC distribution.sys.sp_replmonitorsubscriptionpendingcmds @publisher = N''' | |
+ a.publisher + ''', @publisher_db = N''' + a.publisher_db | |
+ ''', @publication = N''' + a.publication + ''', @subscriber = N''' | |
+ c.name + ''', @subscriber_db = N''' + b.subscriber_db | |
+ ''', @subscription_type =' + CAST(b.subscription_type AS VARCHAR) | |
FROM distribution.dbo.MSreplication_monitordata a ( NOLOCK ) | |
INNER JOIN ( | |
SELECT publication_id ,subscriber_id ,subscriber_db ,subscription_type | |
FROM distribution.dbo.MSsubscriptions (NOLOCK) | |
GROUP BY publication_id ,subscriber_id ,subscriber_db ,subscription_type | |
) b ON a.publication_id = b.publication_id | |
INNER JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_id | |
WHERE a.agent_type = 1 | |
go |
- 查看前 10 个等待分发命令最多的事务数 及 查看命令 （系统）
use distribution | |
go | |
SELECT top 10 A.xact_seqno,A.entry_time,COUNT(*) AS cmds | |
FROM distribution.dbo.MSrepl_transactions A(NOLOCK) | |
INNER JOIN distribution.dbo.MSrepl_commands B(NOLOCK) | |
ON A.xact_seqno=B.xact_seqno | |
GROUP BY A.xact_seqno,A.entry_time | |
ORDER BY cmds DESC | |
go |
- 查看出现错误的事务序列号 (历史记录) (分发服务器执行) （系统）
SELECT 'EXEC distribution.dbo.sp_helpsubscriptionerrors N''' | |
+ a.publisher + ''', N''' + a.publisher_db + ''', N''' + a.publication + ''', N''' + c.name + ''',N''' + b.subscriber_db + '''' | |
FROM distribution.dbo.MSreplication_monitordata a ( NOLOCK ) | |
INNER JOIN ( | |
SELECT publication_id ,subscriber_id ,subscriber_db ,subscription_type | |
FROM distribution.dbo.MSsubscriptions (NOLOCK) | |
GROUP BY publication_id ,subscriber_id ,subscriber_db ,subscription_type | |
) b ON a.publication_id = b.publication_id | |
INNER JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_id | |
WHERE a.agent_type = 1 | |
GO |
- 查询字段详细信息 （表、数据库相关信息）
use YCHMALL; | |
select schema_name(tab.schema_id) as schema_name, | |
tab.name as table_name, | |
col.name as column_name, | |
t.name as data_type, | |
t.name + | |
case when t.is_user_defined = 0 then | |
isnull('(' + | |
case when t.name in ('binary', 'char', 'nchar', | |
'varchar', 'nvarchar', 'varbinary') then | |
case col.max_length | |
when -1 then 'MAX' | |
else | |
case when t.name in ('nchar', | |
'nvarchar') then | |
cast(col.max_length/2 | |
as varchar(4)) | |
else cast(col.max_length | |
as varchar(4)) | |
end | |
end | |
when t.name in ('datetime2', 'datetimeoffset', | |
'time') then | |
cast(col.scale as varchar(4)) | |
when t.name in ('decimal', 'numeric') then | |
cast(col.precision as varchar(4)) + ', ' + | |
cast(col.scale as varchar(4)) | |
end + ')', '') | |
else ':' + | |
(select c_t.name + | |
isnull('(' + | |
case when c_t.name in ('binary', 'char', | |
'nchar', 'varchar', 'nvarchar', | |
'varbinary') then | |
case c.max_length | |
when -1 then 'MAX' | |
else | |
case when t.name in | |
('nchar', | |
'nvarchar') then | |
cast(c.max_length/2 | |
as varchar(4)) | |
else cast(c.max_length | |
as varchar(4)) | |
end | |
end | |
when c_t.name in ('datetime2', | |
'datetimeoffset', 'time') then | |
cast(c.scale as varchar(4)) | |
when c_t.name in ('decimal', 'numeric') then | |
cast(c.precision as varchar(4)) + ', ' | |
+ cast(c.scale as varchar(4)) | |
end + ')', '') | |
from sys.columns as c | |
inner join sys.types as c_t | |
on c.system_type_id = c_t.user_type_id | |
where c.object_id = col.object_id | |
and c.column_id = col.column_id | |
and c.user_type_id = col.user_type_id | |
) | |
end as data_type_ext, | |
case when col.is_nullable = 0 then 'N' | |
else 'Y' end as nullable, | |
case when def.definition is not null then def.definition | |
else '' end as default_value, | |
case when pk.column_id is not null then 'PK' | |
else '' end as primary_key, | |
case when fk.parent_column_id is not null then 'FK' | |
else '' end as foreign_key, | |
case when uk.column_id is not null then 'UK' | |
else '' end as unique_key, | |
case when ch.check_const is not null then ch.check_const | |
else '' end as check_contraint, | |
cc.definition as computed_column_definition, | |
ep.value as comments | |
from sys.tables as tab | |
left join sys.columns as col | |
on tab.object_id = col.object_id | |
left join sys.types as t | |
on col.user_type_id = t.user_type_id | |
left join sys.default_constraints as def | |
on def.object_id = col.default_object_id | |
left join ( | |
select index_columns.object_id, | |
index_columns.column_id | |
from sys.index_columns | |
inner join sys.indexes | |
on index_columns.object_id = indexes.object_id | |
and index_columns.index_id = indexes.index_id | |
where indexes.is_primary_key = 1 | |
) as pk | |
on col.object_id = pk.object_id | |
and col.column_id = pk.column_id | |
left join ( | |
select fc.parent_column_id, | |
fc.parent_object_id | |
from sys.foreign_keys as f | |
inner join sys.foreign_key_columns as fc | |
on f.object_id = fc.constraint_object_id | |
group by fc.parent_column_id, fc.parent_object_id | |
) as fk | |
on fk.parent_object_id = col.object_id | |
and fk.parent_column_id = col.column_id | |
left join ( | |
select c.parent_column_id, | |
c.parent_object_id, | |
'Check' check_const | |
from sys.check_constraints as c | |
group by c.parent_column_id, | |
c.parent_object_id | |
) as ch | |
on col.column_id = ch.parent_column_id | |
and col.object_id = ch.parent_object_id | |
left join ( | |
select index_columns.object_id, | |
index_columns.column_id | |
from sys.index_columns | |
inner join sys.indexes | |
on indexes.index_id = index_columns.index_id | |
and indexes.object_id = index_columns.object_id | |
where indexes.is_unique_constraint = 1 | |
group by index_columns.object_id, | |
index_columns.column_id | |
) as uk | |
on col.column_id = uk.column_id | |
and col.object_id = uk.object_id | |
left join sys.extended_properties as ep | |
on tab.object_id = ep.major_id | |
and col.column_id = ep.minor_id | |
and ep.name = 'MS_Description' | |
and ep.class_desc = 'OBJECT_OR_COLUMN' | |
left join sys.computed_columns as cc | |
on tab.object_id = cc.object_id | |
and col.column_id = cc.column_id | |
order by schema_name, | |
table_name, | |
column_name; |
- 跨数据库 linked (远程)
-- 服务器相关 | |
print '服务器的名称：'+@@SERVERNAME | |
print 'SQL Server的版本' + @@VERSION | |
SELECT @@SERVERNAME as '服务器名称' | |
select @@VERSION as 'SQL Server的版本' | |
数据库跨服务器设置： | |
EXEC sp_addlinkedserver | |
@server='DEV-W2K12-114', -- 链接服务器别名 | |
@srvproduct='', | |
@provider='SQLOLEDB', | |
@datasrc='10.199.206.43' -- 要访问的的数据库所在的服务器的 ip | |
GO | |
/ | |
EXEC sp_addlinkedsrvlogin | |
'dblink_43', -- 链接服务器别名 | |
'false', | |
NULL, | |
'Youcaihua', -- 要访问的数据库的用户 | |
'DF170D23-9946-4340-BBCF-2A37845E8DD9' -- 要访问的数据库，用户的密码 | |
GO | |
exec sp_dropserver'DEV-W2K12-114' ,'droplogins' |
- 查询历史执行的语句 (表、数据库相关信息)
-- 查询历史执行的语句 | |
SELECT | |
dest.text AS QueryText, | |
deqs.creation_time, | |
deqs.execution_count | |
FROM sys.dm_exec_query_stats AS deqs | |
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest | |
WHERE dest.text LIKE '%key_value%' order by deqs.creation_time desc |
- 数据太大 使用 cmd 命令的方式导入数据
osql -S 127.0.0.1,1433 -U sa -P 123456 -i 文件路径 | |
osql -S 127.0.0.1,1433 -U sa -d zcjy_to_server_gzs -P 123456 -i 文件路径 | |
osql ？ 命令可查询相关参数 |
- 查看某个字段的长度
datalenth(field) | |
select a, datalenth(b) from table |
- 拼接列
SELECT STUFF((SELECT ',' + fieldname | |
FROM tablename | |
WHERE fieldname = 'aaa' | |
FOR XML PATH('')), 1, 1, '') AS concatenated_values; |
- sqlserver 查询的数据是数值 如果不足 5 位在前面补 0
SELECT RIGHT(REPLICATE('0', 5) + CAST(NumberColumn AS VARCHAR(5)), 5) AS PaddedNumber | |
FROM YourTable; |
- 获取年度加季度
SELECT | |
DATEPART(YEAR, GETDATE()) AS Year, | |
DATEPART(QUARTER, GETDATE()) AS Quarter; |
- 对比两张表生成差异字段的 alter 执行语句
-- 字段少的一方 | |
SELECT STUFF(( | |
SELECT ', ''' + c.COLUMN_NAME + '''' | |
FROM INFORMATION_SCHEMA.COLUMNS AS c | |
WHERE c.TABLE_NAME = 'YourTableName' | |
FOR XML PATH(''), TYPE | |
).value('.', 'NVARCHAR(MAX)'), 1, 2, '') | |
AS ColumnNames; | |
SELECT | |
'ALTER TABLE ' + QUOTENAME('dist') + ' ADD ' + | |
QUOTENAME(c.name) + | |
N' ' + | |
t.name + | |
CASE | |
WHEN t.name IN ('char', 'varchar', 'nchar', 'nvarchar') THEN | |
N'(' + CAST(c.max_length AS NVARCHAR(10)) + N')' | |
WHEN t.name IN ('decimal', 'numeric') THEN | |
N'(' + CAST(c.precision AS NVARCHAR(10)) + N', ' + CAST(c.scale AS NVARCHAR(10)) + N')' | |
ELSE N'' | |
END + '; | |
' | |
FROM sys.columns AS c | |
INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id | |
LEFT JOIN sys.default_constraints AS d ON c.default_object_id = d.object_id AND c.object_id = d.parent_object_id | |
WHERE c.object_id = OBJECT_ID('dist') | |
and c.name not in('id', '...') | |
ORDER BY c.column_id |
# sqlserver 安装
请看其他专栏
# 总结
主要记录了工作中经常用到的问题排查和优化的 sql 语句
# 参考资料
- 感谢想要的都有
