SQL Server – Count table rows for all databases using sp_MSforeachdb and sp_MSforeachtable

This post is inspired from one of other post in SQL Server Geeks by Ahmad Osama.

http://www.sqlservergeeks.com/sql-server-sp_spaceused-returns-wrong-count/

I would like to extend some of my ideas further to get the table count as below. At times, SQL Server developers/DBAs might need to know the table row count for all tables from all databases available on a server. There are various approaches to get the row counts in SQL Server.

Here are few approaches as below:
Approach 1:


DECLARE @TableRowCounts TABLE ([databaseNAme] Varchar(100),[TableName] VARCHAR(128), [RowCount] INT) ;
	INSERT INTO @TableRowCounts ([databaseNAme],[TableName], [RowCount])
	EXEC sp_MSforeachdb 'select ''?'' as database_name,o.name,max(i.rowcnt )
						  From sys.objects o 
						inner join sys.sysindexes i on o.object_id=i.id 
						where o.type=''U'' 
						group by o.name' ;
						
Select * From @TableRowCounts

For Partition tables, the above query can be changed a bit as below:
Transact-SQL


DECLARE @TableRowCounts TABLE ([databaseNAme] Varchar(100),[TableName] VARCHAR(128), [RowCount] INT) ;
	INSERT INTO @TableRowCounts ([databaseNAme],[TableName], [RowCount])
	EXEC sp_MSforeachdb 'SELECT ''?'',TBL.name, SUM(PART.rows) AS rows
						FROM sys.tables TBL
						INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
						INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
						AND PART.index_id = IDX.index_id
						WHERE IDX.index_id < 2
						GROUP BY TBL.object_id, TBL.name;' ;
						
Select * From @TableRowCounts

Approach 2:


DECLARE @TableRowCounts TABLE ([databaseNAme] Varchar(100),[TableName] VARCHAR(128), [RowCount] INT) ;
	INSERT INTO @TableRowCounts ([databaseNAme],[TableName], [RowCount])
	
EXEC sp_MSforeachdb 'SELECT ''?'',OBJECT_NAME(object_id), SUM(row_count) AS rows
FROM sys.dm_db_partition_stats
WHERE index_id < 2
GROUP BY OBJECT_NAME(object_id)' ;

Select * From @TableRowCounts

Likewise, there are still more ways to get the info in SQL Server. I do not really want to list all the methods, as a simple google search would end up with a lots of scripts for the same. Rather, want to deal about one of major drawbacks with such approaches is it may or may not be accurate in value. If you look at the MSDN articles for each catalogs used in the above approaches, you can see that it clearly suggests based on the approximate values. So if we are looking for an accurate value for tables that are undergoing frequent DELETE/INSERT, then we should really rely on COUNT() function. As COUNT function reads the data, the row count would be more accurate than any other methods.

Here is my attempt to get the count using sp_MSforeachdb and sp_MSforeachtable. I tried to make the script as simple as possible. You may have a look at below:


create Table  TableRowCounts  ([databaseNAme] Varchar(100),[TableName] VARCHAR(128), [RowCount] INT) ;
 
EXEC sp_MSforeachdb
@command1 = 'IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))
				INSERT INTO TableRowCounts ([databaseNAme],[TableName], [RowCount])
				EXEC [?].dbo.sp_MSforeachtable 
				@command1 = ''SELECT ''''?'''',''''&'''',count(1) FROM &'',
					@replacechar = ''&'''

Select * From TableRowCounts
					
Drop Table TableRowCounts
 

Note: You should be very careful as its a very expensive query, you may use at your own risk.Both sp_MSforEachDB and sp_MSforEachtable are undocumented as well.

Hope you enjoyed this post, please share your thoughts on the same.

Advertisements

SQL Formatters – Beautify your code and coding experience

Today, I am going to introduce some of tools available for SQL Server formatting.

The list is not in order of any preferences, but just the way it is. Explore and use as your choice.

1. Format SQL – https://format-sql.com/
2. Instant SQL Formatter – http://www.dpriver.com/pp/sqlformat.htm
3. SQL Prompt from redgate – http://www.red-gate.com/products/sql-development/sql-prompt/
4. SQLinForm – http://www.sqlinform.com/
5. SQL Formatter – http://www.sql-format.com/
6. SQL beautifier – https://sourceforge.net/projects/fsqlf/

You may comment if you are using any other tools of your choice.

CREATE OR ALTER in SQL Server

One of my colleague was asking a follow up question on my earlier post DROP IF EXISTS – A handy feature in SQL Server 2016

Do we have something similar for Creating objects?
The good news is that we have “CREATE or ALTER” in SQL Server 2016 to address our long waiting requirement. However, this will not be available in RTM, but only from SP1 and above.

Applies to : SQL Server 2016 SP1 or later versions

Here is a small script to verify the same.
SQL Script:


--Creating the procedure first time
CREATE OR ALTER PROCEDURE SP_TEST
AS
BEGIN
	SELECT 2
END
GO
EXEC SP_TEST
GO
--Altering the procedure 
CREATE OR ALTER PROCEDURE SP_TEST
AS
BEGIN
	SELECT 200
END
GO
EXEC SP_TEST

create-or-replace

Note: This can also be used for Function objects to create or alter operations.

How do we cleanup plan cache in SQL Server?

At times, we may need to cleanup plan cache in SQL Server. I have come across situations where I need to flush the plans from the cache for our testing purposes
to troubleshoot performance issues related to procedure/test environment.However, there is caution for this to use at production boxes as it may hurt the performance badly.

There are three ways to clean up plan cache as per our requirement.

1. DBCC FREEPROCCACHE

– This DBCC command will clear/clean the entire cached plans from the SQL Server for the instance
– I personally used for my test environment to clean up the entire plan before do a load test and followed by a single user test execution(this to generate fresh plan without affecting the actual load test)


--Clean up the entire plans for the instance
DBCC FREEPROCCACHE with NO_INFOMSGS
GO
--Check the count of the plans
Select dbid,db_name(dbid),count(1) From sys.dm_exec_cached_plans dec
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
where db_name(dbid) in ('Nov_ProdStats','Dec_ProdStats') --Sample Databases for the demonstration
Group by db_name(dbid), dbid


2. DBCC FLUSHPROCINDB

– This DBCC command will flush the plans for a database
– Database id is a mandatory parameter for FLUSHPROCINDB execution
– This way we will make sure the clean up will not have any impact on other databases


--Clean up the plans for a database 
DBCC FLUSHPROCINDB (8) -- 8 is the databaseid of Dec_ProdStats
GO
--Check the count of the plans
Select dbid,db_name(dbid),count(1) From sys.dm_exec_cached_plans dec
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
where db_name(dbid) in ('Nov_ProdStats','Dec_ProdStats') --Sample Databases for the demonstration
Group by db_name(dbid), dbid


3. DBCC FREEPROCCACHE with plan handle as parameter

– This is to clean up the cache only for the plan handle supplied

	
--Identify the plan handle
SELECT cp.plan_handle, st.[text],*
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE object_name(objectid)='test'--test is a sample procedure name
GO
--Clean up the plan for TEST procedure
DBCC FREEPROCCACHE(0x05000C001B67A4684061C3B9010000000000000000000000)
GO
--Check the count of the plans
Select dbid,db_name(dbid),count(1) From sys.dm_exec_cached_plans dec
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
where db_name(dbid) in ('Nov_ProdStats','Dec_ProdStats') --Sample Databases for the demonstration
Group by db_name(dbid), dbid

Hope you enjoyed this one, see you soon with another one. Meanwhile you can share any feedback or your thoughts here.

Query Store in SQL Server 2016 – Overview

QueryStore feature in SQL Server 2016 is very helpful for any SQL Server resource to identify and troubleshoot the performance issues in SQL Server. This feature enables to store the query information along with query plan. The query execution statistics also collected associated with each query.

Applies to: SQL Server 2016 and later

Why do we need QueryStore feature or How does it help?

Yes, we do have an option to analyse the query execution before Querystore using Cachedplan in earlier versions. However, the downside is Cached plan will be saved in memory and it will not be available once SQL Server has been restarted. This was always a challenge for DBA/performance engineer to identify issues with Query execution.

QueryStore actually stores plan and other information in memory initially and as per the configuration it writes to disk.So, even SQL Server gets restarted, the information can be collected from the disk(those written in the disk).

Let us see the plan of the query to understand in detail.
In the plan we can see there are objects like – plan_persist_* (to read the data from disk) and QUERY_STORE_*_IN_MEM (to read data from memory)
*- denotes different objects like query/plan/runtime_stats etc…

To understand simply, let us look at the plan for a simple dmv: Sys.query_store_query

qs_plan

What do we need to configure for Querystore?

qs_properties

General ->
Operation Mode(Actual) – This is disabled by default to represent the Actual mode of Operation.

Operation Mode(Requested) – This is to configure the mode of Operations.
Options are OFF – to disable the QueryStore
Read Only – You can ONLY read information from QueryStore
Read Write – You can READ and WRITE information to QueryStore

Monitoring – >
Data Flush Interval(Minutes) – This is to specify how frequent the data needs to write to Disk from memory. As higher the number, there is a risk of losing data(if any restart occurs between the specified vaue), lower the number there is an overhead of writing the information to disk. Default value is 15 minutes.

Statistics Collection Interval – This is to record the information about execution statistics of queries being executed.Default is 1 hour.

Query Store Retention – >
Max ize(MB) – The maximum size in MB that can be allocated to store the information. Default value is 100. Once the size reaches out the specified value, it will NEVER write further, only read is available.

Query Store Capture Mode – This is to determine what needs to be collected.
Options are All – (default) which collects all query information
Auto – Collects only the relevant queries, filters out the insignificant queries.However, there is no external influence on identifying the significance, its completely an internal process.
None – No collection of information

Size Based Cleanup Mode – Auto – (Default if QueryStore is enabled.)This will cleanup old and least expensive queries from QueryStore as per the size specified.
Off – No automatic cleanup

Stale Query Threshold(Days) – Number days the Query information stays for. By enabling the QueryStore, the default value sets as 30 days.

Where do we get the information from QueryStore?

Microsoft has provided a good number of DMVs to get the QueryStore information.
Sys.database_query_store_options
Sys.query_store_query
Sys.query_store_query_text
Sys.query_context_settings
Sys.query_store_plan
Sys.query_store_runtime_stats
Sys.query_store_runtime_stats_interval

The above DMVs provide enough information about Query execution statistics for you to troubleshoot your query performances.

How to purge the querystore information?
You can always purge the querystore information using wizard “Purge Query Data” in the Database Properties – > Query Store or by using the below T-SQL:

ALTER DATABASE [Test] SET QUERY_STORE CLEAR ALL

DROP IF EXISTS – A handy feature in SQL Server 2016

Microsoft has introduced a handy cool feature in SQL Server 2016 called DROP IF EXISTS for objects.

Applies to : SQL Server 2016 and later
This is applicable for dropping an object with existence check. In early versions, we need to write the existence condition and then drop the objects. For an example, if we need to drop a table, we would write code as follows:


Create Table dbo.ExistsCheck(Col1 int)

--Code to check the existence and drop the object
If exists (Select 1 From sys.tables where name='dbo.ExistsCheck')
	Drop table dbo.ExistsCheck

Let us look at the execution plan and the statistics information for the below:

ifexists_old

SQL Server 2016 and later version has a new way to do as follows:


--Syntax
DORP OBJECT_TYPE [ IF EXISTS ] OBJECT_NAME

      Object_Type can be Table, Procedure, View, Function, Database, Trigger, Assembly(not supported in AZURE), Sequence, Index, Role, user,type,synonym,column.
--Eg:
Drop table If exists dbo.ExistsCheck

Using the above syntax, it is interested to note that no execution plan and no statistics generated as those are trivial operations.

ifexists_new

I personally feel this is a nice feature we can adopt in new development activities(caveat: only for SQL Server 2016 and later versions).