db 管理

db 管理

exec sp_who 'active'

查询CPU占用高的语句 "SELECT TOP 10 total_worker_time/execution_count AS avg_cpu_cost, plan_handle, execution_count, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats ORDER BY [avg_cpu_cost] DESC"等待资源 "SELECT TOP 10 [session_id], [request_id], [start_time] AS '开始时间', [status] AS '状态', [command] AS '命令', dest.[text] AS 'sql语句', DB_NAME([database_id]) AS '数据库名', [blocking_session_id] AS '正在阻塞其他会话的会话ID', der.[wait_type] AS '等待资源类型', [wait_time] AS '等待时间', [wait_resource] AS '等待的资源', [dows].[waiting_tasks_count] AS '当前正在进行等待的任务数', [reads] AS '物理读次数', [writes] AS '写次数', [logical_reads] AS '逻辑读次数', [row_count] AS '返回结果行数' FROM sys.[dm_exec_requests] AS der INNER JOIN [sys].[dm_os_wait_stats] AS dows ON der.[wait_type]=[dows].[wait_type] CROSS APPLY sys.​​dm_exec_sql_text​​ AS dest

WHERE [session_id]>50 ORDER BY [cpu_time] DESC"查看CPU数和user scheduler数目 SELECT cpu_count,scheduler_count FROM sys.dm_os_sys_info查看最大工作线程数 SELECT max_workers_count FROM sys.dm_os_sys_info "SELECTscheduler_address,scheduler_id,cpu_id,status,current_tasks_count,current_workers_count,active_workers_countFROM sys.dm_os_schedulers"在SSMS里选择以文本格式显示结果 "SELECT TOP 10 dest.[text] AS 'sql语句' FROM sys.[dm_exec_requests] AS der CROSS APPLY sys.​​dm_exec_sql_text​​ AS dest

WHERE [session_id]>50 ORDER BY [cpu_time] DESC"各项指标是否正常，是否有阻塞 "SELECT [session_id],[request_id],[start_time] AS '开始时间',[status] AS '状态',[command] AS '命令',dest.[text] AS 'sql语句', DB_NAME([database_id]) AS '数据库名',[blocking_session_id] AS '正在阻塞其他会话的会话ID', [wait_type] AS '等待资源类型', [wait_time] AS '等待时间', [wait_resource] AS '等待的资源', [reads] AS '物理读次数', [writes] AS '写次数', [logical_reads] AS '逻辑读次数', [row_count] AS '返回结果行数', cpu_time FROM sys.[dm_exec_requests] AS der CROSS APPLY sys.​​dm_exec_sql_text​​ AS dest

WHERE [session_id]>50

ORDER BY [cpu_time] DESC"当前的数据库用户连接有多少 "SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50"每个数据占用内存查看 "SET TRAN ISOLATION LEVEL READ UNCOMMITTED SELECT ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName , CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2)) AS [Size (MB)] FROM sys.dm_os_buffer_descriptors GROUP BY database_id ORDER BY DatabaseName"系统占用内存查看 "SELECT m.total_physical_memory_kb,m.available_physical_memory_kb,m.total_page_file_kb,m.available_page_file_kb,m.system_memory_state_descFROM sys.dm_os_sys_memory m" system_memory_state_desc显示为“Available physical memory is high”，表明内存没有压力。如何查看SQLServer数据库每个表占用的空间大小？ "CREATE PROCEDURE [dbo].[sys_viewTableSpace]ASBEGINSET NOCOUNT ON;CREATE TABLE [dbo].#tableinfo( 表名 ​​varchar​​ COLLATE Chinese_PRC_CI_AS NULL,

记录数 [int] NULL, 预留空间 ​​varchar​​ COLLATE Chinese_PRC_CI_AS NULL,

使用空间 ​​varchar​​ COLLATE Chinese_PRC_CI_AS NULL,

索引占用空间 ​​varchar​​ COLLATE Chinese_PRC_CI_AS NULL,

未用空间 ​​varchar​​ COLLATE Chinese_PRC_CI_AS NULL

)insert into #tableinfo(表名, 记录数, 预留空间, 使用空间, 索引占用空间, 未用空间)exec sp_MSforeachtable ""exec sp_spaceused '?'""select * from #tableinfoorder by 记录数 descdrop table #tableinfoEND" exec sys_viewtablespace--查看当前挂起的IO请求 "SELECT DB_NAME(S.database_id) AS DBName,S.file_id,S.io_stall,R.io_pending_ms_ticksFROM sys.dm_io_pending_io_requests RINNER JOIN sys.dm_io_virtual_file_stats(NULL,NULL) SON R.io_handle=S.file_handle"可以查到AWE使用的内存 "select sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb] from sys.dm_os_memory_clerks"如何查看快照隔离是否已启动DBCC USEROPTIONS; select name,snapshot_isolation_state,snapshot_isolation_state_desc from sys.databases where name='zytest' ----用户连接数SELECT login_name, Count(0) user_countFROM Sys.dm_exec_requests dr WITH(nolock) RIGHT OUTER JOIN Sys.dm_exec_sessions ds WITH(nolock) ON dr.session_id = ds.session_id RIGHT OUTER JOIN Sys.dm_exec_connections dc WITH(nolock) ON ds.session_id = dc.session_idWHERE ds.session_id > 50GROUP BY login_nameORDER BY user_count DESCDB更换盘符 "use [master]gosp_detach_db 'DATA'gostep 2:copy database file from old location to new locationstep 3:use [master]gosp_attach_db 'DATA','D:\SQL Data files\DATA.mdf', 'D:\SQL Data files\DATA_log.ldf'go"查看数据库信息sp_helpdb查看链接sp_who

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。