How It Works: Linked Servers and Collation Compatibility

Customer Question:  “Why I am getting different results when is change collation compatibility from true to false?” This actually turned out to be an interesting issue because no error was raised but the results are different. The query looked like the following: select * from    REMOTESERVER…tblTest as t    where t.strID = (select TOP…

2

How it Works: SQL Server Per Query Degree Of Parallelism Worker Count(s)

The question invoking the discussion was why did a query elect to use 100+ workers, approximately half the configured worker threads?  Before erasing this topic from my whiteboard let me document the highlights.  Often overlooked is the degree of parallelism decision is applied to each parallel operator of the plan.  For this example: Assume that…

4

How It Works: SQL Server 2005 Connection and Task Assignments

I have talked about how connections and tasks get assigned as PASS and during many other mentoring opportunities.  I just finished working on an issue that forced me to dig deeper into the subject. The customer reported that on a NUMA machine the connections are always getting assign to the same node.   They had determined…

3

How It Works: SQL Server 2005 Database Snapshots (Replica)

The Senior Escalation Engineers do various training and mentoring activities.  As I do this I thought I would try to propagate some of this information on the blog. I encountered an interesting issue today that I would like to share.   The problem ended up being that the a database snapshot was established for reporting purposes…

5

How It Works: How does SQL Server Backup and Restore select transfer sizes

The Senior Escalation Engineers do various training and mentoring activities.  As I do this I thought I would try to propagate some of this information on the blog. A customer asked: “Why does backup to disk choose a transfer size of 1MB but restore 64K unless I specify BUFFERCOUNT and MAXTRANSFERSIZE?” WARNING: This trace flags…

1

How It Works: SQL Server Backup Buffer Exchange (a VDI Focus)

The Senior Escalation Engineers do various training and mentoring activities.  As I do this I thought I would try to propagate some of this information on the blog. The virtual device interface (VDI) backups allow 3rd party back-up solutions to integrate with the SQL Server.   This means you don’t have to use the built in…

3

How It Works: SQL Server 2005 NUMA Basics

The Senior Escalation Engineers do various training and mentoring activities.  As I do this I thought I would try to propagate some of this information on the blog. I have received several questions on the SQL Server NUMA implementation this week. Much of this was covered in my 2006 PASS presentation in Seattle but allow…

3

How It Works: What is Restore/Backup Doing?

The Senior Escalation Engineers do various training and mentoring activities.  As I do this I thought I would try to propagate some of this information on the blog. A customer asked: “Why does it take me 7 hours to backup my database but 21 hours to restore?” The answer could be many things.   For example,…

5

How It Works: File Stream the Before and After Image of a File

The Senior Escalation Engineers do various training and mentoring activities.  As I do this I thought I would try to propagate some of this information on the blog. The question that spawned this entry was: “I am performing updates of a specific row and column and after 5 updates I see 5 files for the…

3