Monstrosity of a Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Monstrosity of a Query

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2012-10-19 : 15:49:10

This query causes pageiolatch_sh waits and consequently locks of locking and blocking. I don't want to waste anyone's time here asking for a rewrite but is there anything obviously wrong with this query or that could be written better? There doesn't appear to be any SCANS (only SEEKS) in the query plan.


exec sp_executesql N'SELECT [t52].[SubID] AS [ID], [t52].[value] AS [IDString], [t52].[CourseID], [t52].[StudentsID] AS [StudentID], [t52].[value2] AS [Notes], [t52].[SubscriptionNotes] AS [NotesValue], [t52].[FirstName], [t52].[LastName], [t52].[MiddleName], [t52].[Username], [t52].[DepartmentID], [t52].[DepartmentName], [t52].[Location], [t52].[EmployeeNum], [t52].[Email], [t52].[Password], [t52].[Address], [t52].[City], [t52].[Province], [t52].[PostalCode], [t52].[Country], [t52].[PhoneNumber], [t52].[Notes] AS [LearnerNotes], [t52].[value3] AS [DateStarted], [t52].[Completed] AS [CompleteDate], [t52].[StartDate], [t52].[AcceptedTermsDate], [t52].[Status], [t52].[LessonTitle], [t52].[value4] AS [SessionDateStart], [t52].[value5] AS [Venue], [t52].[value6] AS [Complete], [t52].[value7] AS [CompleteValue], [t52].[value8] AS [CertificateID], [t52].[value9] AS [CertificateValid], [t52].[value10], [t52].[value22], CONVERT(NVarChar(MAX),[t52].[value322]) AS [value], [t52].[value11] AS [CertificateExpires], [t52].[value12] AS [ExamScore], [t52].[value13] AS [ExamAttempts], [t52].[value14] AS [ExamScoreAverage], [t52].[value15] AS [StatusDesc], [t52].[value16] AS [ActiveStatus], [t52].[value32] AS [LoginID], [t52].[value17] AS [Proctor], [t52].[BulkName], [t52].[ExpireType], [t52].[DaystoExpire] AS [DaysToExpre], [t52].[value18] AS [ExpireDate], [t52].[LastLoggedIn], [t52].[value19] AS [PercentComplete], [t52].[Extra1], [t52].[Extra2], [t52].[Extra3], [t52].[Extra4], [t52].[Extra5], [t52].[Extra6], [t52].[Extra7], [t52].[Extra8], [t52].[Extra9], [t52].[Extra10], [t52].[Extra11], [t52].[Extra12], [t52].[Extra13], [t52].[Extra14], [t52].[Extra15], [t52].[Extra16], [t52].[Extra17], [t52].[Extra18], [t52].[Extra19], [t52].[Extra20], [t52].[Extra21], [t52].[Extra22], [t52].[Extra23], [t52].[Extra24], [t52].[Extra25], [t52].[Extra26], [t52].[Extra27], [t52].[Extra28], [t52].[Extra29], [t52].[Extra30], [t52].[EmployeeSince], [t52].[HasPaid], [t52].[value20] AS [Tags], [t52].[ExternalCourseID] AS [ExtID], [t52].[VendorName]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t51].[LastName]) AS [ROW_NUMBER], [t51].[SubID], [t51].[value], [t51].[CourseID], [t51].[StudentsID], [t51].[value2], [t51].[SubscriptionNotes], [t51].[FirstName], [t51].[LastName], [t51].[MiddleName], [t51].[Username], [t51].[DepartmentID], [t51].[DepartmentName], [t51].[Location], [t51].[EmployeeNum], [t51].[Email], [t51].[Password], [t51].[Address], [t51].[City], [t51].[Province], [t51].[PostalCode], [t51].[Country], [t51].[PhoneNumber], [t51].[Notes], [t51].[value3], [t51].[Completed], [t51].[StartDate], [t51].[AcceptedTermsDate], [t51].[Status], [t51].[LessonTitle], [t51].[value4], [t51].[value5], [t51].[value6], [t51].[value7], [t51].[value8], [t51].[value9], [t51].[value10], [t51].[value22], [t51].[value322], [t51].[value11], [t51].[value12], [t51].[value13], [t51].[value14], [t51].[value15], [t51].[value16], [t51].[value32], [t51].[value17], [t51].[BulkName], [t51].[ExpireType], [t51].[DaystoExpire], [t51].[value18], [t51].[LastLoggedIn], [t51].[value19], [t51].[Extra1], [t51].[Extra2], [t51].[Extra3], [t51].[Extra4], [t51].[Extra5], [t51].[Extra6], [t51].[Extra7], [t51].[Extra8], [t51].[Extra9], [t51].[Extra10], [t51].[Extra11], [t51].[Extra12], [t51].[Extra13], [t51].[Extra14], [t51].[Extra15], [t51].[Extra16], [t51].[Extra17], [t51].[Extra18], [t51].[Extra19], [t51].[Extra20], [t51].[Extra21], [t51].[Extra22], [t51].[Extra23], [t51].[Extra24], [t51].[Extra25], [t51].[Extra26], [t51].[Extra27], [t51].[Extra28], [t51].[Extra29], [t51].[Extra30], [t51].[EmployeeSince], [t51].[HasPaid], [t51].[value20], [t51].[ExternalCourseID], [t51].[VendorName]
    FROM (
        SELECT [t11].[SubID], CONVERT(NVarChar,[t11].[SubID]) AS [value], [t11].[CourseID], [t11].[StudentsID], ((((((((((((((((((((((((((((((((@p8 + (CONVERT(NVarChar,[t11].[SubID]))) + @p9) + (CONVERT(NVarChar,[t11].[SubID]))) + @p10) + @p11) + @p12) + (
            (CASE 
                WHEN [t11].[SubscriptionNotes] = @p13 THEN @p14
                ELSE CONVERT(NVarChar(20),@p15)
             END))) + @p16) + @p17) + (
            (CASE 
                WHEN [t11].	[SubscriptionNotes] = @p18 THEN @p19
                ELSE CONVERT(NVarChar(20),@p20)
             END))) + @p21) + @p22) + (
            (CASE 
                WHEN [t11].[SubscriptionNotes] = @p23 THEN @p24
                ELSE CONVERT(NVarChar(20),@p25)
             END))) + @p26) + @p27) + (
            (CASE 
                WHEN [t11].[SubscriptionNotes] = @p28 THEN @p29
                ELSE CONVERT(NVarChar(20),@p30)
             END))) + @p31) + @p32) + (
            (CASE 
                WHEN [t11].[SubscriptionNotes] = @p33 THEN @p34
                ELSE CONVERT(NVarChar(20),@p35)
             END))) + @p36) + @p37) + (
            (CASE 
                WHEN [t11].[SubscriptionNotes] = @p38 THEN @p39
                ELSE CONVERT(NVarChar(20),@p40)
             END))) + @p41) + @p42) + (
            (CASE 
                WHEN [t11].[SubscriptionNotes] = @p43 THEN @p44
                ELSE CONVERT(NVarChar(20),@p45)
             END))) + @p46) + @p47) + (
            (CASE 
                WHEN [t11].[SubscriptionNotes] = @p48 THEN @p49
                ELSE CONVERT(NVarChar(20),@p50)
             END))) + @p51) + @p52) + (
            (CASE 
                WHEN [t11].[SubscriptionNotes] = @p53 THEN @p54
                ELSE CONVERT(NVarChar(20),@p55)
             END))) + @p56) + @p57 AS [value2], [t11].[SubscriptionNotes], [t11].[FirstName], [t11].[LastName], [t11].[MiddleName], [t11].[Username], [t11].[DepartmentID], [t11].[DepartmentName], [t11].[Location], [t11].[EmployeeNum], [t11].[Email], [t11].[Password], [t11].[Address], [t11].[City], [t11].[Province], [t11].[PostalCode], [t11].[Country], [t11].[PhoneNumber], [t11].[Notes], (
            SELECT MIN([t14].[ActivityStarted])
            FROM [dbo].[Activity] AS [t14]
            WHERE ([t14].[CourseID] = [t11].[CourseID]) AND ([t14].[StudentsID] = [t11].[StudentsID2])
            ) AS [value3], [t11].[Completed], [t11].[StartDate], [t11].[AcceptedTermsDate], [t11].[Status], [t12].[LessonTitle], (
            SELECT [t16].[DateStart]
            FROM (
                SELECT TOP (1) [t15].[DateStart]
                FROM [dbo].[Classes] AS [t15]
                WHERE [t15].[LessonID] = [t12].[LessonID]
                ) AS [t16]
            ) AS [value4], 
            (CASE 
                WHEN EXISTS(
                    SELECT TOP (1) NULL AS [EMPTY]
                    FROM [dbo].[Classes] AS [t17]
                    WHERE [t17].[LessonID] = [t12].[LessonID]
                    ) THEN 
                    (CASE 
                        WHEN (((
                            SELECT [t19].[ClassroomID]
                            FROM (
                                SELECT TOP (1) [t18].[ClassroomID]
                                FROM [dbo].[Classes] AS [t18]
                                WHERE [t18].[LessonID] = [t12].[LessonID]
                                ) AS [t19]
                            )) IS NOT NULL) AND (((
                            SELECT [t21].[ClassroomID]
                            FROM (
                                SELECT TOP (1) [t20].[ClassroomID]
                                FROM [dbo].[Classes] AS [t20]
                                WHERE [t20].[LessonID] = [t12].[LessonID]
                                ) AS [t21]
                            )) > @p58) THEN (
                            SELECT [t24].[LocationName]
                            FROM (
                                SELECT TOP (1) [t22].[ClassroomID]
                                FROM [dbo].[Classes] AS [t22]
                                WHERE [t22].[LessonID] = [t12].[LessonID]
                                ) AS [t23]
                            LEFT OUTER JOIN [dbo].[Classrooms] AS [t24] ON [t24].[ClassroomID] = [t23].[ClassroomID]
                            )
                        ELSE CONVERT(NVarChar(150),@p59)
                     END)
                ELSE CONVERT(NVarChar(150),@p60)
             END) AS [value5], ((((((((((((@p61 + (
            (CASE 
                WHEN [t11].[Status] = @p62 THEN CONVERT(NVarChar(9),@p63)
                ELSE @p64
             END))) + @p65) + (CONVERT(NVarChar,[t11].[SubID]))) + @p66) + (CONVERT(NVarChar,[t11].[SubID]))) + @p67) + (CONVERT(NVarChar,[t11].[SubID]))) + @p68) + @p69) + (CONVERT(NVarChar,[t11].[SubID]))) + @p70) + (CONVERT(NVarChar,[t11].[SubID]))) + @p71 AS [value6], 
            (CASE 
                WHEN [t11].[Status] = @p72 THEN 1
                WHEN NOT ([t11].[Status] = @p72) THEN 0
                ELSE NULL
             END) AS [value7], 
            (CASE 
                WHEN EXISTS(
                    SELECT TOP (1) NULL AS [EMPTY]
                    FROM [dbo].[Certificates] AS [t25]
                    WHERE ([t25].[CourseID] = [t11].[CourseID]) AND (([t11].[AllowReEnrollement] <> @p73) OR (([t11].[AllowReEnrollement] = @p74) AND ([t11].[Status] = @p75))) AND (([t11].[Type] <> @p76) OR (([t11].[Type] = @p77) AND ([t25].[LessonID] = [t11].[LessonID]))) AND ([t25].[StudentsID] = [t11].[StudentsID2])
                    ORDER BY [t25].[Expires] DESC
                    ) THEN (
                    SELECT [t27].[CertificateID]
                    FROM (
                        SELECT TOP (1) [t26].[CertificateID]
                        FROM [dbo].[Certificates] AS [t26]
                        WHERE ([t26].[CourseID] = [t11].[CourseID]) AND (([t11].[AllowReEnrollement] <> @p73) OR (([t11].[AllowReEnrollement] = @p74) AND ([t11].[Status] = @p75))) AND (([t11].[Type] <> @p76) OR (([t11].[Type] = @p77) AND ([t26].[LessonID] = [t11].[LessonID]))) AND ([t26].[StudentsID] = [t11].[StudentsID2])
                        ORDER BY [t26].[Expires] DESC
                        ) AS [t27]
                    )
                ELSE @p78
             END) AS [value8], (
            SELECT [t29].[Valid]
            FROM (
                SELECT TOP (1) [t28].[Valid]
                FROM [dbo].[Certificates] AS [t28]
                WHERE ([t28].[CourseID] = [t11].[CourseID]) AND (([t11].[AllowReEnrollement] <> @p73) OR (([t11].[AllowReEnrollement] = @p74) AND ([t11].[Status] = @p75))) AND (([t11].[Type] <> @p76) OR (([t11].[Type] = @p77) AND ([t28].[LessonID] = [t11].[LessonID]))) AND ([t28].[StudentsID] = [t11].[StudentsID2])
                ORDER BY [t28].[Expires] DESC
                ) AS [t29]
            ) AS [value9], [t11].[value] AS [value10], [t11].[value2] AS [value22], (
            SELECT [t31].[Expires]
            FROM (
                SELECT TOP (1) [t30].[Expires]
                FROM [dbo].[Certificates] AS [t30]
                WHERE ([t30].[CourseID] = [t11].[CourseID]) AND (([t11].[AllowReEnrollement] <> @p73) OR (([t11].[AllowReEnrollement] = @p74) AND ([t11].[Status] = @p75))) AND (([t11].[Type] <> @p76) OR (([t11].[Type] = @p77) AND ([t30].[LessonID] = [t11].[LessonID]))) AND ([t30].[StudentsID] = [t11].[StudentsID2])
                ORDER BY [t30].[Expires] DESC
                ) AS [t31]
            ) AS [value11], (
            SELECT MAX([t32].[Score])
            FROM [dbo].[Activity] AS [t32]
            LEFT OUTER JOIN [dbo].[Courses] AS [t33] ON [t33].[CourseID] = [t32].[CourseID]
            LEFT OUTER JOIN [dbo].[Lessons] AS [t34] ON [t34].[LessonID] = [t32].[LessonID]
            WHERE (([t33].[Type] = @p79) OR ([t34].[Type] = @p80)) AND ([t32].[CourseID] = [t11].[CourseID]) AND ([t32].[StudentsID] = [t11].[StudentsID]) AND ([t32].[LessonID] IS NOT NULL)
            ) AS [value12], (
            SELECT COUNT(*)
            FROM [dbo].[Activity] AS [t35]
            LEFT OUTER JOIN [dbo].[Courses] AS [t36] ON [t36].[CourseID] = [t35].[CourseID]
            LEFT OUTER JOIN [dbo].[Lessons] AS [t37] ON [t37].[LessonID] = [t35].[LessonID]
            WHERE (([t36].[Type] = @p81) OR ([t37].[Type] = @p82)) AND ([t35].[CourseID] = [t11].[CourseID]) AND ([t35].[StudentsID] = [t11].[StudentsID]) AND ([t35].[LessonID] IS NOT NULL)
            ) AS [value13], (
            SELECT AVG([t38].[Score])
            FROM [dbo].[Activity] AS [t38]
            LEFT OUTER JOIN [dbo].[Courses] AS [t39] ON [t39].[CourseID] = [t38].[CourseID]
            LEFT OUTER JOIN [dbo].[Lessons] AS [t40] ON [t40].[LessonID] = [t38].[LessonID]
            WHERE (([t39].[Type] = @p83) OR ([t40].[Type] = @p84)) AND ([t38].[CourseID] = [t11].[CourseID]) AND ([t38].[StudentsID] = [t11].[StudentsID]) AND ([t38].[LessonID] IS NOT NULL)
            ) AS [value14], (
            SELECT [t42].[StatusDesc]
            FROM (
                SELECT TOP (1) [t41].[StatusDesc]
                FROM [dbo].[DescStatus] AS [t41]
                WHERE ([t41].[Status]) = [t11].[Status]
                ) AS [t42]
            ) AS [value15], (
            SELECT [t44].[TypeDesc]
            FROM (
                SELECT TOP (1) [t43].[TypeDesc]
                FROM [dbo].[DescTypes] AS [t43]
                WHERE ([t43].[TypeGroup] = @p85) AND ([t43].[Type] = (CONVERT(NVarChar,[t11].[ActiveStatus])))
                ) AS [t44]
            ) AS [value16], [t11].[value3] AS [value32], (
            SELECT TOP (1) [t46].[value]
            FROM (
                SELECT ([t45].[ContactFirstName] + @p86) + [t45].[ContactLastName] AS [value], [t45].[LoginID]
                FROM [dbo].[AdminLogins] AS [t45]
                ) AS [t46]
            WHERE [t46].[LoginID] = [t11].[value3]
            ) AS [value17], [t13].[BulkName], [t11].[ExpireType], [t11].[DaystoExpire], 
            (CASE 
                WHEN [t11].[DateExpires] IS NULL THEN 
                    (CASE 
                        WHEN [t11].[ExpireType] = @p87 THEN DATEADD(ms, (CONVERT(BigInt,(CONVERT(Float,[t11].[DaystoExpire])) * 86400000)) % 86400000, DATEADD(day, (CONVERT(BigInt,(CONVERT(Float,[t11].[DaystoExpire])) * 86400000)) / 86400000, [t11].[StartDate]))
                        ELSE [t11].[DateExpires2]
                     END)
                ELSE [t11].[DateExpires]
             END) AS [value18], [t11].[LastLoggedIn], (CONVERT(Float,(
            SELECT COUNT(*)
            FROM (
                SELECT DISTINCT [t47].[LessonID]
                FROM [dbo].[Activity] AS [t47]
                WHERE ([t47].[Status] = @p88) AND ([t47].[CourseID] = [t11].[CourseID]) AND ([t47].[StudentsID] = [t11].[StudentsID]) AND ([t47].[LessonID] IS NOT NULL)
                ) AS [t48]
            ))) / (CONVERT(Float,
            (CASE 
                WHEN (((
                    SELECT COUNT(*)
                    FROM [dbo].[Lessons] AS [t49]
                    WHERE [t49].[CourseID] = [t11].[CourseID2]
                    )) = @p89) OR ([t11].[Type] = @p90) THEN @p91
                ELSE (
                    SELECT COUNT(*)
                    FROM [dbo].[Lessons] AS [t50]
                    WHERE [t50].[CourseID] = [t11].[CourseID2]
                    )
             END))) AS [value19], [t11].[Extra1], [t11].[Extra2], [t11].[Extra3], [t11].[Extra4], [t11].[Extra5], [t11].[Extra6], [t11].[Extra7], [t11].[Extra8], [t11].[Extra9], [t11].[Extra10], [t11].[Extra11], [t11].[Extra12], [t11].[Extra13], [t11].[Extra14], [t11].[Extra15], [t11].[Extra16], [t11].[Extra17], [t11].[Extra18], [t11].[Extra19], [t11].[Extra20], [t11].[Extra21], [t11].[Extra22], [t11].[Extra23], [t11].[Extra24], [t11].[Extra25], [t11].[Extra26], [t11].[Extra27], [t11].[Extra28], [t11].[Extra29], [t11].[Extra30], [t11].[EmployeeSince], [t11].[HasPaid], [dbo].[FuncGetCourseTags]([t11].[CourseID]) AS [value20], [t11].[ExternalCourseID], [t11].[VendorName], [t11].[value32] AS [value322], [t11].[ClientID], [t11].[ActiveStatus]
        FROM (
            SELECT [t8].[SubID], [t8].[StudentsID], [t8].[CourseID], [t8].[BulkID], [t8].[LessonID], [t8].[Status], [t8].[Completed], [t8].[StartDate], [t8].[DateExpires], [t8].[AcceptedTermsDate], [t8].[SubscriptionNotes], [t8].[value], [t8].[value2], (
                SELECT MAX([t9].[LoginID])
                FROM [dbo].[Activity] AS [t9]
                LEFT OUTER JOIN [dbo].[Lessons] AS [t10] ON [t10].[LessonID] = [t9].[LessonID]
                WHERE ([t10].[Type] = @p7) AND ([t9].[CourseID] = [t8].[CourseID]) AND ([t9].[StudentsID] = [t8].[StudentsID]) AND ([t9].[LessonID] IS NOT NULL)
                ) AS [value3], [t8].[value3] AS [value32], [t8].[FirstName], [t8].[LastName], [t8].[MiddleName], [t8].[Username], [t8].[DepartmentID], [t8].[DepartmentName], [t8].[Location], [t8].[EmployeeNum], [t8].[Email], [t8].[Password], [t8].[Address], [t8].[City], [t8].[Province], [t8].[PostalCode], [t8].[Country], [t8].[PhoneNumber], [t8].[Notes], [t8].[StudentsID2], [t8].[AllowReEnrollement], [t8].[Type], [t8].[ActiveStatus], [t8].[ExpireType], [t8].[DaystoExpire], [t8].[DateExpires2], [t8].[LastLoggedIn], [t8].[CourseID2], [t8].[Extra1], [t8].[Extra2], [t8].[Extra3], [t8].[Extra4], [t8].[Extra5], [t8].[Extra6], [t8].[Extra7], [t8].[Extra8], [t8].[Extra9], [t8].[Extra10], [t8].[Extra11], [t8].[Extra12], [t8].[Extra13], [t8].[Extra14], [t8].[Extra15], [t8].[Extra16], [t8].[Extra17], [t8].[Extra18], [t8].[Extra19], [t8].[Extra20], [t8].[Extra21], [t8].[Extra22], [t8].[Extra23], [t8].[Extra24], [t8].[Extra25], [t8].[Extra26], [t8].[Extra27], [t8].[Extra28], [t8].[Extra29], [t8].[Extra30], [t8].[EmployeeSince], [t8].[HasPaid], [t8].[ExternalCourseID], [t8].[VendorName], [t8].[ClientID]
            FROM (
                SELECT [t0].[SubID], [t0].[StudentsID], [t0].[CourseID], [t0].[BulkID], [t0].[LessonID], [t0].[Status], [t0].[Completed], [t0].[StartDate], [t0].[DateExpires], [t0].[AcceptedTermsDate], [t0].[SubscriptionNotes], 
                    (CASE 
                        WHEN ((
                            SELECT [t5].[Valid]
                            FROM (
                                SELECT TOP (1) [t4].[Valid]
                                FROM [dbo].[Certificates] AS [t4]
                                WHERE ([t4].[CourseID] = [t0].[CourseID]) AND (([t2].[AllowReEnrollement] <> @p0) OR (([t2].[AllowReEnrollement] = @p1) AND ([t0].[Status] = @p2))) AND ([t4].[StudentsID] = [t1].[StudentsID])
                                ORDER BY [t4].[Expires] DESC
                                ) AS [t5]
                            )) IS NOT NULL THEN 1
                        ELSE 0
                     END) AS [value], (
                    SELECT [t7].[Valid]
                    FROM (
                        SELECT TOP (1) [t6].[Valid]
                        FROM [dbo].[Certificates] AS [t6]
                        WHERE ([t6].[CourseID] = [t0].[CourseID]) AND (([t2].[AllowReEnrollement] <> @p3) OR (([t2].[AllowReEnrollement] = @p4) AND ([t0].[Status] = @p5))) AND ([t6].[StudentsID] = [t1].[StudentsID])
                        ORDER BY [t6].[Expires] DESC
                        ) AS [t7]
                    ) AS [value2], @p6 AS [value3], [t1].[FirstName], [t1].[LastName], [t1].[MiddleName], [t1].[Username], [t1].[DepartmentID], [t3].[DepartmentName], [t1].[Location], [t1].[EmployeeNum], [t1].[Email], [t1].[Password], [t1].[Address], [t1].[City], [t1].[Province], [t1].[PostalCode], [t1].[Country], [t1].[PhoneNumber], [t1].[Notes], [t1].[StudentsID] AS [StudentsID2], [t2].[AllowReEnrollement], [t2].[Type], [t1].[ActiveStatus], [t2].[ExpireType], [t2].[DaystoExpire], [t2].[DateExpires] AS [DateExpires2], [t1].[LastLoggedIn], [t2].[CourseID] AS [CourseID2], [t1].[Extra1], [t1].[Extra2], [t1].[Extra3], [t1].[Extra4], [t1].[Extra5], [t1].[Extra6], [t1].[Extra7], [t1].[Extra8], [t1].[Extra9], [t1].[Extra10], [t1].[Extra11], [t1].[Extra12], [t1].[Extra13], [t1].[Extra14], [t1].[Extra15], [t1].[Extra16], [t1].[Extra17], [t1].[Extra18], [t1].[Extra19], [t1].[Extra20], [t1].[Extra21], [t1].[Extra22], [t1].[Extra23], [t1].[Extra24], [t1].[Extra25], [t1].[Extra26], [t1].[Extra27], [t1].[Extra28], [t1].[Extra29], [t1].[Extra30], [t1].[EmployeeSince], [t1].[HasPaid], [t2].[ExternalCourseID], [t2].[VendorName], [t1].[ClientID]
                FROM [dbo].[Subscription] AS [t0]
                LEFT OUTER JOIN [dbo].[Students] AS [t1] ON [t1].[StudentsID] = [t0].[StudentsID]
                LEFT OUTER JOIN [dbo].[Courses] AS [t2] ON [t2].[CourseID] = [t0].[CourseID]
                LEFT OUTER JOIN [dbo].[Departments] AS [t3] ON [t3].[DepartmentID] = [t1].[DepartmentID]
                ) AS [t8]
            ) AS [t11]
        LEFT OUTER JOIN [dbo].[Lessons] AS [t12] ON [t12].[LessonID] = [t11].[LessonID]
        LEFT OUTER JOIN [dbo].[SubscriptionBulk] AS [t13] ON [t13].[BulkID] = [t11].[BulkID]
        ) AS [t51]
    WHERE ([t51].[ClientID] = @p92) AND ([t51].[CourseID] = @p93) AND ((
        (CASE 
            WHEN [t51].[ActiveStatus] IS NOT NULL THEN [t51].[ActiveStatus]
            ELSE @p94
         END)) IN (@p95))
    ) AS [t52]
WHERE [t52].[ROW_NUMBER] BETWEEN @p96 + 1 AND @p96 + @p97
ORDER BY [t52].[ROW_NUMBER]',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 int,@p6 nvarchar(4000),@p7 int,@p8 nvarchar(4000),@p9 nvarchar(4000),@p10 nvarchar(4000),@p11 nvarchar(4000),@p12 nvarchar(4000),@p13 varchar(8000),@p14 nvarchar(4000),@p15 nvarchar(4000),@p16 nvarchar(4000),@p17 nvarchar(4000),@p18 varchar(8000),@p19 nvarchar(4000),@p20 nvarchar(4000),@p21 nvarchar(4000),@p22 nvarchar(4000),@p23 varchar(8000),@p24 nvarchar(4000),@p25 nvarchar(4000),@p26 nvarchar(4000),@p27 nvarchar(4000),@p28 varchar(8000),@p29 nvarchar(4000),@p30 nvarchar(4000),@p31 nvarchar(4000),@p32 nvarchar(4000),@p33 varchar(8000),@p34 nvarchar(4000),@p35 nvarchar(4000),@p36 nvarchar(4000),@p37 nvarchar(4000),@p38 varchar(8000),@p39 nvarchar(4000),@p40 nvarchar(4000),@p41 nvarchar(4000),@p42 nvarchar(4000),@p43 varchar(8000),@p44 nvarchar(4000),@p45 nvarchar(4000),@p46 nvarchar(4000),@p47 nvarchar(4000),@p48 varchar(8000),@p49 nvarchar(4000),@p50 nvarchar(4000),@p51 nvarchar(4000),@p52 nvarchar(4000),@p53 varchar(8000),@p54 nvarchar(4000),@p55 nvarchar(4000),@p56 nvarchar(4000),@p57 nvarchar(4000),@p58 int,@p59 nvarchar(4000),@p60 nvarchar(4000),@p61 nvarchar(4000),@p62 int,@p63 nvarchar(4000),@p64 nvarchar(4000),@p65 nvarchar(4000),@p66 nvarchar(4000),@p67 nvarchar(4000),@p68 nvarchar(4000),@p69 nvarchar(4000),@p70 nvarchar(4000),@p71 nvarchar(4000),@p72 int,@p73 int,@p74 int,@p75 int,@p76 int,@p77 int,@p78 int,@p79 int,@p80 int,@p81 int,@p82 int,@p83 int,@p84 int,@p85 varchar(8000),@p86 nvarchar(4000),@p87 int,@p88 int,@p89 int,@p90 int,@p91 int,@p92 int,@p93 int,@p94 int,@p95 int,@p96 int,@p97 int',@p0=1,@p1=1,@p2=2,@p3=1,@p4=1,@p5=2,@p6=N'',@p7=2,@p8=N'<select id="Notes',@p9=N'" name="Notes',@p10=N'">',@p11=N'<option value=""></option>',@p12=N'<option value="Equivalency"',@p13='Equivalency',@p14=N' selected="selected"',@p15=N'',@p16=N'>Equivalency</option>',@p17=N'<option value="Waived"',@p18='Waived',@p19=N' selected="selected"',@p20=N'',@p21=N'>Waived</option>',@p22=N'<option value="Cancelled"',@p23='Cancelled',@p24=N' selected="selected"',@p25=N'',@p26=N'>Cancelled</option>',@p27=N'<option value="Late Cancellation"',@p28='Late Cancellation',@p29=N' selected="selected"',@p30=N'',@p31=N'>Late Cancellation</option>',@p32=N'<option value="No Show"',@p33='No Show',@p34=N' selected="selected"',@p35=N'',@p36=N'>No Show</option>',@p37=N'<option value="Incomplete"',@p38='Incomplete',@p39=N' selected="selected"',@p40=N'',@p41=N'>Incomplete</option>',@p42=N'<option value="Pending"',@p43='Pending',@p44=N' selected="selected"',@p45=N'',@p46=N'>Pending</option>',@p47=N'<option value="Walk-In"',@p48='Walk-In',@p49=N' selected="selected"',@p50=N'',@p51=N'>Walk-In</option>',@p52=N'<option value="Fail"',@p53='Fail',@p54=N' selected="selected"',@p55=N'',@p56=N'>Fail</option>',@p57=N'</select>',@p58=0,@p59=N'',@p60=N'',@p61=N'<input type="checkbox" ',@p62=2,@p63=N'checked',@p64=N'unchecked',@p65=N' onClick="CheckBoxClick(',@p66=N', ''Complete'');" id="Complete',@p67=N'" name="Complete" value=',@p68=N' />',@p69=N'<input type="hidden" id="ValueComplete',@p70=N'" name="ValueComplete" value="',@p71=N'" />',@p72=2,@p73=1,@p74=1,@p75=2,@p76=3,@p77=3,@p78=0,@p79=3,@p80=2,@p81=3,@p82=2,@p83=3,@p84=2,@p85='User',@p86=N' ',@p87=1,@p88=2,@p89=0,@p90=3,@p91=1,@p92=1,@p93=59375,@p94=1,@p95=1,@p96=0,@p97=20

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-19 : 15:57:49

What isolation level are you using? I'd recommend RCSI if you can.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2012-10-19 : 16:24:54

quote:
Originally posted by tkizer

What isolation level are you using? I'd recommend RCSI if you can.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

That's what we are using. It is a mystery to me why we still get locks and blocking on selects but we do.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-10-19 : 16:27:59

Are you sure that the application isn't overriding the isolation level? We have an app that is overriding it. The developers picked serializable for unknown reasons, and that overrides RCSI. Check what isolation level the users have by checking their connection settings. You can easily do this via the "existing connections" event in Profiler.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-19 : 17:22:34

Does the execution plan show any spool operators? I wouldn't say they're the cause of the waits but lots of subqueries could create spools and perhaps cause contention in tempdb.

Was this SQL generated by a program or written by hand? I wonder because:

1. 32 levels of (((((((((((((((((((((((((((((((( is not a good sign if written by hand.
2. All parameters and table aliases are numbered instead of having descriptive names.
3. Multiple joins to the same tables with (nearly) identical criteria (t32, t35, and t38 tables)
4. I counted 17 subqueries and 35 table/join references for 12 unique tables. If this isn't spooling or doing lots of nested loops it would be a miracle.
5. There are a lot of TOP 1 clauses without ORDER BY, which makes it inconsistent at best and meaningless at worst.
6. There are also CASE expressions with nested TOP 1 subqueries that compare to NULL. These can be rewritten as EXISTS() and probably perform better. From what I gather most of the TOP 1 subqueries could probably be rewritten this way.

I'm not trying to criticize, I just take the view that simpler SQL tends to perform better, or at least is a lot easier to tune.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2012-10-19 : 17:31:35

quote:
Originally posted by tkizer

Are you sure that the application isn't overriding the isolation level? We have an app that is overriding it. The developers picked serializable for unknown reasons, and that overrides RCSI. Check what isolation level the users have by checking their connection settings. You can easily do this via the "existing connections" event in Profiler.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2012-10-19 : 17:32:29

quote:
Originally posted by robvolk

Does the execution plan show any spool operators? I wouldn't say they're the cause of the waits but lots of subqueries could create spools and perhaps cause contention in tempdb.

Was this SQL generated by a program or written by hand? I wonder because:

1. 32 levels of (((((((((((((((((((((((((((((((( is not a good sign if written by hand.
2. All parameters and table aliases are numbered instead of having descriptive names.
3. Multiple joins to the same tables with (nearly) identical criteria (t32, t35, and t38 tables)
4. I counted 17 subqueries and 35 table/join references for 12 unique tables. If this isn't spooling or doing lots of nested loops it would be a miracle.
5. There are a lot of TOP 1 clauses without ORDER BY, which makes it inconsistent at best and meaningless at worst.
6. There are also CASE expressions with nested TOP 1 subqueries that compare to NULL. These can be rewritten as EXISTS() and probably perform better. From what I gather most of the TOP 1 subqueries could probably be rewritten this way.

I'm not trying to criticize, I just take the view that simpler SQL tends to perform better, or at least is a lot easier to tune.

Generated from LINQ. I'll post the execution plan.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-19 : 17:35:36

PAGEIOLATCH_SH doesn't mean the query is blocking. PAGEIOLATCH_SH waits are waiting on a database page to be fetched from the database into the page cache. It sounds like you you might be swapping data in and out of memory.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-19 : 17:38:47

Also, be careful with RCSI, as you will tend to generate even more IO which may exacerbate your PAGEIOLATCH_SH issue.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2012-10-19 : 17:42:46

quote:
Originally posted by Lamprey

PAGEIOLATCH_SH doesn't mean the query is blocking. PAGEIOLATCH_SH waits are waiting on a database page to be fetched from the database into the page cache. It sounds like you you might be swapping data in and out of memory.

There is blocking being reported associated with that query (using Confio Ignite).

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2012-10-19 : 17:44:37

quote:
Originally posted by Lamprey

Also, be careful with RCSI, as you will tend to generate even more IO which may exacerbate your PAGEIOLATCH_SH issue.

Interesting. Hadn't thought about that.

Note: I won't post the execution plan. It is several MB in size. There are tons of nested loops but don't see much spooling.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-19 : 17:57:50

If that query definition is static, I would suggest creating a stored procedure to replace it. You can preserve the parameters (and give them better names if you want) and work on tweaking the SQL. If it's dynamic, and/or LINQ changes chunks of it based on parameter values, you may be stuck with it. LINQ really sucks, especially if you have more than 2-3 tables in your queries.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources