December 11, 2013 at 11:28 pm
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/
December 11, 2013 at 11:47 pm
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
December 11, 2013 at 11:56 pm
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/
December 11, 2013 at 11:59 pm
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
December 12, 2013 at 5:52 am
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
December 12, 2013 at 2:04 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 12, 2013 at 5:06 pm
You should wrap your UPDATEs/INSERT/MERGE in a TRANSACTION and then do COMMIT/ROLLBACK consistent with your error handling.
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
December 12, 2013 at 11:20 pm
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/
December 12, 2013 at 11:37 pm
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/
December 13, 2013 at 12:20 am
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.
December 13, 2013 at 10:35 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 14, 2013 at 12:48 am
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/
December 14, 2013 at 2:44 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 14, 2013 at 5:02 pm
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
Change is inevitable... Change for the better is not.
December 14, 2013 at 5:17 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply