Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

SQL lag issues

I'm currently experiencing a massive issue within our Environment.  On Saturday I physically moved servers one by one.  The "move" was roughly 4-7 feet total, however now when a query is ran from a workstation back to the server we are seeing delays of over 20 seconds.  Here's the kicker, it afflicts random users.  One user will have zero issues for hours, while the other is dead in the water.  It can all change on the fly though, there's no specific time frame in which a user will be up and running.

List of steps already taken:
Repaired all the Databases including master and temp.
Truncated log files, as I did have one that was over 19gb.
Swapped ports and switches for all servers concerned, as well as some workstations.  (I have verified we do not have a Layer 2 Loop).
Verified VSS wasn't enabled on Symantec System Restore 2010, and then uninstalled the component completely.
Disabled AV on the server, issue still persisted a hour after disabling.
Ping tests to our primary SQL server is consistent without packet loss.
Ran a few SQL traces through the SQL Server Profiler tool.
 -I did find some of the query's that are taking an incredibly long time from the workstations, but it doesn't appear to be any one particular query.
Query's ran on the server itself are near instant

Got with the manufacturer of the company who installed and supports  our Application (Infor's Visual).  They pointed to a network issue, but couldn't find anything saying it was a network issue, somewhere.  The call resolved with having him saying he will check around and see if anyone else has seen the issue.  While I do believe there maybe a network issue I have nothing to go on to determine what to fix.

I do have a spare gig switch that is being delivered today to eliminate the possibility of network overload, but I'm grasping at straws currently.
0
HBlakei
Asked:
HBlakei
1 Solution
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
One of the worst, random, frustrating SQL experience I ever had was on Windows 2003 Servers... and it was eventually tracked down to TCP Chimney Offload.  (http://support.microsoft.com/kb/912222).  What complicated troubleshooting, and frustrated me was that the problem manifest itself months after the service pack which turned it on, after the fifth reboot or so...

(And the problem manifested with two software applications of ten which used the particular SQL server!  And some communication from those applications were FINE.  So, from my point of view, SQL 'looked fine', and I kept insisting that it was probably their applications.)

Anyway, you can read more about it here: http://blogs.msdn.com/b/psssql/archive/2008/10/01/windows-scalable-networking-pack-possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx

I can't tell from your question whether this applies in your environment.
But it might be something to look at.  (It got turned on in a SP in Win 2003... and if off by default in Win 2008... but even then, it's worth investing a second or three to make certain it is still off.)
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
One thing I would check throughout the day that you see issues, is whether or not the Log File for the database grows during the time it is slow.

What can happen is that say an Update or Delete or Insert query happens and causes the log to grow because you truncated them.  While this auto-growth happens, it will make it look like it is very slow because when growing the log, it has to 0 out the growth. If the auto-growth is set to be something small, it may grow more often and cause these times of slowness.

I would check that out before chasing a network issue.
0
 
HBlakeiAuthor Commented:
This was actually multiple issues occurring at once.  The solution did help in actually finding where the core issue was actually located.  One of our managed switches decided to turn our entire environment into a hub, and not throw any warnings or errors.  Once that was resolved we didn't have any issues.  Very very odd issue to say the least.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now