常用SQL

1.查询CPU平均用时最高5个语句

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

2.查找进程具体语句

SELECT r.session_id,t.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE session_id = 64
dbcc inputbuffer(65)

3.查看查询进程与SQL

select p.spid,p.cpu,p.physical_io,c.client_net_address,p.hostprocess,p.hostname,p.program_name,t.text from master..sysprocesses p
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS t
left join sys.dm_exec_connections c on c.session_id=p.spid
where spid>50
order by p.cpu desc,p.physical_io desc

–dbcc ind(test,student,-1)–dbcc ind(test,student,-1)–DBCC TRACEON(3604,-1)–dbcc page(test,1,380,2)–DBCC WRITEPAGE(‘test’, 1, 380, 100, 10, 0x65656565656565656565) –restore database commbak –from disk ‘e:\\数据库\\hisbak\\COMM-201707062246.bak’

–USE [master]
–RESTORE DATABASE [COMMBAK] FROM  DISK = N’E:\数据库\hisbak\COMM-201707062246.bak’ WITH  FILE = 1,  MOVE N’COMM’ TO N’E:\DATA\COMMBAK.mdf’,  MOVE N’COMM_log’ TO N’E:\DATA\COMMBAK_1.ldf’,  NOUNLOAD,  STATS = 5
–DBCC CHECKDB
DBCC IND(‘COMMBAK’,’DICT.OUT_PATS’,-1)
SELECT * FROM SYS.partitions where object_id=OBJECT_ID(‘dict.out_pats’) and index_id=1select count(*) from dict.OUT_PATS
select * from sys.indexes where object_id=object_id(‘dict.out_pats’)
select * from sys.allocation_units
select * from sys.database_files
dbcc traceon(1304,-1)

 

–查看未公布DBCCdbcc traceon(2588,-1)dbcc help(‘traceon’)–追踪死锁 DBCC errorlog DBCC TRACEON (1204, 1222, -1); DBCC tracestatus–查看页数据dbcc traceon(3604,-1)dbcc ind(‘commbak’,’dict.out_pats’,1)SELECT * FROM SYS.partitions where object_id=OBJECT_ID(‘dict.out_pats’) and index_id=1dbcc page(‘commbak’,1,1527,3)dbcc page(‘commbak’,1,1,3) WITH TABLERESULTSDBCC TRACEOFF (3604,-1)—-清除日志– DBCC errorlog– dbcc help(‘errorlog’)– exec sp_cycle_errorlog

–导入存储过程或DBCC结果语句 SELECT a.*  into #lzq FROM OPENROWSET(‘SQLOLEDB’,’10.68.4.29′;’sa’;’m@ssunsoft009′,
‘exec msdb.dbo.sp_help_job ‘) AS a
–SELECT * INTO #t2 FROM OPENQUERY(HISDB, ‘select TOP 10 * from ZY.[IN].PAT_IN_HOSPITAL’) SELECT * FROM OPENQUERY(HISDB, ‘exec(”dbcc ind(””zyrisdb””,””patient_report””,-1)”)’)

SELECT a.*  FROM OPENROWSET(‘SQLNCLI’, ‘Server=.;Trusted_Connection=yes;’,       ‘exec(” dbcc log(””master””,3) ” )’) AS a;
dbcc showcontig(‘patient_registration’)

 

IF EXISTS ( SELECT  *            FROM    sysobjects            WHERE   id = OBJECT_ID(N'[dbo].[Customers]’)                    AND OBJECTPROPERTY(id, N’IsUserTable’) = 1 )    DROP TABLE dbo.Customers
CREATE TABLE Customers(   FirstName CHAR(50) NOT NULL,   LastName CHAR(50) NOT NULL,   Address CHAR(100) NOT NULL,   ZipCode CHAR(5) NOT NULL,   Rating INT NOT NULL,   ModifiedDate DATETIME NOT NULL,)GO
insert into dbo.Customersselect * from dbo.Customers
INSERT INTO dbo.Customers        ( FirstName ,          LastName ,          Address ,          ZipCode ,          Rating ,          ModifiedDate        )VALUES  ( ‘Woody’ , — FirstName – char(50)          ‘Tu’ , — LastName – char(50)          ‘ZUOQIAO YOUXI TOWN LINHAI CITY’ , — Address – char(50)          ‘0000’ , — ZipCode – char(5)          1 , — Rating – int          ‘2015-05-07 10:09:51’  — ModifiedDate – datetime        )
dbcc ind(‘commbak’,’customers’,-1)   dbcc page(‘commbak’,1,4196,3)
select * from sys.system_internals_allocation_units where  container_id=’72057594469613568′
select * from sys.allocation_units where container_id=’72057594469613568′
select * from sys.partitions  where partition_id=’72057594469613568′

Leave a Reply

Your email address will not be published. Required fields are marked *