Work Item update (sometimes) is taking too long and consuming high SQL Server CPU

Question

Hi,

we are passing through a intermittent problem here in our TFS installation.

Sometimes when we create or update a work item it takes too long to save - about 60-70 seconds.

Monitoring the SQL Server, we saw that sqlservr.exe process starts consuming high CPU during the save process.

All the system is working fast without any problems, but this is a intermittent behavior and the users are complaining about it. 

Using the sq server profile, when this problem occurs it seems to be a probelm inside the WorkItemAuthorizeChanges sp, but we aren't able to see inside because it's a encrypted sp.

Could someone help us?

Here is our system configuration:

• Single server configuration.
• Intel Xeon 2.4GHz 2 HT Processors - x86 - 4GB RAM
• Windows Server 2003 SP2
• SQL Server 2005
• TFS 2008 SP1

Tks

Oberdan

---

Oberdan Borges Nunes

Answer 1

Hello Oberdan,

Is your data tier operating system 64 bit?
If so, you might encounter a known issue. Please read the blog for resolution:
http://blogs.msdn.com/dstfs/archive/2008/12/08/workitem-is-slow-when-doing-a-save-or-update-in-tfs2008.aspx

If it is not 64bit, please run SQLdiag utility (http://msdn.microsoft.com/en-us/library/ms162833.aspx) and reproduce the issue. Please check the tool's output.

 

Hongye Sun [MSFT]
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Hi Hongye, I forgot to mention that.
It's a x86 enviroment in a single server installation.
I'll try the SQLdiag.

Thanks in advance.

Oberdan

---

Oberdan Borges Nunes

Answer 3

I've just used the SQLdiag tool but I'm not able to find any error in the generated files.
Could you please help me with same tip of where I can look for?

Thanks in advance.

---

Oberdan Borges Nunes

Answer 4

Hi Oberdan,

Sorry for my delayed reply.

From my research, I didn't find any performance issue in TFS 2008 and SQL Server 2005. Here are some common suggestions that you may fix this issue:

1. Install latest service pack for SQL Server 2005 or upgrade to SQL Server 2008
2. Change to dual server install of TFS to separate DT from AT
3. Download SQL Server 2005 best practice analyzer tool to find performance suggestions
4. Follow the document at: http://technet.microsoft.com/en-us/library/cc966413.aspx to performance tuning your SQL server by Waits and Queues method which is a proven methodology that allows one to identify the best opportunities to improve performance.

If none of above works for you, you'd better contact Microsoft Support Professional at http://support.microsoft.com/select/Default.aspx?target=assistance.

Hongye Sun [MSFT]
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 5

Is this issue resolved? Thanks.


Hongye Sun [MSFT]
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 6

Hi Hongye, sorry for the delay.
No, the issue wasn't solved yet.
But I'm scheduling a server maintenance for install the SQL Server SP3.
Use a dual server is not an option for now, but we are looking for in the next
months.
I'll try the other tips you gave me, like sql best practice analyzer.

Thank you very much!

---

Oberdan Borges Nunes

Answer 7

Thanks for your reply, Oberdan.

Please let me know if you have any progress on this issue.

 

Hongye Sun [MSFT]
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.