SQL Server Performance Forum – Threads Archive
Sql server 2005 db slow down suddenly – URGENt
i cannot say why but one of my db which is running on sql server 2005 has slow down drastically. while other db on the same server are fine. what can be the reason. this victim db was running fine for last 6 months. another thing i noticed is that CPU consumtion by sql server on this server is fluctuating drastically, sometime its going to 97 -98 and then comes down to 6-7. what is causing this. we are using window server 2003 and memory is 2 GB. Best RegardsWhat kind of scheduled jobs, optimization jobs are set against this database?
What is the volume of the database and user base too? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Run the sql trace and filter on duration and CPU usage…
Check if any of the frequently running procedures are recompiling or executing with bad query plan. Mohammed U.
I have backup schedules at nights and transactional replication which is replicated to subscriber at 12pm and 8 pm. The complete db is slow so i dont see any importance in checking specific query in profiler. I assume there is something in general which went wrong for this db. what can be that. Same db was fine till last week. on weekend the networking guys remove the VLAN setup after that this problem occur. amazing thing is that other db on the same server are fine and when i restore this particular db on test machine, still i am facing the same problem.
Is it slow when u access from the application ?
when u run a query from QA how it perform ?
is any query to any tables are slow on the db or few SPs are slow ?
r u getting any time out error? just post the whole scenario. Madhu
its slow when running from application, its fine when running from QA. i dont see only specific queries are having problem. almost all the operatioinal queries / stored procedures are having this problem. only queries with very small tables are fine. for example: the SQL Batch for the below query has taken 34589 duration
SELECT DISTINCT GPNO FROM RETURN_TRAN WHERE SM_CODE=’4038′ AND GPNO <> ‘NA’ ORDER BY GPNO DESC i suspect something general with db not specific to any query, becuase we are using the same queries / application for last 6 months without any change and it was fine. what are the alternative solutions to apply on db as whole. even trying to restore from a full backup is of no use.
Are you running regular update statistics?
we faced same problem in same scenario. (ie. transactional replicated database). the application was running slow , but the QA was fine. All other DBs were ok on the same server. We ran Profiler but nothing come out. We ran DBCC ReindexDB and no result. Somehow, we missed to Update the Statistics. Finally we did that (Sp_updateStats, ) and the problem got sorted out. so try
sp_updatestats or UPDATE STATISTICS and also i would request u to share the result. Madhu
i dont get any improvement by reindexing and sp_updatestats. still its the same.
Take your time to understand thishttp://sqljunkies.com/Article/7F8518F9-FDAA-4FF3-8FC5-25E8946C8D0C.scuk article and follow what is suggested, as most of them still applicable to SQL 2005. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
is there anything like database corrupting or sql server corrupting. because almost all my results are fine and there is nothing significant happen to it.
Thanks god and thanks everybody. finally, i could make my db back to normal. how ??? i just drop existing indexes and create them back. its funny, earlier i rebuild them but could not get any success, but after deleting and recreating them its fine. It seems sometime logic / sense takes back seat.
True and in general the DBCC DBREINDEX would do the same, as you say it helps fewtimes if you perform such process. But you have to keep in schedule the database optimization tasks in this case. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
once you have found queries that are slow,
and confirm the cpu for the queries are also high,
look at the execution plan,
save the plan in xml format if on 2005, copy and paste into excel if 2000
if you have an old backup of the db,
restore it somewhere, and look at the execution there too understand that just because a queries runs fine at one point in time does not mean it will continue to, this is because your data distribution is changing for the query above, what are the indexes on RETURN_TRAN ?
is it just on SM_CODE, or a compound index on SM_CODE followed by GPNO
]]>