ASP.NET Tips #20 - Don’t call AsEnumerable on a collection before using LINQ

When you're using an ORM that has LINQ to SQL, such as Entity Framework, do not call AsEnumerable on the collection before using LINQ, even if that gives you options that are easier to work with.

If you do, it means your LINQ query is run at web server, rather than converted to SQL and performed on the database server.

Sample C# code:

//So we have this Car class
public class Car
{
  public string Make { get; set; }
  public string Model { get; set; }
  public int Year { get; set; }
  public string Color { get; set; }
}

//In some class or form or service that utilizes this process...
//this method has all the plumbing inside to fill and return a dataset
DataSet dataSet = _dataBase.ExecuteDataSet("sproc_GetMeSomeCarData");
DataTable dataTable = dataSet.Tables[0];

//Now call the new extension passing the Car object for less cumbersome, normalized LINQ-to-Object syntax
var nissanCars = from c in dataTable.AsEnumerable()
                 where c.Make == "Nissan"
                 select c;

ASP.NET Tips #19 - Monitoring memory consumption over time

When you're hunting for a memory leak, it's easy to fall into the trap of not profiling for long enough. Consider this curve:

On the face of it, it looks like we might have a memory problem. But of course, garbage collection only happens periodically, so it’s perfectly legitimate for browser memory usage to increase over time. What matters is the pattern you see when garbage collections occur. You expect a sawtooth pattern, a bit like this:

If the sawtooth stays flat, as in the example above, you probably don’t have a leak. On the other hand, if it's increasing, as in the example below, you probably do:

The important point is to make sure you profile long enough to see the whole curve. Otherwise, you might mistakenly believe you have a memory leak, and misidentify the cause as objects that will be garbage collected.

ASP.NET Tips #18 - Where are your custom performance counters?

Performance counters are a great way to watch how you are using .NET, IIS, the database, and much more on the operating system. However, the real benefits of performance counters come when you start using them to record data that's unique to you, such as how many logins you've had, what's the average time for your web service to process a specific request, and anything else you can imagine.

With .NET's excellent PerformanceCounter class doing the work for you, creating performance counters is so simple a manager could do it. By spending no more than an hour on planning and implementation, you can get a ton of actionable information out of your application in production. Performance counters are cheap, easy, and you can never have enough.

However, it could be interesting to broaden the scope to get key values in order to have a global vision of how the whole server is behaving instead of just IIS or ASP.NET. You need to check CPU, Memory, etc and put this in balance with your application and IIS.

Here is a non-exhaustive list of counters you could use to get this vision:

For IIS

Memory:

• Available Mbytes: Allows you to see the available memory. It's important to be sure the server isn't undersized for the needs of the application
• % Commited Bytes In Use: Allows you to see the used memory. It's interesting to put this in balance with the Available Mbytes counter

Process (For all W3WP.exe processes):

• % Processor Time: Allows you to see the CPU consumption for a given process
• Virtual Bytes: Allows you to see the virtual memory for the process W3WP.exe
• Private bytes: Allows you to see the private memory for the process W3WP.exe

Processor (All instances):

• % Processor Time: Allows you to put in balance the total CPU consumption with each W3WP.exe. For example, if your server is consuming 90% of CPU and the sum of the W3WP.exe CPU consumption is 10%, you clearly have an issue elsewhere than IIS

HTTP Service Request Queues (All instances):

• CurrentQueueSize: Allows you to see the size if the HTTP Kernel side queue and thus to see if a huge number of requests are getting queued without being handled by the User Mode side
• RejectedRequests: Allows you to see if requests are rejected from Kernel side without being handled by the User Mode side

APP_POOL_WAS (For all listed Application Pools):

• Current Application Pool State: Allows you to see the state of an Application Pool
• Current Application Pool Uptime: Allows you to see if the Application has been restarted or not (relay useful during a load test)

For ASP.NET

ASP.NET Applications (For all applications you want to monitor):

• Compilations Total: Allows you to see the number of compiled pages
• Request Bytes In Total: Allows you to see the number of received bytes
• Request Bytes Out Total: Allows you to see the number of sent bytes
• Request Execution Time: Allows you to see the execution time for the most recent request
• Request Wait Time: Allows you to see the time spent in the queue before being handled for the most recent request
• Requests Executing: Allows you to see the number of requests being executed
• Request in Application Queue: Allows you to see the number of requests in the queue
• Requests Timed Out: Allows you to see the number of timed-out requests
• Requests/Sec: Allows you to see the number of requests executed per seconds
• Sessions Active: Allows you to see the number of active sessions

ASP.NET V4.0.30319:

• Application Restarts: Allows you to see the number of restarts for the Application Domain

With all this information, you should be able to determine the threshold where your application is behaving as expected and the threshold where problems should start to occur.

ASP.NET Tips #17 - General and HTML tips

1. Including height and width in <img /> tags will allow your page to render more quickly, because space can be allocated for the image before it is downloaded.
2. Add script references at the bottom of the page before </body>, because asynchronous downloads halt when a script reference is reached. Style sheets and images can be downloaded asynchronously in <head>.
3. Use a content delivery network (CDN) for hosting images and scripts. They may be cached and it will reduce load on your server.
4. Use image sprites to retrieve smaller images in one download.
5. Use AJAX to retrieve components asynchronously that may not be needed immediately, such as the content of a collapsed panel, content behind a tab, and so on.
6. Make sure you've removed HTTP modules that aren't being used (Windows authentication, for example), and that you've disabled services such as FTP and SMTP, if you're not using them.
7. Make sure HTTP compression is turned on for any uncompressed content. HTML in particular compresses significantly, and in this era of mobile friendliness and slow 3G connections, that’s essential.
8. Always set the CacheControlMaxAge attribute in web.config to a high number (a year is good). You don't want people pulling down the same static content they did last week. It'll also save on the bandwidth you’re paying for.
9. Make use of the OutputCache annotation on MVC controllers. If the server can serve from memory, rather than going to disk or database, that's a good win.

ASP.NET Tips #16 - Use StringBuilder or String.Join for string concatenation?

If you are in a loop and adding to a string, then a StringBuilder *could* be most appropriate. However, the overhead of spinning up a StringBuilder instance makes the following pretty dumb:

   var sb = new StringBuilder();
   sb.Append("Frankly, this is ");
   sb.Append(notMoreEfficient);
   sb.Append(". Even if you are in a loop.");
   var whyNotJustConcat = sb.ToString();

Instead, use String.Join, which is typically more effective than spinning up a StringBuilder instance for a limited number of strings.

   string key = String.Join(" ", new String[] { "This", "is", "a", "much", "better", solution, "."});

The first variable of " " can just be set to "" when you don't want a delimiter.

My rule of thumb is to add strings together when got one to five of them (likewise with String.Form if it helps with legibility). For most other cases, I tend to use String.Join. Only when dealing with a loop that isn't limited to about 10 iterations, I prefer to use StringBuilder.

ASP.NET Tips #15 - SQL Index tips

Indexing tables is not an exact science. It requires some trial and error combined with lots of testing to get things just right. Even then, the performance metrics will change over time as you add more and more data.

1. You get exactly one clustered index on a table. Ensure you have it in the right place. First choice is the most frequently accessed column, which may or may not be the primary key. Second choice is a column that structures the storage in a way that helps performance. This is a must for partitioning data.
2. Clustered indexes work well on columns that are used a lot for 'range' WHERE clauses such as BETWEEN and LIKE, where it is frequently used in ORDER BY clauses or in GROUP BY clauses.
3. If clustered indexes are narrow (involve few columns) then this will mean that less storage is needed for non-clustered indexes for that table.
4. You do not have to make the primary key the clustered index. This is default behavior but can be directly controlled.
5. You should have a clustered index on every table in the database. There are exceptions, but the exceptions should be exceptional.
6. Avoid using a column in a clustered index that has values that are frequently updated.
7. Only create non-clustered indexes on tables when you know they’ll be used through testing. You can seriously hurt performance by creating too many indexes on a table.
8. Keep your indexes as narrow as possible. This means reducing the number and size of the columns used in the index key. This helps make the index more efficient.
9. Always index your foreign key columns if you are likely to delete rows from the referenced table. This avoids a table scan.
10. A clustered index on a GUID can lead to serious fragmentation of the index due to the random nature of the GUID. You can use the function NEWSEQUENTIALID() to generate a GUID that will not lead to as much fragmentation.
11. Performance is enhanced when indexes are placed on columns used in WHERE, JOIN, ORDER BY, GROUP, and TOP. Always test to ensure that the index does help performance.
12. If a non-clustered index is useful to your queries, but doesn't have all the columns needed by the query, you can consider using the INCLUDE option to store the extra columns at the leaf level of the index.
13. If temporary tables are in use, you can add indexes to those tables to enhance their performance.
14. Where possible, make the indexes unique. This is especially true of the clustered index (one of the reasons that the primary key is by default clustered). A unique index absolutely performs faster than a non-unique index, even with the same values.
15. Ensure that the selectivity of the data in the indexes is high. Very few unique values makes an index much less likely to be used well by the query optimizer.
16. It is almost always better to let SQL Server update statistics automatically.

ASP.NET Tips #14 - T-SQL tips

While much of your code may be generated, at least some of it will have to be written by hand. If you are writing some or all of your T-SQL code, these tips will help you avoid problems.

1. SELECT * is not always a bad thing, but it's a good idea to only move the data you really need to move and only when you really need it, in order to avoid network, disk, and memory contention on your server.
2. Keep transactions as short as possible and never use them unnecessarily. The longer a lock is held the more likely it is that another user will be blocked. Never hold a transaction open after control is passed back to the application – use optimistic locking instead.
3. For small sets of data that are infrequently updated such as lookup values, build a method of caching them in memory on your application server rather than constantly querying them in the database.
4. If doing processing within a transaction, leave the updates until last if possible, to minimize the need for exclusive locks.
5. Cursors within SQL Server can cause severe performance bottlenecks. Avoid them. The WHILE loop within SQL Server is just as bad as a cursor.
6. Ensure your variables and parameters are the same data types as the columns. An implicit or explicit conversion can lead to table scans and slow performance.
7. A function on columns in the WHERE clause or JOIN criteria means that SQL Server can't use indexes appropriately and will lead to table scans and slow performance.
8. Don't use DISTINCT, ORDER BY, or UNION unnecessarily.
9. Table variables have no statistics within SQL Server. This makes them useful for working in situations where a statement level recompile can slow performance. But, that lack of statistics makes them very inefficient where you need to do searches or joins. Use table variables only where appropriate.
10. Multi-statement user-defined functions work through table variables. Because of this, they also don't work well in situations where statistics are required. If a join or filtering is required, avoid using them, especially if you are working with more than approximately 50 rows in the data set.
11. Query hints are actually commands to the query optimizer to control how a query plan is generated. These should be used very sparingly and only where appropriate.
12. One of the most abused query hints is NO_LOCK. This can lead to extra or missing rows in data sets. Instead of using NO_LOCK consider using a snapshot isolation level such as READ_COMMITTED_SNAPSHOT.
13. Avoid creating stored procedures that have a wide range of data supplied to them as parameters. These are compiled to use just one query plan.
14. Try not to interleave data definition language with your data manipulation language queries within SQL Server. This can lead to recompiles which hurts performance.
15. Temporary tables have statistics which get updated as data is inserted into them. As these updates occur, you can get recompiles. Where possible, substitute table variables to avoid this issue.
16. If possible, avoid NULL values in your database. If not, use the appropriate IS NULL and IS NOT NULL code.
17. A view is meant to mask or modify how tables are presented to the end user. These are fine constructs. But when you start joining one view to another or nesting views within views, performance will suffer. Refer only to tables within a view.
18. If you need to insert many rows at once into a table, use, where possible, the multi-row VALUES clause in INSERT statements.
19. Use extended events to monitor the queries in your system in order to identify slow running queries. If you’re on a 2005 or earlier system you’ll need to use a server-side trace.

ASP.NET Tips #13 - Database design tips

Again, you may not be delving much into this, but there are a few tips to keep in mind.

1. While it is possible to over-normalize a database, under-normalization is much more prevalent. This leads to repetition of data, inefficient storage, and poor performance. Data normalization is a performance tuning technique as well as a storage mechanism.
2. Referential integrity constraints such as foreign keys actually help performance, because the optimizer can recognize these enforced constraints and make better choices for joins and other data access.
3. Unique constraints also help performance because the optimizer can use these in different ways to enhance its query tuning.
4. Make sure your database doesn't hold 'historic' data that is no longer used. Archive it out, either into a special 'archive' database, a reporting OLAP database, or on file. Large tables mean longer table scans and deeper indexes. This in turn can mean that locks are held for longer. Admin tasks such as Statistics updates, DBCC checks, and index builds take longer, as do backups.
5. Separate out the reporting functions from the OLTP production functions. OLTP databases usually have short transactions with a lot of updates whereas reporting databases, such as OLAP and data warehouse systems, have longer data-heavy queries. If possible, put them on different servers.

ASP.NET Tips #12 - Turn off change tracking in Entity Framework (EF)

If you have to query a database for some read-only data in Entity Framework, make sure that Change Tracking is turned off, so you're not tracking individual objects or object graphs whose state won't change. Use some code along these lines to do it:

Context.MyCollection.AsNoTracking().where(x => x.Id);

For Example:

using (var context = new OrdersContext())
{
   // Query for all orders without tracking them
   var allOrders = context.Orders.AsNoTracking().ToList();

   // Query for some orders without tracking them
   var someOrders = context.Orders
      .Where(b => b.Name.Contains("iPad"))
      .AsNoTracking()
      .ToList();
}

AsNoTracking() is an extension method defined on IQueryable, so you'll need to import the System.Data.Entity namespace. For more details, to understand the performance gains of AsNoTracking(), have a look at:

http://msdn.microsoft.com/en-us/library/cc853327.aspx
http://blog.staticvoid.co.nz/2012/4/2/entity_framework_and_ asnotracking

ASP.NET Tips #11 - ORM tips for developers

More and more people are using Object to Relational Mapping (ORM) tools to jump the divide between application code that is object oriented and a database that is storing information in a relational manner. These tools are excellent and radically improve development speed. But, there are a few 'gotchas' to know about.

1. Avoid following the 'Hello World' examples provided with your ORM tool that turns it into an Object to Object Mapping. Database storage is not the same as objects for a reason. You should still have a relational storage design within a relational storage engine such as SQL Server.
2. Parameterized queries are exactly the same as stored procedures in terms of performance and memory management. Since most ORM tools can use either stored procedures or parameterized queries, be sure you’re coding to these constructs and not hard-coding values into your T-SQL queries.
3. Create, Read, Update, and Delete (CRUD) queries can all be generated from the ORM tool without the need for intervention. But, the Read queries generated are frequently very inefficient. Consider writing a stored procedure for complex Read queries.
4. Since the code generated from the ORM can frequently be ad hoc, ensure that the SQL Server instance has 'Optimize for Ad Hoc' enabled. This will store a plan stub in memory the first time a query is passed, rather than storing a full plan. This can help with memory management.
5. Be sure your code is generating a parameter size equivalent to the data type defined within table in the database. Some ORM tools size the parameter to the size of the value passed. This can lead to serious performance problems.
6. Always profile your ORM database hits with SQL Profiler during development. ORMs get away from you very quickly. Before you know it, you've run a query 2000 times in a loop, when you could have retrieved all your data with a single database hit.
7. Watch out for lazy loading in ORMs. You shouldn't lazy load any entities that could be retrieved with a single database hit.
8. Implement different database queries in different contexts. In the API and on a webpage, you'll inevitably require different entity properties, so don't load things you don't need just because it's convenient to reuse a query.
9. Using compiled queries on top of ObjectContext or DBContext can significantly improve application performance, because you save much of the time that Entity Framework would have to spend compiling your query to SQL.

ASP.NET Tips #10 - Database performance tips for developers

As a developer you may or may not need to go into the database and write queries or design tables and indexes, or help determine configuration of your SQL Server systems. But if you do, these tips should help to make that a more pain free process.

T-SQL Tips

While much of your code may be generated, at least some of it will have to be written by hand. If you are writing some, any, or all of your T-SQL code manually, these tips will help you avoid problems.

SELECT * is not necessarily a bad thing, but it's a good idea to only move the data you really need to move and only when you really need it, in order to avoid network, disk, and memory contention on your server.

For small sets of data that are infrequently updated such as lookup values, build a method of caching them in memory on your application server rather than constantly querying them in the database.

Ensure your variables and parameters are the same data types as the columns. An implicit or explicit conversion can lead to table scans and slow performance.

Index Tips

Indexing tables is not an exact science. It requires some trial and error combined with lots of testing to get things just right. Even then, the performance metrics will change over time as you add more and more data.

You get exactly one clustered index on a table. Ensure you have it in the right place. First choice is the most frequently accessed column, which may or may not be the primary key. Second choice is a column that structures the storage in a way that helps performance. This is a must for partitioning data.

Performance is enhanced when indexes are placed on columns used in WHERE, JOIN, ORDER BY, GROUP, and TOP. Always test to ensure that the index does help performance.

Database Design Tips

Again, you may not be delving much into this, but there are a few tips to keep in mind.

While it is possible to over-normalize a database, under-normalization is much more prevalent. This leads to repetition of data, inefficient storage, and poor performance. Data normalization is a performance tuning technique as well as a storage mechanism.

Make sure your database doesn't hold 'historic' data that is no longer used. Archive it out, either into a special 'archive' database, a reporting OLAP database, or on file. Large tables mean longer table scans and deeper indexes. This in turn can mean that locks are held for longer. Admin tasks such as Statistics updates, DBCC checks, and index builds take longer, as do backups.