sqlserver 一些用法

 

一、查看sqlserver数据库的参数配置

sp_configure
官网：http://technet.microsoft.com/zh-cn/library/ms188787%28v=SQL.110%29.aspx
可参考：http://blog.163.com/baoshan_net@126/blog/static/35409110200782031435758/

 举例：

本地sqlserver数据库经常性出现占用超过1.5G内存的情况，导致整体性能下降，无法执行其他操作。
为了系统的可用性，可强制降低sqlserver内存占用比例，方便其他操作。

查看方法：
exec sp_configure;--查看所有参数配置情况；

设置方法：
exec sp_configure 'max server memory (MB)', 800--注意，单位是M。
go
reconfigure with override
go

 

二、查看常规数据

 转自：sqlserver 巡检脚本

 

--1.查看数据库版本信息  
select @@version  


--2.查看所有数据库名称及大小  
exec sp_helpdb  


--3.查看数据库所在机器的操作系统参数  
exec master..xp_msver  


--4.查看数据库启动的参数  
exec sp_configure  


--5.查看数据库启动时间  
select convert(varchar(30),login_time,120)  
from master..sysprocesses where spid=1  


--6.查看数据库服务器名  
select 'Server Name:'+ltrim(@@servername)  


--7.查看数据库实例名  
select 'Instance:'+ltrim(@@servicename)   

--8.数据库的磁盘空间呢使用信息  
exec sp_spaceused  


--9.日志文件大小及使用情况  
dbcc sqlperf(logspace)  

--10.表的磁盘空间使用信息  
exec sp_spaceused 'tablename'  


--11.获取磁盘读写情况  
select   
@@total_read [读取磁盘次数],  
@@total_write [写入磁盘次数],  
@@total_errors [磁盘写入错误数],  
getdate() [当前时间]  

--12.获取I/O工作情况  
select @@io_busy [自上次启动的I/O操作毫秒数],  
@@timeticks [每个时钟周期对应的微秒数],  
@@io_busy*@@timeticks [I/O操作毫秒数],  
getdate() [当前时间]  

--13.查看CPU活动及工作情况  
select  
@@cpu_busy [自上次启动CPU的工作时间毫秒数],  
@@timeticks [每个时钟周期对应的微秒数],  
@@cpu_busy*cast(@@timeticks as float)/1000 [CPU工作时间(秒)],  
@@idle*cast(@@timeticks as float)/1000 [CPU空闲时间(秒)],  
getdate() [当前时间]  

--14.检查锁与等待  
exec sp_lock  



--15.检测死锁和阻塞
declare @spid int,@bl int,
@intTransactionCountOnEntry     int,
@intRowcount             int,
@intCountProperties         int,
@intCounter             int
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 print @@ERROR
insert into #tmp_lock_who(spid,bl) select  0 ,blocked
from (select * from sysprocesses where  blocked>0 ) a
where not exists(select * from (select * from sysprocesses
where  blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where  blocked>0
IF @@ERROR<>0 print @@ERROR

15 -- 找到临时表的记录数
select     @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 print @@ERROR
if    @intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select     @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被'
+ '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end
/
  
--16.用户和进程信息  
exec sp_who  
exec sp_who2  
  
--17.活动用户和进程的信息  
exec sp_who 'active'  




  
--19.查看所有数据库用户登录信息  
exec sp_helplogins   
  
--20.查看所有数据库用户所属的角色信息  
exec sp_helpsrvrolemember  
  
--21.查看链接服务器  
exec sp_helplinkedsrvlogin  
  
--22.查看远端数据库用户登录信息  
exec sp_helpremotelogin  
   
--23.获取网络数据包统计信息  
select   
@@pack_received [输入数据包数量],  
@@pack_sent [输出数据包数量],  
@@packet_errors [错误包数量],  
getdate() [当前时间]  

  
--24.检查数据库中的所有对象的分配和机构完整性是否存在错误  
dbcc checkdb  
  
--25.查询文件组和文件  
select   
    df.[name],df.physical_name,df.[size],df.growth,   
    f.[name][filegroup],f.is_default   
from sys.database_files df join sys.filegroups f   
on df.data_space_id = f.data_space_id   

 
--26.查看数据库中所有表的条数  
select  b.name as tablename ,    
        a.rowcnt as datacount    
from    sysindexes a ,    
        sysobjects b    
where   a.id = b.id    
        and a.indid < 2    
        and objectproperty(b.id, 'IsMSShipped') = 0   

  
--27.得到最耗时的前10条T-SQL语句  
;with maco as     
(       
    select top 10    
        plan_handle,    
        sum(total_worker_time) as total_worker_time ,    
        sum(execution_count) as execution_count ,    
        count(1) as sql_count    
    from sys.dm_exec_query_stats group by plan_handle    
    order by sum(total_worker_time) desc    
)    
select  t.text ,    
        a.total_worker_time ,    
        a.execution_count ,    
        a.sql_count    
from    maco a    
        cross apply sys.dm_exec_sql_text(plan_handle) t   
  
--28. 查看SQL Server的实际内存占用  
select * from sysperfinfo where counter_name like '%Memory%'  
  
  
--29.显示所有数据库的日志空间信息  
dbcc sqlperf(logspace)  
  
--30.收缩数据库  
dbcc shrinkdatabase(databaseName) 

 三、一些常用SQL

1、查询所有表名，各表字段名，字段类型，各字段的最大长度

select a.name,b.name,c.name,b.max_length 
from sys.tables a,sys.columns b,sys.types c
where a.object_id=b.object_id and b.user_type_id=c.user_type_id
order by a.name

 

2、查看sqlserver被锁的表以及如何解锁

查看被锁表：
select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   
from   sys.dm_tran_locks where resource_type='OBJECT'
 
spid   锁表进程 
tableName   被锁表名
 
解锁：
 
declare @spid  int 
Set @spid  = 20 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid  as varchar)
exec(@sql)

 

3、查看表并重建索引

1）DBCC SHOWCONTIG('表名')

详细用法说明可参考：dbcc showcontig查看表页面碎片程度

其中有些很重要的指标，如扫描密度、扫描碎片等。 最佳计数与实际计数相当时说明索引是比较好的，如相差太多，就必须可以重新建或组织索引。

2） 重建索引命令：

若指定表名： DBCC DBREINDEX (Goods, '',  70)

若对全库   ： exec sp_msforeachtable 'DBCC DBREINDEX(''?'')' 

 

   4、查询当前实例使用的内存状况  

234 select counter_name, cntr_value*1.0/1024/1024 Memory from master.sys.dm_os_performance_counters   
where counter_name in ('Target Server Memory (KB)','Total Server Memory (KB)') 

 

 

---

 

组策略 账户可以锁定内存
sql server 启用awe    最大6144mb
boot.ini 开启/pae

确认AWE已启用? exec sp_configure 'awe enabled'

设定SQL Server动态使用内存.