March 15, 2012 at 10:25 am
Hi All,
All of a sudden, we are experiencing a lot of CXPACKET wait types. Digging into it, I found the problem is that there is a web service that calls an SP to upload a file.
wait_typewaiting_tasks_countwait_time_msmax_wait_time_mssignal_wait_time_ms
CXPACKET163349760 341275002 45058 27839926
I checked the query execution plan and it is the index scan is costing 91%.
Due to this there are lots of deadlocks happening on the server.
Any thoughts on how to mitigate this ?
I have attached the query plan.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 15, 2012 at 11:45 am
The problem looks to be the predicate for your scan is using CONVERT_IMPLICIT for your dealer_id and account_no.
I'm guessing this is being caused by the difference in collation between the table and the variable you're passing in.
Have a look at this code for an example why:-
CREATE TABLE md.TestCollation
(ID int IDENTITY(1,1) PRIMARY key
, SomeText varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AI)
INSERT INTO md.TestCollation
VALUES
('Some random text')
GO 10000
INSERT INTO md.TestCollation
VALUES
('Different Text')
CREATE INDEX idx_SomeText
ON md.TestCollation (SomeText)
SELECT * FROM md.TestCollation
WHERE SomeText = 'Different Text'
SELECT * FROM md.TestCollation
WHERE SomeText = 'Different Text' COLLATE Latin1_General_bin
If you check the query plans for the two select statements at the end, you'll see the second is uses a table scan and the CONVERT_IMPLICIT operator on both the search argument AND the column from the table.
Does the query simply not work if you don't include the collation statement?
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
March 15, 2012 at 12:22 pm
Matthew Darwin (3/15/2012)
The problem looks to be the predicate for your scan is using CONVERT_IMPLICIT for your dealer_id and account_no.I'm guessing this is being caused by the difference in collation between the table and the variable you're passing in.
Have a look at this code for an example why:-
CREATE TABLE md.TestCollation(ID int IDENTITY(1,1) PRIMARY key
, SomeText varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AI)
INSERT INTO md.TestCollation
VALUES
('Some random text')
GO 10000
INSERT INTO md.TestCollation
VALUES
('Different Text')
CREATE INDEX idx_SomeText
ON md.TestCollation (SomeText)
SELECT * FROM md.TestCollation
WHERE SomeText = 'Different Text'
SELECT * FROM md.TestCollation
WHERE SomeText = 'Different Text' COLLATE Latin1_General_bin
If you check the query plans for the two select statements at the end, you'll see the second is uses a table scan and the CONVERT_IMPLICIT operator on both the search argument AND the column from the table.
Does the query simply not work if you don't include the collation statement?
Thanks Mathew for your reply !
Yes we need to do a collation comparison as this is collation sensitive server Latin1_General_BIN. The data we are receiving is collation insensitive.
so if it is Plain and PLAIN, we only need PLAIN.
Before the query used to do compare using UPPER and it was the same problem and hence we decided to use COLLATE for comparison.
For this it uses a NC index scan costing 91%.
Is there a way to optimize/tune this ?
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 15, 2012 at 12:22 pm
Matthew Darwin (3/15/2012)
The problem looks to be the predicate for your scan is using CONVERT_IMPLICIT for your dealer_id and account_no.I'm guessing this is being caused by the difference in collation between the table and the variable you're passing in.
Have a look at this code for an example why:-
CREATE TABLE md.TestCollation(ID int IDENTITY(1,1) PRIMARY key
, SomeText varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AI)
INSERT INTO md.TestCollation
VALUES
('Some random text')
GO 10000
INSERT INTO md.TestCollation
VALUES
('Different Text')
CREATE INDEX idx_SomeText
ON md.TestCollation (SomeText)
SELECT * FROM md.TestCollation
WHERE SomeText = 'Different Text'
SELECT * FROM md.TestCollation
WHERE SomeText = 'Different Text' COLLATE Latin1_General_bin
If you check the query plans for the two select statements at the end, you'll see the second is uses a table scan and the CONVERT_IMPLICIT operator on both the search argument AND the column from the table.
Does the query simply not work if you don't include the collation statement?
Thanks Mathew for your reply !
Yes we need to do a collation comparison as this is collation sensitive server Latin1_General_BIN. The data we are receiving is collation insensitive.
so if it is Plain and PLAIN, we only need PLAIN.
Before the query used to do compare using UPPER and it was the same problem and hence we decided to use COLLATE for comparison.
For this it uses a NC index scan costing 91%.
Is there a way to optimize/tune this ?
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 15, 2012 at 12:34 pm
You could add a persisted computed column that consisted of SomeText (from Matthew's sample code) collated with the BIN collation you need, and then add a non-clustered index to SomeTextBin. The optimizer will recognize that the index can help and it will adjust to use the index with no code changes from the client application. Extending Matthew's sample code to show this:
DROP TABLE TestCollation
CREATE TABLE TestCollation
(
ID INT IDENTITY(1, 1)
PRIMARY KEY,
SomeText VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AI,
SomeTextBin AS SomeText COLLATE Latin1_General_bin PERSISTED
)
GO
INSERT INTO TestCollation
SELECT TOP 10000
'Some random text'
FROM master.sys.objects o1
CROSS JOIN master.sys.objects o2
CROSS JOIN master.sys.objects o3
GO
INSERT INTO TestCollation
VALUES ('Different Text')
GO
CREATE INDEX idx_SomeText
ON TestCollation (SomeText)
GO
CREATE INDEX idx_SomeText_BIN
ON TestCollation (SomeTextBin)
GO
SELECT *
FROM TestCollation
WHERE SomeText = 'Different Text'
SELECT *
FROM TestCollation
WHERE SomeText = 'Different Text' COLLATE Latin1_General_bin
You'll notice the last query is still referencing the SomeText column, however if you look at the execution plan you can see SQL Server smartly does an index seek on idx_SomeText_BIN for us, instead of scanning idx_SomeText.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 15, 2012 at 12:41 pm
Well as soon as you alter the collation, you're changing the sort order from the way in which the index is currently sorted, and so you will no longer be able to properly utilise your index.
Both columns being searched against are ID columns, do you definitely have a need for case sensitive IDs? And if you do, and the input is case insensitive, how can you be sure that you're looking up against the correct record?
If you can figure out how you would resolve that, then you should be able to manipulate the input parameter to match the case sensitivity of your table, and then you won't need to use any collation changes?
Or as mentioned above, if you're able to alter the table to add two extra columns with the required collation, then you should be on to a winner.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
March 15, 2012 at 1:10 pm
Matthew Darwin (3/15/2012)
Well as soon as you alter the collation, you're changing the sort order from the way in which the index is currently sorted, and so you will no longer be able to properly utilise your index.Both columns being searched against are ID columns, do you definitely have a need for case sensitive IDs? And if you do, and the input is case insensitive, how can you be sure that you're looking up against the correct record?
If you can figure out how you would resolve that, then you should be able to manipulate the input parameter to match the case sensitivity of your table, and then you won't need to use any collation changes?
Or as mentioned above, if you're able to alter the table to add two extra columns with the required collation, then you should be on to a winner.
The problem is that both ID columns are char columns char(6) and char(15) resp.
Also below are the indexes
index_descriptionindex_keys
nonclustered located on PRIMARYDEALER_ID, STATUS, LAST_UPDATE_DATE
clustered, unique, primary key located on PRIMARYDEALER_ID, ACCOUNT_NO
nonclustered located on PRIMARYDEALER_ID, ENTITY_ID
nonclustered located on PRIMARYENTITY_ID
nonclustered located on PRIMARYACCOUNT_NAME
dont understand why it is doing (from the exec plan) a convert of account_no (see predicate of Index Scan)
CONVERT_IMPLICIT(char(6),.[MASTERACCOUNT_E].[DEALER_ID],0)=CONVERT(char(6),[@dealer_id],0) AND CONVERT_IMPLICIT(char(15),[MASTERACCOUNT_E].[ACCOUNT_NO],0)=CONVERT(char(15),[@account_no],0)
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 15, 2012 at 1:10 pm
Matthew Darwin (3/15/2012)
Well as soon as you alter the collation, you're changing the sort order from the way in which the index is currently sorted, and so you will no longer be able to properly utilise your index.Both columns being searched against are ID columns, do you definitely have a need for case sensitive IDs? And if you do, and the input is case insensitive, how can you be sure that you're looking up against the correct record?
If you can figure out how you would resolve that, then you should be able to manipulate the input parameter to match the case sensitivity of your table, and then you won't need to use any collation changes?
Or as mentioned above, if you're able to alter the table to add two extra columns with the required collation, then you should be on to a winner.
The problem is that both ID columns are char columns char(6) and char(15) resp.
Also below are the indexes
index_descriptionindex_keys
nonclustered located on PRIMARYDEALER_ID, STATUS, LAST_UPDATE_DATE
clustered, unique, primary key located on PRIMARYDEALER_ID, ACCOUNT_NO
nonclustered located on PRIMARYDEALER_ID, ENTITY_ID
nonclustered located on PRIMARYENTITY_ID
nonclustered located on PRIMARYACCOUNT_NAME
dont understand why it is doing (from the exec plan) a convert of account_no (see predicate of Index Scan)
CONVERT_IMPLICIT(char(6),.[MASTERACCOUNT_E].[DEALER_ID],0)=CONVERT(char(6),[@dealer_id],0) AND CONVERT_IMPLICIT(char(15),[MASTERACCOUNT_E].[ACCOUNT_NO],0)=CONVERT(char(15),[@account_no],0)
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
March 15, 2012 at 1:17 pm
Could you post the DDL for the table, the indexes, and the procedure/code being executed please.
March 15, 2012 at 1:21 pm
The CONVERT_IMPLICIT is occurring because in your case sensitive index, "SOMETEXT" is not the same as "SomeText" or "sometext". So it has to convert the text, thus ruining your index. My question is, that if you have unique ids, do those IDs need to be case sensitive? Because your input is not case sensitive, it's suggesting to me that the ID columns don't need to be. In that case, so as not to have to rebuild with new collation you could force the case sensitivity or create the derived columns above with a non case sensitive collation. If you do the former, you can then also force your input parameters to match the data in your table, by doing an UPPER or whatever on the parameter. Or if they do need to be case sensitive, then you probably should be looking at making the input case sensitive in order to be sure you're then retrieving the desired record. Hope that makes sense.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply