sqlserver 处理效率问题常用语句（索引相关等等）

–查索引碎片

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE ind.name in( 'IDX_DCEI_COMP_YEAR_MONTH_new','pk_dept_cost_emp_ini','PK_SYS_DEPT','PK__sys_emp__1299A86160C0635B','PK_SYS_EMP_TYPE','PK__sys_emp___1062D97663E87F77')--indexstats.avg_fragmentation_in_percent > 50
ORDER BY indexstats.avg_fragmentation_in_percent DESC

–清执行计划缓存

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

–创建临时表

select * into dept_cost_emp_ini from aa

–查锁表

select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   
from   sys.dm_tran_locks where resource_type='OBJECT'

DECLARE
 @spid INT
 SET @spid = 52--锁表进程
DECLARE
 @SQL VARCHAR (1000)
 SET @SQL = 'kill ' + CAST (@spid AS VARCHAR) EXEC (@SQL)

–所有索引重建

USE My_Database; 
DECLARE @name varchar(100)

DECLARE authors_cursor CURSOR FOR  Select [name]   from sysobjects where xtype='u' order by id

OPEN authors_cursor

FETCH NEXT FROM authors_cursor  INTO @name

WHILE @@FETCH_STATUS = 0 
BEGIN    

　DBCC DBREINDEX (@name, '', 90)

 FETCH NEXT FROM authors_cursor     INTO @name 
END

deallocate authors_cursor

–查询所有的表名及空间占用量情况

SELECT  OBJECT_NAME(id) tablename ,
         8 * reserved / 1024 reserved ,
         RTRIM(8 * dpages) AS 'used(kb)' ,
         8 * ( reserved - dpages ) / 1024 unused ,
         8 * dpages / 1024 - rows / 1024 * minlen / 1024 free
 FROM    sysindexes
 WHERE   indid = 1
 ORDER BY reserved DESC 

–查询数据库中所有的表名及行数

SELECT  a.name ,  b.rows  FROM    sysobjects AS a
       INNER JOIN sysindexes AS b ON a.id = b.id
 WHERE   ( a.type = 'u' )  AND ( b.indid IN ( 0, 1 ) )
 ORDER BY b.rows DESC

—手动更新统计计划

update STATISTICS  dbo.dept_cost_emp_ini

—使用SQL语句检测执行慢的SQL语句：

SELECT 
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
(CASE WHEN qs.statement_end_offset = -1 
THEN DATALENGTH(qt.text) 
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) 
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
AND total_worker_time>5000000
ORDER BY total_worker_time DESC

–修改密码
–如果提示‘帐户当前被锁定,所以用户 ‘用户名’ 登录失败。系统管理员无法将该帐户解锁’

ALTER LOGIN 用户名 ENABLE ;
GO
ALTER LOGIN 用户名 WITH PASSWORD = 'password' unlock, check_policy = off,
check_expiration = off ;
GO