常用SQL记录

```sql
//收缩索引碎片
set nocount on 
  use RollingStock
  dbcc showcontig(customerinfo) with all_INDEXES

//------------------------------------------------------重建索引------------------------------------------------------
 ALTER INDEX 索引名 on 表名 REBUILD
//------------------------------------------------------重新组织索引------------------------------------------------------
  ALTER INDEX 索引名 on 表名 reorganize
//------------------------------------------------------查询SQL耗时最多------------------------------------------------------
SELECT TOP 1000
        ST.text AS '执行的SQL语句' ,
        s.execution_count AS '执行次数' ,
        s.total_elapsed_time AS '耗时' ,
        s.total_logical_reads AS '逻辑读取次数' ,
        s.total_logical_writes AS '逻辑写入次数' ,
        s.total_physical_reads AS '物理读取次数' ,
        s.creation_time AS '执行时间'
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) ST
WHERE   s.creation_time BETWEEN DATEADD(dd, -1, GETDATE())
                        AND     GETDATE()
ORDER BY s.total_elapsed_time DESC
//------------------------------------------------------删除数据库（批量删除）------------------------------------------------------

 
   declare @onecount int
set @onecount=100000
print getdate()
while 1=1
begin
     delete top(@onecount) from tb_TurnHeader where TurnHeader_WorkTime<'2018-09-03' ;
     
     IF (@@rowcount<@onecount) BREAK;
     print getdate()
end
//-----------------------------------------------------收缩数据库日志------------------------------------------------------

use master
go
alter database Lishen set recovery simple with no_wait
go
alter database Lishen set recovery simple
go
--收缩日志文件
use Lishen
go
--查询文件信息
select * from sysfiles
go
--收缩日志文件为指定大小2MB
dbcc shrinkfile('kinta_log',2,truncateonly)
go
--还原数据库恢复模式为完全模式
use master
go
alter database Lishen set recovery full with no_wait
alter database Lishen set recovery full