Solved

SQL lag issues

Posted on 2013-01-11
3
337 Views
Last Modified: 2013-01-23
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
Comment
Question by:HBlakei
3 Comments
 
LVL 29

Accepted Solution

by:
Rich Weissler earned 500 total points
ID: 38768693
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
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38810103
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
 

Author Closing Comment

by:HBlakei
ID: 38810599
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now