Showing posts with label DBA Worries Continued. Show all posts
Showing posts with label DBA Worries Continued. Show all posts

Sep 28, 2025

Azure DTU

 In Azure SQL Database, DTU stands for Database Transaction Unit — it’s a performance metric that combines CPU, memory, reads, and writes into a single measure.


Think of it as a “power level” — the higher the DTU, the more workload the database can handle.




How to Check DTU Usage

You can monitor DTU utilization via:

Azure Portal → SQL Database → Monitoring → DTU usage (%)

Query via T-SQL

SELECT * FROM sys.dm_db_resource_stats ORDER BY end_time DESC;

You can estimate your DTU requirement using Microsoft’s DTU Calculator:

https://dtucalculator.azurewebsites.net


Jan 3, 2024

SQL Server Statistics

SQL Server Statistics

 


-- Turn on/off statistics IO
SET STATISTICS IO ON 

SET STATISTICS IO OFF 
-- Turn on/off statistics TIME
SET STATISTICS TIME ON 
SET STATISTICS TIME OFF 

-- Turn on/off profile information.

Note: It displays the profile information for a statement. STATISTICS PROFILE works for ad hoc queries, views, and stored procedures.

SET STATISTICS PROFILE ON

SET STATISTICS PROFILE OFF

--Find the statistics of all the objects where modify count is > 1000

SELECT obj.name 'table name', obj.object_id, stat.name 'stats name', schema_name(obj.schema_id)+'.'+obj.name as 'full table name', stat.stats_id, last_updated, modification_counter FROM sys.objects AS obj INNER JOIN sys.stats AS stat ON stat.object_id = obj.object_id CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp WHERE modification_counter > 1000 order by modification_counter desc;

--Get the row count of the table.

DECLARE @TableName sysname SET @TableName = 'RCHOWN.7_SERVICE' SELECT OBJECT_NAME(object_id), SUM(row_count) AS rows FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID(@TableName) AND index_id < 2 GROUP BY OBJECT_NAME(object_id);

--Update statistics with full scan.

UPDATE STATISTICS ARCHOWN.PRIME17_SERVICES WITH FULLSCAN;



--Count the number of rows in a table

Use DBName

DECLARE @TableName sysname SET @TableName = 'schema.TableName' SELECT OBJECT_NAME(object_id), SUM(row_count) AS rows FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID(@TableName) AND index_id < 2 GROUP BY OBJECT_NAME(object_id);

--Size of all tables in DB

SELECT

    t.name AS TableName,

    s.name AS SchemaName,

    p.rows,

    SUM(a.total_pages) * 8 AS TotalSpaceKB,

    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,

    SUM(a.used_pages) * 8 AS UsedSpaceKB,

    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,

    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

FROM

    sys.tables t

INNER JOIN     

    sys.indexes i ON t.object_id = i.object_id

INNER JOIN

    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

INNER JOIN

    sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

    sys.schemas s ON t.schema_id = s.schema_id

WHERE

    t.name NOT LIKE 'dt%'

    AND t.is_ms_shipped = 0

    AND i.object_id > 255

GROUP BY

    t.name, s.name, p.rows

ORDER BY

    TotalSpaceMB DESC, t.name


--DB file info in a Drive

select db_name(database_id) 'Db_Name', name 'Logical_File_Name', (cast(size as float)*8)/1024/1024 as FILE_SIZE_GB,physical_name from sys.master_files where physical_name like 'R%' order by 2 desc;


--Space used per DB file in MB

Use <DBName>

SELECT DB_NAME() AS DbName, name AS FileName, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files order by 4 desc;

--Space used per DB file in GB with % of free space

Use <DBName>

SELECT DB_NAME() AS DbName, name AS FileName, size/(128.0*1024) AS CurrentSizeGB, (size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/(128.0))/1024 as 'Free space in GB', ((size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0)*100)/(size/128.0) AS 'FreeSpace in %'

FROM sys.database_files order by 3 desc;

--Databases file with size and free space

SELECT DB_NAME() AS DbName, name AS FileName, type_desc,size/128.0 AS CurrentSizeMB,  size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB,physical_name FROM sys.database_files NWHERE type IN (0,1);


--To Check Filegroups

SELECT [databasefile].NAME AS [FileName],[filegroup].NAME AS [File_Group_Name],[filegroup].type_desc, physical_name [Data File Location], size / 128 AS [Size_in_MB],state_desc    [State of FILE], growth [Data file growth] FROM  sys.database_files [databasefile] INNER JOIN sys.filegroups [filegroup] ON [databasefile].data_space_id = [filegroup].data_space_id order by 2


--All Table Size in a DB

SELECT 

o.name AS ObjectName,

s.name AS SchemaName,

p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

SUM(a.data_pages) * 8 AS DataSpaceKB

FROM 

sys.objects o

JOIN 

sys.schemas s ON o.schema_id = s.schema_id

JOIN 

sys.partitions p ON o.object_id = p.object_id

JOIN 

sys.allocation_units a ON p.partition_id = a.container_id

WHERE 

o.type = 'U' -- U = User tables

GROUP BY 

o.name, s.name, p.rows

ORDER BY 

TotalSpaceKB DESC;


-------------------------------------------------------------

--Get a Table size in MB

USE DB_Name

SELECT 

(SUM(a.total_pages) * 8)/1024 AS TotalSpaceMB, 

FROM 

sys.partitions AS p

JOIN 

sys.allocation_units AS a 

ON p.partition_id = a.container_id

WHERE 

p.object_id = OBJECT_ID('YourTableName');


-------------------------------------------------------------

