Fortress Version 1.1.1.18060 web pages are way too slow

[Paul Kennedy]

Hi,
we are experiencing extremely slow web pages in the fortress item tracking. The act of opening the 'query' page is almost unworkably slow. Running on firefox on the server host (a quad core xeon with 6gb RAM, only MSSQL+ fortress running) takes as much as 30 seconds for the page to appear.

There are no obvious MSSQL error logs generated. The system is working.

I ran up the SQL profiler, and took a look. It looks like the stored procedure splistusers @projectid=105 is the offender. That stored procedure takes between 9 and 11 seconds to retrieve the list of users (we have about 125 active users )
The trouble is this stored procedure is run 3 times (for the reporter, assignee and resolver list boxes - Doh!), resulting in 30 seconds for the page to appear.

The SQL profiler says mssql did 334,722 reads for the stored procedure. Wow.

RPC:Completed exec splistusers @projectid=105 SourceGear Fortress NETWORK SERVICE NT AUTHORITY\NETWORK SERVICE 9172 334722 0 9253

Can you please help me resolve this? It is really killing us over here.

[Beth]

Can you tell me the OS on the server and the version of SQL that you are running?

What are the sizes of your database files (sgvault.mdf, sgvault_log.ldf, sgmaster.mdf, sgmaster_log.ldf, sgdragnet.mdf, sgdragnet_log.ldf)?

[Paul Kennedy]

Hi
I am running Windows Server 2003 R2.
I am running SQLServer 2005 V9.0.1399

SGVault.mdf = 239Mb
sgvault_log.ldf = 265Mb
sgmaster.mdf = 2Mb
sgmaster_log.ldf = 1.1Gb
sgdragnet.mdf = 3Mb
sgdragnet_log.ldf 768Kb

thanks for your support. I am really looking fwd to sorting this.

[Beth]

It looks like your transaction logs are a bit on the large side compared to your data files. Large transaction logs definitely slow things down. I have some instructions that I can send you on what to do with those. Could you send an email to support at sourcegear.com (attn: Beth) with a link to this forum thread? From there I'll send the directions directly to you.

[Paul Kennedy]

I have emailed my details to support@sourcegear.com.
regards

[Paul Kennedy]

Beth,
I am a bit worried about the difference between the sizes of sgmaster.mdf and sgmaster_log.LDF. The difference is enormous. I find it hard to believe the log file can get so huge yet the table is only a couple of megs

should i be worried?

[Beth]

You only need to worry if you are on SQL 2000, but SQL 2005 and SQL 2008 can deal with large log files. I've just seen where they can cause a performance hit. During backup those logs are supposed to shrink back down. I think SQL 2008 does a better job of that. You might just look into what your SQL backups are doing.

I've received your offline email and will be responding through there with instructions.
F: 222136