?
Solved

SQL lag issues

Posted on 2013-01-11
3
Medium Priority
?
384 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 30

Accepted Solution

by:
Rich Weissler earned 1500 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 25

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

770 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