摘要： sys.fn_dblog返回当前数据库日志的活动部分的内容usedb1goifexists(select1fromsys.tableswherename='t')droptabletgocreatetablet(colint)godbcctraceon(3505,-1)---disable automatic checkpoint behaviorhttp://support.microsoft.com/kb/815436/en-usgocheckpointgoselect[CurrentLSN],[PreviousLSN],[PreviousPageLSN],Partitio 阅读全文

摘要： DROPTABLET_TESTCREATETABLET_TEST(IDINTIDENTITYPRIMARYKEY,STATUSBIT)INSERTINTOT_TESTSELECT0GO100000INSERTINTOT_TESTSELECT1CREATEINDEXIXF_T_TEST_STATUSONT_TEST(STATUS)WHERESTATUS=1UPDATESTATISTICST_TESTwithfullscanDBCCFREEPROCCACHE--正常查询SELECT*FROMT_TESTWHERESTATUS=1--参数化查询SP_EXECUTESQLN'SELECT*FR 阅读全文

摘要： 1 找出效率低的语句SELECTTOP10qs.total_worker_time,qs.total_logical_reads,qs.last_logical_writes,qs.plan_handle,qs.execution_count,(SELECTSUBSTRING(text,qs.statement_start_offset/2+1,(CASEWHENqs.statement_end_offset=-1THENLEN(CONVERT(nvarchar(max),text))*2ELSEqs.statement_end_offsetEND-qs.statement_start_off 阅读全文

摘要： SELECT CASE df.database_id WHEN DB_ID('distribution') THEN 'distDB' WHEN DB_ID('tempdb') THEN 'tempdb' --add other needed database ELSE 'otherDBs' END AS dbName , CASE df.type WHEN 0 THEN 'datafile' WHEN 1 THEN 'logfile' ELSE 'otherfile' 阅读全文

摘要： CREATETRIGGER[ddl_trig_database]ONALLSERVERFORDROP_DATABASEASDECLARE@dbVARCHAR(209)SET@db=(SELECT'DatabaseDropped:'+EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(229)'))RAISERROR(@db,16,1)WITHLOG 阅读全文