Showing posts with label Performance. Show all posts
Showing posts with label Performance. Show all posts

Wednesday, February 14, 2007

Sudden SQL error during INSERT/UPDATE operation (ARITHABORT)

Your database has been running fine for the longest time. All of a sudden INSERT or UPDATE operations fail with an error similar to the following.

UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'

Quick research tells you to simply add the following line right after you open a connection to the database or at the start of your query.

SET ARITHABORT ON

This seems to make things work again, but have you really solved the problem?

It turns out that this error is caused when you create an Indexed View. But I didn't do it? Well, if you run either the Index Tuning Wizard (SQL Server 2000) or Database Engine Tuning Advisor (SQL Server 2005), they will create what are called "Hypothetical Materialized Views" or index views. These views start with the prefix _hypmv. When the analysis is finished these views are supposed to be discarded. However, if for whatever reason these views are not deleted, any attempts to INSERT or UPDATE into the table(s) on which this indexed views were created, the error message relating to incorrect ARITHABORT setting will occur.

In my particular case, the reason why these views were not discarded was because my account had enough privileges to create these views but not delete them. Running the tuning tools would leave these indexed views behind without me realizing it.

So, if you see this error it may be that you have left behind some indexed views you did not create directly. Deleting them should get rid of the error.

Read full post...

Sunday, February 4, 2007

Improve performance when using WindowsTokenRoleProvider with lots of roles

This post got longer than anticipated. So, I broke it down into sections so you can dive right in.</> You don't need to read the Preface section if you're already familiar with how ASP.NET 2.0 implements authentication/authorization.

Preface

ASP.NET 2.0 has built-in support for Authentication and Authorization via its Provider Model. It comes with ready-to-use classes for Integrated Windows Authentication and for role management via WindowsTokenRoleProvider. You can read this article for all the details on how to configure these and other providers to secure your application. This post is focusing only on authentication and authorization against Active Directory (AD).

The short of securing your application is to add the following to your Web.config file.

<authentication mode="Windows" />
<roleManager enabled="true" defaultProvider="AspNetWindowsTokenRoleProvider" />


This will enable authentication using the user's current identity and authorization using the user's AD Group Memberships as the roles.

To ensure IIS passes the user's identity to ASP.NET you need to enable it for the site by setting the Integrated Windows Authentication flag in the Authentication Methods dialog of the site properties Directory Security tab.

IIS Directory Security

Once authentication/authorization is enabled, ASP.NET will verify every HTTP request to ensure only allowed content is returned to the browser. It does this by checking the authorization rules you specify either in your Web.config file on as part of a site map definition that has trimming enabled.

Normally, the process of verifying access to different pages in you application will happen very fast. Nonetheless, you can release some of the burden of accessing AD for every request by enabling caching in the roleManager. To do this you can set the attribute cacheRolesInCookie in the roleManager definition.

<roleManager enabled="true" cacheRolesInCookie="true" defaultProvider="AspNetWindowsTokenRoleProvider" />

This will store the roles for the logged in user in a cookie that gets sent back and forth between server and client and back.

Problem

As you may already know, cookies have a limit of 4096 bytes. If you enable caching in the roleManager and your users have deeply nested group memberships in AD, this limit can be easily exhausted. The result is very slow performance even when caching is enabled.

Solution

One possible way to get around this limitation is to extend the default WindowsTokenRoleProvider to compress the contents of the cookie. The drawback I see with this approach is that the cookie is traveling back and forth from client to server to client and back. Another drawback is the fact that custom code to parse the contents of the cookie needs to be written in order to extract the roles before passing them to the base implementation.

My preferred solution still involves writing custom code, but not to parse any cookies. The implementation still extends the WindowsTokenRoleProvider, but performs the caching on the server side instead, using the HttpContext.Cache as shown in the following sample code.

using System.Web;
using System.Web.Caching;
using System.Web.Security;

// Recommended namespace is YourApplication.Web.Security. Essentially create a project that
// follows the same namespace naming conversion as ASP.NET but instead of System use your
// application name.
namespace YouNamespace
{
/// <summary>
/// Provides role information for an ASP.NET application from Windows group membership.
/// </summary>
/// <remarks>
/// Overriden to add proper caching of roles.
/// </remarks>
class WindowsTokenCachingRoleProvider : WindowsTokenRoleProvider
{
/// <summary>
/// Required for provider model.
/// </summary>
public WindowsTokenCachingRoleProvider()
{
}
/// <summary>
/// Gets a list of the Windows groups that a user is in.
/// </summary>
/// <param name="username">
/// The user to return the list of Windows groups for in the form DOMAIN\username.
/// </param>
/// <returns>
/// A string array containing the names of all the Windows groups that the specified
/// user is in.
/// </returns>
public override string[] GetRolesForUser(string username)
{
// List of Windows groups for the given user.
string[] roles;

// Create a key for the requested user.
string cacheKey = username + ":" + base.ApplicationName;

// Get the cache for the current HTTP request.
Cache cache = HttpContext.Current.Cache;
// Attempt to fetch the list of roles from the cache.
roles = cache[cacheKey] as string[];
// If the list is not in the cache we will need to request it.
if (null == roles)
{
// Allow the base implementation to load the list of roles.
roles = base.GetRolesForUser(username);
// Add the resulting list to the cache.
cache.Insert(cacheKey, roles, null, Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration);
}

// Return the resulting list of roles.
return roles;
}
}
}

With this code in place, you can now replace the default roleManager definition with the newly extended WindowsTokenCachingRoleProvider by replacing the corresponding Web.config file entry with the following.

<roleManager enabled="true" defaultProvider="AspNetWindowsTokenCachingRoleProvider">
<providers>
<add name="AspNetWindowsTokenCachingRoleProvider" applicationName="/" type="YouNamespace.WindowsTokenCachingRoleProvider, YourAssembly" />
</providers>
</roleManager>

Using this method I have seen major improvements for those users who happen to be members of many AD groups.

Read full post...