DaveWentzel.com All Things Data
Data Access Wiki
Some intro information...I wrote an article for SQL Server World Wide Users Group that discusses some of the performance ramifications of all of this. If you aren't a SSWUG member you can read the original version here.
This wiki addresses some of my performance findings over the years related to data access. Often when we look at a performance problem on a running system or during a load test we make very harsh, snap judgments. "Based on the CPU of the application server I can tell the issue isn't here, it therefore must be the database server". Or..."the database server is sitting at 5% processor utilization but it is totally unresponsive from the web tier...there must be a hardware problem". Rest assured I've heard all of these statements...might have even made a few myself.
Or, we see a Profiler trace that shows some RPC or SP:Completed event that takes 15 seconds. We pull the query out and run it to see the execution plan and it returns in 16ms. What's going on? It's a simple SELECT statement, it returns just a few rows, what do I do now?
The problem, in my opinion, is that there isn't really a good data access layer code profiling tool. In SQL Server we have Profiler and my waits and queues monitoring techniques, but these things are really only looking at the database layer. On the app servers we have various profiling tools based on the technology employed. On WebSphere we have monitors, asp.net has other techniques, or we have even used tools such as CompuWare which shows us what is occuring over the wire and tries to correlate it with calls from the app. But the data access layer is weak here. We certainly have ODBC Administrator with some profiling and we can see the RPCs in Profiler, but we often don't know what the calls "mean" (sp_cursorprepare...I don't have that coded anywhere in my app).
Newer data access technologies are evolving everyday as well and it is hard for DBAs to keep up with the technologies. DBAs preach to developers to use proper JOINs, think of indexes, avoid cursors and temp tables, etc. Developers become overwhelmed and decide to look for a tool that can write the sql for them, often dynamically at runtime. The developers are relieved of understanding relational SQL which logically runs contrary to procedural programming. But often in an attempt to be universal and easy to use the SQL these new technologies are creating is horrendous performance-wise on the db server.
This wiki addresses some of those findings, how to spot them for yourself, and various alternate solutions. As DBAs we should be preaching more than the standard "don't use cursors and temp tables" and more about the data access layer. As you will see, these guys can rob performance more than any missing index or bad choice of temp table. OK, I may be exaggerating, but we all need to at least have a better understanding of these items, myself included.
My first big complaint is Server Side Cursors. A thorough understanding of these is a good place to start.
The Prepare/Execute Model is next on my most hated list.
Result Sets Handling is often overlooked and can cause performance issues.
Connection and Network Information shows some interesting information you may not have thought about regarding performance of the data access layer.
Database Connection Information discusses some ways to set up connections to your database server to be more performant. Should you use impersonation? How about the differences between using sql authentication and OS authentication?
But the best thing is to read the nodetitle excerpt.
Other, Newer Topics