Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL lag issues

Posted on 2013-01-11
3
Medium Priority
?
389 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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, show how to shrink a transaction log file down to a reasonable size.

604 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