Archive for the ‘Sqlserver’ Category

nisa490zipu. If you don’t want to receive these emails from Facebook in the future or have your email address used for friend suggestions, please unsubscribe.
Facebook, Inc., Attention: Department 415, PO Box 10005, Palo Alto, CA 94303

email_open_log_pic.php?h=AQBDi0dlgf5IfnQKQT-T4_jQigZgZWzIqcnwOj6FMX749RNZN-vx4XgqYpE1xLaKYa_4ssEr7udKt9WFRP0G0dn4gRE&t=1

Script to Detect TempDB contention

Posted: December 26, 2012 in Sqlserver

Select session_id,
wait_type,
wait_duration_ms,
blocking_session_id,
resource_description,
ResourceType = Case
When Cast(Right(resource_description, Len(resource_description)
– Charindex(‘:’, resource_description, 3)) As Int) – 1 % 8088 = 0 Then ‘Is PFS Page’
When Cast(Right(resource_description, Len(resource_description)
– Charindex(‘:’, resource_description, 3)) As Int) – 2 % 511232 = 0 Then ‘Is GAM Page’
When Cast(Right(resource_description, Len(resource_description)
– Charindex(‘:’, resource_description, 3)) As Int) – 3 % 511232 = 0 Then ‘Is SGAM Page’
Else ‘Is Not PFS, GAM, or SGAM page’
End
From sys.dm_os_waiting_tasks
Where wait_type Like ‘PAGE%LATCH_%’
And resource_description Like ‘2:%’;

This script is to check the completion of database backup/restore or DBCC Shrink operations.

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)

AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],

CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],

CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,

CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)

FROM sys.dm_exec_sql_text(sql_handle)))

FROM sys.dm_exec_requests r WHERE command IN (‘BACKUP DATABASE ‘)

This script is to collect the stats information for a given database on all the indexes part. This query will help you to debug the performance issue related to slowness of database.

The very first step we do when it comes to database tunning is to check the updated statistics on all the indexes and update them if they are old.

——————————————Script——————————————————
Use
GO
Select db_id() as dbid,
case
when indid IN (0, 1) then convert (char (12), rows)
else (select rows from dbo.sysindexes i2 where i2.id = i.id and i2.indid in (0,1))
end as rowcnt,
case
when indid IN (0, 1) then rowmodctr
else convert (bigint, rowmodctr) + (select rowmodctr from dbo.sysindexes i2 where i2.id = i.id and i2.indid in (0,1))
end as row_mods,
case rows when 0 then 0 else convert (bigint,
case
when indid IN (0, 1) then convert (bigint, rowmodctr)
else rowmodctr + (select convert (bigint, rowmodctr) from dbo.sysindexes i2 where i2.id = i.id and i2.indid in (0,1))
end / convert (numeric (20,2), (select case convert (bigint, rows) when 0 then 0.01 else rows end from dbo.sysindexes i2 where i2.id = i.id and i2.indid in (0,1))) * 100)
end as pct_mod,
convert (nvarchar, u.name + ‘.’ + o.name) as objname,
case when i.status&0x800040=0x800040 then ‘AUTOSTATS’
when i.status&0x40=0x40 and i.status&0x800000=0 then ‘STATS’
else ‘INDEX’ end as type,
convert (nvarchar, i.name) as idxname, i.indid,
stats_date (o.id, i.indid) as stats_updated,
case i.status & 0x1000000 when 0 then ‘no’ else ‘*YES*’ end as norecompute,
o.id as objid , rowcnt, i.status
from dbo.sysobjects o, dbo.sysindexes i, dbo.sysusers u
where o.id = i.id and o.uid = u.uid and i.indid between 1 and 254 and o.type = ‘U’
order by pct_mod desc, convert (nvarchar, u.name + ‘.’ + o.name), indid
GO
———————————-Check the Outpu————————————

Once you see old statistics from the above query then you have to update them idvidually or all at once.

To update all the Stats at one shot please use the below query.

———————————Script to update all Stats————————-

EXEC sp_MSforeachtable ‘UPDATE STATISTICS ? WITH FULLSCAN’

————————————–END———————————————

————— SQL and OS Version information for current instance——————-
SELECT @@VERSION AS [SQL Version Info];

——————- Hardware information from SQL Server 2008————————— (Cannot distinguish between HT and multi-core)

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)], sqlserver_start_time
FROM sys.dm_os_sys_info;

————— Get sp_configure values for instance———————————

EXEC sp_configure ‘Show Advanced Options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure;

— Focus on
— backup compression default
— clr enabled
— lightweight pooling (should be zero)
— max degree of parallelism
— max server memory (MB)
— optimize for ad hoc workloads (should be 1)
— priority boost (should be zero)

—–File Names and Paths for TempDB and all user databases in instance—————

SELECT DB_NAME([dbid])AS [Database Name], fileid, [filename]
FROM sys.sysaltfiles
WHERE [dbid] > 4 AND [dbid] 32767
OR [dbid] = 2;

— Things to look at:
— Are data files and log files on different drives?
— Is everything on C: drive?
— Is TempDB on dedicated drives?
— Are there multiple data files?

——— Calculates average stalls per read, per write, and per total input/output for each database file.——————

SELECT DB_NAME(database_id) AS [Database Name], file_id ,io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms,
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null)
ORDER BY avg_io_stall_ms DESC;

— Helps determine which database files on the entire instance have the most I/O bottlenecks

—–Recovery model, log reuse wait description, and compatibility level for all databases on instance————–

SELECT [name] AS [Database Name], recovery_model_desc, log_reuse_wait_desc, [compatibility_level]
FROM sys.databases;

— Things to look at
— How many databases are on the instance?
— What recovery models are they using?
— What is the log reuse wait description?
— What compatibility level are they on?

—————————– Clear Wait Stats———————————–

DBCC SQLPERF(‘sys.dm_os_wait_stats’, CLEAR);

—— Isolate top waits for server instance since last restart or statistics clear—

WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN( ‘SLEEP_TASK’, ‘BROKER_TASK_STOP’,
‘SQLTRACE_BUFFER_FLUSH’, ‘CLR_AUTO_EVENT’, ‘CLR_MANUAL_EVENT’,
‘LAZYWRITER_SLEEP’)) — filter out additional irrelevant waits
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) – W1.pct < 95; — percentage threshold

— Common Significant Wait types with BOL explanations

— *** Network Related Waits ***
— ASYNC_NETWORK_IO Occurs on network writes when the task is blocked behind the network

— *** Locking Waits ***
— LCK_M_IX Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock
— LCK_M_IU Occurs when a task is waiting to acquire an Intent Update (IU) lock
— LCK_M_S Occurs when a task is waiting to acquire a Shared lock

— *** I/O Related Waits ***
— ASYNC_IO_COMPLETION Occurs when a task is waiting for I/Os to finish
— IO_COMPLETION Occurs while waiting for I/O operations to complete.
— This wait type generally represents non-data page I/Os. Data page I/O completion waits appear
— as PAGEIOLATCH_* waits
— PAGEIOLATCH_SH Occurs when a task is waiting on a latch for a buffer that is in an I/O request.
— The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
— PAGEIOLATCH_EX Occurs when a task is waiting on a latch for a buffer that is in an I/O request.
— The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.
— WRITELOG Occurs while waiting for a log flush to complete.
— Common operations that cause log flushes are checkpoints and transaction commits.
— PAGELATCH_EX Occurs when a task is waiting on a latch for a buffer that is not in an I/O request.
— The latch request is in Exclusive mode.
— BACKUPIO Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data

— *** CPU Related Waits ***
— SOS_SCHEDULER_YIELD Occurs when a task voluntarily yields the scheduler for other tasks to execute.
— During this wait the task is waiting for its quantum to be renewed.

— THREADPOOL Occurs when a task is waiting for a worker to run on.
— This can indicate that the maximum worker setting is too low, or that batch executions are taking
— unusually long, thus reducing the number of workers available to satisfy other batches.
— CX_PACKET Occurs when trying to synchronize the query processor exchange iterator
— You may consider lowering the degree of parallelism if contention on this wait type becomes a problem

————-Signal Waits for instance———————————————–

SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits],
CAST(100.0 * SUM(wait_time_ms – signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits]
FROM sys.dm_os_wait_stats;

— Signal Waits above 10-15% is usually a sign of CPU pressure

————– Get CPU Utilization History for last 30 minutes———————

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(30) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 – SystemIdle – SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now – [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int')
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%%’) AS x
) AS y
ORDER BY record_id DESC;

——— Page Life Expectancy (PLE) value for default instance————————

SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = ‘SQLServer:Buffer Manager’ — Modify this if you have named instances
AND counter_name = ‘Page life expectancy’;

— PLE is a good measurement of memory pressure.
— Higher PLE is better. Below 300 is generally bad.
— Watch the trend, not the absolute value.

—— Get Buffer cache hit ratio (higher is better)——————————–

SELECT ROUND(CAST(A.cntr_value1 AS NUMERIC) / CAST(B.cntr_value2 AS NUMERIC),
3) AS [Buffer Cache Hit Ratio]
FROM ( SELECT cntr_value AS [cntr_value1]
FROM sys.dm_os_performance_counters
WHERE object_name = ‘SQLServer:Buffer Manager’ — Modify this if you have named instances
AND counter_name = ‘Buffer cache hit ratio’
) AS A,
(SELECT cntr_value AS [cntr_value2]
FROM sys.dm_os_performance_counters
WHERE object_name = ‘SQLServer:Buffer Manager’ — Modify this if you have named instances
AND counter_name = ‘Buffer cache hit ratio base’
) AS B;

— Buffer cache hit ratio is another measure of memory pressure.
— A higher value is better. Below 95% is generally bad.
— Watch the trend, not the absolute value.

—————-Buffer Pool Usage for instance————————————-

SELECT TOP(20) [type], SUM(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(single_pages_kb) DESC;

— CACHESTORE_SQLCP SQL Plans – These are cached SQL statements or batches that aren’t in
— stored procedures, functions and triggers
— CACHESTORE_OBJCP Object Plans – These are compiled plans for stored procedures,
— functions and triggers
— CACHESTORE_PHDR Algebrizer Trees – An algebrizer tree is the parsed SQL text that
— resolves the table and column names

———— Find single-use, ad-hoc queries that are bloating the plan cache———

SELECT TOP(100) [text], cp.size_in_bytes
FROM sys.dm_Exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = ‘Compiled Plan’
AND cp.objtype = ‘Adhoc’
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

— Gives you the text and size of single-use ad-hoc queries that waste space in plan cache
— Enabling ‘optimize for ad hoc workloads’ for the instance can help (SQL Server 2008 only)
— Enabling forced parameterization for the database can help

—- SQL Server Process Address space info (shows whether locked pages is enabled, among other things)——

SELECT physical_memory_in_use_kb,large_page_allocations_kb, locked_page_allocations_kb,total_virtual_address_space_kb,
virtual_address_space_reserved_kb, virtual_address_space_committed_kb, virtual_address_space_available_kb,
page_fault_count, memory_utilization_percentage, available_commit_limit_kb, process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;

——————- Switch to a user database—————————————-
–USE YourDatabaseName;
–GO

—— Individual File Sizes and space available for current database—————–

SELECT name AS [File Name] , physical_name AS [Physical Name], size/128 AS [Total Size in MB],
size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS [Available Space In MB]
FROM sys.database_files;

— Look at how large and how full the files are and where they are located
— Make sure the transaction log is not full!!

———————-List Indexes With the Most Contention—————————

declare @dbid int
select @dbid = db_id()
Select dbid=database_id, objectname=object_name(s.object_id)
, indexname=i.name, i.index_id –, partition_number
, row_lock_count, row_lock_wait_count
, [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
, row_lock_wait_in_ms
, [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s
,sys.indexes i
where objectproperty(s.object_id,’IsUserTable’) = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by row_lock_wait_count desc

———–Query to gather stats update on given databases—————————-

Select db_id() as dbid,
case
when indid IN (0, 1) then convert (char (12), rows)
else (select rows from dbo.sysindexes i2 where i2.id = i.id and i2.indid in (0,1)) — ”-”
end as rowcnt,
case
when indid IN (0, 1) then rowmodctr
else convert (bigint, rowmodctr) + (select rowmodctr from dbo.sysindexes i2 where i2.id = i.id and i2.indid in (0,1))
end as row_mods,
case rows when 0 then 0 else convert (bigint,
case
when indid IN (0, 1) then convert (bigint, rowmodctr)
else rowmodctr + (select convert (bigint, rowmodctr) from dbo.sysindexes i2 where i2.id = i.id and i2.indid in (0,1))
end / convert (numeric (20,2), (select case convert (bigint, rows) when 0 then 0.01 else rows end from dbo.sysindexes i2 where i2.id = i.id and i2.indid in (0,1))) * 100)
end as pct_mod,
convert (nvarchar, u.name + ‘.’ + o.name) as objname,
case when i.status&0x800040=0x800040 then ‘AUTOSTATS’
when i.status&0x40=0x40 and i.status&0x800000=0 then ‘STATS’
else ‘INDEX’ end as type,
convert (nvarchar, i.name) as idxname, i.indid,
stats_date (o.id, i.indid) as stats_updated,
case i.status & 0x1000000 when 0 then ‘no’ else ‘*YES*’ end as norecompute,
o.id as objid , rowcnt, i.status
from dbo.sysobjects o, dbo.sysindexes i, dbo.sysusers u
where o.id = i.id and o.uid = u.uid and i.indid between 1 and 254 and o.type = ‘U’
order by pct_mod desc, convert (nvarchar, u.name + ‘.’ + o.name), indid
GO

————Query to check fragmentation on given database—————————–

select distinct o.[name] as [Object], i.[name] as [IndexName],page_count, avg_fragmentation_in_percent, i.*
from sys.dm_db_index_physical_stats(db_id(), null, null, null, ‘detailed’) as ips
join sys.indexes as i on i.[object_id] = ips.[object_id] and i.index_id = ips.index_id
join sys.objects as o on o.[object_id] = ips.[object_id]
join sys.schemas as sch on sch.schema_id = o.schema_id
where (i.name is not null)
order by Object asc, type asc

—–Query to update stats and rebuild indexes on all the tables of given databases—

EXEC sp_MSforeachtable ‘UPDATE STATISTICS ? WITH FULLSCAN’
–Update Stats of all indexes

Exec sp_MSforeachtable “dbcc dbreindex(‘?’)”
–Rebuild Indexes of all tables for given databases.

——————- Top Cached SPs By Execution Count (SQL 2008)————————-

SELECT TOP(50) p.name AS [SP Name], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.last_elapsed_time,
qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC;

— Top Cached SPs By Total Worker time (SQL 2008). Worker time relates to CPU cost——

SELECT TOP(25) p.name AS [SP Name],
qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.last_elapsed_time,
qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC;

—-Top Cached SPs By Total Logical Reads (SQL 2008). Logical reads relate to memory –pressure————————–

SELECT TOP(25) p.name AS [SP Name],
qs.total_logical_reads AS [TotalLogicalReads], qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.execution_count,
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.last_elapsed_time,
qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC;

—– Top Cached SPs By Total Physical Reads (SQL 2008). Physical reads relate to disk I/O pressure—————————

SELECT TOP(25) p.name AS [SP Name],
qs.total_physical_reads AS [TotalPhysicalReads], qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads],
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_logical_reads AS [TotalLogicalReads], qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.execution_count,
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.last_elapsed_time,
qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_physical_reads DESC;

———– Top Cached SPs By Total Logical Writes (SQL 2008). Logical writes relate to both memory and disk I/O pressure————————

SELECT TOP(25) p.name AS [SP Name],
qs.total_logical_writes AS [TotalLogicalWrites], qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites],
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_logical_reads AS [TotalLogicalReads], qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.execution_count,
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.last_elapsed_time,
qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_writes DESC;

— Lists the top statements by average input/output usage for the current database—

SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name],
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
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 [Query Text]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.[dbid] = DB_ID()
ORDER BY [Avg IO] DESC;

— Helps you find the most expensive statements for I/O by SP

—————— Possible Bad Indexes (writes > reads)——————————

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates – (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],’IsUserTable’) = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;

————- Missing Indexes for entire instance by Index Advantage——————

SELECT migs.avg_user_impact,migs.user_seeks,user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], migs.last_user_seek,
mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.avg_total_user_cost
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC;

— Look at last user seek time, number of user seeks to help determine source and importance
— SQL Server is overly eager to add included columns, so beware

—- Breaks down buffers used by current database by object (table, index) in the -buffer cache——————

SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName], p.[object_id],
p.index_id, COUNT(*)/128 AS [buffer size(MB)], COUNT(*) AS [buffer_count]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id], p.index_id
ORDER BY buffer_count DESC;

— Tells you what tables and indexes are using the most memory in the buffer cache

———— Detect blocking (run multiple times)————————————-

SELECT t1.resource_type AS [lock type],DB_NAME(resource_database_id) AS [database],
t1.resource_associated_entity_id AS [blk object],t1.request_mode AS [lock req], — lock requested
t1.request_session_id AS [waiter sid], t2.wait_duration_ms AS [wait time], — spid of waiter
(SELECT [text] FROM sys.dm_exec_requests AS r — get sql for waiter
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle])
WHERE r.session_id = t1.request_session_id) AS [waiter_batch],
(SELECT SUBSTRING(qt.[text],r.statement_start_offset/2,
(CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2
ELSE r.statement_end_offset END – r.statement_start_offset)/2)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
WHERE r.session_id = t1.request_session_id) AS [waiter_stmt], — statement blocked
t2.blocking_session_id AS [blocker sid], — spid of blocker
(SELECT [text] FROM sys.sysprocesses AS p — get sql for blocker
CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle])
WHERE p.spid = t2.blocking_session_id) AS [blocker_stmt]
FROM sys.dm_tran_locks AS t1
INNER JOIN sys.dm_os_waiting_tasks AS t2
ON t1.lock_owner_address = t2.resource_address;

