March 3, 2009 at 7:51 am
If this is not the correct place for this - please let me know and/or move it to the correct forum. I'm at my wits end. We have several jobs that run stored procedures that hit linked servers (Oracle and Sybase). The jobs ran okay for a while, but in the last week or so - they hang every morning until we're forced to do a soft reboot of the server and then all is fine until it happens again. It's almost like the queries are not timing out and/or ditching the connections. ANY thoughts/help/suggestions/etc would be beneficial. Thanks!
March 3, 2009 at 8:30 am
One issue I've seen repeatedly with linked servers is running the query such that the filters are processed on the SQL SErver side. This requires that a SELECT * be run on the Oracle machine, all the data moved across, and then the filters get run. Instead, we've been running OPENQUERY against the Oracle servers, being very sure to pass in filter criteria. This makes the oracle server process the query, using it's indexes, etc. and it only returns the data we actually need on the SQL Server side. It makes a huge difference in performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2009 at 8:35 am
Typically we do filter as much as possible on the Oracle side. What stumps me is when the server is rebooted and the connections are fresh - everything seems to move very quick. I can do an openquery where 'rownum <= 5' and it'll return data in less than 30 seconds.
March 3, 2009 at 8:41 am
Hmmm... Well, have you tried looking at the wait states and queues? That's where I'd go next. See what everything is waiting on. Here's an excellent article on it: http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2009 at 8:53 am
I don't mean to play dumb, but I've never seen these white papers before. Is this the same information you get from the 'Activity Monitor' in SSMS?
March 3, 2009 at 9:10 am
It can be yes. If you look through the white paper though they're using DMV (dynamic management views) which give you a lot more detail that you'll get through the SSMS gui usually. It's just a really great way to identify what's going slow. Figuring out why and what to do about it are two more steps. This will tell you if you're waiting on memory or cpu or disk or network. From there you can make the determination of where to drill down.
Since rebooting seems to work, you might be hitting some kind of memory leak, but with a check of the wait stats, you can determine if most of the wait time is spent waiting on memory or something else.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2009 at 9:21 am
A memory leak would not surprise me at all considering problems we've had in the past (that was even my suggestion as to the culprit a couple of years ago). I was just given the approval to build-out a new server a few weeks ago, but until then we have to use this jalopy. We have SQL 2K and SQL 2K5 running side-by-side right now on this machine (SQL 2K agent is stopped though) and I know SQL 2K was a memory hog, but we've been trying to move to SQL 2K5 for the past couple of weeks. I have been watching the activity monitor w/auto-refresh and since the reboot it seems to actually be giving connections back. Before the reboot, I had a ton of "Runnable" processes, zero open transactions and zero blocks.
March 3, 2009 at 9:31 am
That sounds like some app isn't closing it's connections. Lots of open connections will certainly chew up memory eventually.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2009 at 9:45 am
'some app' meaning something besides sql?
March 3, 2009 at 9:47 am
Something. That isn't closing it's connections. Could be SQL, could be something else. Probably something else.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2009 at 2:02 pm
R u using IIS , web based application , Connection pooling might be one reason for it. Refer BOL for Orphaned sessions
If u feel memory is the main deal , Next think check out physical memory available and how much is allocated to SQL server
March 3, 2009 at 3:21 pm
We have reporting services running on the same box as SQL (IIS 5), but I also have 2 other web apps (one of which is used pretty heavily) on a separate web server w/in the domain that read/write to the SQL box. I hate admitting this (but I need a solution), but I'm not sure where to check connection pooling on the server.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply