SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Estimated Operator Cost
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 05/24/2013 :  13:27:25  Show Profile  Reply with Quote
I've included a query plan below. In it you will see several "Index Scans" on indexes for table "Lessons" and "Classes". These indexes show a cost of 0%. You will also see a "Index Seek" on an index for table "Subscription" that shows a 7% cost.

If you mouse over the index scans you see that they have I/O and CPU costs that are orders of magnitudes higher than for the index seek. If both the I/O and CPU costs are massively higher for the scans then why do they have a cost of 0% while the seek has a cost of 7%?

I'm actually trying to get rid of these nonclustered index scans but I'm having trouble determining the index that it wants to use (I've included the query). It seems like it should be using the clustered index.

Plan...

WON'T LET ME POST THE PLAN - PROBABLY TOO LONG

Query...


SELECT [t0].[StudentsID] AS [ID], CONVERT(NVarChar,[t0].[StudentsID]) AS [IDString], [t0].[FirstName], [t0].[LastName], [t0].[MiddleName], [t0].[Username], [t0].[DepartmentID], [t1].[DepartmentName], [t0].[Location], [t0].[EmployeeNum], [t0].[Email], [t0].[Password], [t0].[Address], [t0].[City], [t0].[Province], [t0].[PostalCode], [t0].[Country], [t0].[PhoneNumber], [t0].[EmployeeSince], [t0].[LastLoggedIn], [t0].[DateAdded], [t0].[DateEdited], (
    SELECT [t3].[AcceptedTermsDate]
    FROM (
        SELECT TOP (1) [t2].[AcceptedTermsDate]
        FROM [dbo].[Subscription] AS [t2]
        WHERE ([t2].[CourseID] = @p3) AND ([t2].[LessonID] = @p4) AND ([t2].[StudentsID] = [t0].[StudentsID])
        ) AS [t3]
    ) AS [AcceptedTerms], (
    SELECT [t5].[StartDate]
    FROM (
        SELECT TOP (1) [t4].[StartDate]
        FROM [dbo].[Subscription] AS [t4]
        WHERE ([t4].[CourseID] = @p5) AND ([t4].[LessonID] = @p6) AND ([t4].[StudentsID] = [t0].[StudentsID])
        ) AS [t5]
    ) AS [DateSubscribed], (
    SELECT [t7].[PostponeEnrollDate]
    FROM (
        SELECT TOP (1) [t6].[PostponeEnrollDate]
        FROM [dbo].[PostponeSubscription] AS [t6]
        WHERE ([t6].[CourseID] = @p7) AND ([t6].[LessonID] = @p8) AND ([t6].[StudentsID] = [t0].[StudentsID])
        ) AS [t7]
    ) AS [DateDelaySubscribed], ((((((((((((((((((@p9 + (
    (CASE 
        WHEN (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [dbo].[Subscription] AS [t8]
            WHERE ([t8].[CourseID] = @p10) AND ([t8].[LessonID] = @p11) AND ([t8].[StudentsID] = [t0].[StudentsID])
            )) OR (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [dbo].[PostponeSubscription] AS [t9]
            WHERE ([t9].[CourseID] = @p12) AND ([t9].[LessonID] = @p13) AND ([t9].[StudentsID] = [t0].[StudentsID])
            )) THEN CONVERT(NVarChar(9),@p14)
        ELSE @p15
     END))) + @p16) + (CONVERT(NVarChar,[t0].[StudentsID]))) + @p17) + (CONVERT(NVarChar,[t0].[StudentsID]))) + @p18) + (CONVERT(NVarChar,[t0].[StudentsID]))) + @p19) + @p20) + (CONVERT(NVarChar,[t0].[StudentsID]))) + @p21) + (
    (CASE 
        WHEN (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [dbo].[Subscription] AS [t10]
            WHERE ([t10].[CourseID] = @p22) AND ([t10].[LessonID] = @p23) AND ([t10].[StudentsID] = [t0].[StudentsID])
            )) OR (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [dbo].[PostponeSubscription] AS [t11]
            WHERE ([t11].[CourseID] = @p24) AND ([t11].[LessonID] = @p25) AND ([t11].[StudentsID] = [t0].[StudentsID])
            )) THEN CONVERT(NVarChar,[t0].[StudentsID])
        ELSE CONVERT(NVarChar(MAX),@p26)
     END))) + @p27) + @p28) + (CONVERT(NVarChar,[t0].[StudentsID]))) + @p29) + (CONVERT(NVarChar,[t0].[StudentsID]))) + @p30) + (
    (CASE 
        WHEN EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [dbo].[PostponeSubscription] AS [t12]
            WHERE ([t12].[CourseID] = @p31) AND ([t12].[LessonID] = @p32) AND ([t12].[StudentsID] = [t0].[StudentsID])
            ) THEN (@p33 + (CONVERT(NVarChar(MAX),(
            SELECT [t14].[PostponeEnrollDate]
            FROM (
                SELECT TOP (1) [t13].[PostponeEnrollDate]
                FROM [dbo].[PostponeSubscription] AS [t13]
                WHERE ([t13].[CourseID] = @p34) AND ([t13].[LessonID] = @p35) AND ([t13].[StudentsID] = [t0].[StudentsID])
                ) AS [t14]
            )))) + @p36
        ELSE CONVERT(NVarChar(MAX),@p37)
     END)) AS [Enrolled], 
    (CASE 
        WHEN EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [dbo].[Subscription] AS [t15]
            WHERE ([t15].[CourseID] = @p38) AND ([t15].[LessonID] = @p39) AND ([t15].[StudentsID] = [t0].[StudentsID])
            ) THEN 1
        ELSE 0
     END) AS [EnrolledValue], ((
    SELECT [t19].[StatusDesc]
    FROM (
        SELECT TOP (1) [t16].[StatusDesc]
        FROM [dbo].
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000