Cursor Performance on Multi Proc Box
-
SQL 2000 Enterprise SP1
I have been asked to look at someone elses 8 processor box (NT 4 enterprise box 4GB memory) where a single user runs a stored procedure which loops round 3 million rows using a cursor. On this machine one processor is sitting at 95% and the other seven at around 0%.
I restored the database onto one of my boxes (Advanced Server SP2, 4 processor with 1Gb memory) and reran the query. Where it uses all 4 processors with the following loads (95-85%, 25%, 5-25%, 5-25%)
Any idea why the query only runs using one processor on the bigger NT4 box?
The main difference I can see are
NT4 vs Advanced Server
NT4 box uses fibers and boasted performace
NT4 box has a min query memory of 4096 not default
NT4 box has had its max worker threads increased to 512
Steven
-
Interesting problem. Gotta ask - why would you cursor through 3 million rows?
Andy
-
I didn't write it myself, but basically the data are logs and it is tidying up the data and inserting the required clean data into a more useable table.
Steven
-
3M rows!!! Yikes!!!!
, could probably rewrite and get much better performance.My suspicion (and that's all it is) is that the fibers are making a difference. Also, check the parallelism settings also between the two boxes. Someone may have messed with this.
Steve Jones
-
The advanced server box is set to default, whereas the NT box has been modified eg Fibers, min memory query, max worker threads etc.
I will look at the SP to see if it can be rewritten
Steven
, could probably rewrite and get much better performance.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply