Find currently running SQL queries and long running queries

To find currently running SQL Queries/Processes:

In order to get all the command/queries currently in process, we used to run the following statement.

sp_who2 ‘active’

This will fetch us the list of System processes and User defined processes using which we can “Kill” the process with the SPID.

In the same way, there are situations where we need to know the detail about Query/Stored Proc which is currently running. For that we can make use of the query below,

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

In the same way as we do in sp_who, we can kill the process with Session ID returned from the above.

Finding long running query stats:

To get statistics on the queries which has took more time to provide results/to get complete, we can use the query below. This will be helpful in case we plan to optimize database performance / latency reporting.

SELECT TOP 10
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) AS individual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE
qt.dbid = DB_ID()
ORDER BY
average_seconds DESC;

Enabling SQL trace for Deadlock and simulating deadlock

To Enable deadlock traces in global level in situations where we cannot predict when the deadlock will occur i have used the following methods after googling few sites.

We say “Trace flags” which denotes a logging key for a type of trace logging. Ex. Deadlock traces can be enabled by enabling flags “1204 and 1222”

To check the status of a flag, we use command as below,

GO

DBCC TRACESTATUS (1204);

GO

DBCC TRACESTATUS(1222);
GO

By running this, we will get an output similar to below,

Here, If Global has value “1” it means trace is enabled in global level (Not only for the current session). If Session has value “1” it means trace is enabled for the current session. “0” implies that the trace is not enabled for Session/Global level respectively.

To Turn On Tracing:

For enabling trace at the Session level we can use the following command,

DBCC TRACEON (1204)

GO

DBCC TRACEON (1222)

To enable trace at the Global level which will be traced across sessions we can use the following command,

DBCC TRACEON (1204, -1)

GO

DBCC TRACEON (1222, -1)

Viewing SQL Traces:

Once the log has been enabled in Global/Session level we can view the logs in any of the following methods,

1. Through Management Studio:

In SSMS, Object Explorer -> Management -> Sql Server Logs

2. Through Search Command: 

Execute the following Command in SQL Query window, here “deadlock” is a search text. So that SQL Engine will result Error Log contents which has text “deadlock” in it.

exec xp_readerrorlog 0,1,’deadlock’

 3. Through Log files repository:

To find log files repository OR to view all the logs in SQL window, execute the following command

 sp_readerrorlog

This command will return all the log statements in Query Results window. To get folder path where the logs are written stop the execution when you get around 20 row(s). In the result window we will see a line similar to “Logging SQL Server messages in file ‘E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG’.” This is the folder path on which the logs are being written.

To Turn Off Tracing: 

As how we enabled Trace the same way we should Turn Off tracing. But the difference is, Instead of TRACEON statement we have to use TRACEOFF statement. If we have enabled Session level tracing it should be turned off as below,

DBCC TRACEOFF (1204);
GO
DBCC TRACEOFF(1222);
GO

If we have enabled in Global Level, it should be as follows

DBCC TRACEOFF (1204,-1);
GO
DBCC TRACEOFF(1222,-1);

Simulating a Deadlock to verify Tracing: 

Run the following statement once,

IF DB_ID(‘DeadLockTest’) IS NULL
CREATE Database DeadLockTest
GO

USE DeadLockTest
GO

CREATE TABLE dbo.codl(id int identity(1,1) primary key clustered, col1 char(10) default ‘abc’)

INSERT INTO dbo.codl DEFAULT VALUES

Then, run the following Query statement in two different SQL Management Studio Sessions simultaneously. This will raise a deadlock in any one of the sessions.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM dbo.codl WHERE ID = 1
WAITFOR DELAY ’00:00:05′
UPDATE dbo.codl
SET col1 = ‘xyz’ WHERE id = 1
ROLLBACK TRAN

Now using Trace which we have enabled, we can get details of deadlock occurrences.

Find a column in sql database

If you’re looking to find a specific column in a huge database.

Assume, you need to get the table name which holds a column called “BlogStatus” from the content database of an application.

Found the below query,

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%BlogStatus%’
ORDER BY schema_name, table_name;

SQL Where clause

In Stored Procedures, sometime we need to omit input parameters from Where Clause.

For an instance, If the parameter is null, we should not include the value in Where clause conditional criteria.

In such cases, we can use the query efficiently as below,

 

WHERE

(@StatusCode IS NULL OR [StatusCode] like ‘%’+ @StatusCode +’%’)
AND (@FromDate IS NULL OR (DATEDIFF(DD, @FromDate, [CreatedOn]) >= 0)

AND(@ToDate IS NULL OR DATEDIFF(DD, @ToDate, [CreatedOn]) <= 0))

Finding Leap Year in SQL in a different method

Usually to know whether the given year is Leap/Not a leap we will divide the given year by 4.

If the remainder is 0 its Leap. Otherwise it is not a leap year.  

But this leads to a wrong result in some scenario. Assume that given year is 2300. It can be divided by 4 with 0 as remainder. But 2300 is not a leap year.

So we can use to the different methods to get the desired result. Given one of the method i have used.

DECLARE @YEAR INT

SET @YEAR = 2300

SELECT @YEAR AS‘YEAR’, CASE

WHEN

DATEDIFF(DAY,

’01/FEB/’+CONVERT(VARCHAR,@YEAR),

’01/MAR/’+CONVERT(VARCHAR,@YEAR))

=29

THEN

‘Is a Leap Year’

ELSE

‘Is not a Leap Year’

END

AS RESULT

In the above example, @year acts as the input parameter which get “Year” as input. For the given Year, i was finding the day difference between Feb 01 and March 01. If it is 29, It is a Leap year Otherwise It is not. It will work under all scenario without any issues.

Tree Structure Join in SQL

Assume we are having a table called EmployeeHierarchy as follows,

We need to display an Employee Hierarchy such as,

If we want to know the downliners under Employee “Mark”, we have “Mike” as one trainee under Mark. So the result should display both Mark and Mike.

Assume we need for “John”. We got Roger who has trainee Jeff who holds Mason.  So we need to display all 4 people in the result set.

All the above details should be taken from only one table. So might be complex to implement. I have Used WITH, UNION ALL clauses to acheive this as follows,

DECLARE @INPUTEMPLOYEE  INT

 SET @INPUTEMPLOYEE = 2

 ;WITH LOGICALCOLLECTION(ID,Employee,TraineeID)AS

(

SELECT EmployeeID ,[Employee Name], [TraineeID] FROM EmployeeHierarchy WHERE [TraineeID] IS NOT NULL  AND EmployeeID = @INPUTEMPLOYEE

 UNION ALL

 SELECT E.EmployeeID,E.[Employee Name] ,E.[TraineeID] FROM EmployeeHierarchy AS E JOIN LOGICALCOLLECTION AS M ON E.EmployeeID = M.TraineeID WHERE E.EmployeeID NOT IN

(SELECT EmployeeID FROM EmployeeHierarchy WHERE TraineeID IS NOT NULL AND EmployeeID = @INPUTEMPLOYEE ) )

SELECT * FROM LOGICALCOLLECTION ORDER BY ID

Which will return the result set as follows,

We can also make this more clear when we use SELF JOIN to show Trainee Name instead of the ID.

Hope its clear.

Admin Queries in SQL

Listed below are queries / stored procedure calls that can be used to get information on Sybase objects such as tables, views, indexes, procedures, triggers, schemas, and users.

Tables and Views

To get all tables, views, and system tables, the following Sybase system stored procedure can be executed.

exec sp_tables ‘%’

To filter by database for tables only, for example master:

exec sp_tables ‘%’, ‘%’, ‘master’, “‘TABLE'”

To filter by database and owner / schema for tables only, for example, master and dbo:

exec sp_tables ‘%’, ‘dbo’, ‘master’, “‘TABLE'”

To return only views, replace “‘TABLE'” with “‘VIEW'”. To return only system tables, replace “‘TABLE'” with “‘SYSTEM TABLE'”.

Owners

This is a query to get all Sybase owners.

select name from dbo.sysusers where uid < 16384 order by name

Procedures

This is a query to get all Sybase procedures.

exec sp_stored_procedures ‘%’

The query can be filtered to return procedures for specific owners and databases by appending more information onto the procedure call, such as the following:

exec sp_stored_procedures ‘%’, ‘dbo’, ‘master’

This can be modified as below to know the SP names and its corresponding text

SELECT OBJSYSOBJECT.NAME,OBJSYSCOMMENTS.[TEXT] FROM SYS.OBJECTS OBJSYSOBJECT

LEFT JOIN SYSCOMMENTS OBJSYSCOMMENTS ON

OBJSYSOBJECT.[OBJECT_ID] = OBJSYSCOMMENTS.[ID]

WHERE OBJSYSOBJECT.[TYPE_DESC] =‘SQL_STORED_PROCEDURE’

Procedure Columns

This is a system stored procedure call to get the columns in a Sybase procedure.

exec sp_sproc_columns ‘get_employee_names’, ‘dbo’, ‘sample’

Functions

Given the custom query to get list of functions used in the respective database along with its script.

SELECT OBJSYSOBJECT.NAME,OBJSYSCOMMENTS.[TEXT] FROM SYS.OBJECTS OBJSYSOBJECT  LEFT JOIN

SYSCOMMENTS OBJSYSCOMMENTS ON

 OBJSYSOBJECT.[OBJECT_ID] = OBJSYSCOMMENTS.[ID]

WHERE OBJSYSOBJECT.[TYPE_DESC] like ‘%function%’

Triggers

This is a query to get all Sybase triggers.

select * from sysobjects where type = ‘TR’

The query can be filtered to return triggers for a specific owner by appending a user_name call onto the where clause to the query.

select * from sysobjects where type = ‘TR’ and user_name(sysobjects.uid) = ‘dbo’

Indexes

This is a query to get Sybase indexes for a particular table. In this example, the table used is employee.

exec sp_helpindex ’employee’

Cursor In SQL

I have not used CURSOR in my projects till date. So am not aware of the exact usage in which we can use Cursor. Recently I got time to learn CURSOR.

From my understanding, A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.

Will update the exact usage when i get a chance to work on cursor in future. In the meantime, i want to share the example from which i learnt CURSOR. as follows,

DECLARE @OBJCURSOR CURSOR

DECLARE @INTCURSOR INT

SET @OBJCURSOR =CURSOR

FOR

SELECT DISTINCT(EMPLOYEEID)FROM EMPLOYEEPAYHISTORY

OPEN @OBJCURSOR

FETCH FROM @OBJCURSOR INTO @INTCURSOR

 

WHILE (@@FETCH_STATUS=0)

BEGIN

 

PRINT CONVERT(VARCHAR,@INTCURSOR)

 

FETCH FROM @OBJCURSOR INTO @INTCURSOR

 

END

 

CLOSE @OBJCURSOR

DEALLOCATE @OBJCURSOR

 Here i was getting Employee ID into Cursor and looping through each row in the cursor to print the employee ID.

To work with cursors you must use the following SQL statements:

• DECLARE CURSOR
• OPEN
• FETCH
• CLOSE

 

 

SQL Functions

Got bored of thinking much about Joins and Group By in SQL.

We can use SQL functions to easily achieve some functionality in which we may get confused while using the above.  Hope most of the SQL newbies not aware of that.

Herewith giving a simple example how we can implement the functions in SQL.

Assume a table Called ProductMaster

PRODUCTID   |  PRODUCTNAME

——————————————–

1                             Colgate

2                             Pepsodent

3                             Close UP

Assume another table called PURCHASEMASTER

PRODUCTID   |  PURCHASEVALUE

———————————————

1                                              60

2                                              50

1                                              150

1                                              100

2                                              50

3                                              40

1                                              80

Now Consider that we need to Sum the Product Value of Each Product and the Output Should be like, ProductID, ProductName, Total Value.

We can implement the above requirement easily using SQL function as follows..

CREATE FUNCTION SUMPRODUCT
(
@PRODUCTID INT
)
RETURNS INT
AS
BEGIN
DECLARE @SUM FLOAT

SET @SUM=(SELECT SUM(PURCHASEVALUE) FROM PURCHASEMASTER WHERE PRODUCTID= @PRODUCTID)

RETURN @SUM
END

And the query will be like the following..

SELECT PRODUCTID,PRODUCTNAME,dbo.SUMPRODUCT(PRODUCTID) AS TOTALVALUE FROM PRODUCTMASTER

So you’ll get the output as

PRODUCTID        PRODUCTNAME               TOTALVALUE

————————————————————————————–

1                                              Colgate                                 390

2                                              Pepsodent                          100

3                                              Close UP                              40

Tooooo Simple to watch I hope !!!.. Try it. Easy to implement too..

SQL Joins

Hi All.. 

While googling tech things around, I saw an wonderful article explaining about JOINS in SQL. Its very simple and easy to understand..

Now am sharing that content with you all.

 

REF: http://stackoverflow.com/questions/38549/sql-difference-between-inner-and-outer-join

Assuming you’re joining on columns with no duplicates, which is by far the most common case:

• An inner join of A and B gives the result of A intersect B, i.e. the inner part of a venn diagram intersection.

• An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union.

Examples

Suppose you have two Tables, with a single column each, and data as follows:

A    B
-    -
1    3
2    4
3    5
4    6

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b(+);

a |  b  
--+-----
1 | null
2 | null
3 |    3
4 |    4

Full outer join

A full outer join will give you the union of A and B, i.e. All the rows in A and all the rows in B. If something in A doesn’t have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b  
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5