| Author |
Topic |
|
cragi
Starting Member
14 Posts |
 Posted - 2008-01-15 : 20:56:45
|
| I have a dedicated SQL Server 2005 Workgroup server with Rackspace (RAID 1, which obviously needs to get changed). It's been running relatively smoothly and the last time I traced it (a couple of weeks ago), there were no significant issues. I run a reindex and I reboot every single night, and have built many indices on the tables using Analysis Services.We move a ton of data in a single day, and the users were complaining of lag, which appeared related to this server. After running a trace, I noticed:- my normal SPs were taking roughly 5-20x the normal duration and many were timing out- I was frequently running SPs directly on the server that gave me transaction deadlocked on resource errors- some of the tiny, quick calls made in the trace were showing as impossible, astronomical durations! Like things that should be "3"ms were showing up as "81544123941234123478090"msIs it corrupted?HELP! |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-15 : 23:37:45
|
| Did you check memory usage, cpu usage and i/o? Checked sp's execution plan? Why reboot server everyday? |
 |
|
|
cragi
Starting Member
14 Posts |
Posted - 2008-01-16 : 01:51:24
|
| yes, i have 2 GB memory and it seemed maxed out in several cases, though I thought SQL Server will automatically grab that memory if available and deallocate as necessary. The paging was getting high however. The CPU has spikes, but overall doesnt seem too bad.But can this cause such an unusual number to appear for the duration. Like if you were to update a bit field on a specific record given the ID, is it possible even if memory is hit hard that the duration has basically an 'infinite' number?No reason in particular on the reboots. We just do it on all the servers.I just tried to rebuild the indexes using the maintenance wizard (this is something we are automatically doing weekly and it wasnt having problems). It failed, just saying the 'package execution failed' |
|
|
|
cragi
Starting Member
14 Posts |
Posted - 2008-01-16 : 01:53:47
|
| again, at least one of the issues seems completely unrelated to the SP. If I'm executing a very simple update to a specific field of a table, it will randomly either take a duration of 0 or a duration of some huge unrealistic number (18510723471208347181237048971234)..for the same command |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-16 : 02:13:09
|
Which service pack are you using?This is a SQL Server 2000 forum and you wrote you are using SQL Server 2005.Latest Service Pack for SQL Server 2000 is SP4 (), and lastest Service Pack for SQL Server 2005 is SP2.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
cragi
Starting Member
14 Posts |
Posted - 2008-01-16 : 02:26:20
|
| I just posted there in response to your message here. I see that it's 2000 forum instead of 2005, so I got the wrong forum. The issue is related to 2005 and I checked the version, which is SP1 |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-01-16 : 05:57:39
|
| Get SP2 as there were a few problems with SP1.This will give you a list of Fixes:http://support.microsoft.com/?kbid=921896 |
|
|
|
cragi
Starting Member
14 Posts |
Posted - 2008-01-16 : 15:16:34
|
| ok, I've upgraded the memory and installed SP2 at the same time and the problem seems to have disappeared for now. Thanks! |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-16 : 23:47:55
|
| Don't reboot server everyday, that will clean up cache. Sql has to recreate execution plans and read data from disk next day, not good practice. |
|
|
|
|
Suddenly getting atronomical duration times