March 26, 2012 at 9:04 pm
We have CTP set to 5 and MAX DOP set to 1 at server. I was working on a simple update , it was about an hour to complete with this setting. When i set CTP to 1 and MAX DOP to 0 the same query completed in 35 secs.
i) Is there a way i can identify which queries need to be set to mAX DOP 0 or any other value. I want to be little proactive here . I am not confident in changing the server level settings as there are other pieces of the application which benefits from this.
ii) I would also like to set up a appropriate CTP value.
Thanks.
March 26, 2012 at 9:28 pm
My first question is "Why are you even needing to specify DOP (or any other hint) ?"
Generally SQL Server will do a pretty good job without any hints. When I am writing code, I only specify hints as the very last choice. In fact, except for looking at how hints affect what I have coded, I can't remember the last time I specified a hint.
Most performance improvements can be achieved by having appropriate indexes available and by writing SQL code that is efficient.
As for DOP, the most likely reason for the change in performance with DOP=0 is because this allows SQL Server to use multiple processors to deal with your query. DOP=1 says you can only use one. If the query would benefit from being executed across multiple processors, DOP=0 will allow SQL to do this. There is a cost associated with running parts of the query in parallel. You trade this off against the elapsed time to run the query.
Unless you are trying to extract every last bit of performance from your server, I would not use DOP.
March 27, 2012 at 2:24 am
A cost threshold for parallelism of 1 would make just about every query parallel, which is a bad idea. Most people who have opinions on that matter recommend a figure somewhere around 25-35 or higher. You want expensive queries to parallel, not cheap ones.
Leave MAXDOP at 0 unless you've got good reasons to do otherwise.
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
March 27, 2012 at 7:57 am
happycat59 (3/26/2012)
My first question is "Why are you even needing to specify DOP (or any other hint) ?"Generally SQL Server will do a pretty good job without any hints. When I am writing code, I only specify hints as the very last choice. In fact, except for looking at how hints affect what I have coded, I can't remember the last time I specified a hint.
Most performance improvements can be achieved by having appropriate indexes available and by writing SQL code that is efficient.
As for DOP, the most likely reason for the change in performance with DOP=0 is because this allows SQL Server to use multiple processors to deal with your query. DOP=1 says you can only use one. If the query would benefit from being executed across multiple processors, DOP=0 will allow SQL to do this. There is a cost associated with running parts of the query in parallel. You trade this off against the elapsed time to run the query.
Unless you are trying to extract every last bit of performance from your server, I would not use DOP.
Thanks. Surprisingly the same query worked perfectly fine few weeks back. I guess because of the quality of data the execution plan has changed.
I have pasted the query below. Actually the select statement returns zero records, so basically it is updating zero records .
UPDATE mytable
SET id = 1
WHERE Tid = 224
AND rkey IN (
SELECT DISTINCT A.RId
FROM
(
SELECT cac.RId,p.Note
FROM Cde cac
INNER JOIN Logs P
ON cac.rid=p.Rkey
AND cac.CIC = SUBSTRING(P.Nte,CHARINDEX('C:',P.Nte)+ 4 , 8)
WHERE p.PId=40 AND cac.NUnits IS NULL AND cac.status=3
AND p.Nte LIKE 'Con; Add%'
) A
)
March 27, 2012 at 8:05 am
Can't say much from the query alone. Execution plan please (actual, not estimated), table designs and index designs.
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
March 27, 2012 at 8:46 am
GilaMonster (3/27/2012)
Can't say much from the query alone. Execution plan please (actual, not estimated), table designs and index designs.
I will get the execution plan by eod, mentioned below is the table structure. Our indices are not that great, we have tons of nc indices so i didnt paste here. If you can recommend me one that would be great.
Table Structure:
CREATE TABLE [dbo].[mytable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TID] [int] NOT NULL,
[RKey] [nvarchar](20) NOT NULL,
[Nte] [nvarchar](255) NULL,
[Wt] [int] NULL,
[RSID] [int] NULL,
[CreatedBy] [int] NULL,
[CreatedDate] [datetime] NULL,
[ClosedBy] [int] NULL,
[ClosedDate] [datetime] NULL,
CONSTRAINT [PK_RTE] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
UPDATE mytable
SET[RSID] = 1
WHERE Tid = 224
AND rkey IN (
SELECT DISTINCT A.RId
FROM
(
SELECT cac.RId,p.Note
FROM Cde cac
INNER JOIN Logs P
ON cac.rid=p.Rkey
AND cac.CIC = SUBSTRING(P.Nte,CHARINDEX('C:',P.Nte)+ 4 , 8)
WHERE p.PId=40 AND cac.NUnits IS NULL AND cac.status=3
AND p.Nte LIKE 'Con; Add%'
) A
)
March 28, 2012 at 11:35 am
Consider rewriting the query would be my first choice. 1 thing I notice is the inner most subquery outputs p.Note which is not used in the upper subquery and in the upper you distinct the data. The query engine may be doing this per row or building a temp table with the data in memory you have to check the execution plan. If however, doing per row, then as data increases the slower it will get. Try something like this instead.
UPDATE
mytable
SET
id = 1
FROM
mytable
INNER JOIN
Cde cac
INNER JOIN
Logs P
ON
cac.rid=p.Rkey
AND cac.CIC = SUBSTRING(P.Nte,CHARINDEX('C:',P.Nte)+ 4 , 8)
AND p.PId=40
AND cac.NUnits IS NULL
AND cac.status=3
AND p.Nte LIKE 'Con; Add%'
ON
mytable.rkey = cac.RId
WHERE
mytable.Tid = 224
--I notice you used distinct in your query so this might work or not and may help if does
--I just don't have a place to test it right now.
--if doesn't just drop this part or if it complains about cac.rid drop just that
--either way the above should really improve over the original
GROUP BY
mytable.id, cac.rid
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply