September 26, 2008 at 7:16 am
Greetings,
A few weeks back we implemented a new back end for our production environment. We used to have 2 RW and 3 RO database servers - but bought new hardware with more power which means one big server.
Database and logs are placed on quick SSD Disks - using HBA's < -- So Its not a disk problem
We host websites:
What we experience is that inserts and reads / requests are getting slower / worse and worse in a few hours. When we restart the server everything is fine. At this point we restart the database 3 times a day!
Activity Monitor show 781 processes - no one is blocking
CPU usage is not that high
Memory is not that high
Something is stalling?
We updated the database a few days ago with the following (did nothing)
Cumulative update package 9 for SQL Server 2005 Service Pack 2
http://support.microsoft.com/kb/953752/en-us
We have had a consultant to try to figure out what could cause it.
He deactivated:
Parallel Query
OUR SPECS:
SQL Server 9.0.3282
Microsoft SQL Server Management Studio9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 6.0.3790.3959
Microsoft .NET Framework 2.0.50727.1433
Operating System 5.2.3790
SQL Server 9.0.3282
ProductMicrosoft SQL Server Standard Edition (64-bit)
OS Microsoft Windows NT 5.2 (3790)
Platform NT AMD64
Version9.00.3282.00
Memory32765 (MB)
Processors 16
Is Clusteret False
Any help would be greatly appreciated 🙂
Is there a way to contact Microsoft and get help? I mean we are using MSSQL 2005 STD Edition.
September 27, 2008 at 6:51 pm
Do you have any SQL Server Agent jobs that are failing? I've found that, depending on what they do, they can leave open connections that hold on to resources. Try stopping the SQL Server Agent and restarting it without bouncing the server. If the performance improves, that's the problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 28, 2008 at 6:24 am
Have you limited the server's max memory? If not it would be a very good idea to do that. Set the max memory to around 29GB.
Also have a look at this blog post and see if perhaps you're encountered the issue it describes:
http://sqlinthewild.co.za/index.php/2007/10/21/memory-and-sql-2005-sp2/
Please monitor the following for a few hours (from one bounce of the server to another is good)
physical disks:avg sec\read
physical disks:avg sec\write
physical disks:%idle time
processor: %user time
processor: %privileged time
memory: available MB
SQL Server buffer manager: checkpoint pages/sec
SQL Server buffer manager: lazywrites/sec
SQL Server buffer manager: buffer cache hit ratio
SQL Server buffer manager: page life expectency
SQL Server memory manager: Total server memory
SQL Server memory manager: target server memory.
Also, can you run the following query about every hour. The values are cumulative since the server starts, so it's the change that's important more than the values
select top 20 *, cast(wait_time_ms as float)/waiting_tasks_count as Avg_WaitTime
from sys.dm_os_wait_stats
where waiting_tasks_count>0 and wait_type not like 'Sleep%'
order by wait_time_ms desc
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 28, 2008 at 6:38 am
Hi Jeff Moden
Well havent thought about that - I will give it a try next time.
Regarding the memory - the limit is 29G at this moment. The MSSQL never uses more than 18G at any time.
GilaMonster
Ill try that tommorow.
Greatly appreciated guys!
September 28, 2008 at 7:21 am
Perhaps a silly question, but have you rebooted? i've seen that clear up some bad query plans.
Do you have any reference execution plans to examine the slow queries and see where things might be going wrong or changing?
September 28, 2008 at 8:04 am
Hi Steve Jones,
Yes we did try reboot without any luck.
I tried to analyse where the time went using Nexus - but is seems everything looks good. Ill upload a few screenshot so you guys can look tomorrow.
September 28, 2008 at 8:47 am
If you're able to upload the permon stuff, please not as a screenshot. There's not enough info in a screen shot. Zip and upload whatever you saved (if possible)
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 29, 2008 at 2:29 am
Hello again,
I have uploaded some performance data to a ftp:
@REM To register the collector as a service, open a command prompt, change to this
@REM directory, and run:
@REM
@REM SQLDIAG /R /I "%cd%\SQLDiagPerfStats_Trace.XML" /O "%cd%\SQLDiagOutput" /P
@REM
@REM You can then start collection by running "SQLDIAG START" from Start->Run, and
@REM stop collection by running "SQLDIAG STOP".
START SQLDIAG /I "%cd%\SQLDiagPerfStats_Trace.XML" /O "%cd%\SQLDiagOutput" /P
FTP Hostname: skjelmose.lir.dk
User: mssql
passwd: mssql
Hope its usable - otherwise please direct me to the proper way to obtain debug information.
September 29, 2008 at 6:07 am
GilaMonster
select top 20 *, cast(wait_time_ms as float)/waiting_tasks_count as Avg_WaitTime
from sys.dm_os_wait_stats
where waiting_tasks_count>0 and wait_type not like 'Sleep%'
order by wait_time_ms desc
This is my output:
September 29, 2008 at 1:25 pm
iwantroot (9/29/2008)
GilaMonster
select top 20 *, cast(wait_time_ms as float)/waiting_tasks_count as Avg_WaitTime
from sys.dm_os_wait_stats
where waiting_tasks_count>0 and wait_type not like 'Sleep%'
order by wait_time_ms desc
This is my output:
Was that while the server was very slow?
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 29, 2008 at 1:51 pm
Yes it was - but we did not hit the point where the speed was terrible low. Perhaps because of server agent restart earlier this morning.
September 30, 2008 at 1:24 am
I have another one from this morning
September 30, 2008 at 3:06 am
Ouch. Your IO performance looks terrible. Average of 44 sec for an async IO completion with a max of 22 minutes is way, way too high.
I can't download the perfmon data. It's just too big. What did the physical disk counters look like? Min, max and avg?
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 30, 2008 at 4:04 am
GilaMonster (9/30/2008)
Ouch. Your IO performance looks terrible. Average of 44 sec for an async IO completion with a max of 22 minutes is way, way too high.I can't download the perfmon data. It's just too big. What did the physical disk counters look like? Min, max and avg?
Hi Gila,
Ive uploaded a new perfmon (only 10 Mb i size) im currently trying to import it in nexus. It would be great if you could take a look sometime.
Greatly appreciated.
Daniel
February 15, 2010 at 9:25 am
IwantRoot,
Did you ever discover the culprit for this issue?
I am having a similar issue with a 2005 sp3 box.
The performance starts to hang on a fairly common screen query.
I have found the stalling sql.
The data is being hit by a 3rd party application that I cannot change the SQL. The query is no thing of beauty but has an operating cost < 7 (execution plan).
Killing the query returns the database performance to nominal. Then when the user returns to the query in question it will not complete. MS wait in activity monitor 242###...
After sqlserver restart the performance is again acceptable.
thanks
Daryl
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply