| Author |
Topic |
|
robg69
Starting Member
41 Posts |
 Posted - 2003-08-27 : 13:26:03
|
OK, I have absolutely no idea what the hell is going on with our SQL Server. I just started this job about a month ago and since then the SQL Server (SQL 2000, SP3) has slowly gotten worse and worse. What's happening is, we reboot the server and everything is fine, SQL is running great, and all of our applications are working fine. Then, for no apparent reason, the server just starts getting pounded. I mean it's so bad that you can't even open task manager to look at the cpu/mem stats, much less login with Enterprise Manager (EM). SO, I wrote some SPs that show me the same info that the current activity shows (in EM), and also some extended locking info, so sometimes I can get in with query analyzer to see what's going on. I've also run SQL Profiler for inspection, but nothing jumps out at me. No locks, no strange SQL statements or anything is happening AND this apparently seems to be totally random... I've checked the SQL Server logs, the Server logs (application, security, system) for errors, but nothing. We've checked for the slammer worm, not found. We've tried stopping and restarting SQL (that used to work) and nothing. We've tried pulling out the network cable (that used to work, too), but nothing. The only remedy is to reboot. The problem used to happen every one to two weeks, but now its happening, AT LEAST, 2-3 times a day! Needless to say, I am totally LOST. I guess the only other thing left to mention is what we are using apps/OS/mem etc. We are running SQL Server 2000, SP 3 with 1 gig mem on a Windows 2000 server. We have a large range of apps that use SQL Server including VB/ASP/.NET/Coldfusion/Crystal Reports etc, so on the web server (Win 2k server, IIS 5.0) we have: Coldfusion MX, the .NET framework (v1 & v1.1), and some other Primavera Group Server v3.5.1 thing that runs JSQL commands against SQL Server (I have no idea what this is, I call it a suspect). All of these apps are run throughout the day, and are constantly hitting the SQL server. But, like I said, sometimes they all get along and other times they don't.I've been on both sides of the DBA vs Developer situation, but I can't help thinking that it has to be one (if not all) of these applications causing the problem. All of these tools can talk back and forth between SQL Server and millions of people write apps every day that do this, so what in the world could be the problem? Is it that the app(s) aren't closing connections (shouldn't I be able to see these and kill the processes)? Is it that the web server (IIS) isn't closing connections (again, shouldn't I be able to see this and kill the process)? If a table didn't have keys/indexes could this happen?Well, I know this could be a million things, but aside from taking down the server and adding the databases (and/or applications on the web server) back 1 by 1, does anyone have an idea of what the problem is? Maybe a suggestion to fix it and/or has anyone had a similar problem? PLEASE HELP, I'm at my wits end.  |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-27 : 13:30:39
|
| You need to run Performance Monitor to determine if you have any hardware bottlenecks. SQL Server could be starving for memory after the server has been up for a while. Also, the CPU could be at 99% for quite some time causing the problem. Performance Monitor will let you know if there is a bottleneck and where. You could have multiple bottlenecks.Tara |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-27 : 13:31:34
|
| Check out the performance monitor information on this site to guide you through what you should be looking at:[url]http://www.sql-server-performance.com/[/url]Tara |
|
|
|
robg69
Starting Member
41 Posts |
Posted - 2003-08-27 : 17:43:32
|
| Hi Tara, thanks for your quick response. I've been trying to figure out how to use Performance Monitor for a while now, but I haven't had any luck yet... I guess I'll look around on that website. Any other ideas? I've noticed that if I kill about 4 processes from an app, that SQL Server comes back (although I've only tried this twice and I haven't exactly discovered which app is the culprit yet or what it's doing wrong -- it never happens when you want it to). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-27 : 17:54:43
|
| Well, Performance Monitor and SQL Profiler are tools that need to be used to diagnosis and fix the problem. Do you have Windows server administrators there? If so, they should be familiar with Performance Monitor. Before you kill a spid (a process), you should see what the process is doing and who is doing it. Killing a process could negatively affect the server as the query has to be rolled back now.Tara |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-08-28 : 02:50:30
|
quote:
Originally posted by robg69
Hi Tara, thanks for your quick response. I've been trying to figure out how to use Performance Monitor for a while now, but I haven't had any luck yet... I guess I'll look around on that website. Any other ideas? I've noticed that if I kill about 4 processes from an app, that SQL Server comes back (although I've only tried this twice and I haven't exactly discovered which app is the culprit yet or what it's doing wrong -- it never happens when you want it to).
You might have a memory leak somewhere. It's hard to determine where that might be coming from, but I would start with any COM objects instantiated inside SQL Server Stored Procedures. Check if they are being appropriately released.Owais
Make it idiot proof and someone will make a better idiot |
|
|
|
robg69
Starting Member
41 Posts |
Posted - 2003-08-29 : 10:30:16
|
| OK guys, thanks for the replies...I finally figured out Performance Monitor (PM) from: http://www.sql-server-performance.com/performance_monitor_tips.asp (thanks Tara) so I guess I'm just going to have to do some more investigation. Owais, I know that we aren't using in COM objects, but it could be possible that we have a memory leak somewhere. Hopefully PM will help me. Anyway, thanks for the suggestions. |
|
|
|
robg69
Starting Member
41 Posts |
Posted - 2003-09-02 : 10:56:39
|
| Ok well, It's happened a few more times and I've figured out which application it is (the app is custom built using Coldfusion, and used pretty often. The DB is a huge mess, no indexes, not many P,F keys, redundant data, etc). But when the server starts freaking out, Performance Monitor won't even work anymore. It just freezes until I kill those spids from the app. SQL Profiler continues to work, but the only thing that will bring the server back to "life", is a reboot OR killing those 5-6 spids. Anyone have any more ideas? Should we just try to put this app on a server by itself (since it can't play nice with the other apps)? I wish I knew exactly who was doing what, so I could track down the problem. The network address is all zeros, so I figured it was being re-routed from somewhere.Anymore suggestions? Thanks. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-09-02 : 11:18:17
|
quote:
but the only thing that will bring the server back to "life", is a reboot OR killing those 5-6 spids
.It appears you've got this narrowed down enough to find the cause. What DML is being executed on these SPIDs? Show us the results of your Profiler trace.Jonathan{0} |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-02 : 12:17:03
|
| SQL Server should be on a dedicated server. No other apps should reside on it. This is a performance recommendation due to SQL Server being a memory hog. You also want SQL Server to perform as fast as possible, so why would you add other apps to the server?Tara |
|
|
|
robg69
Starting Member
41 Posts |
Posted - 2003-09-02 : 13:14:51
|
| OK, I found one SQL Statement that was causing a big problem, so I guess I will have to wait and see if fixing it fixes the problem. Tara, we don't have anything else running on that (SQL) server. The applications are on our web server. SQL Server is the only thing installed on our SQL Server. (sorry, what I meant to say was, should we move that db to it's own (SQL) server).Ok, so I guess I'll just keep monitoring with Profiler. Thanks for your help guys. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-02 : 13:22:31
|
| You should move that database to its own SQL Server if this server is maxed out. Is Performance Monitor showing that the server is maxed out, meaning CPU utilization is too high and also is showing a flat line in the graph, not enough RAM available, etc...? If so, are you able to add hardware to it to correct the problem? I ask this question because it is usually cheaper to upgrade a server than to add another SQL Server. This is because of the additional SQL Server license that would be required for this database.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-02 : 13:25:26
|
| BTW, when you run SQL Profiler, it is impacting performance. You should never run SQL Profiler on the database server (not sure if this is what you are doing though). You should run SQL Profiler, Performance Monitor, and other tools from a different machine (such as your machine). Even when you run these tools from another machine though, it will still impact performance but not as much as it would when the tools are running on the server being monitored. These tools should only be run for a period of time and only when data is needed to be collected for analysis. We run Performance Monitor three times per week for four hours. We collect the data at 15 second intervals. We do this for trend analysis. We also run Performance Monitor and/or SQL Profiler when people are complaining about slow performance. We typically will run it during a peak period for about an hour then decide what the upgrade path is going to be to correct the problem.Tara |
|
|
|
robg69
Starting Member
41 Posts |
Posted - 2003-09-02 : 14:46:12
|
I believe the hardware should be enough for what we're doing. We generally are only having about 20 users for all of our DBs. Every once in a while when all of the apps are in use, we will see a few spikes (mainly on the 'network interface' in PM). However when that query gets run, 'processes', 'system:processor queue length', and the 'network interface' are maxed and perfomance monitor freezes (along with the server). And also, yes, I'm monitoring from my machine, we don't do anything on the server, unless we have to. I think the main problem is with this app. It's an intranet web portal that was developed 3rd-party and even though we have contact with the developers, it's tough to get things done, if you know what I mean (plus they don't like testing their software). All of our in-house stuff works great.  And we are going to get a lot more users/developers so we want to try and iron out most of the problems now.Anyway, thanks again Tara and everyone else. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-02 : 14:51:19
|
| Since this app is 3rd party, I would recommend moving it to another server as you mentioned if you can afford the license. I would not recommend housing 3rd party databases along with in-house databases unless a lot of monitoring has been done to determine if they can live together.Tara |
|
|
|
robg69
Starting Member
41 Posts |
Posted - 2003-09-08 : 11:13:24
|
| Well, the server has been running great so I guess you guys were right. It seems to be that one SQL Statement (when I ran it in query analyzer, it froze the server). We haven't moved the DB to it's own server yet, but if we continue to have problems then I think we will have to buy another license (or build our own intranet portal). Anyway, thanks again for all of your help. I should be able to track down future problems, now that I know what tools to use and how to use them. Thanks again! |
|
|
|
|
Very Bad SQL Server problem