How to identify the long-running queries in SQL Server database

 In the large SQL server database maximum time, we are stuck in the situation when database responding very slow when we check in the server end it shows the utilization of CPU is 95-98%, and when these things happen all processes running in the database starts taking so much time to execute.

To overcome this situation or to identify which query is taking time to execute the below query will help you to get SPID, when batch execution started and which query is taking time etc. so that you can go through that particular query to resolve the issue.

SELECT      r.start_time [Start Time],session_ID [SPID],

            DB_NAME(database_id) [Database],

            SUBSTRING(t.text,(r.statement_start_offset/2)+1,

            CASE WHEN statement_end_offset=-1 OR statement_end_offset=0

            THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1

            ELSE (r.statement_end_offset-r.statement_start_offset)/2+1

            END) [Executing SQL],

            Status,command,wait_type,wait_time,wait_resource,

            last_wait_type

FROM        sys.dm_exec_requests r

OUTER APPLY sys.dm_exec_sql_text(sql_handle) t

WHERE       session_id != @@SPID -- don't show this SPID query

AND         session_id > 50 -- don't show system queries

ORDER BY    r.start_time;

What other ideas can you add to this post that I may have not mentioned?

How to add thousand’s separator in SQL server database for numeric value

Suppose we have a column in a table in which we are storing some numeric value and at the time of showing the data in a report or in any platform that we need to show that numeric value with a thousand's separator.

Query:-

Declare @V_ColumnName int

Set @V_ColumnName=1234567890

Print @V_ColumnName

O/P:- 1234567890

In the above block, we have declared one variable i.e @V_ColumnName and in that, we are storing some dummy value and when we execute this block will get the output without thousand’s separator. To show the value in output with thousand’s separator then

Query:-

Declare @V_ColumnName int

Set @V_ColumnName=1234567890

Select CONVERT(VARCHAR, CONVERT(MONEY, @V_ColumnName), 1) as ThousandsValue

 

O/P:- 1,234,567,890.00

As we are adding the comma in between the value that's why we have converted the value to varchar.

What other ideas can you add to this post that I may have not mentioned?

How to find and dynamically delete the default constraint in MS SQL Server

 When we have a condition to delete the constraint where the name of constraint is generated by the database as default then

declare @schema_name Varchar(100)

declare @table_name Varchar(100)

declare @col_name Varchar(100)

declare @Command  varchar(500)

set @schema_name = 'Scott'

set @table_name = 'Employee'

set @col_name = 'IsAdmin'

 

select @Command = 'ALTER TABLE ' + @schema_name + '.[' + @table_name + '] 

                                 DROP CONSTRAINT ' + d.name

from sys.tables t

join sys.default_constraints d on d.parent_object_id = t.object_id

join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id

where t.name = @table_name

and t.schema_id = schema_id(@schema_name)

and c.name = @col_name

print @Command

execute (@Command)

When we execute this block it will find the constraint details of that column and drop the default constraint.

What other ideas can you add to this post that I may have not mentioned?

How to remove everything after the second decimal(or any special character) and count the occurrence of any character in the string in MS SQL Server DB.

 1.       Remove everything after the second decimal(or any special character)

Suppose we have a string in which we have multiple decimals and we have a requirement to cut the string at the occurrence of the second decimal then.

DECLARE @startParam varchar(50)

Set @startParam ='1111.000.00.00.00'

select left(@startParam, charindex('.', @startParam, charindex('.', @startParam)+1)-1);

O/P:-  1111.000

Here, we have declared one variable as “@StartParam” with the length of 50 characters and passed the value with multiple decimals in the string, so according to the condition, we removed everything after the second decimal in the select statement.

2.       Count the occurrence of any character

When we have same string as given above and as per condition needs to count the decimals(dot) in the string then.

Select (len(@startParam) - len(replace(@startParam,'.','')))

O/P:- 4

What other ideas can you add to this post that I may have not mentioned?