February 5, 2007 at 5:24 am
you might want to check for cxpacket waits - suspect parallelism may be blocking. Try adding a maxdop 1 hint to your query.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 5, 2007 at 5:52 am
CXPACKET 135165 3.150802E+07 186078
Running the query with "OPTION (MAXDOP 1)". Currently no SUSPENDED SPIDs, but is still taking a lot longer than it does in SQL2000.
Lets see how that goes...
February 5, 2007 at 7:18 am
I can only generalise about maxdop settings as any given situation may vary!! On a 4 way dual core ( with no HT ) when I had parallelism issues with particular queries I found a maxdop = to the number of sockets performed best where maxdop 1 was slower. e.g. all procs 30 mins , maxdop 1 = 20 mins, maxdop 4 = 15 mins.
The whole process obviously depends upon the query, with HT and dual cores try sockets first, then cores, and then work downwards for best response. I don't suggest making this change on a server basis, only per query. Efficient indexing usually reduces parallelism and makes parallel queries faster.
You also need to find the bottleneck, can you batch this? check disk io completion time for both data and tlog drives and from the source data drive. As I don't know the disk subsystem I can only suggest possible timings, I'd probably not want to see a value for data/mdf/tempdb over 10 ms and ideally zero for t log, io waits on a t log write are very bad.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 5, 2007 at 7:36 am
Ok, well timing for this is not that big an issue really. The view is rather complex and a I have said they will be removed shortly.
The thing is that on a desktop (using a restore of the live database from last night) the SELECT will happily finish in 2:30, which I am perfectly happy with. However on the 2005 server, using the same restore, compatiability level set to 90, statistics and index jobs all run successfully, the same query keeps running.
G
February 5, 2007 at 7:44 am
Forgot, SQL Server is als set to SIMPLE recovery in both environments. On the server, the t logs, data files, and tempdb are all on separate drives. RAID1, RAID 5, RAID0, respectively.
February 5, 2007 at 9:31 am
OK, SP2 has not helped in the least...
Any suggestions?
February 6, 2007 at 1:39 am
try plotting the rate of data transfer into the destination tables, I assume the data is arriving?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 8, 2007 at 5:35 am
well, if I take out the insert, and just return top 10, takes a minute 30. if I return top 100 after 12 minutes its only at record 49.
what is different between 2000 and 2005 that would stop something like this?
at the end of the day, the 2000 machine is of a much lower spec than the 2005 machine
they are both a restore of the same backup file
Viewing 8 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply