Wednesday, September 26, 2007

Assembly Load, LoadFrom, LoadFile

I think I am not very clear about those different loading functions, so I looked up a couple of different sources, and it helps a lot. Here are some understanding I summarized:

  1. If possible, it's better to call Load instead of LoadFrom since the later has to call the first anyway.
  2. LoadFrom actually calls the Load method: Internally, LoadFrom will firstly try to get the AssemblyName by call System.Reflection.AssemblyName.GetAssemblyName( path) method, then it will call Assembly's Load method, passing it the AssemblyName object. If load fails to find an assembly, then LoadFrom loads the assembly at the path name specified in the LoadFrom method. It is possible that there exist two different assemblies which have the same identity, so when LoadFrom internally calls Load, it may load an assembly which is different with the path you specified in the LoadFrom.
  3. LoadFile will load the assembly without CLR applying any policies or searching.
  4. When Load looks for the assembly, it will be based on the following searching path:
    1. GAC
    2. Application's base directory.
    3. Private path subdirectories.
    4. code base locations.
  5. Load has two versions, one is Load(AssembyName), the other is Load(String). When you use the the second method to load, your normally pass in a string like this "mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089". Remember, the first section of the string is name, which doesn't include the path and extension. If you choose to use AssemblyName to load, there are three situations.
    1. You didn't specify the codebase, but include other bits. If you don't specify the code base, and set the AssemblyName.Name, CultureInfo, public key token / public key and/or Version properties, it's essentially the same as the Load(String), because you just parse out that string and put it in individual fields.
    2. You specify the codebase, not the name. The code base will have the format look like "file:///C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll", and if you set the codebase, not the name, then, it's doing the same thing as Assembly.LoadFrom() method based on that code base.
    3. When both are specified, then we do step 1 firstly, and if step 1 failed, we do step 2. The thing which worth noting is when you call Assembly.Load(AssemblyName) with a codepath, it does not mean the assembly will be loaded from that path.

References:

Suzanne Cook's .NET CLR Notes

CLR via C#

Monday, September 17, 2007

SQL Server performance tuning.

My experience tells me that if your web applications (or even the windows applications) run slower, 90 percent of the time, it's because some sql queries, stored procedures, indexes on the tables are not optimized. It's very important that you can find which query, which table are running slowly.

The book "SQL Server Query Performance Tuning Distilled" by Sajal Dam is a very good book on this topic.

  1. Use the sql profiler to find what queries are taking long time, but I finds out it's not very pleasant to work with the user interface of the profiler. It's especially annoying even if you click on the stop button, the events are still being logged. (The stop command sent through the user interface is taking long time because the logging thread is very busy in logging the events in the sql server. This is a typical scenario when you try to kill a thread which is in a busy while loop.) The author provides an alternative way to do this, you can use the sql profiler to create a template or open an existing template, and stop the log immediately after starting the log (You only need do this once, so it won't be too painful). You don't have to do that, this step simply helps you to prepare the sql scripts. You can use sp_trace_create, sp_trace_setevent, sp_trace_setfilter to create all the scripts manually. In the profiler, you can script the trace definition and the step to run the trace to a SQL script file by selecting File->Script Trace -> For SQL Server 2000. SQLProfilerScript
  2. Open the saved script file in Query Analyzer, and modify the InsertFileNameHere parameter of sp_trace_create to a meaningful filename. ChangeScriptFileName
  3. Execute the sql script, which will start the SQL trace as a background process. It also returns the ID of the trace as traceid, which is very important and required to stop the trace later on. You can use the SELECT * FROM ::fn_trace_getinfo(default) to find the trace status.
  4. Use EXEC sp_trace_setstatus traceid, 0 to stop the trace and EXEC sp_trace_setstatus 1,2 to close the trace.
  5. Open the trace log file in the profiler and save it to a table, and you can query the cpu time, logical read, or anything else you selected in the profiling process.