Performance Opinion

  • Hi all,

    I have created a SP in which frequentylr transaction will occur..

    Can you please look at it and tell me if its fine or need some changes in performance point of view...

    ALTER PROCEDURE [dbo].[usp_Job_Inbound_BuildingNote_Working]

    @InstanceID nchar(3),

    @SiteID nchar(3)

    AS

    BEGIN

    SET NOCOUNT ON;

    CREATE TABLE #TEMP

    (

    [InBound_BuildingNoteId] [int] NOT NULL,

    [InstanceId] [nchar](3) NOT NULL,

    [SiteId] [nchar](3) NOT NULL,

    [LocalBuildingNoteId] [nvarchar](20) NOT NULL,

    [LocalBuildingId] [nvarchar](20) NOT NULL,

    [LocalEmployeeId] [nvarchar](20) NULL,

    [Note] [nvarchar](500) NOT NULL,

    [NoteCategoryCode] [nchar](3) NOT NULL,

    [SequenceNumber] [int] NOT NULL,

    [NoteAddedDate] [datetime] NOT NULL,

    [NoteExpirationDate] [datetime] NULL,

    [StatusFlag] [bit] NOT NULL,

    [DateAdded] [datetime] NOT NULL,

    [AddedBy] [nvarchar](75) NOT NULL,

    DateChanged DATETIME NULL,

    ChangedBy nvarchar(75) null,

    [LocalDateChanged] [datetime] NOT NULL,

    [LocalChangedBy] [nvarchar](75) NOT NULL,

    [ProcessedFlag] [bit] NOT NULL,

    [ProcessedDate] [datetime] NULL,

    [ReturnCode] [nchar](6) NULL,

    [EmailSentFlag] [bit] NOT NULL

    )

    ----------------------------------------------------------------------------

    BEGIN TRY

    ---------------CHECK FOREIGN KEY CONSTRAINT---------------------------------

    Update IBC

    SET IBC.ProcessedFlag = 1,

    IBC.ReturnCode = '2001',

    IBC.EmailSentFlag = 0,

    IBC.ProcessedDate = GETDATE()

    FROM InBound_BuildingNote IBC WITH(NOLOCK)

    LEFT JOIN Building B WITH(NOLOCK) ON B.InstanceId = IBC.InstanceId

    AND B.SiteId = IBC.SiteId

    AND B.LocalBuildingId = IBC.LocalBuildingId

    WHERE B.LocalBuildingId IS NULL

    AND IBC.ProcessedFlag = 0

    AND IBC.InstanceId = @InstanceID

    AND IBC.SiteId = @SiteID

    Update IBC

    SET IBC.ProcessedFlag = 1,

    IBC.ReturnCode = '2001',

    IBC.EmailSentFlag = 0,

    IBC.ProcessedDate = GETDATE()

    FROM InBound_BuildingNote IBC WITH(NOLOCK)

    LEFT JOIN Employee B WITH(NOLOCK) ON B.InstanceId = IBC.InstanceId

    AND B.SiteId = IBC.SiteId

    AND B.LocalEmployeeId = IBC.LocalEmployeeId

    WHERE B.LocalEmployeeId IS NULL

    AND IBC.ProcessedFlag = 0

    AND IBC.InstanceId = @InstanceID

    AND IBC.SiteId = @SiteID

    Update IBC

    SET IBC.ProcessedFlag = 1,

    IBC.ReturnCode = '2001',

    IBC.EmailSentFlag = 0,

    IBC.ProcessedDate = GETDATE()

    FROM InBound_BuildingNote IBC

    LEFT JOIN NoteCategory B ON B.NoteCategoryCode = IBC.NoteCategoryCode

    AND B.InstanceId = IBC.InstanceId

    AND B.SiteId = IBC.SiteId

    WHERE B.NoteCategoryCode IS NULL

    AND IBC.ProcessedFlag = 0

    AND IBC.InstanceId = @InstanceID

    AND IBC.SiteId = @SiteID

    ------------------------------------------------------------------------------------------

    INSERT INTO #TEMP

    (

    [InBound_BuildingNoteId] ,

    [InstanceId] ,

    [SiteId] ,

    [LocalBuildingNoteId] ,

    [LocalBuildingId] ,

    [LocalEmployeeId] ,

    [Note] ,

    [NoteCategoryCode] ,

    [SequenceNumber] ,

    [NoteAddedDate] ,

    [NoteExpirationDate] ,

    [StatusFlag] ,

    [DateAdded] ,

    [AddedBy] ,

    DateChanged ,

    ChangedBy ,

    [LocalDateChanged] ,

    [LocalChangedBy] ,

    [ProcessedFlag] ,

    [ProcessedDate] ,

    [ReturnCode] ,

    [EmailSentFlag]

    )

    SELECT

    N.[InBound_BuildingNoteId] ,

    N.[InstanceId] ,

    N.[SiteId] ,

    N.[LocalBuildingNoteId] ,

    N.[LocalBuildingId] ,

    N.[LocalEmployeeId] ,

    N.[Note] ,

    N.[NoteCategoryCode] ,

    N.[SequenceNumber] ,

    N.[NoteAddedDate] ,

    N.[NoteExpirationDate] ,

    N.[StatusFlag] ,

    N.[DateAdded] ,

    N.[AddedBy] ,

    N.DateChanged ,

    N.ChangedBy ,

    N.[LocalDateChanged] ,

    N.[LocalChangedBy] ,

    N.[ProcessedFlag] ,

    N.[ProcessedDate] ,

    N.[ReturnCode] ,

    N.[EmailSentFlag]

    FROM InBound_BuildingNote N WITH(NOLOCK)

    WHERE N.ProcessedFlag = 0

    AND N.InstanceId = @InstanceID

    AND N.SiteId = @SiteID

    ------------------------------------------------------------------------------------------

    ;WITH CTE

    AS

    (

    SELECT

    N.[InBound_BuildingNoteId] ,

    N.[InstanceId] ,

    N.[SiteId] ,

    N.[LocalBuildingNoteId] ,

    B.BuildingId ,

    E.EmployeeId ,

    N.[Note] ,

    NC.NoteCategoryId ,

    N.[SequenceNumber] ,

    N.[NoteAddedDate] ,

    N.[NoteExpirationDate] ,

    N.[StatusFlag] ,

    N.[DateAdded] ,

    N.[AddedBy] ,

    N.DateChanged ,

    N.ChangedBy ,

    N.[LocalDateChanged] ,

    N.[LocalChangedBy] ,

    N.[ProcessedFlag] ,

    N.[ProcessedDate] ,

    N.[ReturnCode] ,

    N.[EmailSentFlag]

    FROM #TEMP N WITH(NOLOCK)

    INNER JOIN Building B WITH(NOLOCK) ON B.InstanceId = N.InstanceId

    AND B.SiteId = N.SiteId

    AND B.LocalBuildingId = N.LocalBuildingId

    INNER JOIN Employee E WITH(NOLOCK) ON E.InstanceId = N.InstanceId

    AND E.SiteId = N.SiteId

    AND E.LocalEmployeeId = N.LocalEmployeeId

    INNER JOIN NoteCategory NC WITH(NOLOCK) ON NC.InstanceId = N.InstanceId

    AND NC.SiteId = N.SiteId

    AND NC.NoteCategoryCode = N.NoteCategoryCode

    )

    MERGE BuildingNote_Working B

    USING CTE C ON B.InstanceID = C.InstanceID

    AND B.SiteID = C.SiteID

    AND B.LocalBuildingNoteID = C.LocalBuildingNoteID

    WHEN MATCHED THEN

    UPDATE

    SET

    B.BuildingId= C.BuildingId ,

    B.EmployeeId= C.EmployeeId ,

    B.[Note]= C.[Note] ,

    B.NoteCategoryId= C.NoteCategoryId ,

    B.[SequenceNumber]= C.[SequenceNumber] ,

    B.[NoteAddedDate]= C.[NoteAddedDate] ,

    B.[NoteExpirationDate]= C.[NoteExpirationDate] ,

    B.[StatusFlag]= C.[StatusFlag] ,

    B.[DateAdded]= C.[DateAdded] ,

    B.[AddedBy]= C.[AddedBy] ,

    B.DateChanged= GETDATE(),

    B.ChangedBy= C.ChangedBy ,

    B.[LocalDateChanged]= C.[LocalDateChanged] ,

    B.[LocalChangedBy]= C.[LocalChangedBy]

    WHEN NOT MATCHED THEN

    INSERT

    (

    [InstanceId] ,

    [SiteId] ,

    [LocalBuildingNoteId] ,

    BuildingId ,

    EmployeeId ,

    [Note] ,

    NoteCategoryId ,

    [SequenceNumber] ,

    [NoteAddedDate] ,

    [NoteExpirationDate] ,

    [StatusFlag] ,

    [DateAdded] ,

    [AddedBy] ,

    DateChanged ,

    ChangedBy ,

    [LocalDateChanged] ,

    [LocalChangedBy]

    )

    VALUES

    (

    C.[InstanceId] ,

    C.[SiteId] ,

    C.[LocalBuildingNoteId] ,

    C.BuildingId ,

    C.EmployeeId ,

    C.[Note] ,

    C.NoteCategoryId ,

    C.[SequenceNumber] ,

    C.[NoteAddedDate] ,

    C.[NoteExpirationDate] ,

    C.[StatusFlag] ,

    C.[DateAdded] ,

    C.[AddedBy] ,

    C.DateChanged ,

    C.ChangedBy ,

    C.[LocalDateChanged] ,

    C.[LocalChangedBy]

    );

    -----------------------------------------------------------------------------------

    --------------Changed Process Flag stauts to 1 -------------------------------

    UPDATE B

    SETProcessedFlag = 1,

    ProcessedDate = GETDATE()

    FROM InBound_BuildingNote B WITH (NOLOCK)

    INNER JOIN #TEMP T WITH (NOLOCK) ON B.LocalBuildingNoteId = T.LocalBuildingNoteId

    AND B.InBound_BuildingNoteId = T.InBound_BuildingNoteId

    AND B.InstanceId = T.InstanceId

    AND B.SiteId = T.SiteId

    END TRY

    BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000);

    SELECT @ErrorMessage = ERROR_MESSAGE()

    RAISERROR (@ErrorMessage, 16, 1);

    Exec [dbo].[usp_Insert_Error] 'StoreProcedure:[usp_Job_Inbound_BuildingnNote]',@@ERROR,@ErrorMessage,'','Stored Procedure';

    END CATCH

    ----------------------------------

    DROP TABLE #temp

    END

    Thanks a lot in advance...

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • More nolock abuse. Are the users aware and happy with the fact that their reports could return incorrect data?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (12/11/2013)


    More nolock abuse. Are the users aware and happy with the fact that their reports could return incorrect data?

    Ok, I will remove that nolock part from code.......................

    Other than that is the code fine?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • I didn't look at it in detail.

    Run it against a representative data volume, check the performance. If acceptable great. If not, identify which parts are slowest and ask for help with those.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I looked through it. I don't see anything egregious. It is doing a lot of work. But you don't have any really obvious issues. At least, none I spotted. I'd need to see the execution plans for the statements to identify if there were better indexes that might help performance.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 1) Maybe you can combine the three separate UPDATEs into one, as shown below, avoiding repeated joins of the same tables.

    2) Depending on the row counts, you might consider creating a filtered index on IBC.ProcessedFlag, including at least InstanceId and SiteId (if they are the clustering keys, of course you won't need to explicitly INCLUDE them, although it doesn't hurt anything to do so).

    3) Beyond just this query, if you most often access the listed tables by InstanceId and SiteId, then if possible cluster the tables that way. That should speed up all your processing.

    I don't know enough about the data distributions to know whether it should be ( InstanceId, SiteId ) or ( SiteId, InstanceId ), but you can determine that. If the third column is consistently specified in the WHERE, you could specify it in the clus key as well if you want; that is: ( key1, key2, LocalBuildingId | LocalEmployeeId | NoteCategoryCode ), although that lengthens the key so much that it must be weighed carefully.

    Update IBC

    SET IBC.ProcessedFlag = 1,

    IBC.ReturnCode = '2001',

    IBC.EmailSentFlag = 0,

    IBC.ProcessedDate = GETDATE()

    FROM InBound_BuildingNote IBC WITH(NOLOCK)

    LEFT JOIN Building B WITH(NOLOCK) ON B.InstanceId = IBC.InstanceId

    AND B.SiteId = IBC.SiteId

    AND B.LocalBuildingId = IBC.LocalBuildingId

    LEFT JOIN Employee E WITH(NOLOCK) ON E.InstanceId = IBC.InstanceId

    AND E.SiteId = IBC.SiteId

    AND E.LocalEmployeeId = IBC.LocalEmployeeId

    LEFT JOIN NoteCategory NC ON NC.NoteCategoryCode = IBC.NoteCategoryCode

    AND NC.InstanceId = IBC.InstanceId

    AND NC.SiteId = IBC.SiteId

    WHERE (B.LocalBuildingId IS NULL OR E.LocalEmployeeId IS NULL OR NC.NoteCategoryCode IS NULL)

    AND IBC.ProcessedFlag = 0

    AND IBC.InstanceId = @InstanceID

    AND IBC.SiteId = @SiteID

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • You should wrap your UPDATEs/INSERT/MERGE in a TRANSACTION and then do COMMIT/ROLLBACK consistent with your error handling.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (12/12/2013)


    You should wrap your UPDATEs/INSERT/MERGE in a TRANSACTION and then do COMMIT/ROLLBACK consistent with your error handling.

    Thanks Dwain, I will do that 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • ScottPletcher (12/12/2013)


    1) Maybe you can combine the three separate UPDATEs into one, as shown below, avoiding repeated joins of the same tables.

    2) Depending on the row counts, you might consider creating a filtered index on IBC.ProcessedFlag, including at least InstanceId and SiteId (if they are the clustering keys, of course you won't need to explicitly INCLUDE them, although it doesn't hurt anything to do so).

    3) Beyond just this query, if you most often access the listed tables by InstanceId and SiteId, then if possible cluster the tables that way. That should speed up all your processing.

    I don't know enough about the data distributions to know whether it should be ( InstanceId, SiteId ) or ( SiteId, InstanceId ), but you can determine that. If the third column is consistently specified in the WHERE, you could specify it in the clus key as well if you want; that is: ( key1, key2, LocalBuildingId | LocalEmployeeId | NoteCategoryCode ), although that lengthens the key so much that it must be weighed carefully.

    Update IBC

    SET IBC.ProcessedFlag = 1,

    IBC.ReturnCode = '2001',

    IBC.EmailSentFlag = 0,

    IBC.ProcessedDate = GETDATE()

    FROM InBound_BuildingNote IBC WITH(NOLOCK)

    LEFT JOIN Building B WITH(NOLOCK) ON B.InstanceId = IBC.InstanceId

    AND B.SiteId = IBC.SiteId

    AND B.LocalBuildingId = IBC.LocalBuildingId

    LEFT JOIN Employee E WITH(NOLOCK) ON E.InstanceId = IBC.InstanceId

    AND E.SiteId = IBC.SiteId

    AND E.LocalEmployeeId = IBC.LocalEmployeeId

    LEFT JOIN NoteCategory NC ON NC.NoteCategoryCode = IBC.NoteCategoryCode

    AND NC.InstanceId = IBC.InstanceId

    AND NC.SiteId = IBC.SiteId

    WHERE (B.LocalBuildingId IS NULL OR E.LocalEmployeeId IS NULL OR NC.NoteCategoryCode IS NULL)

    AND IBC.ProcessedFlag = 0

    AND IBC.InstanceId = @InstanceID

    AND IBC.SiteId = @SiteID

    Thanks Scott for your suggestion, I will combime multiple update statements into a single update statement..

    Below is my table script to review for index point of view if any changes is required:

    /****** Object: Table [dbo].[Employee] Script Date: 12/13/2013 12:05:44 PM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[Employee](

    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,

    [InstanceId] [nchar](3) NOT NULL,

    [SiteId] [nchar](3) NOT NULL,

    [LocalEmployeeId] [nvarchar](20) NOT NULL,

    [LocalEmployeeNumber] [nvarchar](20) NOT NULL,

    [OfficeId] [int] NOT NULL,

    [Prefix] [nvarchar](10) NULL,

    [FirstName] [nvarchar](50) NULL,

    [MiddleName] [nvarchar](50) NULL,

    [LastName] [nvarchar](50) NULL,

    [DisplayName] [nvarchar](125) NULL,

    [WorkPhoneNumber] [nvarchar](25) NULL,

    [CellPhoneNumber] [nvarchar](25) NULL,

    [EmailAddress] [nvarchar](75) NULL,

    [SupervisorId] [int] NULL,

    [PersonTypeId] [int] NULL,

    [DeviceID] [nvarchar](100) NULL,

    [LocalUnionID] [nvarchar](20) NOT NULL,

    [OT1RateMultiple] [decimal](2, 1) NOT NULL,

    [OT2RateMultiple] [decimal](2, 1) NULL,

    [OT3RateMultiple] [decimal](2, 1) NULL,

    [OldTravelRate] [decimal](6, 4) NULL,

    [CurrentTravelRate] [decimal](6, 4) NULL,

    [CurrentRateStartDate] [datetime] NULL,

    [TemporaryRateCode] [nchar](3) NULL,

    [StatusFlag] [bit] NOT NULL,

    [DateAdded] [datetime] NOT NULL,

    [AddedBy] [nvarchar](75) NOT NULL,

    [DateChanged] [datetime] NULL,

    [ChangedBy] [nvarchar](75) NULL,

    [LocalDateChanged] [datetime] NOT NULL,

    [LocalChangedBy] [nvarchar](75) NOT NULL,

    [SyncTimeStamp] [timestamp] NOT NULL,

    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED

    (

    [EmployeeId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],

    CONSTRAINT [uq1_Employee] UNIQUE NONCLUSTERED

    (

    [InstanceId] ASC,

    [SiteId] ASC,

    [LocalEmployeeId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_EmployeeOfficeId] FOREIGN KEY([OfficeId])

    REFERENCES [dbo].[Office] ([OfficeId])

    GO

    ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_EmployeeOfficeId]

    GO

    ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_EmployeeSupervisorId] FOREIGN KEY([SupervisorId])

    REFERENCES [dbo].[Supervisor] ([SupervisorId])

    GO

    ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_EmployeeSupervisorId]

    GO

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • if there is large data set in the #Temp table, all you find in the query execution plan is "tableScan" for #Temp table, If you need to increase the performance then you need to index the temp table as well.

  • I suggest making index changes scripted below, following these steps:

    1) Capture and save the existing index missing/usage stats for that table immediately, before any hanges are made.

    2) Run the code to make index changes; the code may run a while, depending on the table size.

    3) Allow some time for table activity, then re-capture the index missing/usage stats and compare to original.

    DECLARE @list_missing_indexes bit

    DECLARE @table_name_pattern sysname

    SET @list_missing_indexes = 1

    SET @table_name_pattern = 'Employee'

    PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

    -- list missing index info

    IF @list_missing_indexes = 1

    BEGIN

    SELECT

    GETDATE() AS capture_date,

    DB_NAME(mid.database_id) AS Db_Name,

    OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,

    mid.equality_columns, mid.inequality_columns, mid.included_columns,

    migs.*,

    mid.statement, mid.object_id, mid.index_handle

    FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)

    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON

    mig.index_handle = mid.index_handle

    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON

    migs.group_handle = mig.index_group_handle

    --order by

    --DB_NAME, Table_Name, equality_columns

    WHERE

    1 = 1

    AND mid.database_id = DB_ID()

    AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern

    ORDER BY

    --avg_total_user_cost * (user_seeks + user_scans) DESC,

    Db_Name, Table_Name, equality_columns, inequality_columns

    END --IF

    -- list index usage stats (seeks, scans, etc.)

    SELECT

    ius2.row_num, DB_NAME() AS db_name, OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name, i.name AS index_name,

    FILEGROUP_NAME(i.data_space_id) AS filegroup_name,

    (SELECT DATEDIFF(DAY, create_date, GETDATE()) FROM sys.databases WHERE name = 'tempdb') AS sql_up_days,

    dps.row_count,

    fk.Reference_Count AS fk_ref_count,

    ius.index_id, --ius.user_seeks + ius.user_scans AS total_reads,

    ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,

    ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update,

    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,

    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update

    FROM sys.indexes i WITH (NOLOCK)

    INNER JOIN sys.objects o WITH (NOLOCK) ON

    o.object_id = i.object_id

    LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON

    dps.object_id = i.object_id AND

    dps.index_id = i.index_id

    LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON

    ius.database_id = DB_ID() AND

    ius.object_id = i.object_id AND

    ius.index_id = i.index_id

    LEFT OUTER JOIN (

    SELECT

    database_id, object_id, MAX(user_scans) AS user_scans,

    ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans

    FROM sys.dm_db_index_usage_stats WITH (NOLOCK)

    WHERE

    database_id = DB_ID()

    --AND index_id > 0

    GROUP BY

    database_id, object_id

    ) AS ius2 ON

    ius2.database_id = DB_ID() AND

    ius2.object_id = i.object_id

    LEFT OUTER JOIN (

    SELECT

    referenced_object_id, COUNT(*) AS Reference_Count

    FROM sys.foreign_keys

    WHERE

    is_disabled = 0

    GROUP BY

    referenced_object_id

    ) AS fk ON

    fk.referenced_object_id = i.object_id

    WHERE

    i.object_id > 100 AND

    i.is_hypothetical = 0 AND

    i.type IN (0, 1, 2) AND

    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND

    (

    o.name LIKE @table_name_pattern

    )

    ORDER BY

    --row_count DESC,

    --ius2.row_num, --user_scans&|user_seeks

    -- list clustered index first, if any, then other index(es)

    db_name, table_name, CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END, index_name

    PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

    ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [uq1_Employee];

    ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [PK_Employee];

    ALTER TABLE [dbo].[Employee] ADD

    CONSTRAINT [uq1_Employee] UNIQUE CLUSTERED

    (

    [InstanceId] ASC,

    [SiteId] ASC,

    [LocalEmployeeId] ASC

    )WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]

    ALTER TABLE [dbo].[Employee] ADD

    CONSTRAINT [PK_Employee] PRIMARY KEY NONCLUSTERED

    (

    [EmployeeId] ASC

    )WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (12/13/2013)


    I suggest making index changes scripted below, following these steps:

    1) Capture and save the existing index missing/usage stats for that table immediately, before any hanges are made.

    2) Run the code to make index changes; the code may run a while, depending on the table size.

    3) Allow some time for table activity, then re-capture the index missing/usage stats and compare to original.

    DECLARE @list_missing_indexes bit

    DECLARE @table_name_pattern sysname

    SET @list_missing_indexes = 1

    SET @table_name_pattern = 'Employee'

    PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

    -- list missing index info

    IF @list_missing_indexes = 1

    BEGIN

    SELECT

    GETDATE() AS capture_date,

    DB_NAME(mid.database_id) AS Db_Name,

    OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,

    mid.equality_columns, mid.inequality_columns, mid.included_columns,

    migs.*,

    mid.statement, mid.object_id, mid.index_handle

    FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)

    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON

    mig.index_handle = mid.index_handle

    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON

    migs.group_handle = mig.index_group_handle

    --order by

    --DB_NAME, Table_Name, equality_columns

    WHERE

    1 = 1

    AND mid.database_id = DB_ID()

    AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern

    ORDER BY

    --avg_total_user_cost * (user_seeks + user_scans) DESC,

    Db_Name, Table_Name, equality_columns, inequality_columns

    END --IF

    -- list index usage stats (seeks, scans, etc.)

    SELECT

    ius2.row_num, DB_NAME() AS db_name, OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name, i.name AS index_name,

    FILEGROUP_NAME(i.data_space_id) AS filegroup_name,

    (SELECT DATEDIFF(DAY, create_date, GETDATE()) FROM sys.databases WHERE name = 'tempdb') AS sql_up_days,

    dps.row_count,

    fk.Reference_Count AS fk_ref_count,

    ius.index_id, --ius.user_seeks + ius.user_scans AS total_reads,

    ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,

    ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update,

    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,

    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update

    FROM sys.indexes i WITH (NOLOCK)

    INNER JOIN sys.objects o WITH (NOLOCK) ON

    o.object_id = i.object_id

    LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON

    dps.object_id = i.object_id AND

    dps.index_id = i.index_id

    LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON

    ius.database_id = DB_ID() AND

    ius.object_id = i.object_id AND

    ius.index_id = i.index_id

    LEFT OUTER JOIN (

    SELECT

    database_id, object_id, MAX(user_scans) AS user_scans,

    ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans

    FROM sys.dm_db_index_usage_stats WITH (NOLOCK)

    WHERE

    database_id = DB_ID()

    --AND index_id > 0

    GROUP BY

    database_id, object_id

    ) AS ius2 ON

    ius2.database_id = DB_ID() AND

    ius2.object_id = i.object_id

    LEFT OUTER JOIN (

    SELECT

    referenced_object_id, COUNT(*) AS Reference_Count

    FROM sys.foreign_keys

    WHERE

    is_disabled = 0

    GROUP BY

    referenced_object_id

    ) AS fk ON

    fk.referenced_object_id = i.object_id

    WHERE

    i.object_id > 100 AND

    i.is_hypothetical = 0 AND

    i.type IN (0, 1, 2) AND

    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND

    (

    o.name LIKE @table_name_pattern

    )

    ORDER BY

    --row_count DESC,

    --ius2.row_num, --user_scans&|user_seeks

    -- list clustered index first, if any, then other index(es)

    db_name, table_name, CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END, index_name

    PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

    ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [uq1_Employee];

    ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [PK_Employee];

    ALTER TABLE [dbo].[Employee] ADD

    CONSTRAINT [uq1_Employee] UNIQUE CLUSTERED

    (

    [InstanceId] ASC,

    [SiteId] ASC,

    [LocalEmployeeId] ASC

    )WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]

    ALTER TABLE [dbo].[Employee] ADD

    CONSTRAINT [PK_Employee] PRIMARY KEY NONCLUSTERED

    (

    [EmployeeId] ASC

    )WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]

    Hey Scott,

    Can you please explain me what this script will do ?

    DO I need to drop indexes for whole database tables and need to run this script for every tables and recreate the indexes?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk (12/14/2013)


    ScottPletcher (12/13/2013)


    I suggest making index changes scripted below, following these steps:

    1) Capture and save the existing index missing/usage stats for that table immediately, before any hanges are made.

    2) Run the code to make index changes; the code may run a while, depending on the table size.

    3) Allow some time for table activity, then re-capture the index missing/usage stats and compare to original.

    DECLARE @list_missing_indexes bit

    DECLARE @table_name_pattern sysname

    SET @list_missing_indexes = 1

    SET @table_name_pattern = 'Employee'

    PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

    -- list missing index info

    IF @list_missing_indexes = 1

    BEGIN

    SELECT

    GETDATE() AS capture_date,

    DB_NAME(mid.database_id) AS Db_Name,

    OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,

    mid.equality_columns, mid.inequality_columns, mid.included_columns,

    migs.*,

    mid.statement, mid.object_id, mid.index_handle

    FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)

    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON

    mig.index_handle = mid.index_handle

    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON

    migs.group_handle = mig.index_group_handle

    --order by

    --DB_NAME, Table_Name, equality_columns

    WHERE

    1 = 1

    AND mid.database_id = DB_ID()

    AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern

    ORDER BY

    --avg_total_user_cost * (user_seeks + user_scans) DESC,

    Db_Name, Table_Name, equality_columns, inequality_columns

    END --IF

    -- list index usage stats (seeks, scans, etc.)

    SELECT

    ius2.row_num, DB_NAME() AS db_name, OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name, i.name AS index_name,

    FILEGROUP_NAME(i.data_space_id) AS filegroup_name,

    (SELECT DATEDIFF(DAY, create_date, GETDATE()) FROM sys.databases WHERE name = 'tempdb') AS sql_up_days,

    dps.row_count,

    fk.Reference_Count AS fk_ref_count,

    ius.index_id, --ius.user_seeks + ius.user_scans AS total_reads,

    ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,

    ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update,

    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,

    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update

    FROM sys.indexes i WITH (NOLOCK)

    INNER JOIN sys.objects o WITH (NOLOCK) ON

    o.object_id = i.object_id

    LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON

    dps.object_id = i.object_id AND

    dps.index_id = i.index_id

    LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON

    ius.database_id = DB_ID() AND

    ius.object_id = i.object_id AND

    ius.index_id = i.index_id

    LEFT OUTER JOIN (

    SELECT

    database_id, object_id, MAX(user_scans) AS user_scans,

    ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans

    FROM sys.dm_db_index_usage_stats WITH (NOLOCK)

    WHERE

    database_id = DB_ID()

    --AND index_id > 0

    GROUP BY

    database_id, object_id

    ) AS ius2 ON

    ius2.database_id = DB_ID() AND

    ius2.object_id = i.object_id

    LEFT OUTER JOIN (

    SELECT

    referenced_object_id, COUNT(*) AS Reference_Count

    FROM sys.foreign_keys

    WHERE

    is_disabled = 0

    GROUP BY

    referenced_object_id

    ) AS fk ON

    fk.referenced_object_id = i.object_id

    WHERE

    i.object_id > 100 AND

    i.is_hypothetical = 0 AND

    i.type IN (0, 1, 2) AND

    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND

    (

    o.name LIKE @table_name_pattern

    )

    ORDER BY

    --row_count DESC,

    --ius2.row_num, --user_scans&|user_seeks

    -- list clustered index first, if any, then other index(es)

    db_name, table_name, CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END, index_name

    PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

    ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [uq1_Employee];

    ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [PK_Employee];

    ALTER TABLE [dbo].[Employee] ADD

    CONSTRAINT [uq1_Employee] UNIQUE CLUSTERED

    (

    [InstanceId] ASC,

    [SiteId] ASC,

    [LocalEmployeeId] ASC

    )WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]

    ALTER TABLE [dbo].[Employee] ADD

    CONSTRAINT [PK_Employee] PRIMARY KEY NONCLUSTERED

    (

    [EmployeeId] ASC

    )WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]

    Hey Scott,

    Can you please explain me what this script will do ?

    DO I need to drop indexes for whole database tables and need to run this script for every tables and recreate the indexes?

    Those scripts are for the "Employee" table, the one you posted the table definition for.

    A) Run script #1 first and save the results.

    B) Then run script #2.

    C) Then, after a few days, run script #1 again and save the results.

    Then we can compare A to C and see if things have improved.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • twin.devil (12/13/2013)


    if there is large data set in the #Temp table, all you find in the query execution plan is "tableScan" for #Temp table, If you need to increase the performance then you need to index the temp table as well.

    Maybe not. It the only things in the Temp Table are things that are actually needed, the scan of a heap can frequently be faster than adding and using an index. In the name of "It Depends", it is worth a test, though.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • @kapil_kk,

    I don't see any obvious severe problems in the code. I agree with Gail, though. One test is worth a thousand expert opinions. Run the code and if performance and resource usage is satisfactory for as much as as it is going to be used, then leave it alone.

    On the outside chance that you need some improvement, you would probably get some additional performance if you converted the LEFT OUTER JOIN/WHERE IS NULL things to WHERE NOT EXISTS. As already mentioned, consolidation of some of the updates

    Rumor also has it (I've not personally tested it) that traditional "upserts" are faster than MERGE in SQL Server. I can't put my finger on the articles that did the performance comparisons but I'm sure that brother Google could help.

    Instead of just getting rid of the WITH(NOLOCK) on the temp table, change it to a WITH(TABLOCKX) for a possible small improvement. This will prevent smaller locks form going through incremental lock escalation. No one else can get to the table, anyway.

    It's also great to see nicely formatted code such as that as you've written not to mention meeting some of my favorite best practices.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply