Syringe.Net.Nz
Irregular Injection of Opinion
RSS 2.0|Atom 1.0|CDF

 Monday, September 17, 2012
SQL Azure Federations Deep Dive–TechEd ANZ

So I promised that I’d post all the bits from my TechEd Australia and NZ session on SQL Azure Federations. I have been tardy in delivering on this goal.

If you missed the session they recorded it for me in Auckland so you can take a look at the recording on channel 9: http://channel9.msdn.com/Events/TechEd/NewZealand/TechEd-New-Zealand-2012/AZR304

So here goes.

The first item is the SQL Script that I walked through. This will give you a high level idea of the basics of Federations using a simple eCommerce type scenario (partial AdventureWorks)

--<<<<<<<<<<<<<<<<<<<<< Task 1 – Create Federations Root Database >>>>>>>>>>>>>>>>>>>>>
CREATE DATABASE [TechEd2012]
GO

--<<<<<<<<<<<<<<<<<<<<< Task 2 – Connect directly to Root Database >>>>>>>>>>>>>>>>>>>>>
/*
    Using tooling support in latest release of SQML Management studio    
*/

--<<<<<<<<<<<<<<<<<<<<< Task 3 – Create Federation Object >>>>>>>>>>>>>>>>>>>>>
CREATE FEDERATION CustomerFederation(cid BIGINT RANGE)
GO

--<<<<<<<<<<<<<<<<<<<<< Task 4 – View Federations Metadata >>>>>>>>>>>>>>>>>>>>>
-- Route connection to the Federation Root
USE FEDERATION ROOT WITH RESET
GO
SELECT db_name() [db_name], db_id() [db_id]
SELECT * FROM sys.federations
SELECT * FROM sys.federation_distributions
SELECT * FROM sys.federation_member_distributions ORDER BY federation_id, range_low;
SELECT * FROM sys.databases;
GO
-- Route connection to the 1 Federation Member (aka shard)
USE FEDERATION CustomerFederation(cid=100) WITH RESET, FILTERING=OFF
GO
SELECT db_name() [db_name], db_id() [db_id]
SELECT * FROM sys.federations
SELECT * FROM sys.federation_distributions
SELECT * FROM sys.federation_member_distributions
GO
SELECT f.name, fmc.federation_id, fmc.member_id, fmc.range_low, fmc.range_high 
FROM sys.federations f
JOIN sys.federation_member_distributions fmc
ON f.federation_id=fmc.federation_id
ORDER BY fmc.federation_id, fmc.range_low;
GO

--<<<<<<<<<<<<<<<<<<<<< Task 5 – Create Federated Tables >>>>>>>>>>>>>>>>>>>>>
-- Route connection to the 1 Federation Member (aka shard). 
-- Filtering OFF so we can make DDL operations
USE FEDERATION CustomerFederation(cid=10) WITH RESET, FILTERING=OFF
GO
-- Table [dbo].[Customer]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
    [CustomerID] [bigint] NOT NULL,
    [Title] [nvarchar](8) NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [MiddleName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [Suffix] [nvarchar](10) NULL,
    [CompanyName] [nvarchar](128) NULL,
    [SalesPerson] [nvarchar](256) NULL,
    [EmailAddress] [nvarchar](50) NULL,
    [Phone] [nvarchar](25) NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
    [CustomerID] ASC
) 
)FEDERATED ON (cid=CustomerID) --Note the use of the FEDERATED ON statement
GO

CREATE TABLE [dbo].[Order](
    [OrderID] [uniqueidentifier] NOT NULL DEFAULT newid(),
    [CustomerID] [bigint] NOT NULL,
    [OrderTotal] [money] NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
(
    [OrderID],[CustomerID] ASC
) 
)FEDERATED ON (cid=CustomerID) --Note the use of the FEDERATED ON statement
GO

--<<<<<<<<<<<<<<<<<<<<< Task 6 – Insert Dummy Data >>>>>>>>>>>>>>>>>>>>>
-- Route connection to the 1 Federation Member (aka shard). 
-- Filtering OFF so we can insert multiple Atomic Units
USE FEDERATION CustomerFederation(cid=10) WITH RESET, FILTERING=OFF
GO
INSERT INTO [dbo].[Customer]
           ([CustomerID]
           ,[Title]
           ,[FirstName]
           ,[MiddleName]
           ,[LastName]
           ,[Suffix]
           ,[CompanyName]
           ,[SalesPerson]
           ,[EmailAddress]
           ,[Phone])
     VALUES 
(55, 'Mr.', 'Frank', '', 'Campbell', '', 'Rally Master Company Inc', 'adventure-works\shu0', 'frank4@adventure-works.com', '491-555-0132'),
(56, 'Mr.', 'Brian', '', 'Groth', '', 'Latest Accessories Sales', 'adventure-works\david8', 'brian5@adventure-works.com', '461-555-0118'),
(57, 'Ms.', 'Judy', 'R.', 'Lundahl', '', 'Leading Sales & Repair', 'adventure-works\jillian0', 'judy1@adventure-works.com', '260-555-0130'),
(58, 'Mr.', 'Peter', '', 'Kurniawan', '', 'Largest Bike Store', 'adventure-works\jillian0', 'peter4@adventure-works.com', '436-555-0160'),
(59, 'Mr.', 'Douglas', '', 'Groncki', '', 'Locks Company', 'adventure-works\shu0', 'douglas2@adventure-works.com', '385-555-0140'),
(60, 'Mr.', 'Sean', 'J.', 'Lunt', '', 'Main Bicycle Services', 'adventure-works\linda3', 'sean4@adventure-works.com', '183-555-0111'),
(61, 'Mr.', 'Jeffrey', '', 'Kurtz', '', 'Many Bikes Store', 'adventure-works\shu0', 'jeffrey3@adventure-works.com', '452-555-0179'),
(64, 'Mr.', 'Vamsi', '', 'Kuppa', '', 'Metal Clearing House', 'adventure-works\jos‚1', 'vamsi1@adventure-works.com', '290-555-0196'),
(65, 'Ms.', 'Jane', 'P.', 'Greer', '', 'Metro Manufacturing', 'adventure-works\jos‚1', 'jane2@adventure-works.com', '737-555-0163'),
(66, 'Mr.', 'Alexander', 'J.', 'Deborde', '', 'Neighborhood Store', 'adventure-works\garrett1', 'alexander1@adventure-works.com', '394-555-0176'),
(70, 'Mr.', 'Deepak', '', 'Kumar', '', 'Outdoor Aerobic Systems Company', 'adventure-works\jae0', 'deepak0@adventure-works.com', '1 (11) 500 555-0122'),
(73, 'Ms.', 'Margaret', 'T.', 'Krupka', '', 'Outdoor Sports Supply', 'adventure-works\pamela0', 'margaret1@adventure-works.com', '107-555-0132'),
(74, 'Mr.', 'Christopher', 'M.', 'Bright', '', 'Parcel Express Delivery Service', 'adventure-works\david8', 'christopher2@adventure-works.com', '162-555-0166'),
(75, 'Ms.', 'Aidan', '', 'Delaney', '', 'Paint Supply', 'adventure-works\jillian0', 'aidan0@adventure-works.com', '358-555-0188'),
(76, 'Mr.', 'James', 'J.', 'Krow', '', 'Out-of-the-Way Hotels', 'adventure-works\jillian0', 'james11@adventure-works.com', '265-555-0190'),
(77, 'Mr.', 'Michael', '', 'Brundage', '', 'Mechanical Products Ltd.', 'adventure-works\shu0', 'michael13@adventure-works.com', '128-555-0148'),
(78, 'Mr.', 'Stefan', '', 'Delmarco', '', 'Preferred Bikes', 'adventure-works\linda3', 'stefan0@adventure-works.com', '819-555-0186'),
(79, 'Mr.', 'Mitch', '', 'Kennedy', '', 'Reasonable Bicycle Sales', 'adventure-works\shu0', 'mitch0@adventure-works.com', '996-555-0192'),
(82, 'Mr.', 'James', 'D.', 'Kramer', '', 'Refined Department Stores', 'adventure-works\jos‚1', 'james10@adventure-works.com', '814-555-0130'),
(83, 'Mr.', 'Eric', 'J.', 'Brumfield', '', 'Requisite Part Supply', 'adventure-works\jos‚1', 'eric3@adventure-works.com', '644-555-0114'),
(84, 'Ms.', 'Della', 'F.', 'Demott Jr', '', 'Rewarding Activities Company', 'adventure-works\garrett1', 'della0@adventure-works.com', '752-555-0185'),
(88, 'Ms.', 'Pamala', 'M.', 'Kotc', '', 'Closest Bicycle Store', 'adventure-works\jae0', 'pamala0@adventure-works.com', '1 (11) 500 555-0173'),
(91, 'Ms.', 'Joy', 'R.', 'Koski', '', 'Scooters and Bikes Store', 'adventure-works\pamela0', 'joy0@adventure-works.com', '810-555-0198'),
(92, 'Ms.', 'Jovita', 'A.', 'Carmody', '', 'Sports Commodities', 'adventure-works\david8', 'jovita0@adventure-works.com', '646-555-0137'),
(93, 'Mr.', 'Prashanth', '', 'Desai', '', 'Stationary Bikes and Stands', 'adventure-works\jillian0', 'prashanth0@adventure-works.com', '138-555-0156'),
(94, 'Mr.', 'Scott', '', 'Konersmann', '', 'Specialty Sports Store', 'adventure-works\jillian0', 'scott6@adventure-works.com', '556-555-0192'),
(96, 'Ms.', 'Jane', 'N.', 'Carmichael', '', 'Rural Department Store', 'adventure-works\shu0', 'jane0@adventure-works.com', '716-555-0167'),
(97, 'Ms.', 'Bonnie', 'B.', 'Lepro', '', 'More Bikes!', 'adventure-works\linda3', 'bonnie2@adventure-works.com', '354-555-0130'),
(99, 'Mr.', 'Eugene', '', 'Kogan', '', 'Mountain Bike Center', 'adventure-works\shu0', 'eugene2@adventure-works.com', '136-555-0134'),
(100, 'Mr.', 'Kirk', 'T', 'King', '', 'Up-To-Date Sports', 'adventure-works\jos‚1', 'kirk2@adventure-works.com', '979-555-0163'),
(101, 'Mr.', 'William', 'J.', 'Conner', '', 'Urban Sports Emporium', 'adventure-works\jos‚1', 'william1@adventure-works.com', '383-555-0111'),
(102, 'Ms.', 'Linda', '', 'Leste', '', 'National Manufacturing', 'adventure-works\garrett1', 'linda7@adventure-works.com', '493-555-0134'),
(106, 'Ms.', 'Andrea', 'A.', 'Thomsen', '', 'West Side Mart', 'adventure-works\jae0', 'andrea1@adventure-works.com', '1 (11) 500 555-0120'),
(109, 'Mr.', 'Daniel', 'P.', 'Thompson', '', 'Travel Sports', 'adventure-works\pamela0', 'daniel2@adventure-works.com', '247-555-0197'),
(110, 'Ms.', 'Kendra', 'N.', 'Thompson', '', 'Vintage Sport Boutique', 'adventure-works\david8', 'kendra0@adventure-works.com', '464-555-0188'),
(111, 'Mr.', 'Scott', 'A.', 'Colvin', '', 'Westside Cycle Store', 'adventure-works\jillian0', 'scott1@adventure-works.com', '119-555-0144'),
(168, 'Sr.', 'Luis', '', 'Bonifaz', '', 'Economy Bikes Company', 'adventure-works\linda3', 'luis0@adventure-works.com', '688-555-0113'),
(169, 'Ms.', 'Brenda', '', 'Diaz', '', 'Downtown Hotel', 'adventure-works\shu0', 'brenda2@adventure-works.com', '147-555-0192'),
(172, 'Ms.', 'Gabriele', '', 'Dickmann', '', 'Certified Sports Supply', 'adventure-works\jos‚1', 'gabriele0@adventure-works.com', '835-555-0116'),
(173, 'Mr.', 'Rudolph', 'J.', 'Dillon', 'Sr.', 'Sundry Sporting Goods', 'adventure-works\jos‚1', 'rudolph0@adventure-works.com', '722-555-0169'),
(174, 'Mr.', 'Michael', 'L.', 'Bohling', '', 'Toy Manufacturing Inc', 'adventure-works\garrett1', 'michael12@adventure-works.com', '838-555-0147'),
(178, 'Mr.', 'Dick', '', 'Dievendorff', '', 'Rampart Amusement Company', 'adventure-works\jae0', 'dick1@adventure-works.com', '1 (11) 500 555-0193'),
(180, 'Ms.', 'Nicky', 'E.', 'Chesnut', '', 'Full-Service Bike Store', 'adventure-works\michael9', 'nicky0@adventure-works.com', '264-555-0164'),
(181, 'Mr.', 'Michael', 'J.', 'Lee', '', 'Family Entertainment Center', 'adventure-works\pamela0', 'michael18@adventure-works.com', '396-555-0139'),
(182, 'Mr.', 'Stanley', 'A.', 'Alan', 'Jr.', 'Another Bicycle Company', 'adventure-works\david8', 'stanley0@adventure-works.com', '156-555-0126'),
(183, 'Mr.', 'Yao-Qiang', '', 'Cheng', '', 'This Area Sporting Goods', 'adventure-works\jillian0', 'yao-qiang0@adventure-works.com', '344-555-0181'),
(184, 'Ms.', 'Marjorie', 'M.', 'Lee', '', 'Go-cart and Bike Specialists', 'adventure-works\jillian0', 'marjorie0@adventure-works.com', '306-555-0166'),
(185, 'Mr.', 'Sandeep', '', 'Kaliyath', '', 'Weekend Bike Tours', 'adventure-works\shu0', 'sandeep1@adventure-works.com', '495-555-0113'),
(186, 'Mr.', 'Pei', '', 'Chow', '', 'Thrilling Bike Tours', 'adventure-works\linda3', 'pei0@adventure-works.com', '789-555-0184'),
(190, 'Mr.', 'Mark', '', 'Lee', '', 'Racing Partners', 'adventure-works\jos‚1', 'mark5@adventure-works.com', '371-555-0112'),
(191, 'Ms.', 'Sandra', 'T.', 'Kitt', '', 'Non-Slip Pedal Company', 'adventure-works\jos‚1', 'sandra2@adventure-works.com', '303-555-0117'),
(192, 'Mr.', 'Hao', '', 'Chen', '', 'Nuts and Bolts Mfg.', 'adventure-works\garrett1', 'hao1@adventure-works.com', '117-555-0173'),
(196, 'Ms.', 'Jolie', '', 'Lenehan', '', 'Exhibition Showroom', 'adventure-works\jae0', 'jolie0@adventure-works.com', '1 (11) 500 555-0126'),
(197, 'Ms.', 'Diane', 'F.', 'Krane', '', 'Fabrikam Inc., West', 'adventure-works\michael9', 'diane4@adventure-works.com', '224-555-0126'),
(198, 'Mr.', 'Payton', 'P.', 'Benson', '', 'Field Trip Inc', 'adventure-works\michael9', 'payton0@adventure-works.com', '528-555-0183'),
(199, 'Mr.', 'Roger', '', 'Lengel', '', 'Authorized Bike Sales and Rental', 'adventure-works\pamela0', 'roger1@adventure-works.com', '947-555-0143'),
(200, 'Ms.', 'Peggy', 'J.', 'Justice', '', 'Basic Bike Company', 'adventure-works\david8', 'peggy0@adventure-works.com', '170-555-0189'),
(201, 'Ms.', 'Edna', 'J.', 'Benson', '', 'Unsurpassed Bikes', 'adventure-works\jillian0', 'edna0@adventure-works.com', '789-555-0189'),
(202, '', 'A.', 'Francesca', 'Leonetti', '', 'Two-Seater Bikes', 'adventure-works\jillian0', 'a0@adventure-works.com', '645-555-0193'),
(203, 'Ms.', 'Jean', '', 'Jordan', '', 'Separate Parts Corporation', 'adventure-works\shu0', 'jean3@adventure-works.com', '207-555-0129'),
(204, 'Mr.', 'Ido', '', 'Ben-Sachar', '', 'Traction Tire Company', 'adventure-works\linda3', 'ido0@adventure-works.com', '973-555-0112'),
(205, 'Mr.', 'Frank', '', 'Mart­nez', '', 'Rally Master Company Inc', 'adventure-works\shu0', 'frank5@adventure-works.com', '171-555-0147'),
(208, 'Mr.', 'Steve', '', 'Masters', '', 'Professional Cyclists', 'adventure-works\jos‚1', 'steve2@adventure-works.com', '154-555-0115'),
(209, 'Mr.', 'Robert', 'E.', 'Jones', '', 'Purple Bicycle Company', 'adventure-works\jos‚1', 'robert6@adventure-works.com', '595-555-0131'),
(210, 'Mr.', 'Josh', '', 'Barnhill', '', 'Gasless Cycle Shop', 'adventure-works\garrett1', 'josh0@adventure-works.com', '584-555-0192');
GO

INSERT INTO [dbo].[Order](
    [CustomerID],
    [OrderTotal])
VALUES
(55,213.56),
(55,31.56),
(55,3412.56),
(78,312.56),
(78,255.78),
(79,112.12),
(79,555.00),
(79,765.12),
(79,967.88),
(101,903.04),
(101,512.43),
(110,1250.00),
(110,325.68),
(204,112.99),
(205,107.43),
(205,895.21)
GO

--<<<<<<<<<<<<<<<<<<<<< Task 7 – Query Federation Data with Filtering off >>>>>>>>>>>>>>>>>>>>>
-- Route connection to the 1 Federation Member (aka shard)
USE FEDERATION CustomerFederation(cid=100) WITH RESET, FILTERING=OFF
GO
SELECT db_name() [db_name], db_id() [db_id]
SELECT * FROM sys.federation_member_distributions
-- Federatation ranges
SELECT f.name, fmc.federation_id, fmc.member_id, fmc.range_low, fmc.range_high 
FROM sys.federations f
JOIN sys.federation_member_distributions fmc
ON f.federation_id=fmc.federation_id
ORDER BY fmc.federation_id, fmc.range_low;
-- User tables count (federated & reference)
SELECT fmc.member_id,fmc.range_low,fmc.range_high,'[' + s.name + '].[' + t.name + ']' [name],
p.row_count FROM sys.tables t 
JOIN sys.schemas s ON t.schema_id=s.schema_id 
JOIN sys.dm_db_partition_stats p ON t.object_id=p.object_id  
JOIN sys.federation_member_distributions fmc ON 1=1 
WHERE p.index_id=1 ORDER BY s.name, t.name
GO
-- Query customer table for high/low Federated Keys
SELECT MIN(CustomerID) [CustomerID Low], MAX(CustomerID) [CustomerID High] FROM Customer
GO



--<<<<<<<<<<<<<<<<<<<<< Task 8 – Perform Federations Split Operation >>>>>>>>>>>>>>>>>>>>>
USE FEDERATION ROOT WITH RESET
GO
ALTER FEDERATION CustomerFederation SPLIT AT (cid=100)
GO

--<<<<<<<<<<<<<<<<<<<<< Task 9 – Wait for Split to complete >>>>>>>>>>>>>>>>>>>>>
--View the background Federation operations table (Rinse and Repeat Until Complete)
SELECT percent_complete, * FROM sys.dm_federation_operations
GO


--<<<<<<<<<<<<<<<<<<<<< Task 10 – Query Federation Members (again) >>>>>>>>>>>>>>>>>>>>>
-- Route connection to the Federation Root
USE FEDERATION ROOT WITH RESET
GO
SELECT * FROM sys.federations
SELECT * FROM sys.federation_member_distributions ORDER BY federation_id, range_low;
GO

-- Route connection to the 2nd Federation Member (aka shard)
USE FEDERATION CustomerFederation(cid=100) WITH RESET, FILTERING=OFF
GO

--Query metadata
SELECT db_name() [db_name], db_id() [db_id]
SELECT * FROM sys.federation_member_distributions

-- Query customer table for high/low Federated Keys
SELECT MIN(CustomerID) [CustomerID Low], MAX(CustomerID) [CustomerID High] FROM Customer

--Get all rows from Customers in this member
SELECT * FROM Customer
GO




--<<<<<<<<<<<<<<<<<<<<< Task 11 – Query data from Federation member with Filtering On >>>>>>>>>>>>>>>>>>>>>

--Route to the 2nd federation but with filtering on
USE FEDERATION CustomerFederation(cid=100) WITH RESET, FILTERING=ON
GO

-- Query customer table for high/low Federated Keys
SELECT MIN(CustomerID) [CustomerID Low], MAX(CustomerID) [CustomerID High] FROM Customer
--Get all rows from Customers
SELECT * FROM Customer
GO


--<<<<<<<<<<<<<<<<<<<<< Task 12 – Using the SSMS Tooling >>>>>>>>>>>>>>>>>>>>>
-- List federation members

--<<<<<<<<<<<<<<<<<<<<< Perform Additional Split Operation >>>>>>>>>>>>>>>>>>>>>
USE FEDERATION ROOT WITH RESET
GO
ALTER FEDERATION CustomerFederation SPLIT AT (cid=150)
GO

--<<<<<<<<<<Return to Deck>>>>>>>>>>>--








The next item are the fan-out queries I showed. These should be used in conjunction with the Fan Out Query demo tool that can be found at: http://federationsutility-scus.cloudapp.net/





-- FANOUT SAMPLES. Execute using Fanout tool

--Simple Count in each member
SELECT count(*) from Customer

--Simple Select *
SELECT * FROM Customer

-- ALIGNED QUERY with ALIGNED AGGREGATES
-- get total orders value by customer having > $1000
SELECT [order].customerid, SUM(ordertotal) 
FROM customer inner join [order] on customer.customerid=[order].customerid
GROUP BY [order].customerid
HAVING SUM(ordertotal)>1000

--OK to add some more detail from customer table. 
SELECT firstname,lastname,companyname,[order].customerid, SUM(ordertotal) 
FROM customer inner join [order] on customer.customerid=[order].customerid
GROUP BY [order].customerid,firstname,lastname,companyname
HAVING SUM(ordertotal)>1000


--So now to group by Company name. i.e. Non Aligned query
SELECT companyname, SUM(ordertotal) as ordertotal
FROM customer inner join [order] on customer.customerid=[order].customerid
GROUP BY companyname
HAVING SUM(ordertotal)>1000

--We need a post processing step to aggregate the aggregates
SELECT companyname, SUM(ordertotal) 
FROM #Table
GROUP BY companyname


-- ALIGNED QUERY with NONE-ADDITIVE AGGREGATES
-- avg order size per customer
-- OK because we are aggregating within the attomic unit
SELECT firstname,lastname,companyname,[order].customerid, AVG(ordertotal) 
FROM customer inner join [order] on customer.customerid=[order].customerid
GROUP BY [order].customerid,firstname,lastname,companyname

--NON-ALIGNED QUERY with NON-ADDITIVE AGGREGATE
--avg order size
--#FAIL
SELECT AVG(ordertotal) 
FROM [order]

-- success -- 
-- Average: get sum and count instead of avg
SELECT SUM(ordertotal) tot, 
    COUNT(*) cnt
FROM [order]

-- Summary Query: 
SELECT SUM(tot)/SUM(cnt) average 
FROM #Table 


--<<<<<<<<<<<<Back to Deck>>>>>>>>>>>>>>>--

-- deploy a new reference table and add some data
CREATE TABLE tab1(dbname nvarchar(128), secs int, msecs int, primary key (dbname, secs, msecs));
INSERT INTO tab1 values(db_name(), datepart(ss, getdate()), datepart(ms, getdate()));
SELECT * FROM tab1;

-- update stats on all members
EXEC sp_updatestats

-- #session per member
SELECT db_name() dbname, count(a.session_id) session_count
FROM sys.dm_exec_sessions a

-- #session per member
SELECT TOP 5 db_name(),query_stats.query_hash AS "Query Hash", 
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    MIN(query_stats.statement_text) AS "Statement Text"
FROM 
    (SELECT QS.*, 
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END 
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

As always… feel free to flick across comments or questions.

|Monday, September 17, 2012 4:51:04 AM UTC|Comments [0]|