Get table row count for all tables in a database
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
ORDER BY row_count DESC
------------------------------------------------------------------------------------------------------------
Queries taking longest elapsed time:
SELECT TOP 100 qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds
, qs.total_elapsed_time / 1000000.0 AS total_seconds
, qs.execution_count
, SUBSTRING (qt.text,qs.statement_start_offset/2
, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query
, o.name AS object_name
, DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN
sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER
BY average_seconds DESC;
------------------------------------------------------------------------------------------------------------
Queries doing most I/O:
SELECT TOP 100
(total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
(total_logical_reads + total_logical_writes) AS total_IO,
qs.execution_count AS execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS indivudual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
ORDER BY average_IO DESC;
------------------------------------------------------------------------------------------------------------
TO FIND HOW
MUCH % BACKUP
COMPLETED?
SELECT command,
percent_complete,
'elapsed'
= total_elapsed_time /
60000.0,
'remaining'
= estimated_completion_time / 60000.0
FROM sys.dm_exec_requests
WHERE command like
'BACKUP%'
------------------------------------------------------------------------------------------------------------
Sample
code to trace locks:
SELECT
L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id
AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM
sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id =
TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id =
AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE
resource_database_id = db_id()
ORDER BY L.request_session_id
------------------------------------------------------------------------------------------------------------
Script for monitor blocking in my prod server ....
select loginame,cpu,memusage,physical_io,* from master..sysprocesses a where exists ( select b.* from master..sysprocesses b where b.blocked > 0 and b.blocked = a.spid ) and not exists ( select b.* from master..sysprocesses b where b.blocked > 0 and b.spid = a.spid )order by spid
----------------------------------------------------------------------------------------------------------------------------------------------------------------
T-SQL Query to find currently running jobs
JA.Start_execution_date As Starting_time,
datediff(ss, JA.Start_execution_date,getdate()) as [Has_been_running(in Sec)]
FROM msdb.dbo.sysjobactivity JA<a name="more"></a>
JOIN msdb.dbo.sysjobs J
ON J.job_id=JA.job_id
WHERE job_history_id is null
AND start_execution_date is NOT NULL
ORDER BY start_execution_date
--------------------------------------------------------------------------------------------------------------------------------------------------------------
To Drop All Linked Server:
DECLARE db_cursor CURSOR FOR
select 'sp_dropserver ''' + [name] + '''' from sys.servers
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@sql)
FETCH NEXT FROM db_cursor INTO @sql
END
CLOSE db_cursor
DEALLOCATE db_cursor
Note: It will delete even your local server details also.
--------------------------------------------------------------------------------------------------------------------------------------------------------------
If you not found local server details in sys.servers
sp_addserver 'Servername', local
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Robocopy:
Robocopy.exe E:\MSSQL\BAK(source) "\\<server_name>\Latest_BKP"(destination) <Backup_File_name.BAK> /mt:50--------------------------------------------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment