SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding Table Space


Finding Table Space

Author
Message
Amit Lohia
Amit Lohia
SSC Eights!
SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)

Group: General Forum Members
Points: 940 Visits: 174
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aLohia/findingtablespace.asp


Kindest Regards,

Amit Lohia
ChrisM
ChrisM
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1
A genuinely insightful and practical article. I recognise a good article when I go away and try it immediately. Thank you.



Tao Klerks
Tao Klerks
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1521 Visits: 1249

Hi,

Thanks for this article, it was very helpful in explaining how space is reported in the different columns of the sysindexes table, but I am left wondering why we would want to have so many separate queries, and a temporary table, when a single query something like this would do:

/* --optional, if you want to store the data:

if NOT exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[SpaceUsedByObject]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
CREATE Table SpaceUsedByObject
(
[Id] INT Identity(1,1),
ObjName sysname,
TotalSpaceUsed INT,
DataSpaceUsed INT,
IndexSpaceUsed INT,
RowCnt INT,
TodayDate DateTime Default Getdate()
)
End

INSERT INTO SpaceUsedByObject(ObjName, TotalSpaceUsed, DataSpaceUsed, IndexSpaceUsed, RowCnt)
*/

SELECT Object_Name(sysindexes.ID) AS ObjName,
Sum(Used) AS TotalSpaceUsed,
Sum(CASE WHEN IndID = 255 THEN Used ELSE Dpages END) AS DataSpaceUsed,
Sum(Used) - Sum(CASE WHEN IndID = 255 THEN Used ELSE Dpages END) AS IndexSpaceUsed,
Sum(CASE WHEN IndID = 255 THEN 0 ELSE rowcnt END) AS RowCnt
FROM sysindexes
INNER JOIN sysobjects ON sysindexes.Id=sysobjects.Id and type='u'
WHERE Indid IN (0,1,255)
GROUP BY sysindexes.ID
ORDER BY TotalSpaceUsed DESC --Added because that was really what I was interested in

Is there any disadvantage to doing everything in a single query, eg are the CASE statements more expensive than the multiple joins, subqueries and updates?

Thanks,
Tao



http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Amit Lohia
Amit Lohia
SSC Eights!
SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)

Group: General Forum Members
Points: 940 Visits: 174

Tao

The version which I use in my production database is similar to what you have mentioned. Just few more columns and change in the table name. When I wrote the article and send for proof reading with case statements I got a feedback of using multiple queries so it will be easier to understand and NOT using a table variable as many new user many not know about it. Even one of my friend mention DO NOT change the way sp_spaceused have it logic (ie with multiple queries) or you will be receive many questions as why are you using case statement.

In fact the queries which are mentioned in the articles will fail if there are objects with same name and different owners but I did not wanted to complicate the article so I left that segment out of the picture

Secondly I feel case statment will have a better performance then multiple queries but did not bench mark it. Thanks for the feedback I will try to cover this kind of ambiguities in future

Thanks

Amit




Kindest Regards,

Amit Lohia
Tao Klerks
Tao Klerks
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1521 Visits: 1249

Hi Amit,

Thanks for the explanation!

Tao



http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
QueenWai
QueenWai
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 1

Hi ,

I ran that whole query from

CREATE Table SpaceUsedByObject   to  ... UPDATE SpaceUsedByObject
part.
I have sql server 2000 version Enterprise Edition and service packs 3a and ms03-031.:
 
I got error: 
Server: Msg 512, Level 16, State 1, Line 42
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Server: Msg 512, Level 16, State 1, Line 51
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
 
when I double click the error , it is pointing to this line in bold:
UPDATE SpaceUsedByObject
SET IndexSpaceUsed = TotalSpaceUsed - (SELECT DataSpaceUsed
FROM #SpaceUsedByObjectForDpage S1
WHERE S1.ObjName=SpaceUsedByObject.ObjName
AND SpaceUsedByObject.IndexSpaceUsed IS NULL
)
WHERE IndexSpaceUsed IS NULL
 
Did you get an error ?

Amit Lohia
Amit Lohia
SSC Eights!
SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)

Group: General Forum Members
Points: 940 Visits: 174

You have the same object name under different owner that is the reason you are receiving the error. You can use the case statement to get your output




Kindest Regards,

Amit Lohia
Tao Klerks
Tao Klerks
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1521 Visits: 1249

Hi, could this be because of What Amit mentioned above?

The sample script will not work is there are multiple objects with the same name but different owners, in the same database.



http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Amit Lohia
Amit Lohia
SSC Eights!
SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)SSC Eights! (940 reputation)

Group: General Forum Members
Points: 940 Visits: 174

Tao

You are correct again. I guess I should have included this as part of the article. Good I am learning the art of writing

Amit




Kindest Regards,

Amit Lohia
Amit Garg
Amit  Garg
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 12

Hi,

That is a real good solution for calculating table space. However all these days i was using following script:

SET NOCOUNT ON
GO
IF OBJECT_ID('TEMPDB..#TMP_TBL_FOR_SPACEUSED') IS NOT NULL DROP TABLE #TMP_TBL_FOR_SPACEUSED
GO
CREATE TABLE #TMP_TBL_FOR_SPACEUSED (
NAME VARCHAR (130),
ROWS NUMERIC,
RESERVED VARCHAR (20),
DATA VARCHAR (20),
INDEX_SIZE VARCHAR (20),
UNUSED VARCHAR (20))
DECLARE CUR1 CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U'
DECLARE @TAB_NAME AS VARCHAR(256)
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @TAB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TMP_TBL_FOR_SPACEUSED EXEC SP_SPACEUSED @TAB_NAME
FETCH NEXT FROM CUR1 INTO @TAB_NAME
END
CLOSE CUR1
DEALLOCATE CUR1
SELECT * FROM #TMP_TBL_FOR_SPACEUSED ORDER BY 2 DESC
GO
IF OBJECT_ID('TEMPDB..#TMP_TBL_FOR_SPACEUSED') IS NOT NULL DROP TABLE #TMP_TBL_FOR_SPACEUSED
GO
 
May not be a good option, still .........
 
Amit

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search