Bah!

ORM, SPs, View, Magic Wands, or whatever.

Each “tool” has its place in your belt, use the tools you have wisely.

The only thing that has “changed” ( really improved ) is that some ORMs have nice caching tools already baked in and MySql and Sql 2005+ can handle dynamic or ad hoc query/execution plan caching.

The potential performance loss from throwing the all sorts of dynamic sql at your db server has been somewhat mitigated. Its just easier to go without stored procedures now. Stored Procs aren’t going anywhere.

What you could do is add a custom TextWriter implementation to the DataContext.Log which will write the generated sql to a file or memory. Then loop through those queries, executing them with raw ADO.NET code, surrounding each with a stopwatch.

I’ve used a similar technique before because it seemed whenever I was developing some code I never had Profiler open, and it was really easy to output those results to a HTML page. Sure your executing them twice per website request, but its helpful to see execution times asap instead of waiting until you catch something in Profiler.

Also if your going to the SQL Tool route I would recommend googling “slowest query DMV” and getting a stored procedure that can give you stats on the slowest queries in your db. Its not always easy to scroll through profiler results to find the bad queries. Also with the right queries over sql 2005’s dmv you can also do ordering by lets say cpu vs. time and so forth.

A DBA that my company hired to troubleshoot deadlock issues just told me that our OLTP databases locking problems will improve if we set the transaction level to READ COMMITTED from READ UNCOMMITTED.

Isn’t that just 100% false? READ COMMITTED will cause more locks, correct?

More Details:

Our data is very “siloed” and user specific. 99.9999999 % of all user interactions work with your own data and our dirty read scenarios, if they happen, can barely effect what the user is trying to do.

Thanks for all the answers, the dba in question ended up being useless, and we fixed the locking issues by adding a single index.

I regret that I didn’t specify the locking problems were occurring for update statements and not regular selects. From my googing the two different query types have distinct solutions when dealing with locking issues.

I’m not clear about the interaction between database schema changes and differential backups on sql 2005.

Lets say I do a Full backup right right now.
Then I perform some schema changes.
Then I do a diff backup.

What happens? Do I need to create another FULL backup? Are my schema changes and any data in those new schema bits included in my diff backup?

Sounds like you don’t have enough memory, how much is on the Server? More than your page file?

Also could mean Sql Server has “paged out” meaning Windows decided to swap all the info it stored in memory onto the disk.

Open Perfmon ( Goto a command prompt, and type perfmon ) and add these counters:

• SQLServer:Buffer Manager – Buffer cache hit ratio
• SQLServer:Buffer Manager – Page life expectancy
• SQLServer:Memory Manager – Memory Grants Pending

If Buffer Cache Hit Ratio is < 95% it means Sql is using the disk instead of memory a lot you need more memory.

If your page life expectancy is < 500 it mean SqlServer is not keeping results cached in memory, you need more memory.

If you have a lot of Memory Grants Pending, you need more memory.

There are also two stats which let you know how much memory SqlServer wants and how much its actually using. They are called something like “Total Memory Used” and “Total Memory Requested”. If Requested > Used, guess what, you need more memory.

There are also some dmv’s you can use to determine if your queries are being held up while waiting for memory to free up. I think its sys_dmv.os_wait_stats, something like that.

I community wikied this so a real dba can come in here and clean this up. Don’t know the stats off the top of my head.