Category: SCRIPT GALLERY

SCRIPT GALLERY, SQL

How to identify the CPU utilization in SQL Server

Using ring buffers

DECLARE @ts_now BIGINT = (SELECT cpu_ticks / ( cpu_ticks / ms_ticks )
   FROM   sys.dm_os_sys_info WITH (nolock));

SELECT TOP(256) sqlprocessutilization             AS [SQL Server Process CPU Utilization],
                systemidle                        AS [System Idle Process],
               100 - systemidle - sqlprocessutilization AS [Other Process CPU Utilization],
                Dateadd(ms, -1 * ( @ts_now - [timestamp] ), Getdate()) AS [Event Time]
FROM   
(SELECT 
       record.value('(./Record/@id)[1]', 'int') AS record_id,
       record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')         AS [SystemIdle],
       record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
       [timestamp]
        FROM   (SELECT [timestamp],
                       CONVERT(XML, record) AS [record]
                FROM   sys.dm_os_ring_buffers WITH (nolock)
                WHERE  ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
                       AND record LIKE N'%%') AS x
) AS y
ORDER  BY record_id DESC
OPTION (recompile);

ring buffers is a good way to get the CPU utilization as above. Please note, the above code is being used heavily by many DBAs, I do not really know who is the author of the above script, however, would like to give full credit to the author who drafted it.

Using perfmon

Performance monitor (perfmon) is a built-in tool in windows server to track the system performance and other data points. We can configure perfmon to run on scheduled manner and collect the information as per the requirement. Most of the production servers will be enabled with perfmon to track the performance and it has no or less impact on the server.

Once the data is collected, you can even look at the data through graphana or kibana which would provide us a good data representation.

Using open source monitoring tools

There are many open source monitoring tool in the market which can be used to get the information from server. Few of the tools are explained in the link https://geekflare.com/best-open-source-monitoring-software/

SCRIPT GALLERY, SQL

ROW_NUMBER() window function in SQL Server
This blog post explains few methods to generate a running number for a SELECT query. Different SQL versions have different ways to achieve this. Let us quickly see ROW_NUMBER() window function with this blog post.

ROW_NUMBER()OVER(PARTITION BY column_list ORDER BY column_list ASC/DESC)

This returns the sequential number for rows. A Quite simple way in SQL Server so far, note that there are different ways we can generate this numbers on group or set depending on sorted manner.Let us see some of those forms in this post. As first step, let us create a SampleData table populated with few records as below. 

Create Table SampleData
(
	CourseId	Int,
	CourseName	Varchar(100),
	Institute	Varchar(100),
	Fees		Int
)

Insert into SampleData
	Values  (1,'SQL Server', 'Aptech', 1000),
			(1,'SQL Server', 'WowTech', 2000),
			(2,'.NET', 'NetTechs', 6000),
			(2,'.NET', 'Aptech', 8000),
			(2,'.NET', 'SimpleLearn', 7500),
			(3,'Python', 'Aptech', 1000),
			(3,'Python', 'SimpleLearn', 1500),
			(3,'Python', 'PyLearn', 1000),
			(3,'Python', 'NetTechs', 1000),
			(3,'Python', 'WowTech', 1000)

Select * from SampleData

--Drop Table SampleData

Simple Form of generating a running number

We can create a running number column with row_number window function as below: 

Select Row_Number()Over(order by (Select NULL) ASC) Rn,* From SampleData
Select Row_Number()Over(order by (Select NULL) DESC) Rn,* From SampleData

In the above two example, we can see both ASC and DESC returns the same set of ordering and running number because the order by always on NULL value which would have no effect for ASC and DESC.

Form of generating a running number based on a set of data

Yes, this is based on set or group data. For an example, if we need to generate a running number for a grouped records and so on, you can introduce the Partition by clause to the above query as below. 

Select Row_Number()Over( partition by CourseId order by (Select NULL) ASC) Rn,* From SampleData

Form of generating a running number based on a set of data with a defined order of data

In the above example, we have seen the number based on data, now, we are going to quickly see the numbering is based on group of data as well as ordered in a defined way. In our example, we wanted to see the data group for CourseID should be sorted by its Fees in ascending order. 

Select Row_Number()Over( partition by CourseId order by Fees ASC) Rn,* From SampleData

Hope this is helpful to understand better of ROW_NUMBER window function in SQL Server.

Hope you enjoyed this post, share your feedback in comment section. Recommending to go through “Microsoft SQL Server – Beginners Guide” series for more information.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SCRIPT GALLERY, SQL

How to find last restart time of SQL Server
Why do we need this information?

This is an important piece of information for a SQL Server engineer. I carefully tend to use the word “engineer” here because this information helps all kinds of people who works with SQL Server. As an Administrator would like to understand when did it happen to understand the underlining issue with any server level issues/fail over details etc. As a performance Engineer, this information helps when the cache gets cleared and for many other reasons. So its very important and useful information as long as you are working in SQL Server.

Now, there are two different ways your SQL Service could get restart – (1) By restarting the SQL Service and (2) By restarting the Windows Server. Let us look at different ways to find this information.

What are the different ways to get last restart of SQL Service?

Method 1: Using sys.dm_os_sys_info Its the most simple, easiest and my favorite way to get this information. 
SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Method 2: Using tempdb database creation date 
SELECT create_date FROM sys.databases WHERE name = 'tempdb'

Method 3: Using sp_readerrorlog 
sp_readerrorlog 0,1,'Copyright (c)'

What are the different ways to get last restart of Windows Server?

Method 1: Using Task Manager

Up time represents how long the server is being up and running since the last restart. So to calculate when did the reboot happened, need to substract the up time with current date and time.

Method 2: Using systeminfo command 
systeminfo | find /i "Boot Time"

Hope this helps and if you have any other ways, please share in the comment section.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SCRIPT GALLERY, SQL

xp_servicecontrol in SQL Server
Problem Statement:

While I was automating a new server preparation step, one of my requirement is to make sure “MSDTC” service is up and running on the server. To enable this check, we can use an extended stored procedure – xp_servicecontrol. There are two parameters to this procedure as below.

It is important to understand that this extended procedure is an undocumented one which means, it cannot be used for a development purpose, this can be anytime removed from the product by Microsoft. However, this can be used as it warranted.

xp_servicecontrol @Action = N’Action’ @ServiceName = N’Service Name’

Action Description
start To start the service if it is not running. If the service is already in running state, an error is raised.
stop To stop the service if it is running. If the service is not running, an error is raised.
pause To pause a running service. An error is raised if the service is not running. Remember not all services can be paused.
continue To continue running a paused service. An error is raised if the serviced is not in paused state.
querystate To return the current state of the service.

Code:

declare @ServicesStatus table
 ( 
 Status varchar(50)
 )
    
 INSERT @ServicesStatus 
 EXEC xp_servicecontrol N'QUERYSTATE',N'msdtc'
    
 Declare @v varchar(max)
 select @v=status from @ServicesStatus
 select @v

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

DataTypes, SCRIPT GALLERY, SQL

ISUNIQUEIDENTIFIER function in SQL Server
Today, I had a requirement to check a value can be converted as uniqueidentifier datatype or not, so quickly come up with the below function. Hope this would help you if you come across similar requirement in future.

Code: 

CREATE FUNCTION DBO.ISUNIQUEIDENTIFIER
(
@InputVal nvarchar(50)
)  
RETURNS SmallInt AS  
BEGIN

RETURN 
	CASE WHEN SERVERPROPERTY('PRODUCTMAJORVERSION') >=11 THEN
		CASE WHEN TRY_CONVERT(UNIQUEIDENTIFIER,@InputVal) IS NOT NULL THEN 1 ELSE 0 END
	ELSE (
		CASE WHEN LEN(@InputVal) = 36 AND @InputVal LIKE
		   '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+  '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+ '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+ '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+ '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+ '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+  '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+  '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]' THEN 1 ELSE 0 END ) 
	END
END

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!