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 !!!

Advertisements

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).

How to check SQL Services status in SQL Server

As a DBA, it is very important for all of us to make sure SQL Services are up and running.But Is there an easy way to get this information in SQL Server?

The answer is simple, yes. Microsoft has introduced a new DMVs – sys.dm_server_services. dm_server_services DMV is available right from SQL Server 2008R2 (SP1). This has made life easy for most of DBAs from writing a bunch of codes.

SELECT 
	SERVICENAME, STARTUP_TYPE_DESC, STATUS_DESC,
        LAST_STARTUP_TIME,SERVICE_ACCOUNT,
        IS_CLUSTERED ,CLUSTER_NODENAME 
FROM SYS.DM_SERVER_SERVICES

ServiceCheck

Notes:

1. Login needs to be provided with VIEW SERVER STATE PERMISSION
2. Support is available for SQL Server 2008 R2(SP1) and later versions

Read Also:
SYS.DM_SERVER_SERVICES

Workload Group Stats counters and SQL Server Edition

Problem Statement

During one of our performance evaluation, we wanted to assess the resource utilization for one of servers using the Perfmon Counters.

If you are not familiar with perfmon counters, please have a look at the below link for the improtance and significance of perfmon counter in performance evaluation.

https://technet.microsoft.com/en-us/magazine/2008.08.pulse.aspx

In our assessment, in addition to the usual counters, we wanted to collect Workload information from SQL Server.Hence, we enabled the below counters:


SQLServer:Workload Group Stats/ CPU Usage %
SQLServer:Resource Pool Stats/ CPU Usage %

After configuring the above counters in the perfmon, it is observed that the above counters are not collecting the information from the server. However, when we configured it locally, it was collecting the counter values.

These counter values can also be queried in SSMS using the below queries:


Select * From [master].[sys].[dm_os_performance_counters]
Where 
(object_name ='SQLServer:Workload Group Stats' and counter_name ='CPU Usage %') OR
(object_name ='SQLServer:Resource Pool Stat' and counter_name ='CPU Usage %')

Cause/Reason:

On further analysis, we identified that SQLServer:Workload Group Stats object contains performance counters that report information about Resource Governor workload group statistics.

As Resource Governor can only be supported for Datacenter, Enterprise, Developer and Evaluation editions, those counters can not be collected information on any other editions, eventually we have Standard edition in our environment.

Lesson Learned:

When you configure perfmon counters, always check the associated features and its supporting version.

How to track DBCC commands issued in SQL Server using default trace

If you are a production DBA and responsible for your SQL Server box, sometimes, you may need to know certain information like who run the DBCC commands or when did it triggered or How did it triggered etc.

This post provides a quick way to identify those information using the below query to track DBCC commands that were issued in a SQL Server.

The query uses FN_TRACE_GETTABLE function to read information from the default trace in SQL Server.

SELECT
	T.DatabaseName,
	T.TextData,
	T.HostName,
	T.ApplicationName,
	T.LoginName,
	T.StartTime,
    CASE T.IsSystem
		WHEN 1 THEN 'YES'
		ELSE 'NO'
		END AS IsSystem
FROM sys.FN_TRACE_GETTABLE(CONVERT(VARCHAR(150), 
	( SELECT TOP 1 f.[VALUE]                                                 
		  FROM    sys.FN_TRACE_GETINFO(NULL) f
		  WHERE   f.property = 2
		)), DEFAULT) T
WHERE T.EventClass = 116

The above code shows the way to track *ONLY* DBCC commands(EventClass = 116). However, you may be able to change the Query to filter the EVENTCLASS to identify other Audit events.

DBCC_Tracker

To add, How do you identify your default trace?

SELECT * FROM sys.traces WHERE is_default = 1

Alternatively,

SELECT *, f.[VALUE]                                                 
		  FROM    sys.FN_TRACE_GETINFO(NULL) f
		  WHERE   f.property = 2

If you want to explore on other events, you can collect the Event trace id from the below query and replace the EventClass filter value in the above query.

Select * From sys.trace_events