select * from sys.configurations where name='XX'

select * from sys.all_objects --查詢當(dāng)前數(shù)據(jù)庫的所有架構(gòu)范圍的對(duì)象select * from sys.sysobjects --查詢當(dāng)前數(shù)據(jù)庫的所有對(duì)象--sys.all_objects,、sys.sysobjects 這種的視圖，在每個(gè)數(shù)據(jù)庫的系統(tǒng)視圖下面都有
select * from sys.databases --在當(dāng)前數(shù)據(jù)庫下可以查詢到所有數(shù)據(jù)庫信息，包含是否on狀態(tài)select * from sys.sysdatabases --在當(dāng)前數(shù)據(jù)庫下可以查詢到所有數(shù)據(jù)庫信息,，不包含是否on狀態(tài),，這個(gè)系統(tǒng)視圖會(huì)在后續(xù)的版本中刪除--sys.databases、sys.sysdatabases這種的視圖,，在每個(gè)數(shù)據(jù)庫的系統(tǒng)視圖下面都有sys.processes --沒有這個(gè)視圖select * from sys.sysprocesses --在當(dāng)前數(shù)據(jù)庫下可以查詢所有正在SQL Server 實(shí)例上運(yùn)行的進(jìn)程的相關(guān)信息,，也就是所有數(shù)據(jù)庫上的線程，這個(gè)系統(tǒng)視圖會(huì)在后續(xù)的版本中刪除

dbo.backupsetdbo.log_shipping_secondarydbo.restorehistorydbo.sysjobsdbo.sysjobhistory--這些系統(tǒng)表只存在msdb數(shù)據(jù)庫，使用的時(shí)候必須加上msdb前綴

sp_helptext pro_name

DBCC INPUTBUFFER (249)

DBCC SQLPERF (LOGSPACE)

DBCC LOGINFO

CHECKPOINT [ checkpoint_duration ]--checkpoint_duration表示以秒為單位指定手動(dòng)檢查點(diǎn)完成所需的時(shí)間，一般不使用這個(gè)參數(shù)，讓數(shù)據(jù)庫自己控制

select DB_NAME(dbid),* from sys.sysprocesses where dbid=db_id('dbname')

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableNamefrom sys.dm_tran_locks where resource_type='OBJECT' ORDER BY request_session_id ASC

IF EXISTS (SELECT TOP 1 1FROM sys.sysprocessesWHERE program_name = 'SQLAgent - Generic Refresher')SELECT 'Running'ELSESELECT 'Not Running'

SELECT DB_NAME(database_id) AS Exec_DB,percent_complete,CASE WHEN estimated_completion_time < 36000000THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining],b.text as tsql,*FROM SYS.DM_EXEC_REQUESTScross apply sys.dm_exec_sql_text(sql_handle) as bWHERE command LIKE 'Backup%' --and database_id=db_id('cardorder')--OR command LIKE 'RESTORE%'ORDER BY 2 DESC

SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date FROM msdb.dbo.backupset GROUP BY database_name,type ORDER BY database_name,type備注：D 表示全備份,，i 表示差異備份，L 表示日志備份

SELECT TOP 1000       S.database_name [Database],       CASE [S].[type]             WHEN 'L'             THEN N'RESTORE LOG ' + QUOTENAME(S.database_name) + N' FROM DISK = ''' + F.physical_device_name + N''' WITH NORECOVERY;'       END [LogRestore],       F.physical_device_name,       S.[Type],       S.backup_start_date,       S.backup_finish_date FROM msdb.dbo.backupmediafamily F INNER JOIN msdb.dbo.backupset S ON S.media_set_id = F.media_set_id WHERE S.database_name = 'XX' AND       S.type = 'L' AND S.backup_start_date > 'YYYY-MM-DD' ORDER BY S.backup_start_date ASC

SELECTdb_name(database_id),mirroring_state_desc,mirroring_role_desc,mirroring_partner_name,mirroring_partner_instanceFROM sys.database_mirroring

SELECT db_name(database_id),* FROM master.sys.master_files WHERE database_id =DB_ID(N'DBA');

SELECT sum(size*8/1024/1024) FROM master.sys.master_files

SELECT b.name dbname,a.size*8/1024/1024 sum_GB,a.type_desc,a.name datafilename,a.physical_name FROM master.sys.master_files a join sys.sysdatabases b on a.database_id=b.dbid and a.physical_name like 'G:\DEFAULT.DATA%' and a.size*8/1024/1024>8

SELECT TOP 3total_worker_time/1000 AS [總消耗CPU 時(shí)間(ms)],execution_count [運(yùn)行次數(shù)],qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 時(shí)間(ms)],last_execution_time AS [最后一次執(zhí)行時(shí)間],max_worker_time /1000 AS [最大執(zhí)行時(shí)間(ms)],SUBSTRING(qt.text,qs.statement_start_offset/2+1,(CASE WHEN qs.statement_end_offset = -1THEN 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) ObjectNameFROM sys.dm_exec_query_stats qs WITH(nolock)CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qtWHERE execution_count>1 and last_execution_time>dateadd(dd,-5,getdate())ORDER BY total_worker_time DESC

SELECT TOP 10session_id,request_id,start_time AS '開始時(shí)間',status AS '狀態(tài)',command AS '命令',d_sql.text AS 'sql語句', DB_NAME(database_id) AS '數(shù)據(jù)庫名',blocking_session_id AS '正在阻塞其他會(huì)話的會(huì)話ID',wait_type AS '等待資源類型',wait_time AS '等待時(shí)間',wait_resource AS '等待的資源',reads AS '物理讀次數(shù)',writes AS '寫次數(shù)',logical_reads AS '邏輯讀次數(shù)',row_count AS '返回結(jié)果行數(shù)'FROM sys.dm_exec_requests AS d_requestCROSS APPLYsys.dm_exec_sql_text(d_request.sql_handle) AS d_sqlWHERE session_id>50ORDER BY cpu_time DESC--前50號(hào)session_id一般是系統(tǒng)后臺(tái)進(jìn)程,，sys.dm_exec_requests的status顯示為background

EXECUTE msdb.dbo.sp_start_job N'job_name' 

SELECT IDENT_SEED ('表名')

SELECT IDENT_CURRENT('表名')

select SERVERPROPERTY('machinename'),@@SERVERNAME,SERVERPROPERTY ('edition'),@@version

use dbname grant execute to 'username'

select * from sys.dm_hadr_instance_node_map

select * from sys.dm_hadr_name_id_map

select * from sys.availability_group_listeners;

select db_name(database_id),log_send_queue_size/1024 delay_M,* from sys.dm_hadr_database_replica_states where is_primary_replica=0;

select ar.replica_server_name, db_name(drs.database_id),drs.truncation_lsn, drs.log_send_queue_size, drs.redo_queue_size from sys.dm_hadr_database_replica_states drs join sys.availability_replicas ar on drs.replica_id=ar.replica_id where drs.is_local=0;
select ar.replica_server_name, db_name(drs.database_id),drs.truncation_lsn, drs.log_send_queue_size,drs.log_send_rate, drs.redo_queue_size,drs.redo_rate from sys.dm_hadr_database_replica_states drs join sys.availability_replicas ar on drs.replica_id=ar.replica_id where drs.is_local=0--log_send_queue_size 主數(shù)據(jù)庫中尚未發(fā)送到輔助數(shù)據(jù)庫的日志記錄量 (KB)--log_send_rate 在最后一個(gè)活動(dòng)期間，以千字節(jié) (KB) 的平均主副本發(fā)送實(shí)例數(shù)據(jù)的速率/秒--redo_queue_size 在最后一個(gè)活動(dòng)期間,，以千字節(jié) (KB) 的平均主副本發(fā)送實(shí)例數(shù)據(jù)的速率/秒--redo_rate 平均千字節(jié) (KB) 中的給定輔助數(shù)據(jù)庫做的日志記錄速率 / 秒

select db_name(database_id),* from sys.database_filestream_options

SELECT FileTableRootPath()+[file_stream].GetFileNamespacePath(),name FROM testdb.dbo.table1

TABLOCKX SELECT * FROM table WITH (TABLOCKX)

HOLDLOCK SELECT * FROM table WITH (HOLDLOCK)

select a.article,a.source_object,a.destination_object,b.colid from (select article,article_id,source_object,destination_object from [distribution].[dbo].MSarticles where publication_id in ( select publication_id from [distribution].[dbo].MSpublications where publication='XX' ) ) a inner join (select * from replicate1.dbo.sysarticlecolumns) b on a.article_id=b.artid order by a.article

Select * from distribution.dbo.MSpublications 

select * from [distribution].[dbo].[MSlogreader_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE()) select * from [distribution].[dbo].[MSdistribution_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE()) select * from [distribution].[dbo].[MSsnapshot_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE()) select * from [distribution].[dbo].MSrepl_errors order by 2 descselect * from msdb.dbo.sysreplicationalerts order by 7 desc

SET SHOWPLAN_ALL ON; GO select XXX GO SET SHOWPLAN_ALL OFF; GO 或 SET SHOWPLAN_XML ON; GO select XXX GO SET SHOWPLAN_XML OFF; GO

exec sp_spaceused 'crm.EmailLog';

select * from sys.partition_functions

select * from msdb.dbo.sysssispackages

alter index all on table_name rebuild with (online=on)

alter index all on table_name reorganize

select name ,size*8/1024 as Totalsize_MB ,CAST(FILEPROPERTY(name,'SpaceUsed') AS int)*8/1024 as Usedsize_MB, size*8/1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)*8/1024 AS Availabesize_MB  from sys.master_files where database_id=db_id(N'DBNAME')

use XX if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tabName') and xtype='u') drop table #tabName go create table #tabName( table_name varchar(100), rowsNum varchar(100), reserved_size varchar(100), data_size varchar(100), index_size varchar(100), unused_size varchar(100) )   declare @name varchar(100) declare cur cursor for select name from sysobjects where xtype='u' order by name open cur fetch next from cur into @name while @@fetch_status=0 begin     insert into #tabName     exec sp_spaceused @name     fetch next from cur into @name end close cur deallocate cur 
select top 10 table_name, data_size,rowsNum ,index_size,unused_size ,reserved_size,convert(int,SUBSTRING(data_size,0,LEN(data_size)-2)) size from #tabName ORDER BY size desc

或 select top 10 a.tablename,a.SCHEMANAME,sum(a.TotalSpaceMB) TotalSpaceMB,sum(a.RowCounts) RowCounts from ( SELECT     t.NAME AS TableName,     s.Name AS SchemaName,     p.rows AS RowCounts,     SUM(a.total_pages) * 8 AS TotalSpaceKB,     CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,     SUM(a.used_pages) * 8 AS UsedSpaceKB,     CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,     (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,     CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB FROM     sys.tables t INNER JOIN           sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN     sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN     sys.schemas s ON t.schema_id = s.schema_id WHERE     t.NAME NOT LIKE 'dt%'     AND t.is_ms_shipped = 0     AND i.OBJECT_ID > 255 GROUP BY     t.Name, s.Name, p.Rows) a GROUP BY  a.tablename,a.SCHEMANAME order by sum(a.TotalSpaceMB) desc --這個(gè)比上一個(gè)專業(yè) 

SELECT DB_NAME(18) 

SELECT DB_NAME(database_id) AS Exec_DB,percent_complete,CASE WHEN estimated_completion_time < 36000000THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining],b.text as tsql,*FROM SYS.DM_EXEC_REQUESTScross apply sys.dm_exec_sql_text(sql_handle) as bWHERE command LIKE 'DbccFilesCompact%' --and database_id=db_id('cardorder')ORDER BY 2 DESC

SELECT         d.object_id ,         DB_NAME(d.database_id) DBName ,         OBJECT_NAME(object_id, database_id) 'SPName' ,         d.cached_time ,         d.last_execution_time ,         d.total_elapsed_time/1000000    AS total_elapsed_time,         d.total_elapsed_time / d.execution_count/1000000                                         AS [avg_elapsed_time] ,         d.last_elapsed_time/1000000     AS last_elapsed_time,         d.execution_count ,         d.total_physical_reads ,         d.last_physical_reads ,         d.total_logical_writes ,         d.last_logical_reads ,         et.text SQLText ,         eqp.query_plan executionplan FROM    sys.dm_exec_procedure_stats AS d CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) et CROSS APPLY sys.dm_exec_query_plan(d.plan_handle) eqp WHERE   OBJECT_NAME(object_id, database_id) = 'xxxx' ORDER BY [total_worker_time] DESC; 

select * from Sys.traces