Helpful little tip you might want to review !!!
Everyone does the following to get the correct row count…
select count(*) from JustEnergy.dbo.DirectDebitCreditCard
But maybe you should use !!!
select count ( * ) from JustEnergy.dbo.DirectDebitCreditCard option (MAXDOP 1)
You can also use the following which is a hidden Stored Procedure to get the
‘rows’…
use justenergy
go
sp_spaceused ‘dbo.DirectDebitCreditCard’
Which will return the following as an example
name rows reserved
data index_size unused
DirectDebitCreditCard 559028 69808 KB 62136 KB 6752
KB 920 KB
However !!!
SQL Server may not necessarily be giving you the right answer !!
Because if you read this link below you can find out why…
/**
DBCC UPDATEUSAGE(0) --Please reference this page for explainatin of
negative values for your need to run this command first
http://sqlserver2000.databases.aspfaq.com/why-does-sp-spaceused-return-inaccurate-values.html
**/
So you might want to do a à DBCC CHECKTABLE(‘tablename’)
Note: The following in the article is incorrect syntax use the above syntax of :
option (MAXDOP 1)
SELECT COUNT(*) FROM tablename MAXDOP(1)
Sooo !!!
DBCC CheckTable( ‘dbo.DirectDebitCreditCard’ ) – which can take about
10(+) seconds on 560k rows.
Then run the command again just to make sure.
use justenergy
go
sp_spaceused ’ dbo .DirectDebitCreditCard’
name rows reserved
data index_size unused
DirectDebitCreditCard 559028 69808 KB 62136 KB 6752
KB 920 KB
sp_spaceused ’ webje .BillGroup824’
name rows reserved data
index_size unused
BillGroup824 372 16 KB 8 KB 8 KB
0 KB
Notation: Please take note of the schema name change between the two examples
both of which are in the same database. It is necessary because sometimes
you have identical table names in a schema as well as ‘dbo’.
The Prize !!!
If this means anything to you, I have a script I have written that will take a
database name as a variable and cycle through all the tables in a database and
give some really good simple statistics.
This script is available upon request.
Hank Freeman
Senior Systems, Database/Data Warehouse Architect
678.414.0090 my cell Primary
