locked
Tuning Wizard and linq RRS feed

  • Question

  • User-725075801 posted

    I have a large project and need to optimize the database, I would like to use the tuning wizard, but it seems to me that the tuning wizard does not work with linq queries as I get no suggestions. This seems weird as in the end it is just sql statements.

    Am I correct that the wizard does not work with linq?

    if so what can I do to find my missing indexes,

    if not why do I get no suggestions.

    Thanks

    Tuesday, May 19, 2015 11:11 PM

Answers

  • User1711366110 posted

    the column_useage, had values like "Equality" and "Include", what is the meaning here. I assume that Equality means in was included in a where statement "where value = value", but not sure what include means.

       As per this case, check the following details :

    EQUALITY  :

    •  Column contributes to a predicate that expresses equality, of the form:
    •   table.column =constant_value

     INEQUALITY :

    • Column contributes to a predicate that expresses inequality, for example, a predicate of the form:
    • table.column > constant_value
    • Any comparison operator other than "=" expresses inequality

     INCLUDE :

    • Column is not used to evaluate a predicate, but is used for another reason, for example, to cover a query.

    for more information, refer the following links :

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 21, 2015 10:43 PM

All replies

  • User1711366110 posted

    if so what can I do to find my missing indexes,if not why do I get no suggestions.
      

    As per this case, you can find the missing indexes in database side by using the following script :

    SELECT dm_mid.database_id AS DatabaseID,
    dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
    dm_migs.last_user_seek AS Last_User_Seek,
    OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
    'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
    CASE
    WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
    ELSE ''
    END
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
     + ']'
    + ' ON ' + dm_mid.statement
    + ' (' + ISNULL (dm_mid.equality_columns,'')
     + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
    '' END
    + ISNULL (dm_mid.inequality_columns, '')
     + ')'
    + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
    FROM sys.dm_db_missing_index_groups dm_mig
    INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
    ON dm_migs.group_handle = dm_mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details dm_mid
    ON dm_mig.index_handle = dm_mid.index_handle
    WHERE dm_mid.database_ID = DB_ID()
    ORDER BY Avg_Estimated_Impact DESC
    GO

    for more information, Click here to refer about find missing & unused indexes in sqlserver.

    Thursday, May 21, 2015 4:39 AM
  • User-725075801 posted

    Thanks, I used the query from the link, it helped heaps, but I had a few questions about results.
    The first column index_handle, I assume that when they have the same handle they should be in the same index?

    also the column_useage, had values like "Equality" and "Include", what is the meaning here. I assume that Equality means in was included in a where statement "where value = value", but not sure what include means.
    Thanks

    Thursday, May 21, 2015 6:51 AM
  • User1711366110 posted

    the column_useage, had values like "Equality" and "Include", what is the meaning here. I assume that Equality means in was included in a where statement "where value = value", but not sure what include means.

       As per this case, check the following details :

    EQUALITY  :

    •  Column contributes to a predicate that expresses equality, of the form:
    •   table.column =constant_value

     INEQUALITY :

    • Column contributes to a predicate that expresses inequality, for example, a predicate of the form:
    • table.column > constant_value
    • Any comparison operator other than "=" expresses inequality

     INCLUDE :

    • Column is not used to evaluate a predicate, but is used for another reason, for example, to cover a query.

    for more information, refer the following links :

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 21, 2015 10:43 PM
  • User-725075801 posted

    Thanks again, so should "include" columns be included in the index, or just the others?

    Thursday, May 21, 2015 10:58 PM
  • User1711366110 posted

    so should "include" columns be included in the index, or just the others?

      this is additional columns that could be used when the index is created.  for example the query only uses City, StateProvinceID and PostalCode, the StateProvinceID will be handled in the index and the other two columns could be used as included columns when the index is created.. for more information, you can refer the given link from above reply.

    Friday, May 22, 2015 12:46 AM
  • User-725075801 posted

    What I was trying to figure out is if “include” columns were merely included in the select statement or not. From my reading I have concluded that they are in fact used in things like orderby or Aggregates so should be included in index. Correct.

    Thanks

    Friday, May 22, 2015 12:51 AM
  • User1711366110 posted

    What I was trying to figure out is if “include” columns were merely included in the select statement or not. From my reading I have concluded that they are in fact used in things like orderby or Aggregates so should be included in index

       For this case, you can have a glance from the following articles :
    http://sqlmag.com/t-sql/tuning-aggregations

    http://dba.stackexchange.com/questions/15295/why-is-an-aggregate-query-significantly-faster-with-a-group-by-clause-than-witho

    Thanks.

    Friday, June 5, 2015 4:43 AM