Improve Performance in SQL Server

Marking a column as a primary key will automatically create a unique index on the column.

A term that is often used is "SARGable". This basically refers to a condition in a WHERE clause that is able to use an index if one exists.

Examples of SARGable conditions are: A = x, B LIKE 'AB%', C IN (x, y, z). In each of these the optimizer is able to use an index. Examples of Non-SARGable conditions are: A <> x, B LIKE '%AB', C NOT IN (x, y, z).

If you think about each of these it should be obvious why an index cannot be used. To explain why B LIKE '%AB' is bad, I like to use the analogy of a large dictionary. If you know what letters the word starts with you can find it very quickly. If you only know what letters it ends with you have no option but to read the entire dictionary and check every single word.

You should also avoid using NOT in your WHERE clauses. NOT IN, NOT LIKE and IS NOT NULL perform a scan for exactly the reasons given above. The exception may be NOT EXISTS, which can perform very well for many queries.

One very common mistake is to use functions in such a way that a scan is performed. A condition of the form WHERE Fn(A) = x will not use an index on A, as the function has to be applied to this column in every row before it can be compared with x.

This needs an example to explain properly: suppose you want to match rows added in the last 5 days. I often see this written as follows:

This should be rewritten as follows:

There may be situations in which use of a function is unavoidable. In this case you can add a computed column that uses the function, add an index to the computed column, and refer to the computed column directly in the query.

Another common mistake is due to the way indexes are used in an OR condition. This is described in OR Condition Performance

Lambda Expressions

Lambda expressions are simply functions/methods.

They have a different syntax, primarily so that they can be written in expression context (more on this shortly) instead of as a member of a class.  However, that is all they are.  For instance, the following lambda expression:

c => c + 1

is a function that takes one argument, c, and returns the value c + 1.

Difference Between WebFarm and Web Garden

Web Farm: It is a multi-server scenario and we can keep servers separately. If the load on one server is in excess then the other servers step in to bear the strength. Server Load is based on various following models.

1. Round Robin. (All servers share load equally)

2. Network Load Balancer (economical)

3. HLB (expensive but can scale up to 8192 servers)

4. Hybrid (2 and 3).

5. CLB (Component load balancer).

We can implement web farms in .NET. Open web.config file and add mode options.

i) if mode=inproc (non web farm but fast when you have very few customers).

ii) if mode=StateServer (for web farm)

iii) if mode=SqlServer (for web farm)

Whether to use option (ii) or (iii) depends on situation. StateServer is faster but SqlServer is more reliable and used for critical applications.

Web farms means many Application of Multiple server on different location on the world. for example: www.yahoo.in, www.yahoo.uk

Web Garden:

WebGarden is the scenario in which a single physical machine is used for multiple worker processes running simultaneously. Each worker process is responsible for handling all kinds of requests, responses, session data, cache data, etc. and all the ASP.NET functionality inside IIS runs under the scope of the worker process. By default, every application pool contains a single worker process. A WebSite containing multiple worker processes is called a WebGarden.

Put simply – if you increase the number of worker processes for a given ApplicationPool you get a WebGarden.

Generally it is a multi-processor setup i.e. in case of a web garden we have a one server having more than one processor. You can use web gardens in .Net as: Open web.config file and add webGarden = true in process model tag.

If you enable Web-garden mode by setting the webGarden attribute to true in the processModel element of the application's Web.config file, do not use InProc session state mode. If you do, data loss can occur if different requests for the same session are served by different worker processes.

More than one worker process is a "web garden." In-process session state will not work correctly. You'll need to use either a single worker process for your web app, or use a session state server, or SQL Server for session state.

if we configure an ASP.Net application pool with more than one worker process (web garden), they do not share session object, application object or ASP.net intrinsic cache object. If I want all these processes to share one single cache, is it possible to configure at all some way or the other?

Web Gardens means many application of single server.

for example www.mail.yahoo.com, www.chat.yahoo.com