Microsoft SQL Server 2012 Bible

Performance Consideration

In the beginning of this chapter, the following statement was made: “With SQL Server, getting to and querying distributed data is not a problem; SQL Server can access data from a plethora of data sources. SQL Server provides several methods for accessing data from many different external data sources from a source location.” Yet, you cannot simply rely on distributed queries alone to solve complex application performance concerns.

This section, therefore, looks at several options for improving database and application performance. Although SQL Server does as much of the query optimization as it can, there still remain areas where the design and development decision can have a huge impact on query performance.

Optimizing Distributed Queries

As SQL developers the responsibility to write optimal performing queries falls upon you. With tools and utilities such as SQL Server Profiler and Execution Plans, there is no excuse for writing bad queries. This also goes for distributed queries.

However, SQL Server also steps in and performs two types of optimization specific to distributed queries:

• OLE DB SQL Command providers used for remote query execution
• OLE DB Index providers allowing indexed access

The next sections briefly cover each of these.

Remote Query Execution

SQL Server can delegate as much of the elevation of a distributed query to the SQL Command provider as possible. The key here is the use of an OLE DB provider, in that, the OLE DB provider is considered ...