Solved

SQL lag issues

Posted on 2013-01-11
3
347 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

910 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

20 Experts available now in Live!

Get 1:1 Help Now