March 12, 2008 at 4:15 am
Try here : http://technet.microsoft.com/en-us/library/ms178085.aspx
(Good luck - it's a bit of a monster calculation, although the one for non-clustered is even worse).
March 12, 2008 at 6:02 am
Andrew Watson (3/12/2008)
Try here : http://technet.microsoft.com/en-us/library/ms178085.aspx(Good luck - it's a bit of a monster calculation, although the one for non-clustered is even worse).
Nice find. Wonder if anyone has this figured out already ... It's to early for me to start reading ...
March 12, 2008 at 6:11 am
I have to admit that I only found it so quickly because I happened to be looking at this yesterday :).
I eventually gave up trying to do it correctly due to time constraints and just used the 1% rule of thumb mentioned earlier in this thread.
March 12, 2008 at 7:40 am
Adam Bean (3/12/2008)
Andrew Watson (3/12/2008)
Try here : http://technet.microsoft.com/en-us/library/ms178085.aspx(Good luck - it's a bit of a monster calculation, although the one for non-clustered is even worse).
Nice find. Wonder if anyone has this figured out already ... It's to early for me to start reading ...
I spent some time on it a few months back. When I first read your question I almost posted the link, but I did not think it really met your needs as it is really designed for estimating the size when designing vs. determinig the actual size. I thought I had a spreadsheet with my attempt at deciphering the calc, but I can't find it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 12, 2008 at 8:07 am
Did you try the stored procedure
sp_spaceused
?
March 12, 2008 at 8:19 am
SQL ORACLE (3/12/2008)
Did you try the stored proceduresp_spaceused
?
Yes, all M$ system procs (as stated earlier) do not return the results I am looking for.
March 12, 2008 at 8:27 am
Heh... and, still, you have to pay attention to the title... "ESTIMATING the size of a clustered index."
--Jeff Moden
Change is inevitable... Change for the better is not.
March 12, 2008 at 8:32 am
http://msdn2.microsoft.com/en-us/library/ms189792.aspx
total_pages
Total number of pages allocated or reserved by this allocation unit.
data_pages
Number of used pages that have:
In-row data
LOB data
Row-overflow data
Value returned excludes internal index pages and allocation-management pages.
The M$ procs and other references found online use data_pages * 8 (which is not accurate as it does not include data), would it make sense to simply take total_pages * 8 for the clustered? This number still does not add up to the idea that a clustered index is table size + 1% though ...
March 12, 2008 at 9:24 am
Until we can get a definitive answer, I'm taking a break from these procs. I have attached the 2000 and 2005 versions, and as of now they match the output of the M$ system procs. If anyone can give me an extra set of eyes on them to see If I'm pulling back the data correctly, I'd appreciate it. They are not yet finished, but until I get this index size debate put to rest, I'm moving on.
Hopefully I can get an answer from M$ tomorrow at Heroes Happen Here ...
Thanks all
March 12, 2008 at 10:03 am
I'll start out by saying I have nothing new to add to any of the previous comments. However - you seem to be very eager to achieve this. I'm not quite sure I understand why.
What do you see this being used for? There's a lot of size info made available in 2005, through either the DMV's or the standard reports. No - it doesn't do it exactly like you're looking for it to be, but it does have specific index sizes in data pages.
Just curious why the physical size of the clustered index is important? what do you see drawing from that info?
Just trying to understand the goal.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 12, 2008 at 11:02 am
Matt Miller (3/12/2008)
I'll start out by saying I have nothing new to add to any of the previous comments. However - you seem to be very eager to achieve this. I'm not quite sure I understand why.What do you see this being used for? There's a lot of size info made available in 2005, through either the DMV's or the standard reports. No - it doesn't do it exactly like you're looking for it to be, but it does have specific index sizes in data pages.
Just curious why the physical size of the clustered index is important? what do you see drawing from that info?
Just trying to understand the goal.
I am always adding onto my library of scripts and procedures to make life easy. In this particular case, if the situation arises, I want to know how large indexes are quickly and easily. I don't want to have to muck around with other people's code, digging around in BOL and or Microsoft procedures. Not to mention in this case it would appear that the clustered index sizes reported back by system procs are not accurate. The problem presented itself when a former colleauge of mine wanted to estimate how much additional disk space would be required by changing fill factors of over hundred indexes would be, I set out on this new procedure and have already found it to be useful for my own personal activities. One of my former DBA colleagues and I have a shared repository of hundreds of custom scripts, procedures, reports, applications and much more that we continue to build upon which have been by far my greatest asset in my job.
This is a very small piece of my database maintenance application I am working on. Originally VBScript, then C#, now powershell. As of now, I can say that it is by far superior to anything I've seen published on the internet and I am very eager to finish it and publish it. Unfortunately this is my first hands on with powershell, so It probably won't be done for at least another month or so. I have never used maintenance plans, as I find they are way to limited on options and do not provide the level of historical data required for a DBA to really understand a particular environment and where improvements can be made. Same can be said for the built in reports, I don't like to use them as they are just to limited vs. utilizing the DMV's yourself and customizing them.
Once our website is up and running, you and others will have a good idea to as what our true goal is behind having a library of scripts, procedures, knowledge bases and articles readily available.
March 12, 2008 at 2:48 pm
Adam Bean (3/10/2008)
Ok, I'm creating a couple custom proc's (one for 2000, one for 2005+), and I'm trying to figure out once and for all, how SQL determines the size of an index.
I suppose it depends on what part of SQL you mean.
I have an SMO-based SQL Admin Browser that I have been working on for a couple of years now (hobby) and in one view I wanted to show all of the tables and their statistics.
This is part of what I use to get those statistics, which was taken directly from my traces of how SMO does it:
declare @PageSize float
select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'
SELECT SCHEMA_NAME(tbl.schema_id) as [Schema], tbl.*, idx.index_id,
CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusteredIndex],
ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount],
, ISNULL((select @PageSize
* SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
where i.object_id = tbl.object_id )
, 0.0) AS [IndexSpaceUsed]
, ISNULL((select @PageSize
* SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
where i.object_id = tbl.object_id)
, 0.0) AS [DataSpaceUsed]
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
Hope it helps!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply