Re: Web Service Session State

I assume you're running at least SQL Server 2005 SP2, and preferably
2008 at this point. DBCC Pintable was removed in 2005 so this has no
effect. You really don't want to mess with the memory allocation of SQL
Server unless you _really_ know what you're doing.

You could perhaps add in in-proc session-state caching of your session.
When a request comes in look in session for the login information. If
it's there then you just use it, if not then go look at the DB and
retrieve it. Of course this assumes your clients are returning the
cookies needed to maintain session state. If not then each request is a
new session. Speaking of which, you could also disable asp.net session
state all together if you aren't using it. That will also help
performance.

If you don't have session state then you could add a dictionary into the
application context and cache the session info there, if it's in the
dictionary use it, if not go check the DB. But then you're using a
dictionary as a database. If your key has a good hash then that may be
quite fast. Or you could look at some memory cache like Velocity or
MemCacheD.

But all these are performance optimizations that are normally only done
on massive systems where you're willing to trade complexity for
performance. If you're looking to run a DB server on your web server
than I'd guess you don't have the volume to worry.

Have you tested the performance just using the DB? Have you profiled the
application? Do you _know_ the performance problem is in the database?
And is the DB indexed correctly for your lookups?

-Walden