SQL Server 日常维护经典应用

2017-10-23 11:48:21来源:cnblogs.com作者:jearay人点击

分享

1.sql server开启clr权限:

exec sp_configure 'clr enabled', 1GORECONFIGUREGOALTER DATABASE HWMESTC SET TRUSTWORTHY ONALTER AUTHORIZATION ON Database::HWMESTC TO sa;

2.查询数据库大小

Exec sp_spaceusedselect name, convert(float,size) * (8192.0/1024.0)/1024. from dbo.sysfiles

3.数据库日志压缩

--选择需要使用的数据库USE PIMS--将数据库模式设置为SIMPLEALTER 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_textfrom 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.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_clerksgroup by typeorder 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_countfrom    sys.dm_exec_cached_plansgroup by cacheobjtype, objtypeorder by cacheobjtype, objtype

12.查询缓存中具体的执行计划,及对应的SQL

-- 查询缓存中具体的执行计划,及对应的SQL-- 将此结果按照数据表或SQL进行统计,可以作为基线,调整索引时考虑-- 查询结果会很大,注意将结果集输出到表或文件中SELECT  usecounts ,        refcounts ,        size_in_bytes ,        cacheobjtype ,        objtype ,        TEXTFROM    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_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)';

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 PIMSGOCREATE PROCEDURE [dbo].[usp_Truncate_Table]  @TableToTruncate VARCHAR(64)AS BEGINSET NOCOUNT ON--==变量定义DECLARE @i intDECLARE @Debug bitDECLARE @Recycle bitDECLARE @Verbose bitDECLARE @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 ELSEBEGIN    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)...'ENDIF @Recycle = 0BEGIN    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 TRYSET @i = 1WHILE (@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 = 1WHILE (@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 '  > ' + @StatementEND*/IF @Debug = 1     PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']'ELSE    EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']')IF @Verbose = 1    PRINT '  > 已清理数据表[' + @TableToTruncate + ']'    IF @Verbose = 1    PRINT '5. 正在重建外键...'SET @i = 1WHILE (@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    COMMITEND TRYBEGIN CATCH    ROLLBACK     PRINT '出错信息:'+ERROR_MESSAGE()END CATCHIF @Verbose = 1    PRINT '6. 处理完成!'END

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台