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
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
% 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