Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Get record count for a specific database Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2007 2:46 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:38 PM
Points: 473, Visits: 8,736
Comments posted to this topic are about the item Get record count for a specific database

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
Post #434553
Posted Wednesday, February 6, 2008 3:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 1,262, Visits: 3,420
Hi,

Works very nice and ...counting the table with million records!

Thnx!


============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #452105
Posted Wednesday, February 6, 2008 7:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 16, 2011 1:45 PM
Points: 4, Visits: 86
Just a innocent question.. In order for the query to return correct row count, doesn't the stats of the indexes of the tables in the database need to have been updated just before collecting this information.

I have seen in the past the count doesn't quite match with the actual row count. Can you enlighten the reason behind it.
Post #452215
Posted Saturday, February 9, 2008 1:50 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:38 PM
Points: 473, Visits: 8,736
Dugi,

Thanks for the compliment. Most of the credit must go to ‘meird’; I simply had an idea that could possibly improve the usage of the results and provide a more readable structure and who wouldn’t want a grand total at the end? :)

Thanks,
James Rea


~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
Post #453598
Posted Saturday, February 9, 2008 1:53 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:38 PM
Points: 473, Visits: 8,736
Cupidking,

I apologize for the delay in the response; I wanted to ensure that I was thorough in my research and testing to give you my best opinion of the answer. With that said, keep in mind that this is an opinion and based on my independent testing.

I’ve tried researching the subject to see if I could find any evidence that would support or deny the requirement of the stats of the indexes to be updated.

My research has indicated stats could be required to be updated in SQL Server 2000 (especially prior to release of SP 2); however, to get an incorrect reading you’d have to update a table and then query the count within ‘sysindexes’ in the SAME transaction. This was resolved by Microsoft in release of SP 2 for SQL Server 2000. There was another anomaly within that Service Pack release that could potentially create a miscount of rows also. It was more particular to the ‘sysindexes.rowmodctr’ column. I am including the links to those two Knowledge Base articles for your reference; if you are trying to use this script on SQL Server 2000 then these links may help:

KB 308822: http://support.microsoft.com/kb/308822

KB 317847: http://support.microsoft.com/kb/317847

In either case that doesn’t apply to my script because the script was designed for use with SQL Server 2005, not SQL Server 2000.

With that said; I am a firm believer in that nothing is perfect and there must be a way to break the script (well, in this case to make a miscounting of the records ). To do this I had attempted a few different things and all had passed with 100% accurate results. The best method I could think of to create a miscount was to create a test table and load 1,000,000 records in it and verify the count was accurate, all within the same transaction (SQL 2000 problem would’ve resulted in a count of 0); so this confirms that previous problem had been resolved. I then used a script that would create the test table and load 1,000,000 records; then delete 300,000+ records, and then immediately perform a record count. NOTE: All actions were performed within as few commits as possible, and particularly the delete of records and getting a record count was done in ONE commit transaction. My results came back with 100% accuracy. I am including 2/3 of the script (I left out the code for the ‘Get Record Count’ because you obviously will have it already).

From my testing and results I can only deduce that the stats do not need to be updated manually if they are a factor. Again, this is all informal testing and I may very well be incorrect, but I have nothing that can prove the need to update the stats…by all means I don’t believe it’s impossible for the miscount to happen, and maybe someday I might come across a method that does indicate an incorrect count and I’ll update the script or submit a new script to address this. If you can provide any additional information, specific settings or series of events that could replicate this in SQL Server 2005 then I’d be happy to try to replicate it and deduce why this is occurring.

The following is the script I used for testing. For your (and anyone else that comes across this topic) reference, I had used SQL Server 2005 Developer Edition (Product Version: 9.00.3054.00, Product Level: Service Pack 2, IsClustered = 0).

--***START GET RECORD COUNT BREAKING SCRIPT***
USE [TEST_DB];
GO
--Detect if table already exists;
--if so then delete existing table
IF OBJECT_ID('dbo.abc_TestCount', 'TABLE') IS NOT NULL
DROP TABLE dbo.abc_TestCount;
GO
--Create new table (should not be
--included in index since it is new)
CREATE TABLE abc_TestCount
(
--Ensure to avoid using Unique
--(Unique could trigger indexing automatically)
TestID INT NOT NULL,
TestText nvarchar(25) NOT NULL,
TestDate datetime NOT NULL,
);
--Declare variables
DECLARE @tmpCount INT
DECLARE @intCount INT
DECLARE @stTest nvarchar(25)
DECLARE @stTestMod nvarchar(25)
DECLARE @dtDate datetime
DECLARE @dtDateMod datetime
--Set variables
SET @tmpCount = 0
SET @intCount = 1
SET @stTest = 'Testing # '
SET @dtDate = GETDATE()
--Perform Loop to insert code
--1 million records should be sufficient
WHILE @tmpCount < 1000000
--unfortunately must include BEGIN/END
--because of loop, and can't possibly justify
--'GetRecordCount' within loop over 1 million times
--Must keep some realistic measurements/constraints
BEGIN
SET @tmpCount = @tmpCount + 1
SET @stTestMod = @stTest + CAST(@intCount as nvarchar(25))
SET @dtDateMod = DATEADD(day, 1, @dtDate)
SET @intCount = @intCount + 1
INSERT INTO dbo.abc_TestCount
VALUES (@tmpCount, @stTestMod, @dtDateMod);
END
--Immediately deleting 300,000+ records before record count
--This should simulate an 'out-of-date' index
--if an index somehow snuck by
DELETE FROM dbo.abc_TestCOUNT
WHERE TestID > '305675' AND TestID < '623456'
--Now you would put the T-SQL to Get the Record Count
--Remember don't use any additional commits/GO commands
--between this T-SQL and the Get Record Count.


~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
Post #453599
Posted Saturday, February 9, 2008 6:38 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
I found this undocumented statement that works real well.

sp_MSForEachTable 'sp_spaceused "?"'

This statement will return a count for each table in your database / catalog.
Make sure that your query view option is set to "text" instead of the "grid" especially if you have a large number of tables.

enjoy

John Esraelo


Cheers,
John Esraelo
Post #453618
Posted Wednesday, February 13, 2008 12:22 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:38 PM
Points: 473, Visits: 8,736
John Esraelo (2/9/2008)
I found this undocumented statement that works real well.

sp_MSForEachTable 'sp_spaceused "?"'

This statement will return a count for each table in your database / catalog.
Make sure that your query view option is set to "text" instead of the "grid" especially if you have a large number of tables.

enjoy

John Esraelo


John,

That's a great stored procedure to use for iterating through all the tables. In fact that Stored Procedure was introduced back in SQL Server 6.5; it’s always been an undocumented stored procedure.

I have chosen not to use this procedure because you can’t put the results in an ORDER, SUM the results, or omit system tables (i.e. sysdiagrams) without having to use a temp table and performing a SELECT on that temp table, to not have those features would not be helpful for a database that contains millions of records, 100’s or 1000’s of tables; as you can’t easily determine the results for any type of reports or other practical uses because of the default order is not logical (for reporting/reading purposes).

To make this into a useable method you would need to create a temporary table and INSERT INTO that temp table and then perform a SELECT to pull the information you want and present it in the ORDER you find most useful, as well as SUM the results, and omit any tables you would like.

The problem I have with it is that it increases your overhead by more than 500% (this is based on a small table with 160 tables and 3,000,000 records); I couldn’t imagine what the overhead would be on a medium sized table, or yet a large table. It’s a scary thought in a world where performance is everything!

A great way to see this is to perform my script and the below method on AdventureWorks database; you may be surprised to find the execution time is way different…and this is only with 70 tables and a little over 500,000 records! Imagine a terabyte sized database?!

An example to get the same information as my script, and allow the ability to ORDER, SUM, and omit would be:

USE DATABASE_NAME;
GO
CREATE TABLE #t_rCount (vTableName varchar(50), rcount int);
EXEC SP_MSForEachTable
'INSERT INTO #t_rCount SELECT "?", count(*) from ?';
SELECT * FROM #t_rCount
WHERE vTableName <> '[dbo].[sysdiagrams]'
ORDER BY vTableName
COMPUTE SUM(rcount);
DROP TABLE #t_rCount;

I would discourage using this Stored Procedure alone if you need any type of ORDER or Aggregation function performed, or if you need to place this in a report or a logically readable format (i.e. by table name or row count). I would also highly discourage the use of this stored procedure with a temp table as the base performance overhead is well beyond any acceptable levels.

I hope this makes it clear as to why I had chosen not to use the ‘SP_MSForEachTable’ stored procedure.

Thanks,
James Rea


~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
Post #455290
Posted Wednesday, February 13, 2008 12:36 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
I like using sp_msforeachtable for certain quick and dirty works.
sp_msforeachtable as you know is a base function / sp that overloads some requests, meaning you can run other functions thru this sp.
You are absolutely right, there are times that you don't want to make and push the server do silly things and waste the system resouces.
One of the usages for this sp would be something in the line of indexing all the table at 2:00 am and I have tried that with couple of hundred tables and two or three indexes on each table and an average of few 10s of thousands records in 25 seconds.

Your experience shows that you have dealt with many great databases and obviously I can learn few things from you.
thx for the reply and looking forward in learning more cool things from you in SQL Server Central.

John Esraelo


Cheers,
John Esraelo
Post #455307
Posted Wednesday, February 13, 2008 2:52 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:38 PM
Points: 473, Visits: 8,736
John Esraelo (2/13/2008)
I like using sp_msforeachtable for certain quick and dirty works.
sp_msforeachtable as you know is a base function / sp that overloads some requests, meaning you can run other functions thru this sp.
You are absolutely right, there are times that you don't want to make and push the server do silly things and waste the system resouces.
One of the usages for this sp would be something in the line of indexing all the table at 2:00 am and I have tried that with couple of hundred tables and two or three indexes on each table and an average of few 10s of thousands records in 25 seconds.

Your experience shows that you have dealt with many great databases and obviously I can learn few things from you.
thx for the reply and looking forward in learning more cool things from you in SQL Server Central.

John Esraelo


John,

Thanks for the compliment; I just have different experiences. I am sure I can, and will, learn as much from you.

I agree with 100% that SP_MSForEachTable is a great method for small / non-complex tasks that require iterating throughout the database. Indexing, believe it or not, is not something I've thought of using it for...go figure, I've used the good ol' fashioned SSIS Scripting trick...but, I've also liked the ability to log the results (Successful or Failure) to a flat file for later review. I'll definitely give SP_MSForEachTable a shot on one of my test systems to see the benefits.

I look forward to many, many future discussions with you as well.

Thanks,
James Rea


~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
Post #455419
Posted Tuesday, March 25, 2008 5:48 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,308, Visits: 1,378
Thnx guys for all ur solutions. Also thank the author for an article.

:P



Post #473962
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse