How to get last running query based on SPID in SQL Server

We often need to identify the SQL text that was executed last time for a spid in SQL Server. Recently one of my friend was asking to get these information for his troubleshooting purpose. I have come up with a very simple query to help him as below.

Hope this will be helpful for those looking for similar information.


SELECT
sysprc.spid,sysprc.waittime,sysprc.lastwaittype,DB_NAME(sysprc.dbid) AS database_name,
sysprc.cpu,sysprc.physical_io,sysprc.login_time,sysprc.last_batch,sysprc.status,
sysprc.hostname,sysprc.[program_name],sysprc.cmd,sysprc.loginame,
OBJECT_NAME(sqltxt.objectid) AS [object_name],sqltxt.text
FROM sys.sysprocesses sysprc
OUTER APPLY sys.dm_exec_sql_text(sysprc.sql_handle) sqltxt
Advertisements

DBCC UPDATEUSAGE – Is it important post SQL Server version upgrade?

As part of migration from SQL server 2008 R2 to SQL server 2014, we listed out a list of items to be taken care post upgrade. There was a question in the panel on DBCC UPDATEUSAGE – should we really do this as part of post migration. Here are few points that I support to do this operation.

What is DBCC UPDATEUSAGE doing?

UPDATEUSAGE is a DBCC command that reports and correct inaccurate pages and rows in the catalog views.These catalogs will be used to report information for sp_spaceused and therefore it is very important to update those information using UPDATEUSAGE command.

Why do we need post migration?

When we are upgrading a SQL Server version, this is mandatory to update these catalogs, as these catalogs may be used internally for various purposes. UPDATEUSAGE command primarily updates page allocations of data/index. In addition, the command can also be used to update the row counts as well with an optional parameter – WITH COUNT_ROWS.

DBCC UPDATEUSAGE(0) WITH COUNT_ROWS

Few other details –

It is to be noted that you should not get confused by the SELECT COUNT() method and other methods that uses catalogs to get the row count information. SELECT COUNT always reads the from underlying objects, hence the information will be always accurate. Read more about it in the below link.

https://sqlzealots.com/2017/01/29/sql-server-count-table-rows-for-all-databases-using-sp_msforeachdb-and-sp_msforeachtable/

DBCC UPDATEUSAGE holds a shared lock on the object, hence this may cause a blocking scenario in a highly concurrent environment. This is not advisable to run the command on production drectly , rather to plan these to happen on maintenance window.

The other way to invoke the UPDATEUSAGE is with sp_spaceuded as below:

sp_spaceused @updateusage=true

Hope you enjoyed this blog post, let me know your thoughts on the topic.

How to track SQL Server Database connections using T-SQL

One of my friend was asking to get him information from SQL Server to troubleshoot some performance issues.
My initial understanding, to collect data from the Performance counters, however, I realized that my friend is looking for information at grouping HostName, ProgramName etc.

Hence, come up with a small script as below to capture the details every 1 minute(customized as per the request).


If exists(Select 1 From sys.tables where name='Capture_DBConnection')
Drop table Capture_DBConnection

Create Table Capture_DBConnection (CapturedTime datetime,SPID Varchar(MAX), [Status] varchar(50),Login nvarchar(100),HostNAme varchar(5000),BlkBy Varchar(MAX), DBNAME sysname null,Command varchar(MAX),CPUTIME Varchar(MAX), DISKIO Varchar(MAX),
LastBatch Varchar(MAX), ProgramName Varchar(MAX),SPID1 Varchar(MAX), REQUESTID Varchar(MAX))

While(1=1)
Begin
	Create Table #Capture_DBConnection (SPID Varchar(MAX), [Status] varchar(50),Login nvarchar(100),HostNAme varchar(5000),BlkBy Varchar(MAX), DBNAME sysname null,Command varchar(MAX),CPUTIME Varchar(MAX), DISKIO Varchar(MAX),
		LastBatch Varchar(MAX), ProgramName Varchar(MAX),SPID1 Varchar(MAX), REQUESTID Varchar(MAX))
	
	Insert into #Capture_DBConnection Exec Sp_who2
	Insert into Capture_DBConnection 
	Select getdate(),* from #Capture_DBConnection 
	Drop table #Capture_DBConnection
	Waitfor delay '00:01:00'
END

Usage:
Here are few examples how to use the data collected.


-- Row data
Select * From Capture_DBConnection

-- To get count of connection for specified group
Select CapturedTime, HostName, ProgramName, Count(CapturedTime)
From Capture_DBConnection
Group by CapturedTime, HostName, ProgramName

Hope, this script will help you if you come across such situation.

Its always important to look at the License type of your SQL Server

Its always important to look at the License type of your SQL Server!!!

Problem Statement:

We recently had an issue with CPU utilization reaching more than 95% always for database server in one of our performance test environment. Load test environments are resource intensive test, hence it is expected to have high CPU utilization. However, we could observe the number of tests processed and number of transactions are very less spiking the CPU utilization to 95%.

Let me explain a bit more on my environment, We have 4 sockets with 10 physical cores and HT enabled in our test environment. As per the configuration we have total 80 logical CPU available. SQL Server version information as below:


Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 
               Jun 17 2016 19:14:09 
               Copyright (c) Microsoft Corporation
               Enterprise Edition ((missing))Core based Licensing>((missing)) (64-bit) on Windows NT 6.3  (Build 9600: )

Here we can observe that the version information is missing “Core based Licensing”, that means, the SQL version is not Core based, but CAL based. Let us look at the excerpt from the MSDN article:

“Enterprise Edition with Server + Client Access License (CAL) based licensing (not available for new agreements) is limited to a maximum of 20 cores per SQL Server instance. There are no limits under the Core-based Server Licensing model”

So, though we have 4 sockets with 10 Cores, ONLY 20 cores are VISIBLE for SQL Server. In our environment, this was 20*2(HT enabled) = 40 Logical CPUs are visible.

Ref: https://technet.microsoft.com/en-us/library/cc645993(v=sql.120).aspx

Lets confirm the above with other parameters DMV -sys.dm_os_schedulers.


Select parent_node_id,Count(cpu_id) Total_Schedulers,
 count(Case when Status = 'VISIBLE ONLINE' Then 1 Else null End) Visible_Count,
 count(Case when Status = 'VISIBLE OFFLINE' Then 1 Else null End) NotVisible_Count
From sys.dm_os_schedulers 
where status in ('VISIBLE ONLINE','VISIBLE OFFLINE') 
and parent_node_id not in (64)--DAC
Group by parent_node_id

The result looks like below:


This is clear that SQL server was not able to utilize more than 40 logical CPU in the above environment.

Once we upgraded the license to Core Based License, we were able to use all available CPUs in our environment and observed the database CPU utilization has come down to 65% resulting more number of tests and transactions.

Let me reiterate, Its always important to look at the License type of your SQL Server !!!

How to move TEMPDB files to different drives in SQL Server

Today, we are going to see how to move the TEMPDB files from one location to another.
First off, we should know the number of files and the its current location.
Query


SELECT NAME,PHYSICAL_NAME FROM SYS.MASTER_FILES WHERE DATABASE_ID=2

Query to move the files to new location

You may change the @NEWPATH variable value as per your new location.

DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @NEWPATH VARCHAR(MAX) = 'D:\DATA\DATA'
SET @SQL= @SQL + (SELECT 'ALTER DATABASE TEMPDB MODIFY FILE (NAME = ' + NAME + ', FILENAME = ''' + 
				@NEWPATH + RIGHT(PHYSICAL_NAME,CHARINDEX('\',REVERSE(PHYSICAL_NAME))) + ''');'
FROM SYS.MASTER_FILES WHERE DATABASE_ID = 2 FOR XML PATH(''))

EXEC ( @SQL )

Once you executed the above query, you need to restart the server to see the changes.

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

You can also go to the previous file location and delete the old files from the folder.

It is important to know that the moving of TEMPDB files no longer supporting using backup & restore OR  detaching & attaching(system databases).

T-SQL Script to restore backup files from a folder in SQL Server

Very often, myself or my friends were in need of a script to restore the backups from a predefined folder. It was hard to write the script in the hour of need. Hence, I thought I will share the script that has been created recently. Hope this script would be useful for you as well.


DECLARE @BACKUPFILENAME VARCHAR(MAX) = ''
DECLARE @MDFLOGICALNAME NVARCHAR(100)
DECLARE @LDFLOGICALNAME NVARCHAR(100)
DECLARE @DEST_PATH NVARCHAR(100)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DATABASENAME SYSNAME
CREATE TABLE #TRAILUPGRADEBACKUPFILES (ID INT IDENTITY(1,1),FILENAME SYSNAME,RESTORE_DRIVE CHAR(1))

/*********************************************************************************************************************************/
--The below code fetches the file list(backup file names) to be restored from the pre-defined folder
DECLARE @CommandShell TABLE ( Line VARCHAR(512)) 
DECLARE  @CMD VARCHAR(512) ,@BackupFilePath NVARCHAR(256)
SET @BackupFilePath = 'C:\TEMP_TOBEDELETED\' 
SET @CMD = 'DIR /B ' + @BackupFilePath +  ' /TC' 
				
INSERT INTO @CommandShell EXEC MASTER..xp_cmdshell   @CMD 
    
-- Delete lines not containing filename
DELETE 
FROM   @CommandShell 
WHERE  Line is null

If((Select Top 1 Line From @CommandShell) = 'Access is denied.') 
    PRINT 'Folder access is not provided for network path'
				
INSERT INTO #TRAILUPGRADEBACKUPFILES (FILENAME)
	Select * From @CommandShell A WHERE CHARINDEX('.BAK',LINE) > 0
/*********************************************************************************************************************************/

CREATE TABLE #TFILELISTONLY 
(
    TLOGINNAME SYSNAME,TPHYSICALNAME VARCHAR(MAX),
    TTYPE VARCHAR(1),TFILEGROUPNAME  VARCHAR(MAX),
    TSIZE BIGINT,TMAXSIZE BIGINT,TFIELD  VARCHAR(MAX),
    TCREATELSN VARCHAR(MAX),TDROPLSN VARCHAR(MAX),
    TUNIQUEID VARCHAR(MAX),READONLYLSN VARCHAR(MAX),
    READWRITELSN VARCHAR(MAX),BACKUPSIZEINBYTES VARCHAR(MAX),
    SOURCEBLOCKSIZE VARCHAR(MAX),FILEGROUPID VARCHAR(MAX),
    LOGGROUPGUID VARCHAR(MAX),DIFFERENTIALLSN VARCHAR(MAX),
    DIFFERENTIALBASEGUID VARCHAR(MAX),ISREADONLY VARCHAR(MAX),
    ISPRESENT VARCHAR(MAX),TDEHUMBPRINT VARCHAR(MAX),
)

WHILE EXISTS(SELECT 1 FROM #TRAILUPGRADEBACKUPFILES)
BEGIN
	
	SET @BACKUPFILENAME = (SELECT TOP 1 FILENAME FROM #TRAILUPGRADEBACKUPFILES ORDER BY FILENAME ASC)
	--Restore Path needs to be provided in the below
	SET @DEST_PATH = 'C:\DATA\'
		
	SET @DATABASENAME = (SUBSTRING(@BACKUPFILENAME,0,CHARINDEX('.',@BACKUPFILENAME)))
				
	--To check if the database is already present in the environment, If present, Drop the database
	IF Exists(SELECT 1 FROM master.sys.databases WHERE name = @DATABASENAME)
	BEGIN
	
		EXEC('DROP Database '+@DATABASENAME)
		Print @BACKUPFILENAME + ' has been successfully dropped.'
		
	END

	INSERT INTO #TFILELISTONLY
		EXEC ('RESTORE FILELISTONLY FROM DISK ='''+ @BackupFilePath +  @BACKUPFILENAME+'''')

	SET @MDFLOGICALNAME = (SELECT TLOGINNAME FROM #TFILELISTONLY WHERE TTYPE ='D')
	SELECT @LDFLOGICALNAME = (SELECT TLOGINNAME FROM #TFILELISTONLY WHERE TTYPE ='L')
        
	SELECT @SQL ='RESTORE DATABASE ' + @DATABASENAME + ' FROM DISK = ''' +  @BackupFilePath + @BACKUPFILENAME + ''' 
		WITH MOVE ''' + @MDFLOGICALNAME + ''' TO ''' + @DEST_PATH + @DATABASENAME + '.MDF'', 
		MOVE '''  + +  @LDFLOGICALNAME + ''' TO ''' + @DEST_PATH + @DATABASENAME + '.LDF'''
	
	EXEC (@SQL)
	
	Print @BACKUPFILENAME + ' has been successfully restored.'
		
	EXEC ('ALTER DATABASE [' + @DATABASENAME + '] SET RECOVERY SIMPLE;')

	DELETE FROM #TFILELISTONLY


	DELETE FROM #TRAILUPGRADEBACKUPFILES WHERE FILENAME =@BACKUPFILENAME	

END

DROP TABLE #TFILELISTONLY,#TRAILUPGRADEBACKUPFILES

How to identify start up Parameters in SQL Server using T-SQL

Here is a very simple script to identify the startup parameter in SQL Server using T-SQL.


SELECT
    registry_key,
    value_name,
    value_data
FROM sys.dm_server_registry 
WHERE 
    registry_key LIKE N'%MSSQLServer\Parameters';

If you are aware of any other methods, I request you to share for other’s benefits.

EDIT: (On 22nd Aug 2016)
One of my colleague executed the above script and reported me back that he is getting the below error:

Msg 208, Level 16, State 1, Line 9
Invalid object name 'sys.dm_server_registry'.

The issue was that the version of his SQL Server was SQL Server 2008R2 (RTM).
This is a new DMV introduced from service pack (SP1) and available only for later versions of SQL Server 2008R2(SP1).