14 May 2019

DBA's Queries


List of all 'open tran' to stop deadlocks

SELECT

 [tst].[session_id] AS SessionID
 ,[des].[login_name] AS LoginName
 ,DB_NAME (tdt.database_id) AS DatabaseName
 ,[tdt].[database_transaction_begin_time] AS TransactionBeginTime
 ,[tdt].[database_transaction_log_bytes_used] AS LogBytesUsed    
 ,[mrsh].text AS QueryText
 ,[ph].[query_plan] AS QueryPlan
FROM sys.dm_tran_database_transactions [tdt]
JOIN sys.dm_tran_session_transactions [tst]
 ON [tst].[transaction_id] = [tdt].[transaction_id]
JOIN sys.[dm_exec_sessions] [des]
 ON [des].[session_id] = [tst].[session_id]
JOIN sys.dm_exec_connections [dec]
 ON [dec].[session_id] = [tst].[session_id]
LEFT OUTER JOIN sys.dm_exec_requests [der]
 ON [der].[session_id] = [tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([dec].[most_recent_sql_handle]) AS [mrsh]
OUTER APPLY sys.dm_exec_query_plan ([der].[plan_handle]) AS [ph]
ORDER BY [TransactionBeginTime] ASC


Top 5 slowest quries


SELECT TOP 5 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1 ) AS Query ,qp.query_plan ,qs.execution_count ,qs.total_logical_reads ,qs.last_logical_reads ,qs.total_logical_writes ,qs.last_logical_writes ,qs.total_worker_time ,qs.last_worker_time ,qs.total_elapsed_time ,qs.last_elapsed_time ,qs.last_execution_time FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY qs.total_logical_reads DESC


Tables without Primary Key


SELECT OBJECT_SCHEMA_NAME(tables.object_id,db_id()) AS SchemaName,
tables.name As TableName
FROM sys.tables tables join sys.indexes indexes
ON tables.object_id=indexes.object_id
WHERE indexes.is_primary_key=0
GO

Detach or Take Database offline



select name, state_desc from sys.databases where name='AdventureWorks2014'



USE [master]

GO
ALTER DATABASE [AdventureWorks2014] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2014'
GO


USE [master]

GO
CREATE DATABASE [AdventureWorks2014] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.DMSQL2014\MSSQL\DATA\AdventureWorks2014_Data.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.DMSQL2014\MSSQL\DATA\AdventureWorks2014_Log.ldf' )
 FOR ATTACH

GO


ALTER DATABASE AdventureWorks2014 SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE AdventureWorks2014 SET ONLINE WITH ROLLBACK IMMEDIATE


% Fragmentation

select TableName=object_name(dm.object_id)
 ,IndexName=i.name
 ,IndexType=dm.index_type_desc
 ,[%Fragmented]=avg_fragmentation_in_percent
 from sys.dm_db_index_physical_stats(db_id(),null,null,null,'sampled') dm
 join sys.indexes i on dm.object_id=i.object_id and dm.index_id=i.index_id
 order by avg_fragmentation_in_percent desc

No comments:

Post a Comment

Resource Governor

Resource Governor  is a feature that you can use to manage SQL Server workload and system resource consumption. Resource Governor enables y...