How to capture Database files I/O latencies – SQL Server

SELECT
–virtual file latency
[ReadLatency] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
–avg bytes per IOP
[AvgBPerRead] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE
(([num_of_bytes_read] + [num_of_bytes_written]) /
([num_of_reads] + [num_of_writes])) END,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
–[vfs].*,
[mf].[physical_name]
FROM
sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
ORDER BY [Latency] DESC

How to read a SQL Profiler trace file in Management studio – SQL Server

fn_trace_gettable (‘filename’ , number_files )

number_files specifies the number of rollover files to be read.

SELECT *
FROM fn_trace_gettable(‘c:\inside_sql.trc’, 1)

How to find Top CPU intensive queries on a Database – SQL Server

SELECT TOP 20 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),
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/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
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_worker_time DESC — CPU time

Find all the objects that have been modified in last 7 days – SQL Server

USE Test_DB
GO
SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() – 7 — no of days
ORDER BY modify_date;
GO

How to find Row count for all tables in a database – SQL Server

SELECT
QUOTENAME(SCHEMA_NAME(O.schema_id)) + ‘.’ + QUOTENAME(O.name) AS [TableName]
, SUM(P.Rows) AS [RowCount]
FROM
sys.objects AS O
INNER JOIN sys.partitions AS P ON O.object_id = P.object_id
WHERE
O.type = ‘U’ AND index_id < 2
GROUP BY
O.schema_id
, O.name
ORDER BY [TableName]
GO

—————————————————————–

Below script will give you index wise row count.

USE [Adventureworks];
GO
SELECT  OBJECT_NAME([p].[object_id]) AS [Table] ,
        [p].[index_id] AS [Index ID] ,
        [i].[name] AS [Index] ,
        [p].[rows] AS "Number of Rows"
FROM    [sys].[partitions] AS [p]
        JOIN [sys].[indexes] AS [i] ON [p].[object_id] = [i].[object_id]
                                  AND [p].[index_id] = [i].[index_id]
WHERE   [p].[object_id] = OBJECT_ID('dbo.sales');

Get failed jobs report — SQL Server

——–Script to get the failed jobs report

BEGIN
SET NOCOUNT ON
SELECT Convert(varchar(20),SERVERPROPERTY(‘ServerName’)) AS ServerName,
j.name AS job_name,
CASE jh.run_status WHEN 0 THEN ‘Failed’
WHEN 1 THEN ‘Succeeded’
WHEN 2 THEN ‘Retry’
WHEN 3 THEN ‘Canceled’
WHEN 4 THEN ‘In Progress’ ELSE
‘Status Unknown’ END AS ‘last_run_status’,jh.message as MessageDetails
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON
ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity)
and jh.run_status = 0 ORDER BY job_name
END

Recompile a stored procedure – SQL Server

After performing any maintenance activities and bulk updates or inserts , recompiling the stored procedure or updated table will create new plan and give performance advantage.

sp_recompile ‘object_name’

——Script to recompile all the stored procedures on table.

USE AdventureWorks2012;

EXEC sp_recompile N’Sales.Customer’;

——- Script to recompile a specific stored procedure

USE TestDB;

sp_recompile ‘sp_salesorders’

Find IO statistics of a Database – SQL server

USE [Database_Name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[IO_stats]
as
;with IOS as(SELECT TOP 100
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count,
[Total IO] = (total_logical_reads + total_logical_writes),
[Execution count] = qs.execution_count,
qs.last_execution_time as [Time],
qs.total_rows as [Total_rows],qs.total_physical_reads as [Total_physical_reads],qs.last_physical_reads as [Last_physical_reads],
[Individual Query] = 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),
[Parent Query] = qt.text,
qt.objectid as ‘Object_ID’,
DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
where qt.dbid = DB_ID(‘Database_Name’)
ORDER BY [Average IO] DESC)

select P.Name as [Procedure Name],IOS.[Average IO],IOS.[Total IO],IOS.[Execution count],IOS.[Time],IOS.[Total_physical_reads],IOS.[Total_rows],IOS.[Individual Query],IOS.DatabaseName
from IOS left join sys.procedures P on IOS.Object_ID=P.object_id

Go
Exec IO_stats

How to find the version and edition of SQL Server

SELECT SERVERPROPERTY(‘ProductVersion’) AS Version, SERVERPROPERTY(‘ProductLevel’) AS SPLevel, SERVERPROPERTY(‘Edition’) AS Edition;

(or)

SELECT @@VERSION;

How to measure Query response time in SQL Server ?

USE [Test_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[ResponseTime]
as
;with RT as(

SELECT TOP 100
[Average seconds] = qs.total_elapsed_time / qs.execution_count / 1000000.0,
[Total seconds] = qs.total_elapsed_time / 1000000.0,
[Execution count] = qs.execution_count,
[Time] = qs.last_execution_time ,
[Individual Query] = 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),
[Parent Query] = qt.text,
qt.objectid as ‘Object_ID’,
DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
where qt.dbid = DB_ID(‘Test_DB’)
ORDER BY [Average seconds] DESC)
select P.Name as [Procedure Name],RT.[Average seconds],RT.[Total seconds],RT.[Execution count],RT.[Time],RT.[Individual Query], RT.DatabaseName
from RT left join sys.procedures P on RT.Object_ID=P.object_id

Exec [ResponseTime]

selectstarfromyogi

sql server DBA ,sql server 2008 , 2012 , 2014 ,sql

SQL Server