SQL Server Statistics
-- Turn on/off statistics IO SET STATISTICS IO ON
SET STATISTICS IO OFF
-- Turn on/off statistics TIMESET 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;
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;
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;
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 LastUpdateFROM sys.indexes IXINNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_IDINNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_idWHERE --OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1OBJECT_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 intSELECT @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 NameSELECT
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;