SQL Server Performance Forum – Threads Archive

record size

Hi, <br />I’m a junior dba so I apologize if my questions seems pretty dumb <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /><br />Anyway, we have a huge table that we are trying to reduce the size of. How can I calculate how much data space and index space I’ll be saving if I delete a certain number of records. <br />I guess all I’m asking is how do I calculate record size and index size of that record.<br />thanks.

Before you do any of that, also check to see if your table is badly fragmented. If it is, sometimes just defragging a table can free up lots of space. Look up DBCC SHOWCONTIG in Books olnine for more information on how to run it and interpret the results. There are assorted sprocs out there than can help you estimate size etc, and you can search this site and probably find a fw. But for a really simple calculation since you have data, run sp_spaceused on your table. It will give you the number of rows, data space and index space. The average used space per row can be calculated by dividing the space used by the number of rows.
chris

Here’s the results of my dbcc showcontig of that table:
DBCC SHOWCONTIG scanning ‘downloads’ table…
Table: ‘downloads’ (259584063); index ID: 1, database ID: 8
TABLE level scan performed.
– Pages Scanned…………………………..: 125562
– Extents Scanned…………………………: 15808
– Extent Switches…………………………: 15874
– Avg. Pages per Extent……………………: 7.9
– Scan Density [Best Count:Actual Count]…….: 98.87% [15696:15875]
– Logical Scan Fragmentation ………………: 1.34%
– Extent Scan Fragmentation ……………….: 70.60%
– Avg. Bytes Free per Page…………………: 176.3
– Avg. Page Density (full)…………………: 97.82%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
How do I interprete the results?

http://www.sql-server-performance.com/dt_dbcc_showcontig.asp – understand SHOWCONTIG results.
http://www.sql-server-performance.com/q&a13.asp – estimate table size. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>