--All Index Size in a DB

SELECT 

o.name AS ObjectName,

i.name AS IndexName,

SUM(ps.used_page_count) * 8 AS UsedSpaceKB,

SUM(ps.reserved_page_count) * 8 AS ReservedSpaceKB

FROM 

sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps

JOIN 

sys.objects AS o ON ps.object_id = o.object_id

JOIN 

sys.indexes AS i ON ps.object_id = i.object_id AND ps.index_id = i.index_id

GROUP BY 

o.name, i.name

ORDER BY 

UsedSpaceKB DESC;


----------------------------------------------

--Partition Details for a Table

SELECT 

p.partition_number,

p.rows,

f.name AS filegroup_name

FROM sys.partitions p

JOIN sys.objects o ON p.object_id = o.object_id

JOIN sys.indexes i ON o.object_id = i.object_id AND i.index_id = p.index_id

JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id

JOIN sys.partition_schemes ps ON ds.data_space_id = ps.data_space_id

JOIN sys.destination_data_spaces dds ON ps.partition_scheme_id = dds.partition_scheme_id

JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id

WHERE o.name = 'YourTableName'  -- Replace with your table name

ORDER BY p.partition_number;


----------------------------------------------

--JOB History 

USE msdb;
GO
SELECT 
    jobs.name AS JobName,
    history.run_date AS RunDate,
    RIGHT('000000' + CAST(history.run_time AS VARCHAR(6)), 6) AS RunTime,
    DATEADD(SECOND, history.run_duration / 10000 * 3600 + (history.run_duration % 10000) / 100 * 60 + history.run_duration % 100, 0) AS RunDuration,
    CASE history.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        ELSE 'Unknown'
    END AS JobStatus,
    history.message AS Message
FROM 
    sysjobs AS jobs
INNER JOIN 
    sysjobhistory AS history ON jobs.job_id = history.job_id
WHERE 
    history.step_id = 0 -- Step 0 is the overall job outcome
ORDER BY 
    history.run_date DESC, 
    history.run_time DESC;

----------------------------------------------

--Index utilization stats... 

SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
   ,IX.name AS Index_Name
   ,IX.type_desc Index_Type
   ,SUM(PS.[used_page_count]) * 8 IndexSizeKB
   ,IXUS.user_seeks AS NumOfSeeks
   ,IXUS.user_scans AS NumOfScans
   ,IXUS.user_lookups AS NumOfLookups
   ,IXUS.user_updates AS NumOfUpdates
   ,IXUS.last_user_seek AS LastSeek
   ,IXUS.last_user_scan AS LastScan
   ,IXUS.last_user_lookup AS LastLookup
   ,IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
WHERE --OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
OBJECT_NAME(IX.OBJECT_ID) = 'YOUR_TABLE_NAME'
GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,
IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,
IXUS.last_user_seek ,IXUS.last_user_scan ,
IXUS.last_user_lookup ,IXUS.last_user_update


----------------------------------------------
--To determine how much data SQL Server reads and write per hour
--Option 1 Use SQL Query Statistics 
declare @startTime datetime, @hh int
SELECT @startTime = sqlserver_start_time FROM sys.dm_os_sys_info;

select @hh = datediff(hh,@startTime,getdate())

SELECT 
    DB_NAME(database_id) AS DatabaseName,
    (SUM(num_of_bytes_read) / (1024  ))/@hh AS 'KB Read per Hour',
    (SUM(num_of_bytes_written) / (1024	 ))/@hh AS 'KB Write per Hour',
    (SUM(num_of_bytes_read) / (1024 * 1024 ))/@hh AS 'MB Read per Hour',
    (SUM(num_of_bytes_written) / (1024 * 1024 ))/@hh AS 'MB Write per Hour',
	(SUM(num_of_bytes_read) / (1024 * 1024 * 1024))/@hh AS 'GB Read per Hour',
    (SUM(num_of_bytes_written) / (1024 * 1024 * 1024))/@hh AS 'GB Write per Hour'
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id order by 2 desc

select @startTime 'Statistics Since ',  @hh 'Statistics Since Hours'


select (sum(num_of_bytes_read)/(1024*1024*1024))/@hh
		  as total_num_of_gb_read_per_hour, 
                (sum(num_of_bytes_written) / (1024*1024*1024))/@hh 
		  as total_num_of_gb_write_per_hour 
from  sys.dm_io_virtual_file_stats(NULL, NULL)

--Option 2 Use SQL Query Log File 
SELECT [object_name], counter_name, instance_name, cntr_value AS CounterValue FROM sys.dm_os_performance_counters WHERE counter_name IN ('Page reads/sec', 'Page writes/sec');

--Server-Wide I/O Statistics with Host Name
SELECT 
    DB_NAME(vfs.database_id) AS DatabaseName,
    vfs.file_id AS FileID,
    s.host_name AS HostName,
    r.session_id AS SessionID,
    vfs.num_of_reads AS TotalReads,
    vfs.num_of_writes AS TotalWrites,
    (vfs.num_of_bytes_read / 1024.0 / 1024.0) AS Read_MB,
    (vfs.num_of_bytes_written / 1024.0 / 1024.0) AS Written_MB
FROM 
    sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
LEFT JOIN 
    sys.dm_exec_requests AS r
ON 
    vfs.database_id = r.database_id
LEFT JOIN 
    sys.dm_exec_sessions AS s
ON 
    r.session_id = s.session_id
ORDER BY 
    DatabaseName, HostName, TotalReads DESC, TotalWrites DESC;

--SQL Server Instance Start Date:
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
--sp_WhoIsActive Logging