sqlserver日常使用脚本.txt

forever

960

28页

54次

2024-07-24

10墨值下载

--------------------------------------------------------------------------------

------------------------

--Description :sqlserver 常用脚本：

--------------------------------------------------------------------------------

------------------------

SELECT T.text, P.query_plan, S.host_name, S.program_name,

S.client_interface_name, S.login_name, R.*

FROM sys.dm_exec_requests R

JOIN sys.dm_exec_sessions S on S.session_id=R.session_id

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS T

CROSS APPLY sys.dm_exec_query_plan(plan_handle) As P

--磁盘使用情况

SELECT DISTINCT

volume_mount_point [Disk Mount Point],

file_system_type [File System Type],

logical_volume_name as [Logical Drive Name],

CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in

GB], ---1GB = 1073741824 bytes

CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available

Size in GB],

CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS

DECIMAL(18,2)) * 100 AS [Space Free %]

FROM sys.master_files

CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)

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

1.sql server 开启 clr 权限：

exec sp_configure 'clr enabled', 1

RECONFIGURE

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.查看执行时间最长的 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;

--------------------------------------------------------------------------------

------------------------

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_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

of 28

10墨值下载

sqlserver

相关文档

评论