Finding the Largest Tables in SQL Server for Performance Optimization

Finding the Largest Tables in SQL Server for Performance Optimization

When optimizing database performance, one of the key areas to focus on is index design. Often, slow queries are caused by inefficient indexing, especially when dealing with large tables. Identifying the largest tables in your database can help you prioritize index tuning and optimize storage.

Identifying the Top 20 Largest Tables in SQL Server

The following SQL query will return to the top 20 largest tables in your SQL Server database based on the amount of space they consume. It calculates both used and allocated space for each table:

SELECT TOP 20

    SCHEMA_NAME(tab.schema_id) + '.' + tab.name AS [Table],

    CAST(SUM(spc.used_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS Used_MB,

    CAST(SUM(spc.total_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS Allocated_MB

FROM sys.tables tab

JOIN sys.indexes ind ON tab.object_id = ind.object_id

JOIN sys.partitions part ON ind.object_id = part.object_id AND ind.index_id = part.index_id

JOIN sys.allocation_units spc ON part.partition_id = spc.container_id

GROUP BY SCHEMA_NAME(tab.schema_id) + '.' + tab.name

ORDER BY SUM(spc.used_pages) DESC;

Review bad indexes

 -- Review bad indexes

--Possible Bad NC Indexes (writes > reads)  

 -- Taking into connsideration the complete workload, and how long your instance has been running

 -- Advice not to drop or disable  indexes unless the is 100% certainty that it is no loger substantially benefitial

   SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, 

   is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,

   user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],

   user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]

   FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

   INNER JOIN sys.indexes AS i WITH (NOLOCK)

   ON s.[object_id] = i.[object_id]

   AND i.index_id = s.index_id

  WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

  AND s.database_id = DB_ID()

  AND user_updates > (user_seeks + user_scans + user_lookups)

  AND i.index_id > 1

  ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

 

 

Improve the index design-- Review missing indexes

--  Review missing indexes

--Missing Index Query

SELECT TOP 25 dm_mid.database_id AS DatabaseID, dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact, dm_migs.last_user_seek AS Last_User_Seek, OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName], 'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','') + ']' + ' ON ' + dm_mid.statement + ' (' + ISNULL (dm_mid.equality_columns,'') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (dm_mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement FROM sys.dm_db_missing_index_groups dm_mig INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle WHERE dm_mid.database_ID = DB_ID() ORDER BY Avg_Estimated_Impact DESC

Tune inefficient queries in SQL Server

 --Tune inefficient queries in SQL Server

SELECT TOP 50
[Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count,
[Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000,
[Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count,
[Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000,
qs.execution_count,
[Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count,
[Total I/O] = total_logical_reads + total_logical_writes,
Query = SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
(
(
CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.[text])
ELSE qs.statement_end_offset
END - qs.statement_start_offset
) / 2
) + 1
),
Batch = qt.[text],
[DB] = DB_NAME(qt.[dbid]),
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
where qs.execution_count > 5 --more than 5 occurences
ORDER BY [Total MultiCore/CPU time(sec)] DESC

DB last updated and last_select_query_update_script

--DB last updated 

 select * from sys.dm_db_index_usage_stats where database_id = db_id('database_name') order by last_user_update 

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

 --last_select_query_update_script 

WITH LastActivity (ObjectID, LastAction) AS

  (

       SELECT object_id AS TableName,

              last_user_seek as LastAction

         FROM sys.dm_db_index_usage_stats u

        WHERE database_id = db_id(db_name())

        UNION

       SELECT object_id AS TableName,

              last_user_scan as LastAction

         FROM sys.dm_db_index_usage_stats u

        WHERE database_id = db_id(db_name())

        UNION

       SELECT object_id AS TableName,

              last_user_lookup as LastAction

         FROM sys.dm_db_index_usage_stats u

        WHERE database_id = db_id(db_name())

  )

  SELECT OBJECT_NAME(so.object_id) AS TableName,

         MAX(la.LastAction) as LastSelect

    FROM sys.objects so

    LEFT

    JOIN LastActivity la

      on so.object_id = la.ObjectID

   WHERE so.type = 'U'

     AND so.object_id > 100

GROUP BY OBJECT_NAME(so.object_id)

ORDER BY OBJECT_NAME(so.object_id)