查看: 584|回复: 0

[SQLServer] SQL Server 日常维护经典应用

发表于 2017-10-30 08:00:01
太阳http代理AD

SQL Server日常维护常用的一些脚本整理。

1.sql server开启clr权限:

  1. exec sp_configure 'clr enabled', 1
  2. GO
  3. RECONFIGURE
  4. GO
  5. ALTER DATABASE <em>HWMESTC</em> SET TRUSTWORTHY ON
  6. ALTER AUTHORIZATION ON Database::<em>HWMESTC</em> TO sa;
复制代码

2.查询数据库大小

  1. Exec sp_spaceused
  2. select name, convert(float,size) * (8192.0/1024.0)/1024. from dbo.sysfiles
复制代码

3.数据库日志压缩

  1. --选择需要使用的数据库
  2. USE<em> PIMS
  3. </em>--将数据库模式设置为SIMPLE
  4. ALTER DATABASE <em>PIMS</em> SET RECOVERY SIMPLE
  5. -- 将日志文件收缩到1M
  6. DBCC SHRINKFILE ('PIMS_log', 1)
  7. -- 还原数据库
  8. ALTER DATABASE <em>PIMS</em> SET RECOVERY FULL
复制代码

4.查看数据库连接用户

  1. Select * From sys.dm_exec_connections
复制代码

5.查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)

  1. select spid,cmd,cpu,physical_io,memusage,
  2. (select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
  3. from master..sysprocesses order by cpu desc,physical_io desc
复制代码

6.查看缓存中重用次数少,占用内存大的查询语句(当前缓存中未释放的)--全局

  1. SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text]
  2. FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
  3. ORDER BY usecounts,p.size_in_bytes desc
复制代码

7.看BUFFER POOL中,都缓存了哪些表(当前数据库)的数据

  1. select OBJECT_NAME(object_id) 表名,COUNT(*) 页数,COUNT(*)*8/1024.0 Mb
  2. from sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c
  3. where a.allocation_unit_id=b.allocation_unit_id
  4. and b.container_id=c.hobt_id
  5. and database_id=DB_ID()
  6. group by OBJECT_NAME(object_id)
  7. order by 2 desc
复制代码

8.查询SQLSERVER内存使用情况

  1. select * from sys.dm_os_process_memory
复制代码

9.查询SqlServer总体的内存使用情况

  1. select type,
  2. sum(virtual_memory_reserved_kb)*0.1*10/1024/1024 as vm_Reserved_gb,--保留的内存
  3. sum(virtual_memory_committed_kb)*0.1*10/1024/1024 as vm_Committed_gb,--提交的内存
  4. sum(awe_allocated_kb)*0.1*10/1024/1024 as awe_Allocated_gb,--开启AWE后使用的内存
  5. sum(shared_memory_reserved_kb)*0.1*10/1024/1024 as sm_Reserved_gb,--共享的保留内存
  6. sum(shared_memory_committed_kb)*0.1*10/1024/1024 as sm_Committed_gb--共享的提交内存
  7. from sys.dm_os_memory_clerks
  8. group by type
  9. order by type
复制代码

10.查询当前数据库缓存的所有数据页面,哪些数据表,缓存的数据页面数量

  1. -- 查询当前数据库缓存的所有数据页面,哪些数据表,缓存的数据页面数量
  2. -- 从这些信息可以看出,系统经常要访问的都是哪些表,有多大?
  3. select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*)
  4. from sys.allocation_units a,
  5. sys.dm_os_buffer_descriptors b,
  6. sys.partitions p
  7. where a.allocation_unit_id=b.allocation_unit_id
  8. and a.container_id=p.hobt_id
  9. and b.database_id=db_id()
  10. group by p.object_id,p.index_id
  11. order by buffer_pages desc
复制代码

11.查询缓存的各类执行计划,及分别占了多少内存

  1. -- 查询缓存的各类执行计划,及分别占了多少内存
  2. -- 可以对比动态查询与参数化SQL(预定义语句)的缓存量
  3. select cacheobjtype
  4. , objtype
  5. , sum(cast(size_in_bytes as bigint))/1024 as size_in_kb
  6. , count(bucketid) as cache_count
  7. from sys.dm_exec_cached_plans
  8. group by cacheobjtype, objtype
  9. order by cacheobjtype, objtype
复制代码

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

  1. -- 查询缓存中具体的执行计划,及对应的SQL
  2. -- 将此结果按照数据表或SQL进行统计,可以作为基线,调整索引时考虑
  3. -- 查询结果会很大,注意将结果集输出到表或文件中
  4. SELECT usecounts ,
  5. refcounts ,
  6. size_in_bytes ,
  7. cacheobjtype ,
  8. objtype ,
  9. TEXT
  10. FROM sys.dm_exec_cached_plans cp
  11. CROSS APPLY sys.dm_exec_sql_text(plan_handle)
  12. ORDER BY objtype DESC ;
  13. GO
复制代码

13.查询sql server内存整体使用情况

  1. --查询sql server内存整体使用情况
  2. SELECT object_name, cntr_value*0.1*10/1024/1024 ,cntr_value,cntr_type,t.counter_name,t.instance_name
  3. FROM sys.dm_os_performance_counters t
  4. WHERE counter_name = 'Total Server Memory (KB)';
复制代码

14.一次性清楚数据库所有表的数据

  1. CREATE PROCEDURE sp_DeleteAllData
  2. AS
  3. EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
  4. EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
  5. EXEC sp_MSForEachTable 'DELETE FROM ?'
  6. EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
  7. EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
  8. EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'
  9. GO
复制代码

15.SQL优化相关、执行时间

  1. SELECT creation_time N'语句编译时间'
  2. ,last_execution_time N'上次执行时间'
  3. ,total_physical_reads N'物理读取总次数'
  4. ,total_logical_reads/execution_count N'每次逻辑读次数'
  5. ,total_logical_reads N'逻辑读取总次数'
  6. ,total_logical_writes N'逻辑写入总次数'
  7. ,execution_count N'执行次数'
  8. ,total_worker_time/1000 N'所用的CPU总时间ms'
  9. ,total_elapsed_time/1000 N'总花费时间ms'
  10. ,(total_elapsed_time / execution_count)/1000 N'平均时间ms'
  11. ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
  12. ((CASE statement_end_offset
  13. WHEN -1 THEN DATALENGTH(st.text)
  14. ELSE qs.statement_end_offset END
  15. - qs.statement_start_offset)/2) + 1) N'执行语句'
  16. FROM sys.dm_exec_query_stats AS qs
  17. CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
  18. WHERE SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
  19. ((CASE statement_end_offset
  20. WHEN -1 THEN DATALENGTH(st.text)
  21. ELSE qs.statement_end_offset END
  22. - qs.statement_start_offset)/2) + 1) NOT LIKE '%fetch%'
  23. ORDER BY total_elapsed_time / execution_count DESC;
复制代码

16.truncate外键表存储过程

  1. USE PIMS
  2. GO
  3. CREATE PROCEDURE [dbo].[usp_Truncate_Table]
  4. @TableToTruncate VARCHAR(64)
  5. AS
  6. BEGIN
  7. SET NOCOUNT ON
  8. --==变量定义
  9. DECLARE @i int
  10. DECLARE @Debug bit
  11. DECLARE @Recycle bit
  12. DECLARE @Verbose bit
  13. DECLARE @TableName varchar(80)
  14. DECLARE @ColumnName varchar(80)
  15. DECLARE @ReferencedTableName varchar(80)
  16. DECLARE @ReferencedColumnName varchar(80)
  17. DECLARE @ConstraintName varchar(250)
  18. DECLARE @CreateStatement varchar(max)
  19. DECLARE @DropStatement varchar(max)
  20. DECLARE @TruncateStatement varchar(max)
  21. DECLARE @CreateStatementTemp varchar(max)
  22. DECLARE @DropStatementTemp varchar(max)
  23. DECLARE @TruncateStatementTemp varchar(max)
  24. DECLARE @Statement varchar(max)
  25. SET @Debug = 0--(0:将执行相关语句|1:不执行语句)
  26. SET @Recycle = 0--(0:不创建/不清除存储表|1:将创建/清理存储表)
  27. set @Verbose = 1--(1:每步执行均打印消息|0:不打印消息)
  28. SET @i = 1
  29. SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
  30. SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
  31. SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'
  32. -- 创建外键临时表
  33. IF OBJECT_ID('tempdb..#FKs') IS NOT NULL
  34. DROP TABLE #FKs
  35. -- 获取外键
  36. SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
  37. OBJECT_NAME(constraint_object_id) as ConstraintName,
  38. OBJECT_NAME(parent_object_id) as TableName,
  39. clm1.name as ColumnName,
  40. OBJECT_NAME(referenced_object_id) as ReferencedTableName,
  41. clm2.name as ReferencedColumnName
  42. INTO #FKs
  43. FROM sys.foreign_key_columns fk
  44. JOIN sys.columns clm1 ON fk.parent_column_id = clm1.column_id AND fk.parent_object_id = clm1.object_id
  45. JOIN sys.columns clm2 ON fk.referenced_column_id = clm2.column_id AND fk.referenced_object_id= clm2.object_id
  46. --WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
  47. WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate
  48. ORDER BY OBJECT_NAME(parent_object_id)
  49. -- 外键操作(删除|重建)表
  50. IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
  51. BEGIN
  52. IF @Verbose = 1
  53. PRINT '1. 正在创建表(Internal_FK_Definition_Storage)...'
  54. CREATE TABLE [Internal_FK_Definition_Storage]
  55. (
  56. ID int not null identity(1,1) primary key,
  57. FK_Name varchar(250) not null,
  58. FK_CreationStatement varchar(max) not null,
  59. FK_DestructionStatement varchar(max) not null,
  60. Table_TruncationStatement varchar(max) not null
  61. )
  62. END
  63. ELSE
  64. BEGIN
  65. IF @Recycle = 0
  66. BEGIN
  67. IF @Verbose = 1
  68. PRINT '1. 正在清理表(Internal_FK_Definition_Storage)...'
  69. TRUNCATE TABLE [Internal_FK_Definition_Storage]
  70. END
  71. ELSE
  72. PRINT '1. 正在清理表(Internal_FK_Definition_Storage)...'
  73. END
  74. IF @Recycle = 0
  75. BEGIN
  76. IF @Verbose = 1
  77. PRINT '2. 正在备份外键定义...'
  78. WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
  79. BEGIN
  80. SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
  81. SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
  82. SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
  83. SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
  84. SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)
  85. SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
  86. SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
  87. SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName)
  88. INSERT INTO [Internal_FK_Definition_Storage]
  89. SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp
  90. SET @i = @i + 1
  91. IF @Verbose = 1
  92. PRINT ' > 已备份外键:[' + @ConstraintName + '] 所属表: [' + @TableName + ']'
  93. END
  94. END
  95. ELSE
  96. PRINT '2. 正在备份外键定义...'
  97. IF @Verbose = 1
  98. PRINT '3. 正在删除外键...'
  99. BEGIN TRAN
  100. BEGIN TRY
  101. SET @i = 1
  102. WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
  103. BEGIN
  104. SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
  105. SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)
  106. IF @Debug = 1
  107. PRINT @Statement
  108. ELSE
  109. EXEC(@Statement)
  110. SET @i = @i + 1
  111. IF @Verbose = 1
  112. PRINT ' > 已删除外键:[' + @ConstraintName + ']'
  113. END
  114. IF @Verbose = 1
  115. PRINT '4. 正在清理数据表...'
  116. --先清除该外键所在表(由于外键所在表仍可能又被其他外键所引用,因此需要循环递归处理)(注:本处理未实现)
  117. --请不要使用下面注释代码
  118. /*
  119. SET @i = 1
  120. WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
  121. BEGIN
  122. SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
  123. IF @Debug = 1
  124. PRINT @Statement
  125. ELSE
  126. EXEC(@Statement)
  127. SET @i = @i + 1
  128. IF @Verbose = 1
  129. PRINT ' > ' + @Statement
  130. END
  131. */
  132. IF @Debug = 1
  133. PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']'
  134. ELSE
  135. EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']')
  136. IF @Verbose = 1
  137. PRINT ' > 已清理数据表[' + @TableToTruncate + ']'
  138. IF @Verbose = 1
  139. PRINT '5. 正在重建外键...'
  140. SET @i = 1
  141. WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
  142. BEGIN
  143. SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
  144. SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
  145. IF @Debug = 1
  146. PRINT @Statement
  147. ELSE
  148. EXEC(@Statement)
  149. SET @i = @i + 1
  150. IF @Verbose = 1
  151. PRINT ' > 已重建外键:[' + @ConstraintName + ']'
  152. END
  153. COMMIT
  154. END TRY
  155. BEGIN CATCH
  156. ROLLBACK
  157. PRINT '出错信息:'+ERROR_MESSAGE()
  158. END CATCH
  159. IF @Verbose = 1
  160. PRINT '6. 处理完成!'
  161. END
复制代码



太阳http代理AD
回复

使用道具 举报