sql server常用语句总结
-- 查看性能记数器SELECT * FROM sys.dm_os_performance_counters -- 执行过的线程所遇到的所有等待(不是当前正在运行的线程, 为自上次重置统计信息或启动服务器以来累积的数据),可分析靠前的几个等待较高的事件。 select * from sys.dm_os_wait_stats order by wait_time_ms desc该动态视图的细节，请查看帮助文档. -- 重置该动态视图DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);GO -- 正在等待某些资源的任务的等待队列select * from sys.dm_os_waiting_tasks order by wait_duration_ms desc 内存使用：查看当前由 SQL Server 分配的内存对象(KB): select sum((page_size_in_bytes/1024)*max_pages_allocated_count) from sys.dm_os_memory_objects;查看系统内存当前信息: select * from sys.dm_os_sys_memory (这个动态视图只在sql 2008中才有)select cpu_count,hyperthread_ratio,scheduler_count,physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,bpool_committed * 8 / 1024 as bpool_committed_mb,bpool_commit_target * 8 / 1024 as bpool_target_mb,bpool_visible * 8 / 1024 as bpool_visible_mbfrom sys.dm_os_sys_info 限制SQL Server使用的最小,最大内存(MB)：sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'min server memory', 300;GOsp_configure 'max server memory', 850;GORECONFIGURE;GOCPU使用情况:SELECT TOP 50total_worker_time/execution_count AS '每次执行占用CPU(微秒)',execution_count as '执行次数',total_worker_time as '总共占用CPU(微秒)',creation_time as '创建时间',last_execution_time as '最后执行时间',min_worker_time as '最低每次占用CPU',max_worker_time as '最高每次占用cpu',total_physical_readsas '总共io物理读取次数',total_logical_reads as '总共逻辑读取次数',total_logical_writesas '总共逻辑写次数',total_elapsed_time as '完成此计划的执行所占用的总时间(微秒)',(SELECT SUBSTRING(text,statement_start_offset/2,(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 'SQL内容'FROM sys.dm_exec_query_statsORDER BY 1 DESC --下面的查询显示SQL 等待分析和前10 个等待的资源 select top 10 *from sys.dm_os_wait_statswhere wait_type not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')order by wait_time_ms desc;SQL Server中的活动会话数:exec sp_who 'active'print @@rowcount SQL Server等待情况select * from sys.dm_os_waiting_tasks ; --当前等待事件select * from sys.dm_os_wait_stats--历史等待次数,是sqlserver启动后的累计值,需使用下一条语句清空DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);找出进程阻塞:运行下面的查询可确定阻塞的会话select blocking_session_id, wait_duration_ms, session_id from sys.dm_os_waiting_taskswhere blocking_session_id is not null spid 正在阻塞另一个 spid，可在数据库中创建以下存储过程，然后执行该存储过程。此存储过程会报告此阻塞情况。键入 sp_who 可找出 @spid；@spid 是可选参数。create proc dbo.sp_block (@spid bigint=NULL)asselect t1.resource_type, 'database'=db_name(resource_database_id), 'blk object' = t1.resource_associated_entity_id, t1.request_mode, t1.request_session_id, t2.blocking_session_id from sys.dm_tran_locks as t1, sys.dm_os_waiting_tasks as t2where t1.lock_owner_address = t2.resource_address and t1.request_session_id = isnull(@spid,t1.request_session_id) 以下是使用此存储过程的示例。exec sp_blockexec sp_block @spid = 7 select sum((page_size_in_bytes/1024)*max_pages_allocated_count) from sys.dm_os_memory_objects;select * from sys.dm_os_sys_info;select * from sys.dm_os_performance_counters A. 获取有关按平均 CPU 时间排在最前面的五个查询的信息以下示例返回前五个查询的 SQL 语句文本和平均 CPU 时间。复制代码 SELECT TOP 5 total_worker_time/execution_count AS , SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_textFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stORDER BY total_worker_time/execution_count DESC;B. 提供批处理执行统计信息以下示例返回按批执行的 SQL 查询的文本，并提供有关它们的统计信息。复制代码 SELECT s2.dbid, s1.sql_handle, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END)- statement_start_offset) / 2+1))AS sql_statement, execution_count, plan_generation_num, last_execution_time, total_worker_time, last_worker_time, min_worker_time, max_worker_time, total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writesFROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2WHERE s2.objectid is null ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset; -- 数据库文件性能,文件io性能统计(必须重启sql server服务,才能清零该计数器) select DB_NAME(database_id) DB_NAME, file_id,io_stall_read_ms ,num_of_reads,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms',io_stall_write_ms,num_of_writes,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms',io_stall_read_ms + io_stall_write_ms as io_stalls,num_of_reads + num_of_writes as total_io,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'from sys.dm_io_virtual_file_stats(null,null)order by avg_io_stall_ms desc;数据库名文件ID用户等待文件中发出读取所用的总时间（毫秒）对文件发出的读取次数平均读文件等待(毫秒)用户等待在该文件中完成写入所用的总时间（毫秒）在该文件中写入的次数平均写文件等待毫秒(读+写)等待毫秒(读+写)次数(读+写)平均等待毫秒 -- 数据库文件性能,文件io性能统计(必须重启sql server服务,才能清零该计数器),-- 效果和"SQL Server 2005/2008 性能监控（待续）" 中 提到的一致。 SELECT DB_NAME(vfs.DbId) DatabaseName, mf.name,mf.physical_name, vfs.BytesRead, vfs.BytesWritten,vfs.IoStallMS, vfs.IoStallReadMS, vfs.IoStallWriteMS,vfs.NumberReads, vfs.NumberWrites,(Size*8)/1024>FROM ::fn_virtualfilestats(NULL,NULL) vfsINNER JOIN sys.master_files mf ON mf.database_id = vfs.DbIdAND mf.FILE_ID = vfs.FileIdGO -- 性能计数器动态视图,和使用windows性能计数器效果一致。 SELECT , FROM sys.dm_os_performance_countersWHERE ( = '' OR = '_Total') AND ( ( LIKE ('%Plan Cache%') AND IN ('Cache Hit Ratio', 'Cache Hit Ratio Base')) OR ( LIKE ('%Buffer Manager%') AND IN ('Buffer Cache Hit Ratio', 'Buffer Cache Hit Ratio Base', 'Page reads/sec', 'Page writes/sec')) OR ( LIKE ('%General Statistics%') AND IN ('Active Temp Tables', 'User Connections')) OR ( LIKE ('%Databases%') AND IN ('Transactions/sec', 'Log Cache Hit Ratio', 'Log Cache Hit Ratio Base', 'Log Flushes/sec', 'Log Bytes Flushed/sec', 'Backup/Restore Throughput/sec')) OR ( LIKE ('%Access Methods%') AND IN ('Full Scans/sec', 'Range Scans/sec', 'Probe Scans/sec', 'Index Searches/sec', 'Page Splits/sec')) OR ( LIKE ('%Memory Manager%') AND IN ('Target Server Memory (KB)', 'Target Server Memory(KB)', 'Total Server Memory (KB)')) OR ( LIKE ('%SQL Statistics%') AND IN ('SQL Compilations/sec', 'SQL Re-Compilations/sec')) ) -- 查看分区表money，各个分区的行数和边界值.select partition = $partition.分区函数名(userid) ,rows = count(*) ,minval = min(userid) ,maxval = max(userid)from dbo.money with(nolock) group by $partition.分区函数名(userid) order by partition; --查询某个数据库的连接数select count(*) from Master.dbo.SysProcesses where dbid=db_id()--前10名其他等待类型SELECT TOP 10 * from sys.dm_os_wait_stats ORDER BY wait_time_ms DESC SELECT *FROM sys.dm_os_wait_stats WHERE wait_type like 'PAGELATCH%' OR wait_type like 'LAZYWRITER_SLEEP%' --CPU的压力SELECT scheduler_id, current_tasks_count, runnable_tasks_count FROM sys.dm_os_schedulersWHERE scheduler_id < 255 --表现最差的前10名使用查询SELECT TOP10 ProcedureName = t.text, ExecutionCount = s.execution_count, AvgExecutionTime =isnull ( s.total_elapsed_time / s.execution_count, 0 ), AvgWorkerTime = s.total_worker_time / s.execution_count, TotalWorkerTime= s.total_worker_time, MaxLogicalReads= s.max_logical_reads, MaxPhysicalReads = s.max_physical_reads, MaxLogicalWrites = s.max_logical_writes, CreationDateTime = s.creation_time, CallsPerSecond =isnull ( s.execution_count /datediff (second , s.creation_time,getdate ()), 0 ) FROMsys.dm_exec_query_stats s CROSSAPPLY sys.dm_exec_sql_text( s.sql_handle )t ORDER BY s.max_physical_readsDESC SELECT SUM(signal_wait_time_ms) AS total_signal_wait_time_ms总信号等待时间 , SUM(wait_time_ms - signal_wait_time_ms) AS resource_wait_time_ms资源的等待时间,SUM(signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS ,SUM(wait_time_ms - signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS FROM sys.dm_os_wait_stats--一个信号等待时间过多对资源的等待时间那么你的CPU是目前的一个瓶颈。 --查看进程所执行的SQL语句 if (select COUNT(*) from master.dbo.sysprocesses) > 500begin select text,CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) from master.sys.sysprocessesaendselect text,a.* from master.sys.sysprocessesaCROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)where a.spid = '51'dbcc inputbuffer(53) with tbas(select blocking_session_id,session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requestsaCROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)),tb1 as(select a.*,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage*8 as 'memory_usage(KB)',total_scheduled_time,reads,writes,logical_reads from tb a inner join master.sys.dm_exec_sessions bon a.session_id=b.session_id)select a.*,connect_time,client_tcp_port,client_net_address from tb1 a inner join master.sys.dm_exec_connections b on a.session_id=b.session_id --当前进程数 select * from master.dbo.sysprocessesorder by cpu desc--查看当前活动的进程数 sp_who active--查询是否由于连接没有释放引起CPU过高 select * from master.dbo.sysprocesses where spid> 50and waittype = 0x0000and waittime = 0and status = 'sleeping 'and last_batch < dateadd(minute, -10, getdate())and login_time < dateadd(minute, -10, getdate()) --强行释放空连接 select 'kill ' + rtrim(spid) from master.dbo.sysprocesses where spid> 50and waittype = 0x0000and waittime = 0and status = 'sleeping 'and last_batch < dateadd(minute, -60, getdate())and login_time < dateadd(minute, -60, getdate()) --查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)select spid,cmd,cpu,physical_io,memusage,(select top 1 from ::fn_get_sql(sql_handle)) sql_textfrom master.dbo.sysprocesses order by cpu desc,physical_io desc--查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局SELECT TOP 100 usecounts, objtype, p.size_in_bytes,. 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 SELECT top 25 qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectidFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(sql_handle) as qtWHERE plan_generation_num >1ORDER BY qs.plan_generation_num --cpu使用前50的语句SELECT top 50 qt.text AS SQL_text ,SUM(qs.total_worker_time) AS total_cpu_time,SUM(qs.execution_count) AS total_execution_count,SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,COUNT(*) AS number_of_statementsFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtGROUP BY qt.textORDER BY total_cpu_time DESC --统计总的CPU时间--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间-- 计算可运行状态下的工作进程数量SELECT COUNT(*) as workers_waiting_for_cpu,s.scheduler_idFROM sys.dm_os_workers AS oINNER JOIN sys.dm_os_schedulers AS sON o.scheduler_address=s.scheduler_addressAND s.scheduler_id 0 --order by 记录数 descORDER BY 使用空间MB DESCDROP TABLE #tb --查询是否由于连接没有释放引起CPU过高 select * from master.dbo.sysprocesses where spid> 50and waittype = 0x0000and waittime = 0and status = 'sleeping 'and last_batch < dateadd(minute, -10, getdate())and login_time < dateadd(minute, -10, getdate()) --强行释放空连接 select 'kill ' + rtrim(spid) from master.dbo.sysprocesses where spid> 50and waittype = 0x0000and waittime = 0and status = 'sleeping 'and last_batch < dateadd(minute, -60, getdate())and login_time < dateadd(minute, -60, getdate()) ----查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)select spid,cmd,cpu,physical_io,memusage,(select top 1 from ::fn_get_sql(sql_handle)) sql_textfrom master.dbo.sysprocesses order by cpu desc,physical_io desc----查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局SELECT TOP 100 usecounts, objtype, p.size_in_bytes,. 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 SELECT top 25 qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectidFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(sql_handle) as qtWHERE plan_generation_num >1ORDER BY qs.plan_generation_numSELECT top 50 qt.text AS SQL_text ,SUM(qs.total_worker_time) AS total_cpu_time,SUM(qs.execution_count) AS total_execution_count,SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,COUNT(*) AS number_of_statementsFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtGROUP BY qt.textORDER BY total_cpu_time DESC --统计总的CPU时间--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间-- 计算可运行状态下的工作进程数量SELECT COUNT(*) as workers_waiting_for_cpu,s.scheduler_idFROM sys.dm_os_workers AS oINNER JOIN sys.dm_os_schedulers AS sON o.scheduler_address=s.scheduler_addressAND s.scheduler_id 数据库 > 你的业务DB > 安全性 > 架构"中新建. 2. 如果a用户,需要访问b用户的架构(Schema)对象，可以用grant select to a;如果要执行b用户架构下的存储过程，或函数, 触发器等，则除了要 grant execute to a之外，在b用户的创建过程ddl语句中，还要指定: WITH EXECUTE AS N'用户a'， 举一个实际应用的例子。-- 以ddl语句dbo_sp_helptext的执行权限给某特定的用户就可以了，它就可以查看别的用户的对象源码，但无法修改或删除别的用户的对象createasexecutesp_helptext @objname;create WITHEXECUTE AS N'dbo'beginif(charindex('&',@cmd)> 0) print error:不允许执行多条命令;end if (charindex('dir',rtrim(ltrim(@cmd))) = 1)begin execute @i_result= master..xp_cmdshell@cmd,@no_output;end begin return -1;endgo3. 如果A用户,需要创建自已的 job作业, 同时还需要查看其它用户的SQL Server Agent job信息 (SQL Server 代理 作业)，不能修改和删除别人的作业，则可以给它赋 msdb数据库的 "SQLAgentReaderRole"权限.具体参见“联机帮助” view plaincopy-- 导入用户数据库中的 用户及角色权限select 'CREATE USER [' + a.name+'] FOR LOGIN [' + b.name + ']' fromsys.database_principals a inner join sys.server_principals b on a.sid=b.sid select 'exec sp_addrolemember ''' + (select top 1 name from sys.database_principals where principal_id = s.role_principal_id) + ''', ''' + (select top 1 name from sys.database_principals where principal_id = s.member_principal_id) + ''';'from sys.database_role_members s where member_principal_id > 4;
页:
[1]