Useful Scripts for DBA activities.
1. Script to get Database backups for all databases for a specified Period.
-To find Buffer manager:
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page reads/sec'
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page writes/sec'
-To Check If xp_cmdshell is Enabled
SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM sys.configurations
WHERE name = N'xp_cmdshell' ;
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
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Q. How many tasks are currently waiting?
select
count(*)
from
sys.dm_os_waiting_tasks
This query will give you an idea of how many tasks
are waiting in the system. You can use this information to understand blocking
characteristics of your load
------------------------------------------------------------------------------------------------------------
Q. How many tasks that assigned to a worker
(thread/fiber) are waiting?
select
count(*)
from
sys.dm_os_waiting_tasks
where
wait_type <> 'THREADPOOL’
This query shows how many threads are actively
running in the system. Latter on I will show how to find out if number of
threads can be increased
What are the tasks waiting on?
select
wait_type,
count
(*)
from
sys.dm_os_waiting_tasks
group by
wait_type
order by
count
(*) desc
One can use this query to investigate possible
bottlenceks of an active load. This query groups tasks by wait type – it can’t
be directly use to identify the actual bottlenecks on the system. The query
gives you an idea about the wait characteristics of your load
Q. Does my load have an active resource bottleneck?
You can answer this question by looking at the
resource address that tasks are blocked on.
Keep in mind that not all wait types have resource associated with them.
select
resource_address,
count
(*)
from
sys.dm_os_waiting_tasks
WHERE
resource_address <> 0
group by
resource_address
order by
count
(*) desc
Q: Is my system can be possibly bottlenecked on
I/O?
You can answer this question by looking at the wait
type of tasks waiting on specifically you are interested in IO waits
select
*
from
sys.dm_os_waiting_tasks
where
wait_duration_ms > 20 AND
wait_type LIKE '%PAGEIOLATCH%'
You might want to change 20ms base on your I/O
subsystem
Q: Does my load have long waiting chains?
This information is particular interesting to
understand if a single tasks, for example one that generated long I/O, blocks
others. If this happens you will have a way to improve your scalability by
figuring how to remove or minimize chain length.
WITH TaskChain (
waiting_task_address,
blocking_task_address,
ChainId,
Level)
AS
(
-- Anchor member definition: use self join so that
we output
-- Only tasks that blocking others and remove
dupliates
SELECT
DISTINCT
A.waiting_task_address,
A.blocking_task_address,
A.waiting_task_address As ChainId,
0 AS
Level
FROM
sys.dm_os_waiting_tasks as A
JOIN
sys.dm_os_waiting_tasks as B
ON
A.waiting_task_address = B.blocking_task_address
WHERE
A.blocking_task_address IS NULL
UNION ALL
-- Recursive member definition: Get to the next
level waiting
-- tasks
SELECT
A.waiting_task_address,
A.blocking_task_address,
B.ChainId,
Level +
1
from
sys.dm_os_waiting_tasks AS A
JOIN
TaskChain AS B
ON
B.waiting_task_address = A.blocking_task_address
)
select
waiting_task_address,
blocking_task_address,
ChainId,
Level
from
TaskChain
order by
ChainId
If there are no chains, your load is not CPU bound
and you see long waits on THREADPOOL, you might improve your throughput by
increasing a number of threads in the system.
-----------------------------------------------------------------------------------------------------------------------------
In order to answer this question you have to find
out if your load is really CPU bounded.
Your load is really CPU bounded if a number of runnable tasks per each
scheduler always greater than 1 and all of your queries have correct plan. The latter statement is very important, your
load can be CPU bounded due to the fact that somehow optimizer generated bad
plan – it can happen if your statistics out of date or you tried to perform
handcrafted optimization. In this case you don’t want to run to Circuit City to
buy more CPUs right a way – you want to fix the plan. Here is the query to find
out average length of a runable queue on the system:
select
AVG (runnable_tasks_count)
from
sys.dm_os_schedulers
where
status = 'VISIBLE ONLINE'
Buying more CPUs has also to do with capacity
planning. You have to be very careful when performing capacity planning on
hardware with HT enabled – remember you don’t have extra physical CPUs. Keep in
mind that if your load runs at 60% CPU utilization - it doesn’t mean that you
have 40% of extra CPU capacity. You will be very surprise how fast CPU load
will jump from 60% to 80% and then even faster to 100% once you apply more and
more load.
-----------------------------------------------------------------------------------------------------------------------------
Q. What is affinity of my schedulers to CPUs?
select
scheduler_id,
CAST
(cpu_id as varbinary) AS scheduler_affinity_mask
from
sys.dm_os_schedulers
Does my machine have either hard or soft NUMA
configuration enabled?
select
CASE
count( DISTINCT parent_node_id)
WHEN 1
THEN 'NUMA disabled'
ELSE 'NUMA enabled'
END
from
sys.dm_os_schedulers
where parent_node_id <> 32
-----------------------------------------------------------------------------------------------------------------------------
Q. Should I configure SQL Server to use more
threads – sp_configure ‘max server threads’?
You can answer this question by looking at the work
queue length for each scheduler. If on average such value is above 1 then you
might benefit from adding more threads to the system but only if
A. Your load
currently is not CPU bounded (See info above on how to find out if your load is
CPU bound)
B. Your load currently doesn’t experience any other
heavy waits (If you add more threads in this case they will just end up waiting
as everyone else)
select
AVG (work_queue_count)
from
sys.dm_os_schedulers
where
status = 'VISIBLE ONLINE'
-----------------------------------------------------------------------------------------------------------------------------
Q: Is my system I/O bound?
You can answer this question by monitoring length
of I/O queues.
select
pending_disk_io_count
from
sys.dm_os_schedulers
If over time
they keep on growing or you are seeing periodic jumps or numbers stay
relatively high most likely your system is I/O bound. In order to identify the
cause you will have to dive further.
-----------------------------------------------------------------------------------------------------------------------------
Q. How many sockets does my machine have?
select
cpu_count/hyperthread_ratio AS sockets
from
sys.dm_os_sys_info
-----------------------------------------------------------------------------------------------------------------------------
Q. Is my machine hyper threaded?
Well unfortunately you can’t derive this
information using this DMV today though there is a column called
hyperthread_ratio. On the other hand this column can tell you:
-----------------------------------------------------------------------------------------------------------------------------
Q. How many either cores or logical CPU share the
same socket?
select
hyperthread_ratio AS
cores_or_logical_cpus_per_socket
from
sys.dm_os_sys_info
Q. Does my 32 bit system have /3GB or /Userva
switch in boot.ini?
select
CASE
WHEN virtual_memory_in_bytes / 1024 / (2048*1024)
< 1 THEN 'No switch'
ELSE '/3GB'
END
from sys.dm_os_sys_info
Q. How much physical memory my machine has?
select
physical_memory_in_bytes/1024 AS physical_memory_in_kb
from
sys.dm_os_sys_info
Q. How many threads/workers SQL Server would use if
the default value in sp_configure for max worker threads is zero:
select
max_workers_count
from
sys.dm_os_sys_info
Q. What is a size of AWE window or what is the max
amount of memory right now that can be used by QO, QE and Caches simultaneously
when SQL Server running in AWE mode?
select
bpool_visible
from
sys.dm_os_sys_info
-----------------------------------------------------------------------------------------------------------------------------
Q. Counting
Un-replicated Commands and Transactions-How to get the count of un-replicated transactions
and commands from tech distribution server without using system views.
With
MaxXact (ServerName, DistAgentName, PublisherDBID, XactSeqNo)
As (Select S.name, DA.name, DA.publisher_database_id, max(H.xact_seqno)
From distribution.dbo.MSdistribution_history H with(nolock)
Inner Join distribution.dbo.MSdistribution_agents DA with(nolock) On DA.id = H.agent_id
Inner Join master.sys.servers S with(nolock) On S.server_id = DA.subscriber_id
Group By S.name, DA.name, DA.publisher_database_id)
Select MX.ServerName, MX.DistAgentName, MX.PublisherDBID, COUNT(*) As TransactionsNotReplicated
From distribution.dbo.msrepl_transactions T with(nolock)
Right Join MaxXact MX On MX.XactSeqNo < T.xact_seqno And MX.PublisherDBID = T.publisher_database_id
Group By MX.ServerName, MX.DistAgentName, MX.PublisherDBID;
As (Select S.name, DA.name, DA.publisher_database_id, max(H.xact_seqno)
From distribution.dbo.MSdistribution_history H with(nolock)
Inner Join distribution.dbo.MSdistribution_agents DA with(nolock) On DA.id = H.agent_id
Inner Join master.sys.servers S with(nolock) On S.server_id = DA.subscriber_id
Group By S.name, DA.name, DA.publisher_database_id)
Select MX.ServerName, MX.DistAgentName, MX.PublisherDBID, COUNT(*) As TransactionsNotReplicated
From distribution.dbo.msrepl_transactions T with(nolock)
Right Join MaxXact MX On MX.XactSeqNo < T.xact_seqno And MX.PublisherDBID = T.publisher_database_id
Group By MX.ServerName, MX.DistAgentName, MX.PublisherDBID;
With MaxXact (ServerName, DistAgentName, PublisherDBID,
XactSeqNo)
As (Select S.name, DA.name, DA.publisher_database_id, max(H.xact_seqno)
From distribution.dbo.MSdistribution_history H with(nolock)
Inner Join distribution.dbo.MSdistribution_agents DA with(nolock) On DA.id = H.agent_id
Inner Join master.sys.servers S with(nolock) On S.server_id = DA.subscriber_id
Group By S.name, DA.name, DA.publisher_database_id)
Select MX.ServerName, MX.DistAgentName, MX.PublisherDBID, COUNT(*) As CommandsNotReplicated
From distribution.dbo.MSrepl_commands C with(nolock)
Right Join MaxXact MX On MX.XactSeqNo < C.xact_seqno And MX.PublisherDBID = C.publisher_database_id
Group By MX.ServerName, MX.DistAgentName, MX.PublisherDBID;
As (Select S.name, DA.name, DA.publisher_database_id, max(H.xact_seqno)
From distribution.dbo.MSdistribution_history H with(nolock)
Inner Join distribution.dbo.MSdistribution_agents DA with(nolock) On DA.id = H.agent_id
Inner Join master.sys.servers S with(nolock) On S.server_id = DA.subscriber_id
Group By S.name, DA.name, DA.publisher_database_id)
Select MX.ServerName, MX.DistAgentName, MX.PublisherDBID, COUNT(*) As CommandsNotReplicated
From distribution.dbo.MSrepl_commands C with(nolock)
Right Join MaxXact MX On MX.XactSeqNo < C.xact_seqno And MX.PublisherDBID = C.publisher_database_id
Group By MX.ServerName, MX.DistAgentName, MX.PublisherDBID;
bs.backup_finish_date,bs.is_copy_only,bf.backup_size,bs.user_name,
bf.logical_name,bs.server_name,bs.machine_name,bs.type
from msdb..backupmediafamily bmf join msdb..backupmediaset bms
on bmf.media_set_id =bms.media_set_id
join msdb..backupset bs on bms.media_set_id =bs.media_set_id
join msdb..backupfile bf on bs.backup_set_id =bf.backup_set_id
--where bs.type='D'
where datediff(d,backup_start_date,getdate())<=5 and bs.database_name='Taxwarehouse'
--and bs.user_name='REDMOND\v-jadadi'
order by bs.backup_start_date desc
-------------------------------------------------------------------------------------------------------------------------
Buffer Cache Hit Ratio
Buffer Cache Hit Ratio shows how SQL Server utilizes buffer cache
“Percent of page requests satisfied by data pages from the buffer pool”
SELECTobject_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio'
The recommended value for Buffer Cache Hit Ratio is over 90. A lower value indicates a memory problem.
-------------------------------------------------------------------------------------------------------------------------
Page Life Expectancy
“Duration, in seconds, that a page resides in the buffer pool”
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy'
The normal values are above 300 seconds (5 minutes) and the trend line should be stable. If the value is below 300, it’s a clear indication that something is wrong
Also, a value drop of more than 50% is a sign for deeper investigation.
------------------------------------------------------------------------------------------------------------
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;
------------------------------------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------------------------------------------------------------------------------------
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