SQL Server Performance Forum – Threads Archive
Can’t find the performance bottleneck, any help?
Hi, I’m having some problems trying to find the bottleneck in this query. I have some clues but would like some help in validating my assumptions.Here is the query: UPDATETable1
SET Table1.Metric1 = Table2.Metric1 ,
Table1.SortOrder = Table2.Metric1 * @nScaleFactor
FROM Table1
INNER JOIN Table2 ON
Table1.PKID = Table2.PKID AND
Table1.ID2 = Table2.ID2 * Both of the tables have about 192 Million rows
* Both of the tables have clustered indices in the following order: (PKID, ID2) In enterprise manager I see 8 different processes (for this query). All of them are sleeping and one of them has waittype PAGEIOLATCH_SH the rest have CX_PACKET.
The processor usage on the machine barely registers above 1%.
However the disk queues are highly irregular. A decent amount of sustained activity. My guess is that this is one instance where parallelism is hurting my query. Any help/advice would be greatly appreciated. Thanks, Mitu PS SERVER SPECS:
Quad Xeon HT w/ 8GB of RAM
Three data partitions (2 R5 and one R0) and a different R1 system partition.
Data files and Logs are on different raid 5 partitions with separate raid controllers and the TempDB is on a the raid 0 partition.
SQL Server decides if it should run a Query in Parallism or as a stream. You can trace parallel execution plans using Profiler or SET SHOWPLAN_ALL. When Parallelism is hurting performance you can set a query hint to force the query not to run in parallel execution using a query hint i.e. OPTION (MAXDOP 1)
You might want to consider having a non-clustered index on PKID, ID2, Metric1 and SortOrder (for Table 1). This will make the query covered. I think what’s happening is that since almost all the table is needed in the query, the disk I/O and memory management is taking all the time. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Which version of SQL Server and what service pack has been applied? I remember that there is a fix related to parallelism and deadlock or locking. However, your issue may be related to bottleneck in I/O. Also, I kind of remember that Raid5 is slower in writes according to Inside SQL Server 2000 by Kalen Delaney. Also, you may want to check how often the transaction log is growing.
Is this a development enviroment? How wide are the columns Metric1 and SortOrder. Assuming you are doing this during a non/low activity window I would drop the indexes on Table1 use a Covered index on Table2, perform the insert then recreate the indexes.
Hi, Thank you for your replies. Lazy_DBA: Both Metric1 and SortOrder are ints. Please correct me if I’m wrong, since it is an update and both SortOrder and Metric1 don’t have any indices on them, no index rebuilds should be required, right? pcsql: The SQL Server is 2000 with SP3. You’re right, I think I read that R5 is slow for writes. However, since we can’t afford a R10 at this point our best option was to get a 14 drive array and divide it into two R5 partitions. gaurav_bindlish: Thanks, I’ll try that on my next run and report back. Thanks again, Mitu
Yes if you dont have an index on the updated column then no index rebuild will be issued. Have you concluded the cause of the bottleneck?
Well, I figured it out (I think). It turns out that for this query the optimizer selects merge inner join. Even though merge joins are very fast, in this instance they end up hurting the performance. Adding a join hint to change from merge to loop join dramatically reduces the update time. UPDATE Table1
SET Table1.Metric1 = Table2.Metric1 ,
Table1.SortOrder = Table2.Metric1 * @nScaleFactor
FROM Table1
INNER LOOP JOIN Table2 ON
Table1.PKID = Table2.PKID AND
Table1.ID2 = Table2.ID2 Indices:
Clustered on both PKID, ID2 This query used to run for about 4 hours and now it takes just under 20 mins.
Mitu, This is good news. I remember I have tried that inner loop hint (suggested by derrickleggett) in my case but it does not help. So, I wonder under what situation the inner loop hint should be used.
Peter
]]>