If you ever designed a table, you must have thought how much space this table will take on disk, memory, how can I estimate the amount of memory for the server etc'. well, I can't help you there, there are too many things to take into consideration to write in a short blog post. However, I can help you with one thing, estimating the size of a row, just double it with the amount of rows you estimate will be in the table and you have a pretty close number, but you do have...
Wednesday, March 14, 2012
Tuesday, March 6, 2012
SQL Table Space
I was doing capacity planning the other day and I needed to know which tables take how much space, how many rows they have etc'. There's a system stored procedure for that, sp_spaceused, but it only works on the whole database or a specific table and i tried pushing its results into a table and sorting by it, but it converts the numbers to a string so the order didn't exactly worked. so I looked in the sp's innards and wrote the following: select stats.name, row_count, stats.reserved_page_count * 8 as reservedKB, stats.pages * 8 as dataKB,...
Tags: dm_db_partition_stats, rows, sp_spaceused, space, sql
Monday, March 5, 2012
Rebuild database
While still in design and development, a database goes through many changes, tables and columns are added and removed, records are added and deleted, sometimes performance tests are performed, a data structure goes through sanity testing, indexes get fragmented, etc'. this leaves a lot of junk in the database files, sometimes this space is reclaimed by the SQL engine and sometimes you need to ask for it specifically, that is the purpose of this stored procedure. I've used portions of it over the years but only this time I've decided to collect all of...
Tags: sql
Subscribe to:
Posts (Atom)