SQL Server 日常维护经典应用

转载

牛魔王MrDing 2017-11-23 09:16:42 博主文章分类：三、SqlServer

SQL Server日常维护常用的一些脚本整理。 1.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;

2.查询数据库大小 Exec sp_spaceused select name, convert(float,size) * (8192.0/1024.0)/1024. from dbo.sysfiles

3.数据库日志压缩 --选择需要使用的数据库 USE PIMS --将数据库模式设置为SIMPLE ALTER DATABASE PIMS SET RECOVERY SIMPLE -- 将日志文件收缩到1M DBCC SHRINKFILE ('PIMS_log', 1) -- 还原数据库 ALTER DATABASE PIMS SET RECOVERY FULL

4.查看数据库连接用户 Select * From sys.dm_exec_connections

5.查看当前占用 cpu 资源最高的会话和其中执行的语句(及时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

6.查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局 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

7.看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

8.查询SQLSERVER内存使用情况 select * from sys.dm_os_process_memory

9.查询SqlServer总体的内存使用情况 select type, sum(virtual_memory_reserved_kb)0.110/1024/1024 as vm_Reserved_gb,--保留的内存 sum(virtual_memory_committed_kb)0.110/1024/1024 as vm_Committed_gb,--提交的内存 sum(awe_allocated_kb)0.110/1024/1024 as awe_Allocated_gb,--开启AWE后使用的内存 sum(shared_memory_reserved_kb)0.110/1024/1024 as sm_Reserved_gb,--共享的保留内存 sum(shared_memory_committed_kb)0.110/1024/1024 as sm_Committed_gb--共享的提交内存 from sys.dm_os_memory_clerks group by type order by type

10.查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量 -- 查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量 -- 从这些信息可以看出，系统经常要访问的都是哪些表，有多大？ 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

11.查询缓存的各类执行计划，及分别占了多少内存 -- 查询缓存的各类执行计划，及分别占了多少内存 -- 可以对比动态查询与参数化SQL（预定义语句）的缓存量 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

12.查询缓存中具体的执行计划，及对应的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 ; GO

13.查询sql server内存整体使用情况 --查询sql server内存整体使用情况 SELECT object_name, cntr_value0.110/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)';

14.一次性清楚数据库所有表的数据 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

15.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;

16.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