jkavx
asked on
Performance issues
I've begun a consulting assignment and find an extremely slow database with a confused political environment where it's not clear who's responsible for what? As an example, getting the count from a table (admittedly large with about 6 million rows) took more than 4 minutes.
I'd like to get some feel for how this database is being maintained on my own and I'm wondering if there is anything available in the sys tables that would indicate when UpdateStatistics was last run on a given table. Also, is there any way to determine when an index was created?
I'd like to get some feel for how this database is being maintained on my own and I'm wondering if there is anything available in the sys tables that would indicate when UpdateStatistics was last run on a given table. Also, is there any way to determine when an index was created?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can get a row count (table info) with out waiting.... use this:
;WITH table_space_usage(filegrou
AS (SELECT ds.[name] AS filegroup,
s.name,
o.name,
Coalesce(i.name,'HEAP'),
Cast(p.[used_page_count] AS BIGINT) * 8,
Cast(p.[reserved_page_coun
Cast(p.[row_count] AS BIGINT) AS [rows],
CASE
WHEN i.index_id IN (0,1) THEN p.[row_count]
ELSE 0
END
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects AS o
ON o.object_id = p.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.indexes AS i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
LEFT JOIN sys.data_spaces ds
ON ds.[data_space_id] = i.[data_space_id]
WHERE o.type_desc IN ('USER_TABLE','VIEW')
AND o.is_ms_shipped = 0)
SELECT t.table_name,
Sum(t.tbl_rows) tbl_rows,
Sum(Cast(t.reserved / 1024.0 AS DECIMAL(9,2))) AS reserved_in_mb,
Sum(Cast(t.used / 1024.0 AS DECIMAL(9,2))) AS used_in_mb,
(Sum(Cast(t.used * 1024.0 AS FLOAT)) + 1.0) / (Sum(Cast(t.tbl_rows AS FLOAT)) + 1.0) AS avg_row_size_bytes
FROM table_space_usage AS t
GROUP BY t.table_name
ORDER BY reserved_in_mb DESC
;WITH table_space_usage(filegrou
AS (SELECT ds.[name] AS filegroup,
s.name,
o.name,
Coalesce(i.name,'HEAP'),
Cast(p.[used_page_count] AS BIGINT) * 8,
Cast(p.[reserved_page_coun
Cast(p.[row_count] AS BIGINT) AS [rows],
CASE
WHEN i.index_id IN (0,1) THEN p.[row_count]
ELSE 0
END
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects AS o
ON o.object_id = p.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.indexes AS i
ON i.object_id = p.object_id
AND i.index_id = p.index_id
LEFT JOIN sys.data_spaces ds
ON ds.[data_space_id] = i.[data_space_id]
WHERE o.type_desc IN ('USER_TABLE','VIEW')
AND o.is_ms_shipped = 0)
SELECT t.table_name,
Sum(t.tbl_rows) tbl_rows,
Sum(Cast(t.reserved / 1024.0 AS DECIMAL(9,2))) AS reserved_in_mb,
Sum(Cast(t.used / 1024.0 AS DECIMAL(9,2))) AS used_in_mb,
(Sum(Cast(t.used * 1024.0 AS FLOAT)) + 1.0) / (Sum(Cast(t.tbl_rows AS FLOAT)) + 1.0) AS avg_row_size_bytes
FROM table_space_usage AS t
GROUP BY t.table_name
ORDER BY reserved_in_mb DESC
ASKER