———————————–END———————————————-

You can stripe a SQL Server’s database backup across more than one file. Striping a SQL Server backup across more than one backup file can provide performance advantages as well as make it possible to backup a VLDB where there is no single drive available with enough free space. Striped backups are supported in SQL Server 2000 & SQL Server 2005.

select r.session_id,
r.start_time,
db = db_name(database_id),
r.command,
st.text,
substring(st.text,
r.statement_start_offset/2+1,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), st.text)) * 2 else r.statement_end_offset end – r.statement_start_offset)/2+1),
r.blocking_session_id,
r.wait_type,
r.last_wait_type,
r.cpu_time,
r.total_elapsed_time,
r.reads,
r.writes,
s.memory_usage,
s.host_name,
s.program_name,
s.login_name
from sys.dm_exec_requests r
join sys.dm_exec_connections c on r.session_id = c.session_id
join sys.dm_exec_sessions s on s.session_id = r.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) st
where r.session_id > 50 and r.blocking_session_id>1
order by r.session_id

Script to find CPU Bottel Necks

Posted: March 30, 2011 in Sqlserver

CPU Bottlenecks

Please run the below code from Cmd prompt to change the server level collation for Sqlserver2008.

start /wait \\server\d$\ms_sql_install\ms_sql2008_standard\cd1\setup.exe /QUIET

/ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Domain\adminxxxx

/SAPWD= Test /SQLCOLLATION=SQL_Latin1_General_CP1250_CI_AS

Problem

I had a scenario where one of my production sqlserver agent went into hung state due to which none of the sql jobs were running and no backups were happening though we have a NSM agent sitting on each of the production server which monitors the services and disk threshold. Since the Sql agent service was in running status and due to some reason it was not working as expected. Unless and until the service is down CA NSM agent will not trigger the event as an incident request to DBA.

Solution

I started to thing about something which monitor all my production server’s backup and send as an consolidated email for the backup status of all sqlserver’s.

I got a idea to develop something which is easy to maintain and identify the failure of backup by seeing the email.

Powershell script is a upgraded part of technet library.  I have rebuilded this script to record the output in excel sheet and send as an e-mail attachment to the DBA Team.

This scripts point you exactly which server and which database did not got backed up and from how many days backups are failing.

—————————————————————————————————————————————

### Code that can be used to Monitor all you Sql Instances Backups from One Location

 

 

#Create a new Excel object using COM

$ErrorActionPreference = “silentlycontinue”

$Excel = New-Object -ComObject Excel.Application

$Excel.visible = $False

$Excel.DisplayAlerts = $false

$ExcelWorkbooks = $Excel.Workbooks.Add()

$Sheet = $ExcelWorkbooks.Worksheets.Item(1)

#$MonitorBody = “D:\PowerShell\PScripts\Mail.htm”

#$date = get-date -uformat “%Y%m%d”

$date = ( get-date ).ToString(‘yyyy/MM/dd’)

$save = “D:\PowerShell\ExcelReports\DatabaseBackup_Report.xls”

#Counter variable for rows

$intRow = 1

#Read the contents of the Servers.txt file

#foreach ($instance in get-content “serverlist.txt”)

 

##################Loop in all your sqlserver instances#########################

