Databases Reference
In-Depth Information
LISTING 15-49:
Table names, row counts, and compression status
-- Get Table names, row counts, and compression status
-- for the clustered index or heap
SELECT OBJECT_NAME(object_id) AS [ObjectName],
SUM(Rows) AS [RowCount], data_compression_desc AS [CompressionType]
FROM sys.partitions WITH (NOLOCK)
WHERE index_id < 2 --ignore the partitions from the non-clustered index if any
AND OBJECT_NAME(object_id) NOT LIKE N'sys%'
AND OBJECT_NAME(object_id) NOT LIKE N'queue_%'
AND OBJECT_NAME(object_id) NOT LIKE N'filestream_tombstone%'
AND OBJECT_NAME(object_id) NOT LIKE N'fulltext%'
AND OBJECT_NAME(object_id) NOT LIKE N'ifts_comp_fragment%'
AND OBJECT_NAME(object_id) NOT LIKE N'filetable_updates%'
GROUP BY object_id, data_compression_desc
ORDER BY SUM(Rows) DESC OPTION (RECOMPILE);
-- Gives you an idea of table sizes, and possible data compression opportunities
This query returns all your table sizes, including row count and data compression status (for the
clustered index), ordered by row counts. It is a good idea to have a notion of how many millions or
billions of rows are contained in the larger tables in your database. This is one indirect way of keep-
ing tabs on the growth and activity of your database. Knowing the compression status of the clus-
tered index of your largest tables is also very useful, as it might uncover some good candidates for
data compression. As previously discussed, SQL Server data compression can be a huge win in many
scenarios if you are able to take advantage of it with Enterprise Edition.
Next, using the query shown in Listing 15-50, you can i nd out the last time that statistics were
updated for all indexes in the database.
LISTING 15-50:
Last statistics update for all indexes
-- When were Statistics last updated on all indexes?
SELECT o.name, i.name AS [Index Name],STATS_DATE(i.[object_id],
i.index_id) AS [Statistics Date], s.auto_created,
s.no_recompute, s.user_created, st.row_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id]
AND i.[index_id] = st.[index_id]
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);
-- Helps discover possible problems with out-of-date statistics
-- Also gives you an idea which indexes are most active
Search WWH ::
Custom Search