SQL Server Commands

–Performance tracking

sp_who2 65
DBCC opentran()
dbcc inputbuffer(71)

DECLARE @Spid TABLE
(
SIID VARCHAR (MAX), [Status] VARCHAR(MAX), [Login] VARCHAR(MAX),
HostName VARCHAR(MAX), BlkBy VARCHAR (MAX), DBName VARCHAR(MAX),
COMMAND NVARCHAR(MAX), CPUTime INT, DiskIO INT,
LastBatch VARCHAR(MAX), ProgramName NVARCHAR(MAX),
SPID VARCHAR (MAX), REQUESTID VARCHAR (MAX)
)
INSERT INTO @Spid EXEC sp_who2
SELECT * FROM @Spid
— Filter by database
WHERE DBName =’testdb’ and login=’ggadmin’
— Filter examples
–WHERE COMMAND like ‘DBCC%’
–WHERE BlkBy <> NULL
ORDER BY DiskIO DESC;

 

 

—CDC query:

SELECT TOP (1000) [start_lsn]
,[tran_begin_time]
,[tran_end_time]
,[tran_id]
,[tran_begin_lsn]
FROM [sparcsn4].[cdc].[lsn_time_mapping]
order by tran_begin_time desc;

 

select st.name as SourceTableName,ct.CDC_SchemaName, ct.CDC_TableName, ct.cdc_obj_id as CDC_ObjectID,ct.RowCounts,ct.CDC_Used_MB, ct.CDC_Unused_MB,ct.CDC_Total_MB
from (SELECT
s.Name AS CDC_SchemaName,
t.Name AS CDC_TableName,
t.object_id as cdc_obj_id,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS CDC_Used_MB,
CAST(ROUND((SUM(a.total_pages) – SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS CDC_Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS CDC_Total_MB
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
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
where s.Name =’cdc’ and t.name like ‘%CT’ and i.index_id=1
GROUP BY t.Name, s.Name, t.object_id,p.Rows) ct
inner join cdc.change_tables ch on ct.cdc_obj_id= ch.object_id
inner join sys.tables st on st.object_id=ch.source_object_id;

 

 

Leave a Reply

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