Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

The Troubleshooting List Expand / Collapse
Author
Message
Posted Friday, August 24, 2007 2:17 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:45 PM
Points: 6,779, Visits: 1,856
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/awarren/3211.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #393787
Posted Wednesday, October 3, 2007 2:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 28, 2014 1:54 AM
Points: 171, Visits: 229
Ill add an extra point :)

#Get to know your general IT support structure you don't control
Over the years ive been databasing, ive got to know how our IT support works here (and more importantly common problems that seem to re-occur). Knowing the really good guys (and building those working relationships that let you bypass red tape), and knowing the `holes` in support.

For me it helps prioritise my first trouble shooting steps, especially for those trickier things. e.g. We had an access gui that connected over odbc to an sql server. All was fine for months - then some new machines were installed. Some processes in the front end that were normally instant suddenly took minutes. Spent all sorts of time profiling this, checking that. Turned out to be missing DNS entries for the sql server were causing the problem! Now when I see slow connections to a newly set up sql server, its the first thing I check. It has happened again!

martin :)
Post #406077
Posted Wednesday, October 3, 2007 5:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 3:55 PM
Points: 442, Visits: 185
Worst performance problem:

Developers not understanding transaction isolation levels.

My specific worst performance problem: Developers not understanding what is going on behind the scenes of a 5th GL that was used to write several applications. An upgrade of one of these applications caused the first read (or query but they were always reads before updates later) executed to start an implicit transaction in serializable isolation mode. This was a little detrimental to performance. A user would run an report or just a view to look at something and lock everything in it's path, then would go on to do other things and continue locking until they eventually did an update which released the locks -- until it all started over again. System was brought to it's knees -- actually it was laid flat out. The software change was tested in depth with one user doing all the testing sequentially. No load testing at all. No verifying what the software was doing under the covers with transactions.

Post #406132
Posted Wednesday, October 3, 2007 9:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 15, 2008 11:26 AM
Points: 3, Visits: 18
Blocking is consistantly the biggest problem for us, too. I've written a small application that, every two minutes, checks for blocking on our main sql server. When detected it waits for 15 seconds then checks again. If the same spid(s) is still blocking the app emails me the details about every processid that is blocking or being blocked, including duration, the command being issued, hostname, program, login, etc.

This has allowed us to be extremely proactive in troubleshooting blocking issues. Its funny when we do determine who is running the process that's blocking, call them and suggest something is wrong. "How'd you know?!?!"
Post #406256
Posted Wednesday, October 3, 2007 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 26, 2010 3:37 AM
Points: 8, Visits: 54
I did the same. I wrote sql sp and added it as SQL JOB to log into table all lockings/blockings holding more than some criteria(logging blocking/locked required resource,type of lock,cmds,duration,application,user,etc.). If it continues more than normal time script sent error on email. This helped a lot in troubleshooting applications, especially new ones. You could kill process and then analyze the log on problem cause.
Post #406272
Posted Wednesday, October 3, 2007 10:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:37 PM
Points: 1,612, Visits: 1,536
I agree with checking for blocking first, but I'm surprised that two of the most fundamental things aren't even listed: check the SQL log for unreported errors and check the server performance counters for any current bottlenecks.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #406294
Posted Wednesday, October 3, 2007 10:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 10, 2010 4:35 PM
Points: 37, Visits: 127
I am a very green production DBA - always been a SQL developer who has just been thrown into the thick of server maintenance now. To make matters more hot we just launched a new app this week to beta and I have already had to troubleshoot an incident or two. Have to ask who is PSS (SQL Server consultant firm mentioned in the article). I really like the idea of having a pre-approved call in my back pocket, but haven't known where to start looking for the right outfit.
Thanks in advance, NJ
Post #406316
Posted Wednesday, October 3, 2007 11:06 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579
Very Nice!
I will forward this to our Lead DBA who was (translation-upset) Friday when the vendor blamed the DB performance and it was a front-end issue so he sent out an email with a subject " It's always the DB"

To add to your list:
- Check if it is really a DB, not the application or web pages (see above)
- Check if another job or process is running on the server, especially if the performance is not good at certain times of the day. We once found a Windows backup job was running at 10 AM of all times. The counter I was monitoring was Disk Queue Length that was consistently up starting 10 AM. Rescheduling the backup job helped.
- Check the Processor Utilization.
- Tell them, developers to check on indexes. I would not give examples of full table scans multiple times for one query - will take to much time.
- Tell them, developers to check how connections are opened / closed during on person's session. Had a case when by a typo or something the code had Connection.Open statement inside the loop while saving records, so the new connection was created for each processed record. It was long time ago. What was not long time ago was Sharepoint 2 opening about 400 connections with each user session. This issue was resolved by our Sharepoint admin after calling Microsoft..

I have much more, but the page may time out before I post it.

Yelena



Regards,
Yelena Varshal

Post #406328
Posted Wednesday, October 3, 2007 11:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:37 PM
Points: 1,612, Visits: 1,536
PSS = Product Support Services. Basically, it's Microsoft SQL Server support.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #406348
Posted Wednesday, October 3, 2007 12:20 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 18, 2012 8:55 AM
Points: 162, Visits: 334
Number 10 reminded of a recently learned lesson. I came in one morning and one of our major applications wasn't working....users could not log in . Of course, when an application isn't working everyone likes to blame the database. A quick sp_who2 and a check of CPU utilization and a few other indicators all said the database was purring like a kitten. And with pride I responded that this time, it wasn't the database. The application developers rolled up their sleeves and began pouring thru code. The network engineers were checking switches and the deployment team was checking recent changes. And I kept watching the database. Turning on profiler I saw RPC calls and SQL statements executing at a normal pace.

But then I look closer.....they were repeating the same queries over and over again. Hmmmm, wonder what that means. Finally, I tried executing one of the RPC calls directly from Query Analyzer. What the @#$%@! An error message: Time out occurred while waiting for buffer latch type 2 After 3 hours of declaring the database innocent, I was not ready to call it guilty. Fifteen minutes later and the outage was over.

The lesson: Never stop looking until the problem is found. If all indicators say the database is fine....check more indicators.

Fortunately, rather than being chastised for my hubris, I was praised for my perseverance.

Gordon


Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
Post #406362
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse