sql server 数据库优化

  ----修改表字段长度    Alter Table gsp039 Alter Column spmch Varchar(100) Not Null–查询某个数据库的连接数select count(*) from Master.dbo.SysProcesses where dbid=db_id()–前10名其他等待类型SELECT TOP 10 *from sys.dm_os_wait_statsORDER BY wait_time_ms DESCSELECT *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_countFROM sys.dm_os_schedulersWHERE scheduler_id–表现最差的前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 sCROSS  APPLY sys.dm_exec_sql_text( s.sql_handle )  t ORDER BYs.max_physical_reads  DESCSELECT 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) > 500beginselect text,CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) from master.sys.sysprocesses  aendselect 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_readsfrom 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.sysprocesseswhere spid> 50and waittype = 0×0000and waittime = 0and status = ‘sleeping ‘and last_batchand login_time–强行释放空连接select ‘kill ‘ + rtrim(spid) from master.dbo.sysprocesseswhere spid> 50and waittype = 0×0000and waittime = 0and status = ‘sleeping ‘and last_batchand login_time–查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)select spid,cmd,cpu,physical_io,memusage,(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_textfrom master..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) sqlORDER BY usecounts,p.size_in_bytes descSELECT 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_idWHERE o.state=’RUNNABLE’GROUP BY s.scheduler_id–表空间大小查询create table #tb(表名 sysname,记录数 int,保留空间 varchar(100),使用空间 varchar(100),索引使用空间 varchar(100),未用空间 varchar(100))insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused ''?'''select * from #tbgoSELECT表名,记录数,cast(ltrim(rtrim(replace(保留空间,'KB',''))) as int)/1024 保留空间MB,cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024 使用空间MB,cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024/1024.00 使用空间GB,cast(ltrim(rtrim(replace(索引使用空间,'KB',''))) as int)/1024 索引使用空间MB,cast(ltrim(rtrim(replace(未用空间,'KB',''))) as int)/1024 未用空间MBFROM #tbWHERE cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024 > 0 --order by 记录数 descORDER BY 使用空间MB DESCDROP TABLE #tb–强行释放空连接select ‘kill ‘ + rtrim(spid) from master.dbo.sysprocesseswhere spid> 50and waittype = 0×0000and waittime = 0and status = ‘sleeping ‘and last_batchand login_time—-查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)select spid,cmd,cpu,physical_io,memusage,(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_textfrom master..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) sqlORDER BY usecounts,p.size_in_bytes descSELECT 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_idWHERE o.state=’RUNNABLE’GROUP BY s.scheduler_id –查询是否由于连接没有释放引起CPU过高select * from master.dbo.sysprocesseswhere spid> 50and waittype = 0×0000and waittime = 0and status = ‘sleeping ‘and last_batchand login_time < dateadd(minute, -10, getdate())