February 5, 2007 at 5:09 am
Good day,
Busy trying to test SQL2005, and having a couple of issues. This is my biggest problem currently:
Basically we copy all info from a view into a table over night so that we can report on a table rather than the view. This is only a temporary solution, until all the data has been moved into our data warehouse.
This works perfectly well on SQL2000 SP4 (running on a desktop), but on SQL2005 (running on a HP ML520 G2 server) this runs endlessly. sp_who2 shows a load of SUSPENDED SPIDs, which I have not seen before.
EDIT - While this query is running, the processor usage hits 95 - 100%.
I have read that SP2 is a potential fix, but also read that it does not actually fix it. I am busy downloading this, and will install and test, but I am not confident in this solution.
Does anyone have any other ideas?
Thanks in advance,
Graham
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply