EX0018 : Analyze execution plan and check for high cost operations

 1CREATE PROCEDURE [dbo].[uspTest]
 2    @ManagerID [int],
 3    @ManagerID1 [int] = 1,
 4    @ManagerID2 [int] = 12,
 5        @ManagerID3 [nvarchar](50) = NULL,
 6        @ManagerID4 nvarchar(50) = 'NULL1',
 7        @ManagerID5 nvarchar(50) = 'NULL'
 8
 9AS 
10BEGIN   
11    SET NOCOUNT ON;
12
13    -- Use recursive query to list out all Employees required for a particular Manager
14    WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns
15    AS (
16        SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], 0 -- Get the initial list of Employees for Manager n
17        FROM [HumanResources].[Employee] e 
18            INNER JOIN [Person].[Contact] c 
19            ON e.[ContactID] = c.[ContactID]
20        WHERE [ManagerID] = '@ManagerID'
21        UNION ALL
22        SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
23        FROM [HumanResources].[Employee] e 
24            INNER JOIN [EMP_cte]
25            ON e.[ManagerID] = [EMP_cte].[EmployeeID]
26            INNER JOIN [Person].[Contact] c 
27            ON e.[ContactID] = c.[ContactID]
28        )
29    -- Join back to Employee to return the manager name 
30    SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName',
31        [EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- Outer select from the CTE
32    FROM [EMP_cte] 
33        INNER JOIN [HumanResources].[Employee] e 
34        ON [EMP_cte].[ManagerID] = e.[EmployeeID]
35        INNER JOIN [Person].[Contact] c 
36        ON e.[ContactID] = c.[ContactID]
37    ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]
38    OPTION (MAXRECURSION 25) 
39
40
41
42SELECT  *
43FROM  Sales.SalesOrderHeader AS SOH
44JOIN Sales.SalesOrderDetail AS SOD
45ON  SOH.SalesOrderID = SOD.SalesOrderID
46
47SELECT 
48    e.[EmployeeID]
49    ,c.[Title]
50    ,c.[FirstName]
51    ,c.[MiddleName]
52    ,c.[LastName]
53    ,c.[Suffix]
54    ,e.[Title] AS [JobTitle] 
55    ,c.[Phone]
56    ,c.[EmailAddress]
57    ,c.[EmailPromotion]
58    ,a.[AddressLine1]
59    ,a.[AddressLine2]
60    ,a.[City]
61    ,sp.[Name] AS [StateProvinceName] 
62    ,a.[PostalCode]
63    ,cr.[Name] AS [CountryRegionName] 
64    ,c.[AdditionalContactInfo]
65FROM [HumanResources].[Employee] e
66    INNER JOIN [Person].[Contact] c 
67    ON c.[ContactID] = e.[ContactID]
68    INNER JOIN [HumanResources].[EmployeeAddress] ea 
69    ON e.[EmployeeID] = ea.[EmployeeID] 
70    INNER JOIN [Person].[Address] a 
71    ON ea.[AddressID] = a.[AddressID]
72    INNER JOIN [Person].[StateProvince] sp 
73    ON sp.[StateProvinceID] = a.[StateProvinceID]
74    INNER JOIN [Person].[CountryRegion] cr 
75    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
76
77SELECT ContactID,
78LastName,
79Phone
80FROM Person.Contact
81WHERE EmailAddress LIKE 'sab%'
82
83SELECT *
84FROM [dbo].[DatabaseLog]
85WHERE DatabaseLogID = 1
86
87SELECT  * FROM Person.AddressType WHERE Name = 1
88
89END;