Urgent: SQL Server PID Blocking

[jclausius]

Also, on the SQL maintenance routine.

By default, all commands are commented out in my posting. Please verify that you have uncommented only one of the commands (UPDATE STATISTICS, DBCC DBREINDEX, or DBCC INDEXDEFRAG)

[asills]

Let's narrow things down a bit.

Try:

EXEC sgvault.dbo.spdeletecheckoutlists @repid = 2, @daysold = 5

Then re-try the get lock changes from above.

Well just the spdeletecheckoutlists has taken 13 minutes so far and I'm not quite sure what's going on (should it take that long?). The sqlserver.exe process is anywhere between 1% and 3% of total CPU usage (sometimes 0 actually). Taking a peek at the SPID for this command has 4601 locks (nearly all are tempdb EXT locks).

Not sure what other info to give you about why this command is taking so long to execute.

[asills]

Also, on the SQL maintenance routine.

By default, all commands are commented out in my posting. Please verify that you have uncommented only one of the commands (UPDATE STATISTICS, DBCC DBREINDEX, or DBCC INDEXDEFRAG)

Each one is done separately (so all reindex, then all update statistics, then all defrag).

[asills]

Let's narrow things down a bit.

Try:

EXEC sgvault.dbo.spdeletecheckoutlists @repid = 2, @daysold = 5

Then re-try the get lock changes from above.

Well just the spdeletecheckoutlists has taken 13 minutes so far and I'm not quite sure what's going on (should it take that long?). The sqlserver.exe process is anywhere between 1% and 3% of total CPU usage (sometimes 0 actually). Taking a peek at the SPID for this command has 4601 locks (nearly all are tempdb EXT locks).

Not sure what other info to give you about why this command is taking so long to execute.

Okay it finished after 23 minutes. The second query (spgetlockedfilechanges) took 11 seconds and returned 2 rows.

[jclausius]

Try the spdeletecheckoutlists again (with days old = 5).

This shouldn't take that long... How long does it take to finish now?

[asills]

9 seconds that time

[jclausius]

If you try the operations, are you still blocked now?

[asills]

If you try the operations, are you still blocked now?

NO! I can checkout/checkin/undo perfectly now. Thank you!

Now, can you by chance tell me any more info and/or what we just actually did so I can communicate to the team what they need to do if anything (such as we just deleted their checkout lists...?)

[jclausius]

Also, on the maintenance routines, I'd make the following recommendations.

1) Run the loop using UPDATE STATISTICS about once a week or once every other week. If you have 50+ users, once a week should be sufficient.

2) Run the loop using INDEXDEFRAG (first) and then UPDATE STATISTICS (second) about once a month or every other month.

3) Run the loop using DBREINDEX about every 3 months.

Note, this is a general suggestion, so please modify this to better fit your needs.

[jclausius]

I can checkout/checkin/undo perfectly now. Thank you!

No problem.

Now, can you by chance tell me any more info and/or what we just actually did so I can communicate to the team what they need to do if anything (such as we just deleted their checkout lists...?)

The Vault server maintains a "version" of each checkout list. So when a client refreshes their folders, it communicates the current list and the server calculates the changes back to the client.

In any case, as time passes, the importance of these lists decreases. So every so often, the server will remove lists older than X days (where X in your case was 5 days). For some reason, removing lists older than 5 days old was taking a really long time.

Is it possible there were some really, really big checkout lists (hundreds of thousands or millions checked out items) from 5+ days ago? Deleting that big of a chunk from the server might explain things.

[asills]

I don't think we could have done hundreds of thousands or millions of checkouts. We're in a pretty active development cycle and have probably 80 projects with an average of 25 files per project, plus a few branches plus about 75 installshield merge modules (which end up being like 110 files in the source control), but even with all that added up, we would have had to check everything out numerous times.

[ismangil]

Also, on the maintenance routines, I'd make the following recommendations.

1) Run the loop using UPDATE STATISTICS about once a week or once every other week. If you have 50+ users, once a week should be sufficient.

2) Run the loop using INDEXDEFRAG (first) and then UPDATE STATISTICS (second) about once a month or every other month.

3) Run the loop using DBREINDEX about every 3 months.

Note, this is a general suggestion, so please modify this to better fit your needs.

Jeff,

Are there any summary of db maintenance suggestions? I seem to pick up bits here and there, would like a succinct "maintenance cycle" procedures.

[lbauer]

See this link for Jeff's "best practices" for Vault/Dragnet maintenance:

http://support.sourcegear.com/viewtopic.php?t=2924

[asills]

So here I am a while later and the Vault server's performance has degraded significantly. It was great for a few days then today it has taken upwards of 5 minutes to complete a checkout and two or three times that long to complete a checkin.

I ran the suggested sql commands again and I'm down to 40 or so seconds for a checkout and 15 or so for an undo checkout (both numbers seem to vary greatly, but never perform /quickly/).

I also ran the spgetlockedfilechanges query and received 4638 rows (the 'lockwhen' column returns a wide date range between 2/22/2005 and today's date). Not sure what that means, but I figured I'd tell you (also not sure why I only saw 2 rows last time I ran it, maybe I missed something).

I really want to try and figure out why I keep seeing this performance degradation. The server itself is a decent box (win2k3, xenon 2.4ghz, 1.5gb ram, striped normal-speed HDD) and for the most part reports itself as in good health (I'm having trouble with the sql mdf files being overly fragmented, but I think I'm stuck with that). Could someone on my team here be doing something that you could identify as something that would cause a gradual degradation from last week?

[jclausius]

Could someone on my team here be doing something that you could identify as something that would cause a gradual degradation from last week?

Can you describe your process? It is recommended people only checkout files they intend to edit. Do people checkout entire folders, modify one or two files, and then check in the folder? That would explain a large list, but it wouldn't necessarily explain the performance degredation.


If the database is on a badly fragmented drive, that may be another part of the explanation. As your database grows, SQL Server will allocate new space for the database files. If that drive is on a badly fragmented disk, performance will be degraded when SQL Server expands the file.

Defragging the hard drive where the database files live (with SQL Server shutdown) would be one recommendation.

Secondly, we've made some changes to the index of the checkout list tables in Vault 3.1 Server. I'd also recommend upgrading to Vault 3.1

If you're still experiencing problems, we may need to take a deeper look at the database through a profiler. This would give us a better understanding of exactly what is occuring on in your paticular setup.