December 4, 2002 at 5:33 pm
That is true, and again you are correct. I just felt that it was worth mentioning. Its an indicator of whether the rows is accurate or not. A proc which checks for a 0 value there before doing an actual count of the rows can save a major amount of processing time and in my experience has been trustworthy. With as impressed with your overall knowledge base that I've seen on the posts, I think I'd be more likely to believe you forgot it.
Oh, and not updates. Only inserts or deletes affect it.
Edited by - scorpion_66 on 12/04/2002 5:39:03 PM
December 4, 2002 at 6:29 pm
Cool something else I can add to my personal knowledge. Thanks. ANd I love the idea of check for value of 0 to verify count is accurate. I need to move that concept into some of the extra work I have going. Will also be help since I manually update my stats to get a better picture of when I need to run that.
December 5, 2002 at 6:23 am
Try using exec sp_spaceused 'tbl_TableName'
This returns how many rows and how much space the table takes up and the indices too.
December 5, 2002 at 9:21 am
Do you realize that sp_spaceused gets the rows from sysindexes?
If someone needed the sizes and such, I could see them using it, but it's a lot of unneccessary overhead if the size is not wanted.
December 6, 2002 at 3:58 am
In answer to the original question: -
'Also, in EM, when I right-click properties a table, it tells me the number of records in the table. Does anyone know how this number is generated?'
EM uses the stored procedure sp_MStablespace in the master db to get this value. This returns the three values Rows, DataSpaceUsed and IndexSpaceUsed
and internally uses the command: -
SELECT convert(int, rowcnt) FROM dbo.sysindexes WHERE indid < 2 and id = @id to retrive the number of rows.
Interestingly, I tried looking in BOL for 'sp_MStablespace' and got 'No topics found'.
Regards,
Andy Jones
.
December 6, 2002 at 5:50 am
Almost all of the sp_MS-procs are undocumented (all but one actually, of almost 500!). Many of them are not really a part of SQL Server per se, but rather apart of Enterprise Manager, just as you would create procedures for your apps. In order to have a look at them, just run sp_helptext sp_MStablespace in master, they're usually pretty well commented.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
December 11, 2002 at 10:21 pm
Try this undocumented sp.
set nocount on
create table #sp_mstablespace (Rows INT, DataSpaceUsed INT, IndexSpaceUsed INT )
Insert into #sp_mstablespace
Exec sp_mstablespace <YourTableName>
select * from #sp_mstablespace
paul
paul
December 12, 2002 at 2:52 pm
Just another comment about sp_spaceused...Even after an "update statistics" the rowcount can still be reported incorrectly in sp_spaceused. You must run a "DBCC UPDATEUSAGE ... WITH COUNT_ROWS" command to correct the number in sysindexes.
I'm not sure if using the "WITH FULLSCAN" option on "update statistics" will ensure the rowcount is reported correctly?
-Dan
-Dan
December 12, 2002 at 3:19 pm
With Fullscan does not affect rowcounts.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy