Improving Debugging Performance

Question

I've got SQL CLR debugging going.  It works, mostly.  But it is variably slow.  Two processes seem to be the problem.  The first is the remote debugger (msvsmon.exe).  This one only seems to be problematic when starting debugging, and even then the problem is variable, but it pretty much hoses my box when it does have a problem (CPU pegs at 100% for quite some time).  In fact, I'd say it does this more often than not, but how long it pegs varies greatly (one time it pegged for 10 minutes before I killed it).  I have no idea why, so I'd appreciate any insight there.  I have checked, and it is unblocked in Windows Firewall (this is on XP SP2).

By the way, I should mention that I am debugging a local instance of SQL server (on my workstation).

The second process that is problematic is lsass.exe.  This doesn't hose, but while my stuff is executing, it is very clearly the biggest CPU hog.  I'm assuming this is due to the built-in security features of the remote debugger (and I'm on a slow network in a domain).  So I can understand why it is happening; however, I want to be able to run the debugger with no authentication.  I know this is possible because msvsmon has switches that allow for that.  Since it is development local host on a secured network, I'm not concerned with the security feature and would rather just turn it off.  Unfortunately, I don't see how to configure the SQL Server Project to do that.  Please let me know if there's a way to do this.

If not, does anyone have any ideas?  I've tried to configure a standard code library to debug SQL CLR, but when I attach to the sqlsvr process, it shows that no symbols are loaded, and when I try to manually load the (right) symbols, it says they don't match.  I'd be okay with starting msvsmon directly if I could take advantage of noauth that way.

Looking forward to any suggestions.

Answer 1

Okay, I figured out how to solve these problems, sorta.  I found a different (better) approach to debugging that allows me to "step into" debugging SQL from my app, which is what I want to do anyways.  Here are the steps I went through to set this up (in not necessarily the same order I did them).

1. Made the gp changes in this blog: http://blogs.msdn.com/vsdata/archive/2004/10/26/248118.aspx.

2. Created a connection in server explorer to my test db.

3. Marked the connection as enabled for CLR debugging and application debugging (right-click on connection to see these options).

4. Marked the application project as enabled for SQL debugging (check box on the Debug tab of the project properties).

5. Added the PDB file to the SQL assembly registration; see the ALTER ASSEMBLY ADD FILE command to add a file for an assembly.

6. Manually run the remote debugger (msvsmon.exe) and configure it to not require authentication—this is just a perf thing; having to authenticate everything was churning lsass.exe a lot and slowing debugging down.

I think if you do all this, you should be able to debug stored procs while debugging  the app.  Just keep the limitations from here in mind.  As you can imagine, I tweaked and tried a lot of stuff while trying to get this to work, so that’s why I say I think this should do it.  I may be forgetting something, but I don’t think so.  I also found this guide helpful.

Answer 2

Sadly... didn't help... Still can not debug via a test script....

Do to another reason, we rebuilt the system from a fresh format. 
So after Windows 2003 Enterprise was installed, then SP1 for 2003... Then SQL2005, Then Visual Studio 2005  (the latest versions of both)....

Same results... Hangs up when it gets to the CLR object...

Any other ideas... As this is a VERY important aspect of the Visual Studio / SQL Development enviornment for us.....