TOP 5 costly Stored Procedures in a SQL Server Database

TOP 5 costly Stored Procedures in a SQL Server Database: "

I was recently doing a query to find out the Stored Procedures which took maximum time to execute. Here’s the query (thanks to gbn) I executed on a sample database (AdventureWorks) using SQL Server 2005/2008:

SELECT TOP 5 obj.name, max_logical_reads, max_elapsed_time
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(sql_handle) hnd
INNER JOIN sys.sysobjects obj on hnd.objectid = obj.id
ORDER BY  max_logical_reads  DESC

OUTPUT

If you know of a better way, please share it in the comments section.

"

GROUPING SETS in SQL Server 2008

GROUPING SETS in SQL Server 2008: "

With the new GROUPING SETS operator included in SQL Server 2008, you have more control over what is aggregated. Here’s a practical example of using GROUPING SETS in SQL Server 2008

SAMPLE DATA

--DROP TABLE #Student

CREATE TABLE #Student
(
StudentID int ,
CourseYear smallint,
Semester smallint,
Marks float
)

INSERT INTO #Student VALUES (1, 2008, 1, 5.6)
INSERT INTO #Student VALUES (1, 2008, 2, 6.5)
INSERT INTO #Student VALUES (1, 2008, 3, 8.9)
INSERT INTO #Student VALUES (1, 2008, 4, 9.1)
INSERT INTO #Student VALUES (1, 2009, 1, 4.4)
INSERT INTO #Student VALUES (1, 2009, 2, 7.9)
INSERT INTO #Student VALUES (1, 2009, 3, 8.5)
INSERT INTO #Student VALUES (1, 2009, 4, 8.7)
INSERT INTO #Student VALUES (2, 2008, 1, 5.4)
INSERT INTO #Student VALUES (2, 2008, 2, 9.9)
INSERT INTO #Student VALUES (2, 2008, 3, 8.5)
INSERT INTO #Student VALUES (2, 2008, 4, 4.7)
INSERT INTO #Student VALUES (2, 2009, 1, 6.4)
INSERT INTO #Student VALUES (2, 2009, 2, 7.9)
INSERT INTO #Student VALUES (2, 2009, 3, 7.4)
INSERT INTO #Student VALUES (2, 2009, 4, 9.7)

Let’s do some common GROUPBY operations

-- Calculate Total Marks obtained by student each year
SELECT StudentID, CourseYear, SUM(Marks) AS TotalMarks
FROM #Student
GROUP BY StudentID, CourseYear
ORDER BY StudentID

-- Calculate Total Marks obtained by student
-- grouped by Semester for both years
SELECT StudentID, Semester, SUM(Marks) AS TotalMarks
FROM #Student
GROUP BY StudentID, Semester
ORDER BY StudentID

However with the GROUPING SETS operator, you can define different aggregate groups in a single query as shown below

SELECT StudentID, CourseYear, Semester, SUM(Marks) AS TotMarks
FROM #Student
GROUP BY GROUPING SETS((StudentID, CourseYear), (StudentID, Semester))
ORDER BY StudentID, Semester

Here’s a very nice article by Craig on GROUPING SETS in SQL Server 2008

"

Find Primary Key of a SQL Server Table

Find Primary Key of a SQL Server Table: "

Here’s a quick way to find the primary key of a SQL Server 2005/2008 Table using TABLE_CONSTRAINTS

USE Northwind
go
SELECT ISKC.TABLE_SCHEMA, ISKC.TABLE_NAME, ISKC.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS ISTC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ISKC
ON ISTC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
ISTC.CONSTRAINT_NAME = ISKC.CONSTRAINT_NAME
WHERE ISKC.TABLE_NAME = 'Employees'
ORDER BY ISKC.TABLE_NAME, ISKC.ORDINAL_POSITION

Another way that I know of is to make a join between sys.indexes, sys.index_columns and sys.columns and get the desired results.

"