Hi All,
We have a query that is parameterize sql and is called quite a number of times and app team tells it is slow.
Is there a way to improve the performance of this particular query? I have attached the query details, tbl structure , actual plan for hard code values, index info etc. From the plan, it is going for clustered index scan as they are selected all the data columns of the table. will there any help in creating additional non-clustered idx on the where clause columns? I didnt see any missing index recommendations coming from sql though from the actual execution plan.
Any inputs will be helpful. Thank you.
Regards,
Sam
February 17, 2023 at 11:13 am
Your query can be simplified to this:
SELECT TOP(11)
c.rowid_object,
c.creator,
c.create_date,
c.updated_by,
c.last_update_date,
c.consolidation_ind,
c.deleted_ind,
c.deleted_by,
c.deleted_date,
c.last_rowid_system,
c.dirty_ind,
c.interaction_id,
c.hub_state_ind,
c.party_id,
c.contact_role,
c.current_allocation,
c.cmid
FROM [dbo].[C_B_PARTY_RLE_COUNT_CUAM] c
WHERE c.cmid = '800000012780'
AND c.contact_role = 'Training Admin'
ORDER BY c.rowid_object ASC
;
For the best performance you should either create a clustered index on the following columns (this would mean changing the primary key to be non-clustered that might have performance implications elsewhere):
CREATE CLUSTERED INDEX IX_c_b_party_rle_count_cuam_1 ON [dbo].[C_B_PARTY_RLE_COUNT_CUAM]
(
cmid, contact_role, rowid_object ASC
)
ON [CMX_INDX]
;
Or create a non-clustered index:
CREATE NONCLUSTERED INDEX IX_c_b_party_rle_count_cuam_1 ON [dbo].[C_B_PARTY_RLE_COUNT_CUAM]
(
cmid, contact_role, rowid_object ASC
)
INCLUDE
(
rowid_object, creator, create_date, updated_by, last_update_date, consolidation_ind, deleted_ind, deleted_by, deleted_date,
last_rowid_system, dirty_ind, interaction_id, hub_state_ind, party_id, current_allocationcmid
)
ON [CMX_INDX]
;
Or even this non-clustered index that will use less space than the previous one, but will have do an index key lookup on the 11 rows, should still make a lot of difference:
CREATE NONCLUSTERED INDEX IX_c_b_party_rle_count_cuam_1 ON [dbo].[C_B_PARTY_RLE_COUNT_CUAM]
(
cmid, contact_role, rowid_object ASC
) ON [CMX_INDX]
;
February 17, 2023 at 4:15 pm
Your query can be simplified to this:
SELECT TOP(11)
c.rowid_object,
c.creator,
c.create_date,
c.updated_by,
c.last_update_date,
c.consolidation_ind,
c.deleted_ind,
c.deleted_by,
c.deleted_date,
c.last_rowid_system,
c.dirty_ind,
c.interaction_id,
c.hub_state_ind,
c.party_id,
c.contact_role,
c.current_allocation,
c.cmid
FROM [dbo].[C_B_PARTY_RLE_COUNT_CUAM] c
WHERE c.cmid = '800000012780'
AND c.contact_role = 'Training Admin'
ORDER BY c.rowid_object ASC
;For the best performance you should either create a clustered index on the following columns (this would mean changing the primary key to be non-clustered that might have performance implications elsewhere):
CREATE CLUSTERED INDEX IX_c_b_party_rle_count_cuam_1 ON [dbo].[C_B_PARTY_RLE_COUNT_CUAM]
(
cmid, contact_role, rowid_object ASC
)
ON [CMX_INDX]
;Or create a non-clustered index:
CREATE NONCLUSTERED INDEX IX_c_b_party_rle_count_cuam_1 ON [dbo].[C_B_PARTY_RLE_COUNT_CUAM]
(
cmid, contact_role, rowid_object ASC
)
INCLUDE
(
rowid_object, creator, create_date, updated_by, last_update_date, consolidation_ind, deleted_ind, deleted_by, deleted_date,
last_rowid_system, dirty_ind, interaction_id, hub_state_ind, party_id, current_allocationcmid
)
ON [CMX_INDX]
;Or even this non-clustered index that will use less space than the previous one, but will have do an index key lookup on the 11 rows, should still make a lot of difference:
CREATE NONCLUSTERED INDEX IX_c_b_party_rle_count_cuam_1 ON [dbo].[C_B_PARTY_RLE_COUNT_CUAM]
(
cmid, contact_role, rowid_object ASC
) ON [CMX_INDX]
;
Hi Jonathan,
Thanks for the inputs. I would think the last non-clustered idx is better to create. Also, I have a question on this, do we need to mention ROWID_OBJECT in the index key columns because ROWID_OBJECT is clustered idx and it should be part of all non-clustered idx. correct me if am wrong. Also, mentioned "but will have do an index key lookup on the 11 rows,", why 11 rows? how do we equate to 11?
Here, in my case, I feel that the Application table design is not correct. The rowid_object is nchar column instead of int or numeric with identity or ever increasing value? do you have to say anything on the datatype chosen for the table in terms fastness or rowsize etc..?
CREATE TABLE [dbo].[Table_xxxxxxx](
[ROWID_OBJECT] [nchar](14) NOT NULL,
[CREATOR] [nvarchar](50) NULL,
[CREATE_DATE] [datetime2](7) NULL,
[UPDATED_BY] [nvarchar](50) NULL,
[LAST_UPDATE_DATE] [datetime2](7) NULL,
[CONSOLIDATION_IND] [bigint] NOT NULL,
[DELETED_IND] [bigint] NULL,
[DELETED_BY] [nvarchar](50) NULL,
[DELETED_DATE] [datetime2](7) NULL,
[LAST_ROWID_SYSTEM] [nchar](14) NOT NULL,
[DIRTY_IND] [bigint] NULL,
[INTERACTION_ID] [bigint] NULL,
[HUB_STATE_IND] [bigint] NOT NULL,
[CM_DIRTY_IND] [bigint] NULL,
[PARTY_ID] [nchar](14) NULL,
[CONTACT_ROLE] [nvarchar](500) NULL,
[CURRENT_ALLOCATION] [bigint] NULL,
[CMID] [nchar](14) NULL,
CONSTRAINT [SVR1_18OKTB5] PRIMARY KEY CLUSTERED
(
[ROWID_OBJECT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
February 17, 2023 at 4:23 pm
Other thing is, SQL optimizer has not suggested any missing indexes in the actual execution plan.
We are on SQL Server 2017 EE.
[
Hi Jonathan,
Thanks for the inputs. I would think the last non-clustered idx is better to create. Also, I have a question on this, do we need to mention ROWID_OBJECT in the index key columns because ROWID_OBJECT is clustered idx and it should be part of all non-clustered idx. correct me if am wrong. Also, mentioned "but will have do an index key lookup on the 11 rows,", why 11 rows? how do we equate to 11?
Here, in my case, I feel that the Application table design is not correct. The rowid_object is nchar column instead of int or numeric with identity or ever increasing value? do you have to say anything on the datatype chosen for the table in terms fastness or rowsize etc..?
CREATE TABLE [dbo].[Table_xxxxxxx]( [ROWID_OBJECT] [nchar](14) NOT NULL, [CREATOR] [nvarchar](50) NULL, [CREATE_DATE] [datetime2](7) NULL, [UPDATED_BY] [nvarchar](50) NULL, [LAST_UPDATE_DATE] [datetime2](7) NULL, [CONSOLIDATION_IND] [bigint] NOT NULL, [DELETED_IND] [bigint] NULL, [DELETED_BY] [nvarchar](50) NULL, [DELETED_DATE] [datetime2](7) NULL, [LAST_ROWID_SYSTEM] [nchar](14) NOT NULL, [DIRTY_IND] [bigint] NULL, [INTERACTION_ID] [bigint] NULL, [HUB_STATE_IND] [bigint] NOT NULL, [CM_DIRTY_IND] [bigint] NULL, [PARTY_ID] [nchar](14) NULL, [CONTACT_ROLE] [nvarchar](500) NULL, [CURRENT_ALLOCATION] [bigint] NULL, [CMID] [nchar](14) NULL, CONSTRAINT [SVR1_18OKTB5] PRIMARY KEY CLUSTERED ( [ROWID_OBJECT] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) GO
re: " I would think the last non-clustered idx is better to create"
Yes, probably, I would try adding that first and see if it makes a difference. You might have to change the query to the select TOP(11) I put to get any benefit as I'm not sure if the ROW_NUMBER method would search all rows that satisfy the where criteria before it. I would also try the other non-clustered index to see if that was much better.
re: "Also, I have a question on this, do we need to mention ROWID_OBJECT in the index key columns because ROWID_OBJECT is clustered idx and it should be part of all non-clustered idx. correct me if am wrong."
I would add that column to the index. It is needed as the results are sorted by this, I don't think the value of the clustered key can be got from the address held on the index, it would take a key lookup to find the order of the row_object.
re: "Also, mentioned "but will have do an index key lookup on the 11 rows,", why 11 rows? how do we equate to 11?"
The 11 rows is from the original query that selects WHERE row_number BETWEEN 1 AND 11:
SELECT rowid_object,
creator,
create_date,
updated_by,
last_update_date,
consolidation_ind,
deleted_ind,
deleted_by,
deleted_date,
last_rowid_system,
dirty_ind,
interaction_id,
hub_state_ind,
party_id,
contact_role,
current_allocation,
cmid
FROM (SELECT *
FROM (SELECT Row_number()
OVER (
ORDER BY row_nbr ) AS ROW_NUMBER,
C_B_PARTY_RLE_COUNT_CUAM.*
FROM (SELECT Row_number()
OVER (
ORDER BY
c_b_party_rle_count_cuam.rowid_object ASC )
AS
ROW_NBR,
c_b_party_rle_count_cuam.rowid_object,
c_b_party_rle_count_cuam.creator,
c_b_party_rle_count_cuam.create_date,
c_b_party_rle_count_cuam.updated_by,
c_b_party_rle_count_cuam.last_update_date,
c_b_party_rle_count_cuam.consolidation_ind,
c_b_party_rle_count_cuam.deleted_ind,
c_b_party_rle_count_cuam.deleted_by,
c_b_party_rle_count_cuam.deleted_date,
c_b_party_rle_count_cuam.last_rowid_system,
c_b_party_rle_count_cuam.dirty_ind,
c_b_party_rle_count_cuam.interaction_id,
c_b_party_rle_count_cuam.hub_state_ind,
c_b_party_rle_count_cuam.party_id,
c_b_party_rle_count_cuam.contact_role,
c_b_party_rle_count_cuam.current_allocation,
c_b_party_rle_count_cuam.cmid
FROM c_b_party_rle_count_cuam
WHERE cmid = '800000012780'
AND contact_role = 'Training Admin')
C_B_PARTY_RLE_COUNT_CUAM)
T_OUTER
WHERE row_number BETWEEN 1 AND 11) T_OUTER_OUTER
re: "The rowid_object is nchar column instead of int or numeric with identity or ever increasing value? do you have to say anything on the datatype chosen for the table in terms fastness or rowsize etc..?"
I don't think changing it from nchar(14) to int would make a noticeable difference to performance.
February 17, 2023 at 5:36 pm
Other thing is, SQL optimizer has not suggested any missing indexes in the actual execution plan.
We are on SQL Server 2017 EE.
The optimizers suggesting for missing indexes are, at best, guesses. It frequently doesn't see the optimal index. It can also suggest indexes that are useless. It can even suggest indexes that hurt performance. Don't assume that because it didn't suggest an index, one isn't needed. Also, never assume that because it suggested an index, you need that index. Testing and evalutation are keys to using these things.
"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
February 17, 2023 at 6:59 pm
vsamantha35 wrote:Other thing is, SQL optimizer has not suggested any missing indexes in the actual execution plan.
We are on SQL Server 2017 EE.
The optimizers suggesting for missing indexes are, at best, guesses. It frequently doesn't see the optimal index. It can also suggest indexes that are useless. It can even suggest indexes that hurt performance. Don't assume that because it didn't suggest an index, one isn't needed. Also, never assume that because it suggested an index, you need that index. Testing and evalutation are keys to using these things.
+ 1 Million 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2023 at 7:18 pm
Nicely done, Jonathan!
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2023 at 7:30 pm
Thanks you very much Jonathan, Grant Sir and Jeff. Thank you.
February 17, 2023 at 9:44 pm
Thanks you very much Jonathan, Grant Sir and Jeff. Thank you.
You're welcome but I've done nothing on this thread except to like what a couple of others have stated or written code for. Jonathan did all the work on this thread and he's spot on.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply