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;GO  CPU使用情况:  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_reads  as '总共io物理读取次数',total_logical_reads   as '总共逻辑读取次数',total_logical_writes  as '总共逻辑写次数',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 [Avg CPU Time],    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_writes  FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  WHERE 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服务,才能清零该计数器),-- 效果和&quot;SQL Server 2005/2008 性能监控（待续）&quot; 中 提到的一致。 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 [counter_name], [cntr_value] FROM sys.dm_os_performance_counters  WHERE ([instance_name] = '' OR [instance_name] = '_Total') AND (         ([object_name] LIKE ('%Plan Cache%') AND [counter_name] IN          ('Cache Hit Ratio', 'Cache Hit Ratio Base')) OR         ([object_name] LIKE ('%Buffer Manager%') AND [counter_name] IN          ('Buffer Cache Hit Ratio', 'Buffer Cache Hit Ratio Base', 'Page reads/sec', 'Page writes/sec')) OR         ([object_name] LIKE ('%General Statistics%') AND [counter_name] IN          ('Active Temp Tables', 'User Connections')) OR         ([object_name] LIKE ('%Databases%') AND [counter_name] IN          ('Transactions/sec', 'Log Cache Hit Ratio', 'Log Cache Hit Ratio Base', 'Log Flushes/sec',            'Log Bytes Flushed/sec', 'Backup/Restore Throughput/sec')) OR         ([object_name] LIKE ('%Access Methods%') AND [counter_name] IN          ('Full Scans/sec', 'Range Scans/sec', 'Probe Scans/sec', 'Index Searches/sec', 'Page Splits/sec')) OR         ([object_name] LIKE ('%Memory Manager%') AND [counter_name] IN          ('Target Server Memory (KB)', 'Target Server Memory(KB)', 'Total Server Memory (KB)')) OR         ([object_name] LIKE ('%SQL Statistics%') AND [counter_name] 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 TOP  10    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 ) FROM  sys.dm_exec_query_stats s    CROSS  APPLY sys.dm_exec_sql_text( s.sql_handle )  t ORDER BY     s.max_physical_reads  DESC      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 [signal_wait_percent信号等待％],SUM(wait_time_ms - signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [resource_wait_percent资源等待％]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.sysprocesses  aend  select text,a.* from master.sys.sysprocesses  aCROSS 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_requests  aCROSS 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> 50  and waittype = 0x0000  and waittime = 0  and 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> 50  and waittype = 0x0000  and waittime = 0  and 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 [text] 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,[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    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 DESC  DROP TABLE #tb    --查询是否由于连接没有释放引起CPU过高 select * from master.dbo.sysprocesses where spid> 50  and waittype = 0x0000  and waittime = 0  and 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> 50  and waittype = 0x0000  and waittime = 0  and 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 [text] 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,[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    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  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 数据库 > 你的业务DB > 安全性 > 架构&quot;中新建. 2. 如果a用户,需要访问b用户的架构(Schema)对象，可以用grant select to a;  如果要执行b用户架构下的存储过程，或函数, 触发器等，则除了要 grant execute to a之外，在b用户的创建过程ddl语句中，还要指定: WITH EXECUTE AS N'用户a'， 举一个实际应用的例子。  -- 以ddl语句dbo_sp_helptext的执行权限给某特定的用户就可以了，它就可以查看别的用户的对象源码，但无法修改或删除别的用户的对象createas  executesp_helptext @objname;create WITHEXECUTE AS N'dbo'begin  if(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;endgo  3. 如果A用户,需要创建自已的 job作业, 同时还需要查看其它用户的SQL Server Agent job信息 (SQL Server 代理 作业)，不能修改和删除别人的作业，则可以给它赋 msdb数据库的 &quot;SQLAgentReaderRole&quot;权限.  具体参见“联机帮助”    [sql] view plaincopy-- 导入用户数据库中的 用户及角色权限  select 'CREATE USER [' + a.name+'] FOR LOGIN [' + b.name + ']'    from  sys.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;