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?

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.

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.

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.

Many to Many relationships aren’t supported in Linq2Sql.

There are a couple of workarounds:

http://www.iaingalloway.com/many-to-many-relationships-in-linq-to-sql

http://blogs.msdn.com/mitsu/archive/2008/03/19/how-to-implement-a-many-to-many-relationship-using-linq-to-sql-part-ii-add-remove-support.aspx

Weird that the picture of your db schema is the same as one of the articles…

I’m working with XML that was designed by somebody who got paid by the level of nesting. The different xml files always looks something like this:

<Car>
   <Color>
       <Paint>
          <AnotherUselessTag>
               <SomeSemanticBs>
                   <TheImportantData>

With LINQ its easy to get what I want: ( not exactly, but you get the point )

from x in car.Descendants("x")
from y in x.Descendants("y")
from z in y.Descendants("z")
select z.WhatIWant();

I’m asking if there is a better way to do this? Some way of navigating the DOM with Linq?

The Black Swan is a pretty good book for changing the way you think. Once you learn to suffer through the authors arrogance and self-promotion there are some real gems inside.

I’m running into Db performance issues with an OTLP project I’m working on. Another developer and I have reached the end of our accumulated performance knowledge and seek out an individual to join the team to help us speed up our application.

For some background we’ve done schema changes to denormalize pieces of the data, optimized every query, ran multiple database tuning advisers to get our indexes just right, tuned MSSql’s server options.

We don’t need somebody to come in and tell us joins can be slow and what deadlocking is, we need somebody who knows what to do after exhausting all the steps listed above.

Anybody have any tips or experiences hiring OLTP DBA’s to share? What kind of questions can we ask the DBA during the interview process?

Its an odd situation to be in, we know we need somebody who knows more than the current team, but we don’t know what questions to ask because we don’t know what the next steps are. Does that make sense?

Because people are generally good.