August 25, 2008 at 2:26 pm
Hi, I am relatively new to SQL. I have three databases running on SQL 2000 (8.00.2050 )server. This 3 databases (1 for payroll, 1 for accounts payable and 1 for a Customs system- all of them form different vendors), were working just fine for about 1 Year. About 1 month ago, one of the Databases (Customs), is having some kind of slowness. There is one process this database does, which is to verify some part numbers- This query took about 1 or 2 minutes, nowadays, it takes about 10 to 15 minutes!!!. We did some repairs, check indexes, shrinks, deleted the Log file (which BTW grows big, about 1GB) the MDF is about 2GB.
Well, the only thing we found out to be effective was to Stop and Start the SQL Service with this commands on a batch file I took from a web site:
@ECHO OFF
NET STOP SQLSERVERAGENT
NET STOP MSSQLSERVER
NET START MSSQLSERVER
NET START SQLSERVERAGENT
When I run this, the query takes again 1 to 2 minutes., but next day, the same thing happens.
I don't want to run this everytime I have problems, because, there are another users, using the "other" databases, and I guess that can cause troubles somehow.
Question: What can be causing this behavior?, is there any "log" I can check ?, How should I approach this problem?
Thanks.
August 26, 2008 at 12:41 am
Firstly, don't delete your log. You are asking for a suspect database doing that. It will grow when you do index rebuilds.
It could be a number of things. If you run the process from query analyser how long does it take? Run the process from query analyser with the following settings on
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
and look for the queries that take the longest. If you find one or two that are taking the longest, run then with the execution plan on and see where the high costs are. See if perhaps an index needs changing or the code needs changing. If you want advice on that, post the queries here, along with the table and index definitions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2008 at 12:47 pm
Thanks Gail, I appreciate your help. As I mentioned, my sql expericence is very limited, what I am trying to accomplish here is found out if the problem resides on my server, or , if the problem resides on the Vendor Database.
As I stated before, we have 3 db's on that server, but, just the Customs application, is the one who's behaving slow.
I sent the ".mdf" file to the vendor, but he said, the DB has nothing wrong on it, but I am a little bit "concern" about he is not really giving the importance it demands. I cannot kick them out at the moment, because we run some custom-made interfases with his software, and, is not the moment.
But, what I can't explain, is why the simple fact of reset the sql agent will fix this for some hours.
- He also mentioned that I must upgrade to sql 2005, to see if that helps.
or even considering buying a new server to host just this application.
But how can I ensure this measures, will fix the problem in the first place?
August 26, 2008 at 2:16 pm
light.harper (8/26/2008)
But, what I can't explain, is why the simple fact of reset the sql agent will fix this for some hours.
You're not resetting SQL agent. Those commands you listed earlier are restarting the entire SQL Server Service. That means all the cached data and plans are discarded and SQL's starting from scratch.
I can't say what the issue could be without a lot more investigation. The thing is, a well-designed and well written database does not behave like that.
- He also mentioned that I must upgrade to sql 2005, to see if that helps.
or even considering buying a new server to host just this application.
But how can I ensure this measures, will fix the problem in the first place?
If it's a performance issue, neither of those will truly fix it. If it's bad code or bad indexes then at best doing one or both of those will slightly allieviate the symptoms, for a while,
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2008 at 9:24 am
like Gail said its basically a restart.
instead of doing that next time try
checkpoint
dbcc freeproccache
dbcc dropcleanbuffers
checkpoint
it will have to recompile all the query plans so briefly there might be a performance hit. Let us know if that helps.
Upgrading to sql2005 and upgrading hardware for just one long running query sounds crazy
August 28, 2008 at 6:47 pm
thanks, I will try, and let you know
August 29, 2008 at 5:03 am
I would not completely rule out that it could be that your database(s) have outgrown your server hardware. SQL can be a bit of a memory hog if you allow it to be.
This would seem to fall in line with the fact that right after you restart the SQL service the process runs fine, because right after you restart the service, you have more unallocated memory available. Additionally, as your databases have grown over time, the amount of memory the SQL Service would be consuming would also be growing.
If you have your 3 databases on seperate instances, you could set the memory allocations for each individual instance.
Memory is rather inexpensive, so it may be a bit easier and more effective to upgrade the hardware.
August 29, 2008 at 10:55 am
light.harper (8/28/2008)
thanks, I will try, and let you know
I made some testing yesterday:
I ran a process (verify an invoice) on the Customs Application, before any command or SQL restart.
1.- Invoice verification took 8 minutes
2.- I Ran the
checkpoint
dbcc freeproccache
dbcc dropcleanbuffers
checkpoint
Invoice verification: 8.15 minutes
3.- I did the :
NET STOP SQLSERVERAGENT
NET STOP MSSQLSERVER
NET START MSSQLSERVER
NET START SQLSERVERAGENT
Invoice verification: 1.30 minutes.
August 29, 2008 at 12:59 pm
Very strange.
Is the invoice verification using linked servers by any chance?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2008 at 1:16 pm
What do you see for activity when you experience the slowness? Any blocking? (sp_who2 - look at the blkby column).
If you can get the SPID for the process that is running the query that is running slow and then look at the status of that query in the results of sp_who2 as well.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 29, 2008 at 1:46 pm
If you can get the SPID for the process that is running the query that is running slow and then look at the status of that query in the results of sp_who2 as well.
And get the last wait type as well please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2008 at 5:01 pm
no linked Servers
September 1, 2008 at 5:34 pm
It could simply be a case of "parameter sniffing"... Google it.
Also, are "auto statisics" enabled or not?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply