Tag Archives: CROSS APPLY

List of T-SQL Running at the moment with Sys.dm_exec_requests and sys.dm_exec_sql_text in SQL Server 2008

This is something we may need many times as a administrator. Sometime when we don’t have profiler running and don’t want to go for any other route to troubleshoot  server performance, I would execute on simple T-SQL statement with the help of DMV (Sys.Exec_Requests) and DMF (Sys.dm_exec_sql_text) which can list out all the T-SQL Statement running at the moment in our database. There are many different ways to go for but this is something very quick and efficient so I keep this simple query handy all time.

SELECT
req.session_id,
req.command,
txt.text,
req.start_time,
req.status,
req.command
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) txt;

Sometime, when you suddenly started facing low performance on the server, you can run above query as a quick glance and look at the insight of SQL Server whether any heavy query is going on right now or not. This is not the only solution to performance issue, there are lot more tools and way but this could be first and quick step.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Cross Apply in inline table valued function in SQL Server 2005

CROSS APPLY and OUTER APPLY is new in Microsoft SQL Server 2005. CROSS APPLY works like JOIN and a.k.a. INNER APPLY. You can use it like co-related query.

–create one table for demo

use adventureworks

–department table

CREaTE TABLE deptInfo

(

DeptName VARCHAR(10),

Description VARCHAR(20)

)

–INSERT records

INSERT INTO DeptInfo

SELECT ‘MIS’,‘IT DEPT’ UNION ALL

SELECT ‘account’,‘finance department’ UNION ALL

SELECT ‘chemical’,‘chemical department’

GO

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

GO

–creating inline table valued function

CREATE FUNCTION dbo.GetEmployeeData(@dept VARCHAR(10))

RETURNS TABLE

AS

RETURN

(

SELECT * FROM emps WHERE dept=@dept

)

GO

–use cross apply in above function

SELECT d.Deptname,d.description,e.name,e.company FROM DeptInfo d

CROSS APPLY

dbo.getemployeedata(d.deptname) as e

WHERE d.deptname=‘MIS’

Note: You can refer another article on Cross Apply and Outer apply at:

http://www.sqlhub.com/2009/03/cross-apply-and-outer-apply-clause-in.html

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

CROSS APPLY and OUTER APPLY clause in SQL-SERVER 2005

CROSS APPLY and OUTER APPLY clause in SQL-SERVER 2005
I will be introducing you with magical feature of Microsoft SQL-Server 2005, called CROSS APPLY and OUTER APPLY. If you want to get top 2 or 3 maximum inventory quantity of each product or all product of AdventureWorks database then it is bit difficult in SQL-Server 2000 as it doesn’t support CROSS APPLY or OUTER APPLY. You can use UDF or sub query for CROSS APPY or OUTER APPLY.
BTW, you can consider CROSS APPLY clause as INNER APPLY also as it will use outer (main) query as an input of subquery or function and will return the result set. In CROSS APPLY we will be getting full set of left side query (main query) and its corresponding value from right side query or function, if it is not available in right side query or function, it will return NULL.
First let us see use of CROSS APPLY:
We are going to create one function which will return top row from production.productinventory table of AdventureWorks database based on supplied productID and row number.
CREATE FUNCTION dbo.fn_GetMax_ProductItem(@Productid AS int, @rownum AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@rownum) *
FROM Production.ProductInventory
WHERE ProductID = @Productid
ORDER BY Quantity DESC
GO
You can run above UDF and test it with following query.
select * from dbo.fn_GetMax_ProductItem(1,2)
It will return top two row of productID 1.
Note: I strongly recommend not using “*” in query in live production environment due to performance issue, this is ok in this example.
As function is ready, we will start using that function in our CROSS APPLY example.
SELECT
p.ProductID,
p.Name,
p.ProductNumber,
pmi.Quantity,
pmi.locationID
FROM
Production.Product AS p
CROSS APPLY
dbo.fn_GetMax_ProductItem(p.productid, 2) AS pmi
WHERE
p.ProductID in (1,2,3,531,706)
ORDER BY
p.productid ASC
As soon as you run above query, you will get 8 rows. Two rows for each productID (1,2,3,531). You will not get any row for ProductID 706 as it is not available in Production.Product table. This proves that CROSS APPLY clause works like INNER APPLY.
Now let us tweak above query a bit with OUTER APPLY instead of CROSS APPLY.
SELECT
p.ProductID,
p.Name,
p.ProductNumber,
pmi.Quantity,
pmi.locationID
FROM
Production.Product AS p
OUTER APPLY
dbo.fn_GetMax_ProductItem(p.productid, 2) AS pmi
WHERE
p.ProductID in (1,2,3,531,706)
ORDER BY
p.productid ASC
This time you will get records for 706 productID as well but it will come with NULL in Quantity and LocationID.
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah