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 WHERE 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
No comments:
Post a Comment
If you have any doubt or question, please contact us.