sqlserver常用脚本及问题定位脚本.txt

forever

240

43页

15次

2024-10-11

10墨值下载

一、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 内存使用情况

of 43

10墨值下载

sqlserver

关注

评论