December 4, 2007 at 11:55 pm
hi,
we are maininting more than 40 databases in sql server 2005(4 GB RAM ).one week back we are moved from old sql server 2005(2GB RAM) to new sql server 2005. Everything is working fine except one application. we checked server side no issues. users are accessed through application.
for one applicaion previously it used to take 2 seconds (old Server in the beginning) to save an account and now it is taking 2 - 24 seconds (sometimes it was taking 3-4 Minutes) to save an account this occurred around 20-25 times during the day (where it took more than 10 Secs – to 4 Minutes) to save an account) and varies at different time to save an account or to view the report
any one pls help...
thanks ,
kumar
December 5, 2007 at 1:35 am
Could be out of date statistics or fragmented indexes. Is there any blocking? If you look at the sys.dm_exec_requests, can you see if the query is waiting for anything? If so, what's it waiting on
I'd suggest you run profiler for a short period while someone is saving an account, and watch what statements take the time (you can capture the RPC:completed to get calls to stored procs, or the SP_stmtCompleted to get the individual statements in the proc)
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
December 5, 2007 at 6:06 am
Once you identify the offending procedure using Gail's method, get execution plans to see what is occuring within the script.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 5, 2007 at 7:06 am
Not sure why this would have changed - but what do your autogrowth/sizing parameters look like?
Also - did the auto-close get "flipped on"? That could create some really interesting delays....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 6, 2007 at 9:16 am
Did you do a full update of all indexes/statistics after the upgrade? This is mandatory when moving databases from 2000 to 2005.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 7, 2007 at 9:38 am
If you are using Win 2003 32 bits with Sql Server 2005 32 bits AND the bigger wait type is "RESOURCE_SEMAPHORE_QUERY_COMPILER" in a multi processor machine, don´t loose your time: MIGRATE TO 64 bits today.
To find the worst wait type, run this:
select * from sys.dm_os_wait_stats order by wait_time_ms desc
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply