none
Recommended indexes RRS feed

  • Question

  • Hi all,

    I'm a bit confused with some of recommended indexes i see in SSMS query plans i.e. some of the recommendations explicitly INCLUDE the primary key. 

    Isn't the primary key always included?

    Thanks for your help.

    Clay

    Monday, September 9, 2019 2:02 AM

Answers

  • if The Primary Key is the Clustered Key then it is already included implicitly and you do not need to include it. You can include it if you like and it will not make any difference.

    Until the day you for decide to change the clustered index table to something else! (So from this perspective, it can be considered best practice to explicitly include a column you know you need.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by clay123123123 Monday, September 9, 2019 10:16 PM
    Monday, September 9, 2019 10:05 PM

All replies

  • Are you talking about Missing index recommendation? I strongly recommend you not apply it blindly but test it and see how they impact  on all queries, and not just that specific one you are running right now, 

    Not all requests are helpful

    https://www.brentozar.com/archive/2017/02/crappy-missing-index-requests/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 9, 2019 5:12 AM
    Answerer
  • Hi all,

    I'm a bit confused with some of recommended indexes i see in SSMS query plans i.e. some of the recommendations explicitly INCLUDE the primary key. 

    Isn't the primary key always included?

    Thanks for your help.

    Clay

    Good day,

    Non-clustered indexes implicitly include the clustered index key automatically and use it as the lockup parameter to get the row. With that being said, the Primary key is not always the clustered key. For example, you can have cluster index on column X and Primary Key on column Y. In this case you might need to include the Primary Key column y in the Non-clustered index.

    By the way, if you include clustered index column in Non-clustered index definition it will be ignored by SQL Server, which mean it make no bad. You can confirm this by checking the internals of the data stored in the disk using the undocumented commands DBCC PAGE and DBCC IND

    Therefore, to address you direct question:

    >> Isn't the primary key always included?

    Not necessarily.
    Only if the Primary Key is the clustered index (Don't confuse Primary Key with Clustered Key)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Monday, September 9, 2019 5:18 AM
    Moderator
  • Hi clay123123123

    Following query return more impacted missing indexes 

    ;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT p.usecounts, p.refcounts, p.objtype, p.cacheobjtype,    db_name(t.dbid) as database_name, t.text as query, q.query_plan FROM sys.dm_exec_cached_plans AS p    CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t    CROSS APPLY sys.dm_exec_query_plan(p.plan_handle) AS q WHERE q.query_plan.exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/ QueryPlan/MissingIndexes/MissingIndexGroup') <> 0 ORDER BY p.usecounts DESC 

    Source: Examref 70-764 

    Best Regards,

    Natig


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. 

    Monday, September 9, 2019 5:41 AM
  • Thanks Ronen, 

    The Primary Key is the Clustered Key - I meant to include this in my post

    Here's an example missing index suggestion (SQL2016) and the relevant part of the table and key definition.

    -- missing index suggestion
    
    
    CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
    ON [dbo].[F420_PICK_SLIP_HEADER] ([R420_PICK_SLIP_STATUS])
    INCLUDE ([R420_RECORD_ID],[R420_PICK_SLIP_NO],[R420_ORIGINAL_PICK_SLIP_NO])
    -- key def
    
     CONSTRAINT [pk_F420_PICK_SLIP_HEADER] PRIMARY KEY CLUSTERED 
    (
    	[R420_RECORD_ID] ASC
    )

    Note, this is not causing me any issue - just curious.

    Cheers, Clay

    Monday, September 9, 2019 6:03 AM
  • Thanks Uri, I'm not implementing them blindly.
    Monday, September 9, 2019 6:04 AM
  • Hi Clay,

    My answer is 'it's not always the thing'. The reason why we use indexes is that we want the query more efficient and in default, when we set 'primary key', the column has already be clustered index. But in fact, we can amend that according to our demands. Here are some tips about when we should use index/non-index just in terms of T-sql:  Design effective clustered index and Design effective non-clustered index

    Sabrina 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 9, 2019 6:30 AM
  • Hi,

    I think I covered this in my answer above :-)

    if The Primary Key is the Clustered Key then it is already included implicitly and you do not need to include it. You can include it if you like and it will not make any difference. This is not related to being a Primary Key but to fact that it is Clustered Key.

    You should consider these index recommendations as it is - just a recommendations. As Uri said, do not blindly try to implement it ). It is an auto tool which has internal rules and it does not cover all real cases.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Monday, September 9, 2019 9:27 AM
    Moderator
  • some time your indexes can't help you for optimization process too

    1) if dont will replication proces in your dwh 

     stop replication clustered columns 

    for example that

    CREATE TABLE [Coonfig].[Groups](
    [GroupID] [dbo].[id] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [GroupName] [dbo].[nvn] NOT NULL,
    [Description] [dbo].[nvn] NOT NULL,
     CONSTRAINT [PK_G] PRIMARY KEY CLUSTERED 
    (
    [GroupID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO 



    2)for giving succes select query create table valued function with table 

    server will scan your table from function 

    and cached plan size will be low around 35-40 %

     for example will be that select for id query

    ALTER  FUNCTION [dbo].[FN_GET_for_ID](@GRP_ID int)
    RETURNS @retContactInformation TABLE 
    (
    GROUP_NAME NVARCHAR(50),
    [DESCRIPTION] NVARCHAR(50)
    )
    AS 
    
    BEGIN
        DECLARE 
          @GROUP_ID int,
    @GROUP_NAME NVARCHAR(50),
    @DESCRIPTION NVARCHAR(50)
        SELECT 
             @GROUP_ID =[GroupID] ,
    @GROUP_NAME =[GroupName],
    	@DESCRIPTION =[Description]
    
        FROM [Groups]
        WHERE @GRP_ID = [GroupID];
    
         
    
        IF @GROUP_ID IS NOT NULL 
        BEGIN
            INSERT @retContactInformation
            SELECT   @GROUP_NAME ,
    @DESCRIPTION 
    
    		END;
    
        RETURN;
    END;

    and you can see cached plan size wth xml format from master  USE master;  
    USE master;  
    GO  
    SELECT * 
    FROM sys.dm_exec_cached_plans AS op 
    CROSS APPLY sys.dm_exec_query_plan(op.plan_handle);  
    GO






    Monday, September 9, 2019 10:58 AM
  • if The Primary Key is the Clustered Key then it is already included implicitly and you do not need to include it. You can include it if you like and it will not make any difference.

    Until the day you for decide to change the clustered index table to something else! (So from this perspective, it can be considered best practice to explicitly include a column you know you need.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by clay123123123 Monday, September 9, 2019 10:16 PM
    Monday, September 9, 2019 10:05 PM
  • Thanks Erland, I get it.
    Monday, September 9, 2019 10:16 PM
  • Until the day you for decide to change the clustered index table to something else! (So from this perspective, it can be considered best practice to explicitly include a column you know you need.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    This is good point, to think about future optional changes in the table structure 👍

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Tuesday, September 10, 2019 1:58 AM
    Moderator