1) There has to be a system lookup no matter whether it is SELECT * or SELECT (all explicit columns) because the data type of each column also has to...

1) See this blog (http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx) and it's followons for an example of how select and indexing can lead to deadlocks you wouldn't expect. Also very good advice on how...

1) A 15GB log file with a few MB used leads me to believe that you had the database set for FULL recovery mode but weren't doing tlog backups. ...

I am one of those consultants who recommends 28-29GB to start on a dedicated 32GB sql server box. Then monitor for paging. I also heard from somewhere (can't...

Two options quickly come to mind:

1) Assuming you are at a colocation facility you can pay them to process and handle backup archival and offsite storage. Or have...

1) Jeff is right - please avoid posting to multiple forums on essentially the same topic.

2) Creating an index strategy for an entire database application cannot be done via a...

Also is the new machine a single 400GB hard drive while the 100Gb machine was 5 20GB hard disks raided? Is the I/O system configured optimally start to finish:...

I have a solution - don't use DTA! 🙂 Tune your stuff yourself or hire someone to do it for you. DTA often does bad things.

Agree Gila. I have seen it pick 75% of the columns on a table as either fields or includes in an index! One client implemented all of the...

I have been tuning almost as long as you have Colin (since 6.5 first came out) - it has actually become one of the primary areas of my consulting business...

I'm with Grant and sven. DTA is a 3-legged dog, especially in the hands of the untrained. And the trained can do much better without it. You...

phred: >>I've been going through them over the past couple of days and I'm not sure what I'm even looking for now.

Never ceases to amaze me that companies...

In addition to refactoring your code for tighter transactions and more efficient queries and also improving indexing, make sure you are regularly updating statistics. The locking types are chosen...

1) tempdb should have only 1 log file.

2) 64K is typically the 'generically best' option for NTFS allocation unit size when formatting the partitions. Default isn't good...

1) Version 3042 is a BAD version of SQL Server 2005 to be on. You are missing some very important updates related to the SP2 debacle(s). See here:...