Urgent: SQL Server PID Blocking

asills · Post by **asills** » Tue Jul 19, 2005 10:21 pm

No, nobody checks out entire folders to edit single files. Well, that's not entirely true, since Installshield 8 is a big POS and its source control "integration" effectively exports all the various portions of a merge module/installer into about 120 separate files. In order to edit anything in the InstallShield UI, you have to checkout the ENTIRE project. So yes, there are some cases where entire folders are checked out to edit one file. I would guess that about 80% of those 4600 items in that query are installshield files, which is probably about 30 merge modules that are currently checked out. So aside from InstallShield, pretty much everyone only checks out what is necessary (and even if they did, I would think Vault should be able to handle 10 users activity pretty easily).

Oh and I was wrong, the HDD isn't striped it's mirrored and it's basically just two 7200RPM drives with the windows OS mirroring.

I installed 3.1 yesterday evening (and gave it one day before posting), hoping to see some performance boost, and I didn't see any (and it just so happened that after installing 3.1 was when it got even worse).

As far as the other, general performance junk... A few minutes after I posted earlier I took the server down, defragged the hard drive (0% fragmented now), shrunk the DB file (since it had grown exponentially because somebody had been checking out/in binary installer files ~30MB for a while, so we deleted and obliterated them), ran all my regular maintenance routines (update statistics, reindex, etc).

I did a few tests and connecting to the server initially has become blazing fast compared to before (bringing up a repository list was always fast, but loading the tree always took a good 30 seconds; that's down to maybe 7 or 8 seconds). However, a checkout of a single file took 15 seconds and a checkin just as much (and the log files confirm my basic visual time estimation). The second time I did a checkout/in was quicker, but definitely not in what I'd consider expected.

Post by **jclausius** » Wed Jul 20, 2005 6:57 am

OK. Let's take a look at things through SQL profiler. I've attached a SQL Trace Template for SQL Server 2000. Can you run a trace with this template? Basically, start the Trace, and then do a login, check out, undo the check out lock and then log off. Save the trace and email it back to me.

We might have to dig deeper, but this will be a good starting point.

Post by **jclausius** » Wed Jul 20, 2005 7:12 am

One other thought - Has the user account been denied any part of the tree? Can the denial rights be removed or at least set to read-only?

If you do remove denied rights, does that help performance of checkouts?

asills · Post by **asills** » Wed Jul 20, 2005 9:29 am

jclausius wrote:One other thought - Has the user account been denied any part of the tree? Can the denial rights be removed or at least set to read-only?

If you do remove denied rights, does that help performance of checkouts?

I have folder security disabled right now. I disabled it the moment we saw performance problems. However, the security group for Engineering has full permission on $ and only read permission on a couple of our older branches we don't want people checking into without approval.

I'm going to run the profiler at 11 (central) and get back to you after that.

Post by **jclausius** » Wed Jul 20, 2005 9:35 am

Do you mean folder security for the repository is disabled?

asills · Post by **asills** » Wed Jul 20, 2005 9:36 am

Yes. It's disabled, just letting you know however that there are security groups applied to every user, but the repository folder security option is off.

Post by **jclausius** » Wed Jul 20, 2005 9:44 am

Is it safe to assume checkout / checkin performance is acceptable with security disabled?

asills · Post by **asills** » Wed Jul 20, 2005 9:51 am

jclausius wrote:Is it safe to assume checkout / checkin performance is acceptable with security disabled?

No.

asills wrote:I have folder security disabled right now. I disabled it the moment we saw performance problems.

It's been disabled for days now.

Post by **jclausius** » Wed Jul 20, 2005 10:10 am

The 15+ seconds is when folder security is disabled? That is surprising.

I'll await for your first profile run.

asills · Post by **asills** » Wed Jul 20, 2005 10:13 am

Well here's the trace file. spgetlockedfilechanges takes about 14 seconds to run, and it happens on getting the repository, checkout and undo checkout.

asills · Post by **asills** » Wed Jul 20, 2005 10:15 am

jclausius wrote:The 15+ seconds is when folder security is disabled? That is surprising.

I'll await for your first profile run.

Not only is folder security disabled on that repository, but I removed my account from all groups (so there should be no security applied to my account whether it's checked or not).

asills · Post by **asills** » Wed Jul 20, 2005 10:17 am

asills wrote:Well here's the trace file. spgetlockedfilechanges takes about 14 seconds to run, and it happens on getting the repository, checkout and undo checkout.

Also note that sqlserver.exe goes to to 25% CPU usage (the max it pretty much ever has with the 4 procs taskmgr reports me as having) for the entire duration of just about every operation on the server.

Post by **jclausius** » Wed Jul 20, 2005 10:21 am

I just sent an updated .sql file in a private message. I'll be updating / adding a new trace shortly.

Post by **jclausius** » Wed Jul 20, 2005 10:33 am

Here is the new trace file.

Post by **jclausius** » Wed Jul 20, 2005 10:37 am

When you get the results, can you email them to me? Just use the email button below, so I can respond with my address.