SQL Server 常用命令 (自己参考,逐渐增加)

SQL Server 常用命令 (自己参考,逐渐增加)

SELECT servicename, instant_file_initialization_enabled FROM sys.dm_server_services WHERE filename LIKE ‘%sqlservr.exe%’;

alter database testdb set single_user with rollback immediate

alter database testdb set multi_user

这个是service level的单用户模式， 启动后用sqlcmd 连接数据库操作， 然后再正常模式重启sql service

Net stop "sql server (sql)”
Net start "sql server (sql)” /mSQLCMD

Sqlcmd -S ServerName\InstanceName -E

Net stop "sql server (sql)”
Net start "sql server (sql)”

需要查询filter的column放在table ()里面， 其他select的列但不需要filter （不在where里的), 放在include

select top 100 t.rownumber, te.name as EventClass, t.TextData, t.SPID, t.starttime,t.endTime,t.duration/1000000 as Duration_seconds,t.duration,
t.databasename, t.hostname, t.error, t.reads, t.writes, t.cpu, t.objectName, t.ApplicationName,t.LoginName
from trace1 t
inner join sys.trace_events TE ON T.EventClass = TE.trace_event_id
where --t.TextData not like ‘job%’ and
–t.TextData not like ‘Warning: The join order has been enforced because a local join hint is used%’ and
t.TextData not like ‘changed database context to%’ and TextData not like ‘core job details%’
and t.TextData not like ‘Changed language setting to us_english%’
–and te.name like ‘%error%’
–and t.rownumber > 14930

ALTER INDEX index_name ON table
REBUILD 或者reorganize, 就这样rebuild就可以了，

SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)；

可以设置自动增长的， 右击数据库，然后properties

set statistics IO,time ON

在sql server agent里可以看到所有的job, 是否enable， 还有job history

如果Index frangmentation 不很严重的话，比如低于30%的情况，sql server 也可以用 reorganize index, 高于30%的时候用rebuild

企业版的话，ALTER INDEX index_name ON table REBUILD WITH(ONLINE = ON)

还有在跑query以前可以用dbcc dropcleanbuffers , dbcc freeproccache ， 可以删除cached的已有的execution plan ，生成新的plan.