foreach ($instance in get-content “D:\PowerShell\PScripts\Backupserverlist.txt”)

{

#Create column headers

$Sheet.Cells.Item($intRow,1) = “INSTANCE NAME:”

$Sheet.Cells.Item($intRow,2) = $instance

$Sheet.Cells.Item($intRow,1).Font.Bold = $True

$Sheet.Cells.Item($intRow,2).Font.Bold = $True

 

$intRow++

 

$Sheet.Cells.Item($intRow,1) = “DATABASE NAME”

$Sheet.Cells.Item($intRow,2) = “LAST FULL BACKUP”

$Sheet.Cells.Item($intRow,3) = “LAST LOG BACKUP”

$Sheet.Cells.Item($intRow,4) = “FULL BACKUP AGE(DAYS)”

$Sheet.Cells.Item($intRow,5) = “LOG BACKUP AGE(HOURS)”

 

#Format the column headers

for ($col = 1; $col –le 5; $col++)

{

$Sheet.Cells.Item($intRow,$col).Font.Bold = $True

$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 50

$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 36

}

 

$intRow++

#######################################################

#This script gets SQL Server database information using PowerShell

 

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null

 

# Create an SMO connection to the instance

$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance

 

$dbs = $s.Databases

#Formatting using Excel

ForEach ($db in $dbs)

{

if ($db.Name -ne “tempdb”) #We do not need the backup information for the tempdb database

{

#We use Date Math to extract the number of days since the last full backup

$NumDaysSinceLastFullBackup = ((Get-Date) – $db.LastBackupDate).Days

#Here we use TotalHours to extract the total number of hours

$NumDaysSinceLastLogBackup = ((Get-Date) – $db.LastLogBackupDate).TotalHours

if($db.LastBackupDate -eq “1/1/2005 12:00 AM”)

#This date is a start of Sqlserver infra.
#This is the default dateTime value for databases that have not had any backups
{

$fullBackupDate=”Never been backed up”
$fgColor3=”red”

}

else

{

$fullBackupDate=”{0:g}” -f $db.LastBackupDate
}

$Sheet.Cells.Item($intRow, 1) = $db.Name
$Sheet.Cells.Item($intRow, 2) = $fullBackupDate
$fgColor3=”green”

#Use the .ToString() Method to convert the value of the Recovery model to string and ignore Log #backups for databases with Simple recovery model

if ($db.RecoveryModel.Tostring() -eq “SIMPLE”)
{

$logBackupDate=”N/A”
$NumDaysSinceLastLogBackup=”N/A”

}
else

{

if($db.LastLogBackupDate -eq “1/1/2011 12:00 AM”)

{
$logBackupDate=”Never been backed up”
}

else

{
$logBackupDate= “{0:g2}” -f $db.LastLogBackupDate
}

}

$Sheet.Cells.Item($intRow, 3) = $logBackupDate

#Define your service-level agreement in terms of days here.

if ($NumDaysSinceLastFullBackup -gt 0)

{

$fgColor = 3

}

else

{

$fgColor = 50

}

$Sheet.Cells.Item($intRow, 4) = $NumDaysSinceLastFullBackup
$Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor
$Sheet.Cells.Item($intRow, 5) = $NumDaysSinceLastLogBackup
$intRow ++

}
}
$intRow ++
}

 
$Sheet.UsedRange.EntireColumn.AutoFit()
$ExcelWorkbooks.SaveAs($save)
$Excel.quit()
CLS

######Send Email with excel sheet as a attachment#######

$mail = New-Object System.Net.Mail.MailMessage
$att = new-object Net.Mail.Attachment($save)
$mail.From = “shamsul.hassan@gmail.com”
$mail.To.Add(“shamsul.hassan@gmail.com”)
$mail.Subject = “Database Backup Report of all Sqlserver for $date ”
$mail.Body = “This mail gives us the detail information of all the database backup of production sqlserver which are schedule to run every day. Please review the Excel report enclosed with the mail every day and fix the failed backups which is marked in Red color and make sure the Full Backup Age(DAYS) is Zero days.Thanks – Md.S.Hassan”
$mail.Attachments.Add($att)
$smtp = New-Object System.Net.Mail.SmtpClient(“smtp.us.tesco.org”)
$smtp.Credentials = New-Object System.Net.NetworkCredential(“us\DBA_mon”,”xxxxx”)
$smtp.Send($mail)