Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1878
Members Online:
2
Guests Online:
108

Total Post History
Posts:
81448
Topics:
18714

7-Day Post History
New Posts:
1
New Topics:
0
Active Topics:
1

Our newest member
kowilmar1990z

Other

FAQ
All times are UTC [ DST ]

Google Ads

Server Metrics Data Mart

Please wait...

Server Metrics Data Mart

Postby cacapo on Mon Dec 10, 2012 8:31 pm

I have been working on getting a SQL Server data mart in place to gather metrics on our servers and everything in them. I am fairly new to writing complex SQL scripts and am having a hard time figuring out how to gather how much storage space a particular column in a table is using. Any help is much appreciated.
cacapo
Newbie
Newbie
 
Posts: 3
Joined: Mon Dec 10, 2012 8:25 pm
Unrated

Re: Server Metrics Data Mart

Postby SQLDenis on Mon Dec 10, 2012 9:39 pm

Any reason you want a granularity like that? why not per table?
For numeric(int, float,numeric, tinyint, smallint,bigint) data types it is storage * rows

for (n)varchar and (n)char it is datalength per row, for example


  1. SELECT SUM(DATALENGTH(NAME)) FROM sysobjects


Don't forget you could also have 2 indexes that use this column, you have to account for that as well


I like to use sp_spaceused instead, it will give me the space that is reserved, if you have a lot of fragmentation or your fillfactor is low your storage calculation won't match what is actually being used

example
  1. exec sp_spaceused 'UserInfo'

name	        rows	        reserved	data	     index_size	unused
UserInfo 66148 28304 KB 25800 KB 2280 KB 224 KB
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Server Metrics Data Mart

Postby onpnt on Mon Dec 10, 2012 10:05 pm

To really get this done you need to consider a whole lot more. Starting point
http://msdn.microsoft.com/en-us/library/ms178085.aspx
http://msdn.microsoft.com/en-us/library/ms189124
http://msdn.microsoft.com/en-us/library/ms190620

I can easily see the reasoning for this but only for preventative performance issues like forwarded records or page splits and so on. For metrics I see no real reason not to point out the total table space utilized both on used and unused. Truly, that is the lowest level you would need for scalability estimates and growth estimates. This would become even more of an interesting thought when you bring in columnstore indexing (which is easy enough to get the size) but in thinking about that, we come back to total disk space utilized for both used and unused potential, is really the important thing. Again, unless this is for prevention of typical poorly typed columns, fill factor settings and so on that is causing normal poor design performance issues like mentioned.
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: Server Metrics Data Mart

Postby cacapo on Mon Dec 10, 2012 10:06 pm

We are setting up data mart to monitor each server, each database on server, each table in each database, and so forth as far down as you can go. So that is why I am looking for such granularity.
cacapo
Newbie
Newbie
 
Posts: 3
Joined: Mon Dec 10, 2012 8:25 pm
Unrated

Re: Server Metrics Data Mart

Postby onpnt on Mon Dec 10, 2012 10:08 pm

btw...I agree completely with Denis and sp_spaceused. No reason to overcomplicate what you need accomplished
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: Server Metrics Data Mart

Postby cacapo on Mon Dec 10, 2012 10:12 pm

onput I agree that I don't really need this level of detail. It's a task at work that is meant make me learn the ins and outs of SQL Server. I'm just in the design stage right and the other DBA told me what I need to do it on.
cacapo
Newbie
Newbie
 
Posts: 3
Joined: Mon Dec 10, 2012 8:25 pm
Unrated

Re: Server Metrics Data Mart

Postby onpnt on Mon Dec 10, 2012 11:21 pm

I'm with you there. Denis already gave you the column level space used and went into the index considerations (which is why I linked to the links provided on HEAP, clustered and nonclustered index calculations) because all that will need to come into consideration. You could simply use a CTE to smash it all together with sys.all_columns, sys.tables, sys.types and then index_stats DMVs. That would be how I'd go about it. Tried a search but didn't hit anything pre-written to that level. Seems like a strange thing to go after so may be hard to find anyone writing anything and blogging it beyond the datelength method
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: Server Metrics Data Mart

Postby gmmastros on Tue Dec 11, 2012 12:51 am

The problem here is... a column may not take up any (additional) storage space.

SQL Server stores data in 8K pages. If you already have a wide table (where the columns widths add up to something large), adding a narrow column may not take any additional space.

For example, suppose you had a table that requires 5,000 bytes for each row. SQL Server will only put one row for each 8k data page. If you added a small column (like an int) to this table, SQL Server would still store just one row of the table in each 8k data page. SQL Server wouldn't use any additional data pages either, so the size of the db wouldn't change and the size of the table wouldn't change either.

On the other hand, your original table could we 4k wide so there are 2 rows per page. Adding an int column could effectively double the size of the table because it would push it beyond the 2 rows per page to just 1.

Here's an example:

  1. Create Table DeleteMe(Id Int, Data Char(4035))
  2. GO
  3. Insert Into DeleteME Values(1, Replicate('A', 4035))
  4. Go 1000
  5. sp_spaceused 'DeleteMe'
  6. dbcc checktable('DeleteMe')
  7. GO
  8. Alter Table DeleteMe Add Data2 int
  9. go
  10. Update DeleteMe Set Data2 = 100
  11. GO
  12. sp_spaceused 'DeleteMe'
  13. dbcc checktable('DeleteMe')
  14. GO
  15. Drop Table DeleteMe
  16.  


The code shown above creates a table that is fairly wide, where just 2 rows fit within a page. The code then adds an int column to the table. When you run the code, you will see that the table size doubles by adding a simple int column. This table has just 1,000 rows, but the size increases by 4,000 kb (4 megabytes). That's 4 megabytes of additional storage space to store 1,000 integers. Each integer is 4 bytes, so it should only take 4 kilobytes to store.

This is why it's not particularly useful to know the storage space for a single column. The space required depends on the other columns in the table.
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2367
Joined: Tue Oct 09, 2007 5:19 pm