Kill Process

Many a times, we come across the situations like immediate refresh of Dev or QA or Unit database with that of Production backup.

In that situation, despite many warnings, some connections always exist. Manually killing each and every process connecting to the database is not a reliable solution. Its tiresome,time-taking and as soon as we finish killing all the process we find new processes.

In that case, the following procedure can come to the rescue of a DBA.

USE MASTER
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'KillProcess' AND ROUTINE_TYPE = 'PROCEDURE')
DROP PROCEDURE dbo.KillProcess
GO
CREATE PROCEDURE dbo.KillProcess(@dbname VARCHAR(100))
AS
BEGIN
SET NOCOUNT ON
DECLARE @process TABLE (PId INT)
DECLARE @pid INT, @qry NVARCHAR(15)

INSERT @process
SELECT spid FROM master.dbo.sysprocesses WHERE dbid = DB_ID(@dbname)

SET @pid = (SELECT TOP 1 PId FROM @process )
WHILE @pid IS NOT NULL
BEGIN
SET @qry = 'KILL '+CAST(@pid AS VARCHAR)
--PRINT @qry
EXEC SP_EXECUTESQL @qry
DELETE FROM @process WHERE PId = @pid
SET @pid = (SELECT TOP 1 PId FROM @process )
END
END
-- EXEC KillProcess 'TestDev'
GO

This procedure kills all the process that exist and make the database ready to get refreshed for a moment.

ISNULL() , IS NOT NULL --- Performance rescuers

Hi Everyone,

Good Day!!!

Sometimes, we cannot know the importance of some simple things until we experience them. Whether it may be in life or may it be in SQL Server. In SQL Server, I realized this when I faced a severe performance problem in one of the functions I was given to enhance. I was new to the database.

I have a table valued function with the underlying query as:


SELECT T1.Col1 BaseCol

, SUM(T1.Amount1 + T3.Amount3) A1

, SUM(T2.Amount2) A2

FROM Table1 T1

INNER JOIN Table2 T2 ON T1.Col1 = T2.Col2

LEFT JOIN
(
SELECT Col3, SUM(Amount3) Amount3
FROM Table3
GROUP BY Col3
) T3 ON T2.Col2 = T3.Col3

GROUP BY T1.Col1


This simple query is giving wrong results and taking minutes of time to get executed for less than a million records.

I have tried it in several other ways but the result was same -- wrong one. Finally, I noticed that some of the Amount columns in the SELECT list were null and used ISNULL() function.

Thank God. Now I got correct results. It means when either T1.Amount or T3.Amount was NULL, the entire value has become NULL and never contributed during the summation. When ISNULLL() function is used, the NULL is converted into 0 and as a result atleast the other amount is contributed to the summation and resulted in correct amounts finally.

Now, I was getting the results correct but what about the performance.

I had to complete that quick and the timeline was small. I was annoyed and I hastily checked the execution plan. I found nested loop joins but noted some look-ups and created indexes on that particular tables. But there was no improvement in performance.

I have checked the execution plan and found that the newly created index was being used but there was no change in the performance. Having learnt a lesson from the above ISNULL() scenario. I have executed the following query

SELECT Col3, SUM(Amount3) Amount3 FROM Table3 GROUP BY Col3

To my astonishment, I have found many Amount values were NULL.
So, I included a WHERE clause with "IS NOT NULL".

It worked, it means we are bringing unnecessary data from Table3 and since its volume is huge, it created an issue which is filtered off using "IS NOT NULL".

So, my final query looked like:


SELECT T1.Col1 BaseCol

, SUM(ISNULL(T1.Amount1,0) + ISNULL(T3.Amount3,0)) A1

, SUM(T2.Amount2) A2

FROM Table1 T1

INNER JOIN Table2 T2 ON T1.Col1 = T2.Col2

LEFT JOIN
(
SELECT Col3, SUM(Amount3) Amount3
FROM Table3
WHERE Amount3 IS NOT NULL
GROUP BY Col3
) T3 ON T2.Col2 = T3.Col3

GROUP BY T1.Col1


Finally, I got the desired output in desired time.
Cool.....

Thanks,
Sunil.