Temporary table caching in SQL Server

Caching of Temporary table is an interesting topic but not appreciated/recognized one in SQL Server due to several reasons.

First off, Let us see whats caching of temporary tables? – Caching temporary objects like temp tables/table variables will improve the performance of execution by eliminating the
recreation of objects.There are certain operations which will prevent the caching of temporary objects are below.

1. DDL commands once the object is created
2. Recompilation of Procedure associated
3. Dynamic SQL is used to create the objects
4. Named Constraints etc

Today, we are going to see how DDL commands is preventing the caching of temp objects.

Lets Create a Procedure and create a temp object:


--Create a procedure 
create Procedure Test_TempCaching
as
Begin
	create Table #Temp (EmpId int, FirstName varchar(50), LastName Varchar(50))
	create clustered index ix_test on #Temp(EmpId)
	Insert into #Temp Values(1,'SQL','Server'),(2, 'Biz','Talk')
End

We are going to see the temp tables creation rate using the below query:


SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate'

The above will result a cumulative information on the number of times the temp objects created.

For the first time, when we create the procedure and executing the procedure, it will create a plan for the procedure and temp object is being created and cached.
If you look at the procedure, A temp table is created and later the object has been modified with DDL to add an index on the temp table.

Every time, the procedure gets executed, you can see the value gets changed,
Use the below query multiple times, and see the difference.


SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate'
EXEC  Test_TempCaching
SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate'


The above behavior shows that, if there is any DDL applied after the temp object, then the object will become obsolete and evict from the cache(later) and create a new object. You may find a performance issue if the procedure is executing very frequently in your application.

Now, Let us modify the procedure to comine the DDL command inline at the creation of temporary table as below:


ALTER Procedure Test_TempCaching
as
Begin
	create Table #Temp (EmpId int, FirstName varchar(50), LastName Varchar(50), Primary Key(EmpID))
	Insert into #Temp Values(1,'SQL','Server'),(2, 'Biz','Talk')

End

If you execute for the first time, you can execute the value gets changed. This is because, for the first time, the plan from the cache will be deleted once the procedure altered and a new plan will be created for the procedure. But for subsequent execution, the cntr_value never gets changed as the object has already been cached.


SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate'
EXEC  Test_TempCaching
SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate'

Conclusion:
If you see more high temp tables creation rate in your application, you may find the procedures and see if you can take advantage of the above scenario.
There is a caveat – statistics with temp objects(I am not covering here),you may need to verify and confirm the changes thoroughly to make sure you do not have any other performance issues.

Advertisements

ALTER DATABASE failed because a lock could not be placed on database ‘DBNAME’. Try again later. in SQL Server

At times, we may need to take a database offline. And there is a chance that we encounter an error message like below.
How do we solve this issue and take the database offline?


Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'DBNAME'. Try again later.

Solution:
If there are any connections open, then there is a lock on the database while getting the database into Single user mode/taking offline.
To resolve the issue, as a first step you need to kill all the sessions on the database followed by the ALTER database statement.


USE master;

DECLARE @killSessions varchar(8000) = '';  
SELECT @killSessions = @killSessions + 'kill ' + CONVERT(varchar(5), spid) + ';'  
FROM master..sysprocesses  
WHERE dbid = db_id('dbname')
EXEC(@killSessions); 

ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE dbname SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE dbname  SET MULTI_USER;

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

One of my colleague was facing an issue while inserting 2000 values to a table variable as below:


 DECLARE @EMPLOYEEDETAILS TABLE
(OLDEMPLOYEECODE NVARCHAR(30), NEWEMPLOYEECODE NVARCHAR(30))

INSERT INTO @EMPLOYEEDETAILS(OLDEMPLOYEECODE, NEWEMPLOYEECODE)
VALUES
('93466','0000007'),
('93467','0000010'),
...
...(2000 Records)
...
('93467','00002000')

Error Message:
Msg 10738, Level 15, State 1, Line 1006
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

This is a self explanatory error message as SQL Server does not support row value expressions for more than 1000 values.
To overcome the issue, the code has been modified as below and the query has been executed successfully.

Solution:


DECLARE @EMPLOYEEDETAILS TABLE
(OLDEMPLOYEECODE NVARCHAR(30), NEWEMPLOYEECODE NVARCHAR(30))

INSERT INTO @EMPLOYEEDETAILS(OLDEMPLOYEECODE, NEWEMPLOYEECODE)
SELECT * FROM (VALUES
('93466','0000007'),
('93467','0000010'),
...
...(2000 Records)
...
('93467','00002000')
) A(Col1, Col2)

You may share any other alternatives/thoughts….

Could not load assembly ‘Microsoft.SqlServer.Connectioninfo, Version=10.0.0.0, Culture=netural, PublicKeyToken=89845dcd8080cc91’ or one Of its dependencies. The system cannot find the file specified.

Problem Statement:

When trying to migrate SQL Server 2008 R2 to SQL Server 2016, we identified an issue with the following error:

Could not load assembly ‘Microsoft.SqlServer.Connectioninfo, Version=10.0.0.0, Culture=netural, PublicKeyToken=89845dcd8080cc91’ or one
Of its dependencies. The system cannot find the file specified.

As a knee jerk reaction, we initially thought it was an issue with SQL Server 2016 installation and need to install the Feature Pack for SQL Server 2016. Once applied the feature Pack, we again tested and found the same issue unfortunately(but really fortunate one!!!).

Analyzing further(I mean, reading the error message clearly, It is found the version 10.0.0 that represents Microsoft® SQL Server® 2008.
As we do not have SQL Server 2008 installed in the environment and the code(.NET application) has some reference to the version mentioned, this was not able to find the dlls in the specified path(C:\windows\assemblies\).

To proceed with our testing, we installed SQL Server 2008 R2 Feature pack and resumed our testing. Finally, it worked like a treat!!!

The learning is we always need to go to the facts(error message) than blindly presume things!!!

please find the below information for various version feature pack references:

Microsoft® SQL Server® 2016 Feature Pack (Version: 13.0.1601.0)
https://www.microsoft.com/en-us/download/details.aspx?id=52676
Microsoft® SQL Server® 2014 SP2 Feature Pack (Version: 12.0.5000.0)
https://www.microsoft.com/en-us/download/details.aspx?id=53164
Microsoft® SQL Server® 2014 Feature Pack (Version: 12.0.0.0)
http://www.microsoft.com/en-us/download/details.aspx?id=42295
Microsoft® SQL Server® 2012 SP1 Feature Pack (Version: 11.0.0.0)
http://www.microsoft.com/en-us/download/details.aspx?id=35580
Microsoft® SQL Server® 2012 Feature Pack (Version: 11.0.2100.60)
https://www.microsoft.com/en-us/download/details.aspx?id=29065
Microsoft® SQL Server® 2008 R2 SP2 Feature Pack (Version: 10.50.4000.0)
https://www.microsoft.com/en-us/download/details.aspx?id=30440
Microsoft® SQL Server® 2008 R2 SP1 Feature Pack (Version: 10.50.2500.0)
http://www.microsoft.com/en-us/download/details.aspx?id=26728
Microsoft® SQL Server® 2008 R2 Feature Pack (Version: 10.50.1600.1)
https://www.microsoft.com/en-us/download/details.aspx?id=16978
Microsoft SQL Server 2008 Service Pack 4 Feature Pack (Version: 10.0.6000.29)
https://www.microsoft.com/en-in/download/details.aspx?id=44277
Microsoft SQL Server 2008 Service Pack 3 Feature Pack (Version: 10.00.5500.00)
https://www.microsoft.com/en-us/download/details.aspx?id=27596
Microsoft SQL Server 2008 Service Pack 2 Feature Pack (Version: 10.00.4000.00)
https://www.microsoft.com/en-us/download/details.aspx?id=6375
SQL Server 2008 Service Pack 1 (Version: 10.00.2531.00)
https://www.microsoft.com/en-in/download/details.aspx?id=20302

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.

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.