June 22, 2006 at 3:58 am
Hi,
We have a job that runs over night that rebuilds 3 tables one of these has 16 million rows.
The thing is, this build typically took 1 hour to run..which is fine.
For the last few days I have see that this build is taking nearly 4 hours ??????
Nothing has change on the server and no extra processes are running. We have two other similar configured servers and these are running the same job with the same data and they still take 1 hour.
Does this point to a corrupted table/index or hardware ? Having run dbcc checktable I get no errors, the indexes are all fine and not fragmented.
My option tonight is to drop and rebuild the tables before they are re-populated..other than that I'm stumped.
Any thoughts on how I can interrogate this? Unfortunately these are live production servers so it is a bit difficult.
Thanks
Graeme
June 22, 2006 at 8:30 am
What error messages are seen?
1. check the SQL Server Error Logs
(in Enterprise Manager, expand until you see Management, expand that to see SQL Server Logs, expand that and click on the most recent log).
2. check the Windows Event Viewer Logs
(right click on My Computer, select Manage, expand Event Viewer. Check Application and System logs).
3. You are doing this as a job, so there should be clues in the Job History.
(in Enterprise Manager, again expand into Management. Expand SQL Server Agent and click on Jobs. Find the job, right click on it and select View Job History. Check the box for Show Job Steps. Look at the messages, examine the times to see where it took longer than it should have.)
-SQLBill
June 22, 2006 at 9:03 am
Hey,
That's the frustrating thing...there are no error messages being generated ???? None in the SQL error log or the WIndows error log
Job history shows the steps completing but one step is now taking over 3.5 hours instead of about 1 hr.
The server does seem to get really hammered (high cpu) during the build process but there is no failure.
We are running SP3. I've read that there can be issues with frequent DML statements to large tables and that SP4 would fix that...but then again we have two other servers doing the same thing and they are running fine.
This is very odd.
Thanks for your input
Graeme
June 22, 2006 at 9:15 am
A drastic change in execution time, but with the correct result still being generated does not indicate an error situation. It is more likely that a query execution plan has changed in response to changes to some of the underlying tables and/or statistics on their data distribution.
Are statistics kept up to date on the tables referenced by the long running query ?
Have you verified that no-one has added/dropped/modified an index on any of the tables ?
Do all the tables have a clustered index to prevent them becoming large fragmented heaps ?
June 22, 2006 at 1:06 pm
Okay, now you've narrowed it down to one specific step. Next steps......
1. what does that step do?
a. does it do anything that could cause the slow down?
2. when is that step running?
a. are there any other SQL Server scripts running at that time? (Reindexing and other things can take up resources and block a script from running in a timely manner)
b. is there any other activity going on at that time? Network maintenance? Backups? Antivirus checks running?
-SQLBill
June 22, 2006 at 1:07 pm
run sql-profiler during your rebuild.
Maybe it shows conflicts or ongoing queries of applications (wich you didn't expect to take that long)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 23, 2006 at 1:10 am
Morning...
Well, I tried rebuilding the tables before the population (this runs at 2:00 am).
No joy...the step still took 4 hours. I guess the next step is execution plans.
I actually managed to get up at 4 this morning to see what was happening...
No other processes are running at that time, but there were a lot of locks...57000 at one point and these were mostly Key locks.
All that happens in this step is that 3 tables are truncated and then repopulated with a series of inserts and updates. Like I said, on the two other servers this job has no problem.
Thanks for your input guys
Graeme
June 23, 2006 at 1:15 am
If you're going to populate the tables , do you use table hints (tablock) ?
This may avoid all the rowlevel locking overhead you're suffering.
Offcourse, you need to be the only active spid for those table at that time, because you'll take a exclusive lock on the whole table !
btw wich version of sqlserver is this db hosted on ? (+sp)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 23, 2006 at 6:59 am
This sounds very similar to issues observed on one of our production servers. After several weeks of logically sequenced troubleshooting to no avail with red faced managers, Intuition screamed at me saying that it must be a hosed-up process outside of SQL Server.
The voice of Logic quietly retorted that this is a dedicated server with restricted access and no other processes run on it, then drove home the point by showing the Task Manager having only normal processes displayed.
Intuition yelled back that some of the TSQL scripts execute DTS as well as SMTP notification, each from home grown code. The Task Manager may show a normal display, but these external processes used by TQSQL could leave orphaned objects considering their management is done in a black box. This would use processor time (not always obvious in Task manager) and drag down the system.
Intuition suggested a cold boot on the server, and Logic agreed only if it was done when none of the SQL Agents were running any processes against this server from any other server. I, the mediator, located such a time and restarted the machine.
All SQL processes returned to normal processing time. Intuition was ecstatic, Logic smiled, and I shook my head with great relief.
...Mel
(When shooting in the dark, it's best to use a shotgun)
June 24, 2006 at 12:13 am
My friend Whiskey Brian slept with a shotgun under his pillow. He shot a bear in his cabin in Alaska, in the dark, twice (or more), with his shotgun. His gun was loaded with the first round buckshot, followed by slugs. He told me that when they skinned the bear, none of the buckshot pellets had gone through the four inches of fat on the bear's back.
(When shooting a bear in the dark with a shotgun, it's best to use slugs)
June 26, 2006 at 1:04 am
Hello,
We are using tablocks.
Our system is SQL2000 SP3a.
Interesting point from Mel although I don't think that applies to us...a reboot might be worth a try.
Still looking at the TSQL now....and I'm still puzzled why other servers are running fine.
Thanks for your input.
Regards..Graeme
June 26, 2006 at 1:05 am
Hello,
We are using tablocks.
Our system is SQL2000 SP3a.
Interesting point from Mel although I don't think that applies to us...a reboot might be worth a try.
Still looking at the TSQL now....and I'm still puzzled why other servers are running fine.
Thanks for your input.
Regards..Graeme
June 26, 2006 at 1:08 am
Hello,
We are using tablocks.
Our system is SQL2000 SP3a.
Interesting point from Mel although I don't think that applies to us...a reboot might be worth a try.
Still looking at the TSQL now....and I'm still puzzled why other servers are running fine.
Thanks for your input.
Regards..Graeme
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply