How to find largest objects in a SQL Server database

Question

How would I go about finding the largest objects in a SQL Server database   First  by determining which tables  and related indices  are the largest and then determining which rows in a particular table are largest  we re storing binary data in BLOBs    Are there any tools out there for helping with this kind of database analysis   Or are there some simple queries I could run against the system tables

User · Answer

I ve found this query also very helpful in SqlServerCentral  here is the link to original post  Sql Server largest tables    select name object schema name object id          object name object id    rows sum case when index id  lt  2 then row count else 0 end    reserved kb 8 sum reserved page count    data kb 8 sum  case       when index id lt 2 then in row data page count   lob used page count   row overflow used page count       else lob used page count   row overflow used page count      end     index kb 8  sum used page count       - sum  case             when index id lt 2 then in row data page count   lob used page count   row overflow used page count          else lob used page count   row overflow used page count          end                unused kb 8 sum reserved page count-used page count  from sys dm db partition stats where object id  gt  1024 group by object id order by  rows desc      In my database they gave different results between this query and the 1st answer   Hope somebody finds useful

User · Answer

In SQL Server 2008  you can also just run the standard report Disk Usage by Top Tables   This can be found by right clicking the DB  selecting Reports- Standard Reports and selecting the report you want

User · Answer

If you are using Sql Server Management Studio 2008 there are certain data fields you can view in the object explorer details window   Simply browse to and select the tables folder   In the details view you are able to right-click the column titles and add fields to the  report    Your mileage may vary if you are on SSMS 2008 express

User · Answer

You may also use the following code   USE AdventureWork GO CREATE TABLE  GetLargest      table name    sysname     row count     INT    reserved size VARCHAR 50     data size     VARCHAR 50     index size    VARCHAR 50     unused size   VARCHAR 50     SET NOCOUNT ON  INSERT  GetLargest  EXEC sp msforeachtable  sp spaceused         SELECT    a table name    a row count    COUNT    AS col count    a data size   FROM  GetLargest a      INNER JOIN information schema columns b      ON a table name collate database default        b table name collate database default        GROUP BY a table name  a row count  a data size        ORDER BY CAST REPLACE a data size    KB       AS integer  DESC  DROP TABLE  GetLargest

User · Answer

This query help to find largest table in you are connection   SELECT  TOP 1 OBJECT NAME OBJECT ID  TableName  st row count FROM sys dm db partition stats st WHERE index id  lt  2 ORDER BY st row count DESC

User · Answer

marc s s answer is very great and I ve been using it for few years  However  I noticed that the script misses data in some columnstore indexes and doesn t show complete picture  E g  when you do SUM TotalSpace  against the script and compare it with total space database property in Management Studio the numbers don t match in my case  Management Studio shows larger numbers   I modified the script to overcome this issue and extended it a little bit   select     tables  name  as table name      schemas  name  as schema name      isnull db name dm db index usage stats database id    Unknown   as database name      sum allocation units total pages    8 as total space kb      cast round   sum allocation units total pages    8    1024 00   2  as numeric 36  2   as total space mb      sum allocation units used pages    8 as used space kb      cast round   sum allocation units used pages    8    1024 00   2  as numeric 36  2   as used space mb       sum allocation units total pages  - sum allocation units used pages     8 as unused space kb      cast round   sum allocation units total pages  - sum allocation units used pages     8    1024 00  2  as numeric 36  2   as unused space mb      count distinct indexes index id  as indexes count      max dm db partition stats row count  as row count      iif max isnull user seeks  0     0 and max isnull user scans  0     0 and max isnull user lookups  0     0  1  0  as no reads      iif max isnull user updates  0     0  1  0  as no writes      max isnull user seeks  0   as user seeks      max isnull user scans  0   as user scans      max isnull user lookups  0   as user lookups      max isnull user updates  0   as user updates      max last user seek  as last user seek      max last user scan  as last user scan      max last user lookup  as last user lookup      max last user update  as last user update      max tables create date  as create date      max tables modify date  as modify date from      sys tables     left join sys schemas on schemas schema id   tables schema id     left join sys indexes on tables object id   indexes object id     left join sys partitions on indexes object id   partitions object id and indexes index id   partitions index id     left join sys allocation units on partitions partition id   allocation units container id     left join sys dm db index usage stats on tables object id   dm db index usage stats object id and indexes index id   dm db index usage stats index id     left join sys dm db partition stats on tables object id   dm db partition stats object id and indexes index id   dm db partition stats index id group by schemas  name   tables  name   isnull db name dm db index usage stats database id    Unknown   order by 5 desc   Hope it will be helpful for someone  This script was tested against large TB-wide databases with hundreds of different tables  indexes and schemas

User · Answer

I ve been using this SQL script  which I got from someone  somewhere - can t reconstruct who it came from  for ages and it s helped me quite a bit understanding and determining the size of indices and tables   SELECT      t name AS TableName      i name as indexName      sum p rows  as RowCounts      sum a total pages  as TotalPages       sum a used pages  as UsedPages       sum a data pages  as DataPages       sum a total pages    8    1024 as TotalSpaceMB        sum a used pages    8    1024 as UsedSpaceMB        sum a data pages    8    1024 as DataSpaceMB 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 WHERE      t name NOT LIKE  dt   AND     i object id  gt  255 AND       i index id  lt   1 GROUP BY      t name  i object id  i index id  i name  ORDER BY      object name i object id     Of course  you can use another ordering criteria  e g   ORDER BY SUM p rows  DESC   to get the tables with the most rows  or   ORDER BY SUM a total pages  DESC   to get the tables with the most pages  8K blocks  used

[sql-server] How to find largest objects in a SQL Server database?

Examples related to sql-server

Examples related to database