SQL Server Customer Advisory Team - SQL Server Best Practices

Microsoft SQL Server 2008 Analysis Services Consolidation Best Practices

CarlRabeler — Tue, 09 Feb 2010 01:21:00 GMT

Author: Carl Rabeler

Contributor: Josh Caplan

Reviewers: Prem Mehra, Denny Lee, John Desch, Wayne Robertson, Steve Pontello

This technical note describes the consolidation options, considerations, and best practices for Microsoft® SQL Server® 2008 Analysis Services that the SQL CAT team has derived from working with Analysis Services customers. In this technical note, we also discuss an Analysis Services load balancer solution that one of our internal customers uses to load balance queries from Microsoft Excel® and SQL Server Reporting Services clients across multiple query servers to increase query performance and provide availability.

Why Consolidate

Our customers are consolidating Microsoft SQL Server 2008 Analysis Services for the following reasons:

Better utilization of hardware resources, either of existing servers or in conjunction with the acquisition of new, more powerful servers
Reduction of sprawl, particularly of many small to medium-sized Analysis Services cubes
Increased importance of availability, as business intelligence solutions become more business critical
Green IT, with fewer more powerful servers utilizing less energy resources than more, less powerful servers

Consolidation Solutions

In working with our customers, we have implemented either one or both of the following Analysis Services consolidation solutions:

Co-locating the SQL Server relational data warehouse with the Analysis Services instance on a single physical server (but with the Online Transactional Processing (OLTP) system).
Co-locating multiple Analysis Services databases onto a single physical server, either within a single Analysis Services instance or through the use of multiple Analysis Services instances

Co-Locating the Relational Data Warehouse with the Analysis Services Instance

Frequently, an Analysis Services database is located on a dedicated Analysis server and Analysis Services processing queries are sent across the wire over TCP/IP or named pipes to a SQL Server relational data warehouse on its own, dedicated server. In this scenario, network congestion can be the processing performance bottleneck, particularly when the volume of data being loaded into the Analysis Services database for each processing operation is large. If this is the case, the ASYNC_NETWORKIO wait will appear high in the SQL Server wait stats list.

Network congestion can be addressed by improving the network connection performance in one or more of the following manners:

Using Jumbo frames and tuning network parameters
Utilizing a dedicated network connection or employing faster Ethernet connections, such as 10-gigabit Ethernet and InfiniBand
Reducing the amount of data involved with each processing operation, such as by utilizing incremental updates
Network congestion can also be addressed by co-locating the relational data warehouse onto the same physical server as the Analysis Services database. Co-location facilitates can improve processing performance because Analysis Services can connect to SQL Server by using the shared memory network protocol. The shared memory protocol is enabled by default on the SQL Server relational engine and, if it is enabled as a client protocol, is always used by Analysis Services to communicate with the relational engine on the same physical server.

Co-locating the SQL Server relational data warehouse with the Analysis Services instance on a single physical server is particularly appealing as servers become more powerful (multiple cores and more memory) and less expensive. On these more powerful servers, dedicating one powerful server for the relational data warehouse and another powerful server for the Analysis Services instance may not fully utilize the processor and memory resources on either server. Co-locating the relational data warehouse with the Analysis Services instance may enable better utilization of available hardware resources. Co-location requires that you address and resolve resource contention issues. These issues are discussed later in this paper.

Co-Locating Multiple Analysis Services Databases onto a Single Server (Using a Single Instance or Multiple Instances)

To more fully utilize these more powerful and less expensive servers, it is tempting for IT to consolidate multiple small and medium-sized Analysis Services databases onto a single physical server. In many environments, there are numerous small to medium-sized cubes spread out across the enterprise that do not fully utilize the hardware resources of the servers on which they are running. Furthermore, managing and monitoring this sprawl requires significant human effort and power resources to keep running on a 24 x 7 basis. This phenomenon is exacerbated as older servers are retired and newer servers are purchased.

Consolidating Analysis Services databases onto a single physical server reduces the number of servers, which results in better utilization of hardware and a reduction in power usage. Managed appropriately, such consolidation also results in a reduction in management and monitoring costs by the IT department.

Considerations

Each of these consolidation solutions addresses specific needs. These consolidation solutions can be used separately or can used in combination. There are some common considerations for these consolidation solutions and some unique considerations for each solution.

Common Considerations When Consolidating

Consolidation will result in competition for server resources such as processor, memory, and I/O resources. In some circumstances, it may also result in locking issues or inefficient use of existing resources. When you are consolidating separate systems onto a single server platform, proper control of the utilization and sharing of resources is vital to the success of the consolidation effort. The following resources are affected by consolidation:

Memory – when multiple processes run on a single server, all share the available memory on the server. The use of substantial memory by any single process may leave insufficient memory for other processes.
Processor – when multiple processes run on a single server, all share the available processor resources on the server. The use of substantial processor resources by any single process may leave insufficient processor cycles for other processes. On large multi-processor servers, the relational engine may generate parallel execution plans for each individual query in a processing operation. When there are a large number of concurrent relational queries, which is typical of Analysis Services processing operations, excess parallelism can result in contention for processor resources and inefficient use of L1 and L2 caches.
I/O – when multiple processes run on a single server, all share I/O resources on the server. In the absence of proper disk layout and an adequate disk I/O subsystem, there is contention for the same I/O resources.

Considerations When Co-Locating the Relational Data Warehouse with the Analysis Services Instance

Co-locating the SQL Server relational data warehouse with the Analysis Services instance on a single physical server can result in significant contention for hardware resources during processing as both the SQL Server relational instance and the Analysis Services instance each require processor, memory, and I/O resources to complete the processing operation. The SQL Server relational engine and the Analysis Services multidimensional engine will each, by default, attempt to utilize all available server resources. This may result in contention for these resources during processing. For example, the SQL Server relational engine may utilize a substantial percentage of memory on the server and be slow to give up these allocated memory resources to Analysis Services when memory resources are needed during the indexing and aggregation phase of the Analysis Services processing operation.

Additionally, this resource contention may negatively affect querying, if querying is occurring concurrently with processing. For example, contention for memory resources may result in the clearing of Analysis Services caches that would otherwise be used to improve query performance. Additionally, locking during processing may result in short queries taking longer than expected and new connections failing when a long-running user query blocks the commit phase of a processing operation. When processing is affecting query performance, utilizing separate query and processing servers can resolve this resource contention. The use of separate query and processing servers is discussed later in this article.

Considerations When Co-Locating Multiple Analysis Services Databases onto a Single Server (Using a Single Instance or Multiple Instances)

Co-locating multiple Analysis Services databases onto a single server can result in several types of resource contention issues:

The resources required to process any Analysis Services database can negatively impact the query performance of all other databases on the server while the processing of any database is occurring. With multiple databases on a single physical server, processing operations may affect querying for longer periods of time, and you will generally not want to process too many databases simultaneously (due to resource requirements of each processing operation).
A single resource-intensive user query (such as multi-threaded storage engine intensive queries) against any single cube in any database can consume a significant portion of the resources on the entire server and negatively affect the performance of all other user queries on that server. For more information, see the Analysis Services Performance Guide.

Co-locating multiple Analysis Services databases onto a single server can result in several types of maintenance issues. When you consolidate multiple databases into a single physical server, down time related to operating system or hardware issues will negatively affect more databases and consequently more users. For example, applying an operating system service pack or upgrading hardware could take all of the databases on the consolidated physical server offline for a period of time. Furthermore, if you consolidate using a single instance of Analysis Services for all databases, instance management (such as SQL Server service packs and cumulative updates) can also impact availability of the databases in a consolidated environment.

The best practices discussed in the remainder of this paper describe how we have resolved these resource contention issues.

Best Practices

The following best practices describe the consolidation solutions that we have successfully implemented in customer environments to realize the benefits and mitigate the risks inherent in each of these consolidation solutions. Utilizing these best practices, we have increased processing performance and hardware utilization, decreased power consumption, simplified manageability through standardization, and increased availability.

Co-Locating the Relational Data Warehouse with the Analysis Services Instance

The challenge when the relational data source is collocated with the Analysis Services instance is to realize the benefits of improved processing performance and increased hardware utilization without impacting user queries. We implemented this consolidation option in two different ways to avoid resource contention with user queries.

Utilizing a Processing Window

A processing window is a period of time during which the Analysis Services instances on a single server are resolving few or no user queries and one that is long enough for all required processing operations to complete. A typical scenario is an environment where querying is not occurring overnight (although the definition of overnight is getting shorter). The benefit of this scenario is that the co-location of the SQL Server relational warehouse with the Analysis Services instance on a single physical server enables you to reduce the size of this processing window by increasing the speed of processing. If you have such a processing window, you will be able to complete processing during a period in which there is little or no resource contention on the server related to querying. However, as you place more Analysis Services databases on a single server, you need either more server resources or more time.

When you have such a processing window, the resource contention is only between the SQL Server relational engine and the SQL Server analysis engine for processing operations, and it does not affect a significant number of user queries. Solving contention between these two processes during the Analysis Services processing operation requires the application of a number of techniques.

Restricting Memory and Processor Utilization in the SQL Server Relational Engine

You can restrict the amount of memory and processors resources used by the SQL Server relational engine to ensure sufficient resources are available to Analysis Services. You can also use disk layout techniques to limit I/O contention between the SQL Server relational engine and Analysis Services. For more information about limiting I/O contention, see Resolving Disk Contention and Maximizing I/O Resources .

Restricting Memory Utilization in the SQL Server Relational Engine

The SQL Server relational engine uses memory in its buffer pool and memory outside of its buffer pool.

You can directly restrict the memory used by SQL Server in its buffer pool.
You can indirectly restrict the memory used for connections by limiting the number of connections.
You cannot restrict the amount of memory used by SQL Server for threads, extended stored procedures, and the CLR.

To manage the size of the SQL Server buffer pool, you can use:

The max server memory setting of the sp_configure system stored procedure to place an upper limit the amount of buffer pool memory used by an instance of the SQL Server relational engine.
Resource Governor in SQL Server 2008 to set different amounts of buffer pool memory within an instance on a per workload basis, guaranteeing a minimal amount of memory for each workload. For more information about Resource Governor, see Managing SQL Server Workloads with Resource Governor and Using the Resource Governor.

You can use these options together. For example, if you have users querying the relational data warehouse for reporting purposes and Analysis Services querying the relational data warehouse for processing purposes, you can use the max server memory setting to set an upper limit on usage of buffer pool memory and then use Resource Governor to guarantee the Analysis Services processing queries (or the reporting queries) a specified amount of memory resources.

Important: You should use the capabilities built into the SQL Server relational engine to restrict its use of memory; you should not use Windows® System Resource Manager (WSRM) for restricting memory use by the SQL Server relational engine. For more information about WSRM and SQL Server, see Scaling Up Your Data Warehouse with SQL Server 2008 and Consolidation Using SQL Server 2008.

Restricting Processor Utilization in the SQL Server Relational Engine

You can restrict the use of processor resources by the SQL Server relational engine either by limiting the use of all processor resources by the SQL Server relational engine or by allocating specific processors to the SQL Server relational engine:

Use WSRM to limit the amount of processor resources utilized by the SQL Server relational engine across all processors. WSRM is an optional (and free) component of the Enterprise and Datacenter editions Windows Server® 2003 and of all editions of Windows Server 2008. WSRM uses Windows Management Instrumentation (WMI) to monitor running processes, monitoring the memory size (virtual and physical) and processor utilization, and to prevent any process from exceeding specified upper limits. WSRM is our preferred approach to regulating the utilization of processor resources by all processes. For more information about WSRM, see WSRM for Windows Server 2008 R2, WSRM for Windows Server 2008, and WSRM for Windows Server 2003.
You can also use processor affinity within the SQL Server relational engine to restrict processor utilization to specific processors by means of two affinity mask options, affinity mask and affinity I/O mask, thus leaving remaining processor cores for the SQL Server analysis engine.

Important: On large multi-processor servers, you may need to set MAXDOP to 4 (or less) to prevent excessive parallelization of Analysis Services processing queries by the SQL Server relational engine.

Note: If you are running SQL Server 2008, you can also use Resource Governor to throttle CPU consumption by processing queries.

Tip: You can use WMI to dynamically configure these resource utilization settings during processing and then reconfigure them when processing completes. For more information, see WMI Provider for Configuration Management Concepts.

Restricting Memory and Processor Resource Utilization in Analysis Services

You cannot directly restrict the SQL Server analysis engine to specific memory and processor resources.

Utilizing Settings in Analysis Services to Restrict Memory and Processor Utilization

Analysis Services does not support processor affinity, and the max memory settings for Analysis Services are soft settings, not hard settings. By soft, we mean that when specified maximum memory settings are exceeded, objects in cache will be evicted to attempt to avoid exceeding the memory settings, but you can still blow through these memory settings under a variety of circumstances. For more information, see the SQL Server Analysis Services 2008 Performance Guide. There is a HardMemoryLimit setting in the msmdsrv.ini file in SQL Server 2008 Analysis Services that enables you to specify a threshold beyond which Analysis Services will start cancelling operations if the memory exceeds a specified threshold. However, the use of this setting will not prevent Analysis Services from exceeding the specified threshold under all circumstances.

The buffered file mode that Analysis Services uses can cause the Windows system file cache to grow quite large and starve the relational engine of all memory resources during Analysis Services processing, resulting in an apparent “hang” during processing. If you encounter this scenario, you can limit the amount of system file cache used by Analysis Services by modifying the LimitSystemFileCacheSizeMB setting in the msmdsrv.ini file. Limiting the amount of system file cache used by Analysis Services will ensure that memory remains available for the relational engine.

Utilizing WSRM to Restrict Memory and Processor Utilization in Analysis Services

WSRM is our preferred approach to place hard restrictions on the usage of memory and processor resources used by the Analysis Services process.

Important: When you utilize WSRM to place a hard restriction on the usage of memory resources by Analysis Services, the Analysis Services process will still see the total amount of memory on the server. Therefore, you need to adjust the values for the TotalMemoryLimit and LowMemoryLimit properties to reflect the appropriate percentage of the total physical memory on the server that WSRM is allowing Analysis Services to utilize in order for these settings to function as designed.

Note: If you utilize the Preallocate setting in the msmdsrv.ini file, do not grant the Lock Pages in Memory local user policy to the Analysis Services service account if you utilize WSRM. If you use Local System as the service account, be aware that Local System has this privilege by default.

Resolving Disk Contention and Maximizing I/O Resources

You can resolve disk contention between the relational engine and Analysis Services by utilizing separate LUNs, utilizing one set of LUNs for Analysis Services and another set for the relational engine. For more information, see Accelerating Microsoft adCenter with Microsoft SQL Server 2008 Analysis Services.

Maximizing Processing Performance in a Consolidated Environment

For many consolidated environments, you may want to separate the Process Data and Process Index phase of the Analysis Services processing operation to improve the performance of the entire processing operation. Separating these processing operations enables you to reconfigure the SQL Server relational engine after the Process Data phase of the processing operation completes to enable all server resources to be made available to Analysis Services during the Process Index phase of the processing operation. The Process Index phase is much more processor and memory intensive than the Process Data phase from a single thread perspective. By running the Process Index phase separately, you can parallelize the indexing operation to make use of all available resources, because the Process Index phase does not require access to the SQL Server relational engine. For more information, see the Analysis Services Performance Guide.

Utilizing a Dedicated Processing Server

In the absence of a processing window, we implemented this consolidation solution by performing all processing on physical servers not utilized for querying (called processing servers) and directing all user queries to one or more physical servers not utilized for processing (called query servers). Utilizing separate servers for querying and processing eliminates the resource contention during processing that reduces query performance while enabling you to maximize processing performance. For more information about the benefits of utilizing multiple query servers, see Utilizing Multiple Query Servers.

Utilizing a dedicated processing server and one or more query servers may, at first, seem inconsistent with consolidation. Indeed, it is if you are only looking at consolidating a very small number of Analysis Services databases. However, if you are looking to consolidate dozens of older, department-sized servers onto a small number of newer, server-class servers, consolidating to a small number of servers works very well – each server with its own standardized role.

Note: When utilizing a dedicated processing server, we have frequently consolidated the relational data warehouse on the processing server.

This solution requires you to use one of several options to synchronize the newly processed objects from the processing server to the query server(s):

Analysis Services Synch Method – You can use the Analysis Services synch method to perform an online synchronization of the newly processed database on the processing server with the database on one or more query servers. This method synchronizes only the changed files between the processing server and the query server(s), provided that your folder structures match between the source and destination databases. If the folder structure does not match, all files are copied and synchronized rather than only the changed files.
Backup and Restore Method – You can back up the newly processed database on the processing server, copy the backup file to the query server(s), and then restore the database while querying is occurring (a database lock is required at the end of the restore operation). To maximize query performance during restoration, you can restore the database on the query server using a different database name and then, after the restore is complete, drop the existing database and rename the newly restored database. This latter option also enables you to perform any quality checks on the restored database before you present it to users.
Attach and Detach Method – You can detach the newly processed database from the processing server, copy the detached database folder to the query server(s), and then attach it to the query server(s). You cannot attach over an existing database, and you cannot change the name of the database while you are attaching. You can attach it as read/write to a single query server or as read-only to multiple query servers. This latter option is quite useful if your query workload is processor and memory intensive rather than I/O intensive. You can attach the newly processed database to a physical or a virtual server. For more information about utilizing a virtual server, see Utilizing Hyper-V in a Consolidated BI Environment.
Robocopy Method – You can use Robocopy to copy only the delta files from your processing server to your query server(s). This method, however, requires you to either stop the Analysis Services engine or detach the database that is being synchronized. For more information, see Sample Robocopy Script. In Windows 7 and Windows Server 2008, Robocopy has been enhanced with the multi-thread support. For syntax, see Robocopy.

For more information about these options, see Analysis Services Synchronization Best Practices.

Co-Locating Multiple Analysis Services Databases onto a Single Server

In order to realize the benefits of increased hardware utilization when co-locating multiple Analysis Services databases onto a single server, we have implemented this consolidation option either with a single Analysis Services instance or with multiple Analysis Services instances. When consolidating multiple databases, we have generally utilized multiple query servers and one or more processing servers, and we have co-located the relational database source with its corresponding processing server. When we utilize multiple query servers, we use a number of techniques to load-balance user queries.

Important: We have only implemented this type of consolidation with small to medium sized Analysis Services databases; consolidating multiple very large or very complex databases onto a single instance simply has not made sense from a resource contention point of view. While the definition of a small or medium sized Analysis Services database is at best imprecise, our definition includes databases against which individual queries do not consume a significant portion of the server resources to resolve and which are primarily constrained by concurrency rather than query complexity. For more information about resource usage by queries, see the SQL Server Analysis Services 2008 Performance Guide.

Utilizing a Single Instance or Multiple Instances

We have rarely seen the need to spread Analysis Services databases across multiple instances of Analysis Services on a single physical server to improve query performance. We have used multiple instances of Analysis Services on a single instance when users are querying a database in one instance while the same database in a second instance is being synchronized. This configuration is used to enable user queries to be directed to the newly synchronized database on the second instance without downtime.

Note: Multiple instances of the SQL Server relational engine are sometimes used to manage the priority of queries. With Analysis Services, you can use the CoordinatorQueryBalancingFactor and CoordinatorQueryBoostPriorityLevel properties to prioritize long-running queries versus short queries. For more information, see the SQL Server Analysis Services 2008 Performance Guide.

When choosing between querying all databases in a single instance versus spreading the databases across multiple instances on a single server, consider the following:

Utilizing a single instance rather than multiple instances for the same number of databases generally uses fewer system resources.
All data security operations can be performed with database level permissions such that each database can have a different set of database administrators and users with read permissions.
Processing and synchronization operations are generally scripted and performed by SQL Server Agent or other scheduling tools, and they are executed using server-level permissions. For these types of operations, it generally does not make a difference whether a single or multiple instances are used.
If you have different sets of instance-level administrators, you need multiple instances. For example, to perform a trace of data within a database or to clear the cache of a database, you must be a server-level administrator. If you need to allow the administrators of a database to trace queries in their database without being able to see the data in another database, you will need to place that database within another instance.
If you have different instance-level properties for different databases, you need multiple instances. For example, you may want to have different priorities for long-running queries on one instance versus another instance. We have not, however, found this need to date in the customer organizations with which we have worked.

Utilizing Multiple Processing Servers and Query Servers

With multiple Analysis databases on a single physical server, the processing of any single database can affect the query performance of any query against any database on the server. A long-running query against a database can prevent the processing of that database from completing, unless the long-running query is forcibly terminated. Furthermore, within a single instance, a long-running query can prevent a processing operation from completing its commit phase and block subsequent queries as well as all new connections until the commit operation completes successfully or times out.

When consolidating databases onto a query server, we have generally utilized one or more processing servers and multiple query servers, and we have co-located the relational database source with its corresponding processing server. These servers provide the following benefits:

Processing servers – We have sometimes utilized multiple processing servers so that multiple databases can easily be processed simultaneously. Processing databases on servers that are not being used for querying insulates the query performance of all Analysis databases from resource contention due to the processing of any single database. Your need for one or more processing servers will depend upon the frequency with which each Analysis database is processed and the number of databases that you are processing. Co-locating the relational data warehouse with its corresponding processing server increases processing performance and server utilization for the reasons previously discussed.
Query servers – When using separate processing and query servers, we have always utilized multiple query servers for performance and availability. With multiple query servers, we can load balance our workload across multiple query servers to maximize performance or take a server offline without impacting availability.

Synchronizing a processed database from its processing server to multiple query servers adds minimal additional complexity or synchronization time compared to synchronization with a single query server. You can synchronize all query servers either simultaneously or in two (or more) groups. For example, you could synchronize a processed database to your first group of query servers while directing all new queries against that database to the second group of query servers. If you use Server Sync, this approach allows existing queries against that database to complete while synchronization is occurring and new queries to execute against the servers that are not being synchronized – to maximize query performance. After synchronization is complete for the first group, all queries against that database can then be directed to the first group of query servers so that the newest data is being queried while the second group of query servers is synchronized. This approach is illustrated in figures 1 and 2.

Figure 1: Synchronizing First Query Server

Figure 2: Synchronizing Second Query Server

Load Balancing User Queries for Performance and Availability

When you utilize multiple query servers, you need to load balance the user queries across the query servers for performance and availability. We are taken several approaches to load balance user queries across multiple query servers.

Load Balancing at the Presentation Layer

One approach is to load balance at the presentation layer. This approach utilizes multiple Web servers, with some Web servers directing MDX queries to one query server and other Web servers directing MDX queries to a second query server. This approach is discussed in Scale-Out Querying with Analysis Services and is illustrated in figure 3.

Figure 3: Query Scale-Out Topology with Multiple Web Servers

This approach works well as long as the workload directed to each Web server is evenly balanced, but it does not provide load balancing where that is not the case. For example, suppose some users are connecting from Excel to one query server and through Reporting Services to a second query server. In this scenario, the load from each set of users may well not be balanced.

Load Balancing using Network Load Balancing Software or Hardware Solutions

Another approach is to use network load balancing software, such as Microsoft’s Network Load Balancing (NLB) software solution, or network load balancing hardware, such F5.

Using Network Load Balancing Software

NLB is an optional component in the Windows Server 2008 and Windows Server 2008 R2 operating systems and is built into Windows Server 2003. NLB load balances network traffic among multiple servers in an IP cluster. For more information, see Windows Server 2008 NLB and Windows Server 2003 NLB.

NLB is a software solution to create a cluster of machines, each with two IP addresses – a private unique one and a virtual one that is shared by all machines in the cluster. Each machine in the cluster runs an algorithm that determines whose turn is next in responding to requests. These machines also exchange heartbeats with each other, so they all know if one server goes down and won’t allocate any more requests to such a machine until it is healthy again.

NLB supports a number of affinity options depending on your version of Windows Server. When no affinity is specified, all network requests are load-balanced across the cluster without respect to their source. In general, when affinity is enabled, all client requests from the same IP address are responded to by the same cluster host.

However, NLB has several limitations when working with multiple Analysis Services query servers, particularly when each query server has a subset of the Analysis Services databases to which queries are being directed (for example, all databases are located on at least two servers for availability, but all databases are not located on all servers):

NLB load balances at the IP address level, rather than at the database level. For NLB to load balance across multiple Analysis Services query servers, each must contain the same set of databases.
NLB monitors availability at the server level, rather than at the Analysis Service process or database level. As a result, if the Analysis Services process is not responding for any reason, NLB will continue to forward user queries to the nonresponsive Analysis Services query server. In addition, as an Analysis Services database is being updated or is out-of-date, NLB has no ability to remove that database from the cluster. Rather, an NLB administrator would have to remove the entire server from the cluster during an Analysis Service synchronization process.
NLB has no ability to monitor processor load on each of the servers in an NLB cluster and direct user queries to the server with the least load.

Due to these limitations, NLB is not our recommended solution for load balancing user requests across multiple Analysis Services query servers.

Using Network Load Balancing Hardware

Network load balancing hardware solutions have more sophisticated affinity options, such as cookie persistence, and they include dynamic load balancing algorithms that track server performance. However, these solutions do not solve the problem of removing a single database on an Analysis Services query server from the cluster while continuing to direct user queries to the other databases on that query server. In addition, these hardware solutions do not, out of the box, monitor the availability of the Analysis Services process itself.

Due to these limitations, using network load balancing hardware solutions is not our recommended solution for load balancing user requests across multiple Analysis Services query servers.

Load Balancing Using a Custom Load Balancing Solution

When using multiple query servers with multiple databases on each server, we have found the need to load balance queries at the database level as well as the need to monitor service availability at the Analysis Services level. To accomplish this, we wrote a custom Analysis Services load balancing solution (ASLB) that consists of a load balancing Web service backed by a SQL Server load balancing metadata database to load balance MDX queries across multiple query servers. The metadata database contains information about each Analysis Services query server to which queries can be redirected by the Web service.

Note: This load balancing Web service can be clustered using NLB for availability, and the load balancing metadata database can also be mirrored for availability.

The ALSB solution solves the problems previously discussed as follows:

Process availability monitoring – A SQL Server Agent job monitors the availability of the Analysis Service process on each query server. If the process fails to respond, this information is recorded in the metadata database so that no further user queries are directed to that instance of Analysis Services until it is healthy.
Resource Utilization Monitoring – A SQL Server Agent job monitors the processor utilization on each query server and records this information into the metadata database. The Web service uses this information to direct user queries to a query server that is not overloaded and which has a copy of the requested database.
HTTP Redirection – As illustrated in figure 4, the user application (such as Excel or Reporting Services) connects to the ASLB Web service specifying a particular Analysis Services database in the URL. The Web service performs a table lookup to retrieve the name of a query server that is running, that contains the most up-to-date version of the requested database, and that has the lowest workload. The load balancing Web service constructs the URL for the appropriate Analysis server and redirects the user application to that URL.

Figure 4: Individual Query Using the Analysis Services Load Balancer

Removing individual databases from a cluster – ASLB enables you to remove a database from being available to accept new queries by simply modifying a value in the appropriate table in the metadata database. Figure 5 illustrates this concept during the synchronization of cube B from the processing server to a query server, directing all new queries to the second query server until server synchronization completes. Meanwhile, queries to all other databases on the first query server continue unabated, enabling you to drain a server workload without terminating running queries.

Figure 5: Querying and Processing Using the Analysis Services Load Balancer

Utilizing different databases on each server – If you utilize more than two query servers, each query server does not have to have the same list of Analysis Services databases because ASLB has a record in the metadata database of which servers have which databases. For example, if you have four query servers, you can have your most heavily queried databases on all four query servers and your least frequently queried servers on only two of the query servers. This configuration provides load balancing and availability benefits. Finally, you can also have Analysis Services databases on multiple query servers and use ASLB to load balance among some of these query servers while leaving other query servers “in reserve” – for example, bringing them online for a particular database only during peak time periods. The query servers “in reserve” can also be virtual servers that are provisioned automatically based on demand (more on this in another paper).

Important: This solution uncovered several bugs that have been fixed in the most recent cumulative updates to SQL Server 2008 Service Pack 1 (SP1). To receive these bug fixes, you should patch your SQL Server using Service Pack 1 Cumulative update 4 or newer. For more information about the newest cumulative updates, see Knowledge Base article #970365.

Note: The code for the ASLB solution as well as an installation and configuration document is available for download on the Microsoft download center at http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=c7737e80-8dfd-4cb6-a27e-69bbe03b2f9e.

Links

SQL Server Consolidation at Microsoft

http://technet.microsoft.com/en-us/library/dd557540.aspx

Green IT in Practice: SQL Server Consolidation in Microsoft IThttp://msdn.microsoft.com/en-us/architecture/dd393309.aspx

Conclusions

Server consolidation in the Analysis Services environment can be used to increase processing performance, fully utilize server resources, reduce management costs, and save energy. Analysis Services server consolidation may consist of co-locating the relational data warehouse with the Analysis Services instance and / or standardizing on Analysis Services processing and query servers. To realize these benefits and mitigate the risks, you must manage the resource contention inherent in Analysis Services consolidation. Properly managed, Analysis Services server consolidation can be used to increase performance as well as availability.

Upgrading Replication from SQL Server 2000 32-Bit to SQL Server 2008 64-Bit without re-initialization

AlexeiK — Mon, 08 Feb 2010 10:22:00 GMT

Author: Alexei Khalyako

Contributors: Dmitry Artemov, Sergey Pyrin

Technical Reviewers: Vijay Tandra Sistla, Mike Weiner, Mark Souza

Editor: Beth Inghram

Summary

In this article we summarized the experiences we gathered during the planning and upgrade of a customer's project. The customer is one of the largest retail shops in its area. Their architecture is made up of a main server, which is located at headquarters and hosts several hundred publications containing over a thousand articles. Regional stores are located all over the country acting as subscribers for the publications hosted on the main server. Based on the business needs and infrastructure limitations the customer set the following goals for the project:

Install all SQL Server instances on the Windows Server® 2003 x64 platform.

Upgrade the publisher and distributor from the 32-bit edition of SQL Server 2000 to the 64-bit edition of SQL Server 2008. (Upgrading subscribers was not a goal of this phase of the project.)

Avoid replication re-initialization.

This paper describes how to upgrade replication members such as Publishers and Distributors from the 32-bit edition of SQL Server 2000 to the 64-bit edition of SQL Server 2008. Both clustered and nonclustered environments are discussed.

SQL Server Consolidation Guidance

Mike Weiner — Fri, 05 Feb 2010 01:27:00 GMT

Writers: Sung Hsueh, Antony Zhong, Madhan Arumugam

Technical Reviewers: Claude Lorenson, Clifford Dibble, Lindsey Allen, Sambit Samal, Sethu Kalavakur, Prem Mehra, Sameer Tejani, Il-Sung Lee, Jack Richins, Brian Dewey, Mathew John, Jamie Reding, Jonathan Morrison, Omri Bahat, S Muralidhar, Haydn Richardson

Editor: Beth Inghram

Published: November 2009

Applies to: SQL Server 2008 and SQL Server 2008 R2

Summary: The goal of this white paper is to provide a framework for choosing among virtualization, multi-database, and multi-instance consolidation strategies for SQL Server Database Engine OLTP applications by highlighting some of the key decision points based on technical analysis. Some foundational topics and terminology will be included to provide the basis for the discussion, because some terms or strategies might be worded differently in other papers.

The entire paper can be found at: SQL Server Consolidation Guidance

Full Text Indexing Terabytes of Files with SQL Server and Cloud Storage

Microsoft SQL Server Development Customer Advisory Team — Wed, 03 Feb 2010 02:52:00 GMT

Author: Darko Sancanin, Nicholas Dritsas Reviewers and contributors: Lubor Kollar, Stuart Ozer, Michael Thomassy Business Case There are currently over 50 million files (over 10 Terabytes of data) that the customer is migrating into a custom project management application (for international customers) that can be accessed via the application and can be searched upon. SQL Server 2008 full text indexing is used to index the content of these files which gives them rich searching capabilities within...(read more)

10 рекомендаций по использованию служб интеграции SQL Server

AlexeiK — Tue, 26 Jan 2010 12:58:00 GMT

Авторы: Томас Кайзер (Thomas Kejser), Дэнни Ли (Denny Lee)

Соавторы: Раньюин Мао (Runying Mao)

Технические редакторы: Бурцин Пэйтел (Burzin Patel), Кевин Кокс (Kevin Cox), Питер Карлин (Peter Carlin), Марк Суза (Mark Souza), Ричард Ткачук (Richard Tkachuk), Лен Уайатт (Len Wyatt), Линдcи Аллен (Lindsey Allen), Прем Мехра (Prem Mehra), Николас Дритсас (Nicholas Dritsas), Карл Рабилер (Carl Rabeler), Кан Ченг (Kun Cheng), Стюарт Озер (Stuart Ozer), Дональд Фармер (Donald Farmer)

Дата публикации: 10.01.2008 Дата обновления: 12.12.2008

Вероятно, вам приходилось слышать миф о том, будто службы интеграции Microsoft® SQL Server® Integration Services (SSIS) не масштабируются? В ответ мы для начала спросим: «А нужно ли масштабировать вашу систему для объемов, превышающих 4,5 миллионов строк транзакций продаж в секунду?» Службы интеграции SQL Server — это высокопроизводительная платформа для извлечения, преобразования и загрузки данных (ETL), способная масштабироваться для самых требовательных сред. И, как описано в документе Мировой рекорд производительности SSIS ETL, службы интеграции SQL Server могут обрабатывать 4,5 миллионов строк транзакций продаж в секунду. Чтобы оптимизировать вашу рабочую среду, мы подготовили 10 лучших рекомендаций по использованию служб интеграции SQL Server 2008. Большинство из них применимы и к SQL Server 2005.

SSIS — это конвейер обработки в памяти, поэтому убедитесь, что все транзакции выполняются в оперативной памяти.

Службы интеграции используются в наборе функциональности SQL Server в качестве гибкого, надежного конвейера, способного эффективно проводить построчные вычисления и разбор данных непосредственно в оперативной памяти.

Хотя стадии извлечения и загрузки конвейера задействуют диск (при считывании и записи соответственно), само преобразование должно проходить исключительно в памяти. Если по каким-то причинам при преобразовании будет задействовано дисковое пространство (например, при больших операциях сортировки), производительность значительно ухудшится. Поэтому пакеты следует секционировать и фильтровать в них данные таким образом, чтобы все преобразования умещались в оперативной памяти.

Чтобы проверить, помещаются ли пакеты в оперативной памяти, можно просмотреть счетчик производительности служб интеграции Выгружено буферов, исходное значение которого равно 0. Большее нуля значение указывает, что модуль начал выгружать данные на диск. Дополнительные сведения см. в разделе Некоторые сведения о датчиках производительности служб интеграции SQL Server.

Планируйте объемы обработки, исходя из понимания использования ресурсов.

Службы интеграции SQL Server предназначены для высокоскоростной построчной обработки больших объемов данных в оперативной памяти. Поэтому важно понимать механизм использования ресурсов, то есть ЦП, оперативной памяти, ресурсов ввода-вывода и сетевого трафика всеми пакетами.

Ограничение по производительности ЦП: выясните, какая доля загрузки ЦП приходится на службы интеграции и насколько в целом ЦП используется сервером SQL Server во время их выполнения. Последнее особенно важно, если SQL Server и службы интеграции расположены на одном компьютере, так как в случае конфликта за ресурсы приоритет обычно остается за SQL Server, в результате данные служб интеграции выгружаются на диск, что замедляет преобразование.

Самым важным будет счетчик монитора производительности ( perfmon) Процесс / % процессорного времени (Общий). Значение этого счетчика следует определить для процессов sqlservr.exe и dtexec.exe.

Если службам интеграции не удается задействовать около 100% ресурсов ЦП, это может указывать на следующее:

Конфликт приложений: например, SQL Server использует значительную часть ресурсов процессора, вследствие чего они недоступны службам интеграции.

Конфликт за оборудование: часто происходит при недостаточной мощности подсистемы дискового ввода-вывода или недостаточном для обрабатываемых данных объеме оперативной памяти.

Конструктивные ограничения: применяемый пакет служб интеграции спроектирован таким образом, что в нем не задействуется параллелизм или в нем используется слишком много однопоточных заданий.

Ограничение по пропускной способности сети: службы интеграции могут передавать данные лишь с максимальной обеспечиваемой сетью скоростью. Поэтому следует проверить топологию сети и убедиться, что путь передачи данных от источника к месту назначения отличается низкой латентностью и высокой пропускной способностью.

При настройке топологии сети будут полезны следующие счетчики монитора производительности perfmon:

Сетевой интерфейс / Текущая пропускная способность: этот счетчик содержит приблизительное значение текущей используемой полосы пропускания.

Сетевой интерфейс / всего байтов/сек: скорость передачи и приема байтов в каждом из сетевых адаптеров.

Сетевой интерфейс / Передач/сек: сообщает, сколько пакетов отправляется за секунду. Если значение счетчика приближается к 40 000 операций, необходимо включить в конфигурацию компьютера еще один сетевой адаптер и настроить совместную работу сетевых адаптеров.

Вышеуказанные счетчики позволяют определить уровень приближения к максимальной пропускной способности системы. Зная эту степень, можно принимать решения по планированию расширения мощностей, будь то установка гигабитных сетевых интерфейсов, увеличение числа устанавливаемых в сервера сетевых адаптеров или создание отдельных сетевых адресов, специально предназначенных для трафика ETL.

Ограничение по дисковому вводу-выводу: Если обеспечено минимальное взаимодействие служб интеграции с диском, обращение к диску будет осуществляться только при чтении исходных и записи целевых данных. В случае медленной подсистемы ввода-вывода операции чтения и особенно записи могут быть «узким местом» и ограничивать общую производительность.

Поскольку настройка ввода-вывода выходит за рамки этой технической статьи, дальнейшие сведения см. в разделе Лучшие методики настройки ввода-вывода до развертывания.

Помните, что система ввода-вывода характеризуется не только объемом (например, «требуется 10 ТБ»), но и обеспечиваемой скоростью непрерывного обмена данными (например, «требуется выполнение 20 000 операций ввода-вывода в секунду»).

Ограничение по памяти: При использовании служб интеграции очень важен ответ на вопрос «Сколько памяти использует мой пакет?»

Основными счетчиками для служб интеграции и для SQL Server являются:

Процесс / Байты исключительного пользования (DTEXEC.exe) — Объем памяти, используемой в данный момент службами интеграции. Эта память не может использоваться совместно с другими процессами.

Процесс / Рабочее множество (DTEXEC.exe) — Общий объем памяти, выделенной службам интеграции.

SQL Server: Диспетчер памяти / Общая память сервера: Общий объем памяти, выделенный для SQL Server. Поскольку SQL Server может резервировать память другим способом, с помощью AWE API, этот счетчик точнее всего указывает общий объем используемой SQL Server памяти. Чтобы лучше понять принципы выделения памяти для SQL Server, обратитесь к блогу Славы Окса (Slava Oks).

Память / Чтений страниц/сек — представляет общую нагрузку на память в системе. Если это значение постоянно превышает 500, то система испытывает нехватку памяти.

Базовая скорость извлечения исходной системы.

Выясните особенности своей исходной системы и скорость извлечения из нее данных. В конце концов, никакая настройка не поможет службам интеграции обрабатывать данные быстрее, чем они извлекаются, ведь чтобы преобразовать данные, их необходимо сначала прочесть.

Измерьте скорость работы исходной системы, создав очень простой пакет, считывающий данные из источника с назначением в «Счетчике строк»:

Запустите пакет из командной строки (DTEXEC) и замерьте время, которое потребуется на выполнение задания. Для точного измерения времени воспользуйтесь выводом журнала служб интеграции. Требуется вычислить число строк в секунду:

Строк/сек = Счетчик строк/времяПоток данных

Исходя из этого значения определяется максимальное число строк, которое можно считать из источника за секунду; эта скорость будет также предельной скоростью обработки данных. Чтобы увеличить этот показатель, можно сделать следующее:

Обновить драйверы и их конфигурации: Убедитесь, что используются самые последние драйверы (и их конфигурации) для сети, источника данных и дисковой подсистемы ввода-вывода. Нередко сетевые драйверы, устанавливаемые на сервере по умолчанию, не настроены оптимальным образом для конкретного сетевого стека, что понижает производительность при большом числе одновременных сетевых запросов. Стоит отметить, что на 64-разрядных системах во время разработки могут загружаться 32-разрядные драйверы; убедитесь, что во время эксплуатации используются 64-разрядные драйверы.
Создать несколько подключений: Чтобы преодолеть ограничения драйверов, можно попробовать создать несколько подключений к источнику данных. Пока источник сможет обслуживать такое количество одновременных соединений, пропускная способность при одновременном запуске нескольких процессов загрузки будет расти. Если одновременные обращения будут вызывать блокировки, рассмотрите возможность секционирования источника и настройки пакетов на считывание из различных секций; при этом нагрузка распределится более равномерно.
Используйте несколько сетевых адаптеров: Если «узким местом» системы является сеть, причем вы уже перешли на гигабитные сетевые карты и маршрутизаторы, возможно, проблему можно решить, применяя на серверах по несколько сетевых адаптеров. Помните, что настраивать среды с несколькими сетевыми интерфейсами следует внимательно, иначе могут возникнуть сетевые конфликты.

Оптимизируйте источник данных SQL, преобразования типа «Уточняющий запрос» и назначение данных.

При выполнении инструкций SQL в службах интеграции (как показано выше в диалоговом окне Режим доступа к данным), будь то инструкции чтения данных из источника, выполнения преобразования типа «Уточняющий запрос» или изменения таблиц, некоторые стандартные методы оптимизации могут существенно улучшить производительность:

Используйте подсказки NOLOCK или TABLOCK, чтобы устранить накладные расходы, связанные с блокировкой.
Чтобы оптимизировать использование памяти, выбирайте с помощью инструкции SELECT лишь действительно необходимые столбцы. При выборе с помощью SELECT всех столбцов таблицы (например, инструкцией SELECT * FROM) на получение и хранение столбцов, которые так и не будут использованы, будут зря израсходованы ресурсы памяти и пропускной способности.
По возможности выполняйте преобразования данных типа datetime в пределах исходной или целевой баз данных, так как внутри служб интеграции это преобразование расходует больше ресурсов.
В службах интеграции SQL Server 2008 введен новый компонент — общий кэш уточняющих запросов. Он обеспечивает высокоскоростной кэш с общим доступом для параллельных конвейеров (см. пункты №8 и №10 ниже).
Если службы интеграции и SQL Server выполняются на одном сервере, используйте в качестве назначения SQL Server вместо OLE DB, чтобы повысить производительность.
Размер фиксации 0 работает быстрее всего для массовых неиндексированных назначений, так как фиксируется лишь одна транзакция. Если использовать значение 0 невозможно, выберите максимальное возможное значение размера фиксации, чтобы сократить накладные издержки при записи многих пакетов.
Используйте размер фиксации, меньший 5000, чтобы избежать эскалации блокировки при вставке; не забывайте, что в SQL Server 2008 теперь можно включать и отключать эскалацию блокировок на уровне объектов, но этой функцией следует пользоваться осторожно.
* Неиндексированная вставка обычно быстрее, чем с применением кластеризованного индекса. Это означает, что при изменении значительной части таблицы назначения может быть выгодно удалить индексы, а затем пересоздать их; чтобы выяснить, какой из способов быстрее, необходимо проверить длительность операции вставки без удаления индексов и с удалением и последующим перестроением всех индексов.
Используйте секционирование и команду секционирования SWITCH; то есть загрузите рабочую таблицу, содержащую одну секцию, и переключите ее командой SWITCH в главную таблицу после построения индексов и наложения ограничений.
Еще одна очень полезная ссылка от рабочей группы по производительности SQL — Как добиться оптимальной производительности при работе уточняющих запросов служб интеграции.

Оптимизируйте производительность сети.

Одним из ключевых свойств сети является размер передаваемых в соединении пакетов. По умолчанию он равен 4096 байт. Это значит, что для каждых 4 КБ данных собирается отдельный сетевой пакет. Как указано в описании свойства SqlConnection.PacketSize библиотеки классов .NET Framework, при увеличении размера пакета производительность возрастет, так как для передачи больших наборов данных потребуется меньше операций чтения и записи.

Если ваша система по сути является транзакционной, с большим количеством операций чтения или записи данных малого объема, то производительность улучшится при уменьшении этого значения.

Так как при работе служб интеграции передаются большие объемы данных, желательно минимизировать сетевые накладные расходы. Максимальную скорость обеспечивает значение размера пакета, равное 32К (32767 байт). Хотя размер сетевых пакетов можно настроить на уровне сервера с помощью хранимой процедуры sp_configure, делать этого не следует. У администратора базы данных могут быть причины для использования на сервере параметра размера, отличного от 32K. Вместо этого измените настройки сервера в диспетчере соединений, как показано ниже.

Кроме того, чтобы настроить сеть, можно использовать настройки сетевого сходства (network affinity) на уровне операционной системы. При использовании значительной пропускной способности это иногда помогает повысить производительность.

Что касается собственно сети, можно, совместно с сетевыми администраторами , включить поддержку в сети кадров крупного размера (jumbo frames), при этом изменив объем фрейма от размера по умолчанию, составляющего 1500 байт, до 9000 байт. Применяя фреймы крупного размера, можно дополнительно уменьшить количество сетевых операций, необходимых для перемещения больших наборов данных.

Используйте типы данных продуманно.

Это самая очевидная из 10 перечисленных здесь рекомендаций. Но она настолько важна, что мы вынуждены упомянуть ее отдельно. Следуйте этим рекомендациям:

Используйте как можно более малые типы данных, тогда для преобразования потребуется выделять меньше памяти.
Не злоупотребляйте приведением типов данных — это лишь ухудшит производительность. Используйте типы данных, соответствующие типам в источнике или назначении, а необходимые приведения типов задавайте явным образом.
Следите за точностью при использовании типов money, float и decimal. Кроме того, не забывайте, что тип money работает быстрее, чем тип decimal, при этом тип money позволяет меньше заботиться о точности, чем тип float.

Измените структуру решения.

Существуют определенные задачи, которые лучше всего решать с помощью служб интеграции, в то время как для других задач эффективнее использовать иные средства. Выбор инструмента должен быть основан на действительном понимании сути проблемы и оптимального способа ее решения. Чтобы сделать правильный выбор, учтите следующие рекомендации:

Не выполняйте сортировку внутри служб интеграции без крайней необходимости. Чтобы выполнить сортировку, службы интеграции выделяют в памяти пространство для всего преобразуемого набора данных. По возможности выполните предварительную сортировку данных перед их подачей на конвейер. Если сортировка данных необходима, постарайтесь сортировать на конвейере только маленькие наборы данных. Вместо сортировки данных средствами служб интеграции используйте инструкции SQL с предложением ORDER BY для сортировки больших наборов данных в базе данных; отмечайте выходные данные как отсортированные, изменив метаданные конвейера служб интеграции для источника данных.
Иногда использование Transact-SQL оказывается более быстрым методом, чем обработка данных в службах интеграции. Как правило, все операции на основе наборов в Transact-SQL выполняются быстрее, так как задачу можно преобразовать в формулировку реляционной алгебры (домены и кортежи), для работы с которой оптимизирован SQL Server. Кроме того, оптимизатор SQL Server автоматически применяет высокую степень параллелизма и управление памятью к операциям на основе наборов — в службах интеграции эти операции, возможно, придется выполнять вручную. Типичными операциями на основе наборов являются следующие:

Инструкции UPDATE на основе наборов, которые значительно эффективнее построчных вызовов OLE DB.
Статистические вычисления, например, GROUP BY или SUM. Обычно с помощью Transact-SQL они выполняются быстрее, чем аналогичные вычисления, выполняемые в оперативной памяти на конвейере.

Обнаружение разности — это механизм, изменяющий существующие строки в целевой таблице вместо перезагрузки всей таблицы. Для выполнения обнаружения разности можно воспользоваться механизмом обнаружения изменений, например, новой функцией SQL Server 2008 — системой отслеживания измененных данных (CDC). Если эта функция недоступна, обнаружение разности придется выполнять путем сравнения исходных входных данных с целевой таблицей. Такая операция может потребовать значительного объема ресурсов, включая поддержание специальных индексов и контрольных сумм, предназначенных исключительно для этой цели. Часто более быстрым методом будет простая перезагрузка целевой таблицы. Как правило, если целевая таблица изменилась больше, чем на 10%, ее перегрузка может оказаться более быстрой, чем обнаружение разности.

Разделите задачу на составляющие.

Один из главных принципов масштабируемых вычислений — разделение задач на меньшие, проще управляемые фрагменты. Это упрощает оценку размера задачи, а также позволяет ускорить решение за счет применения параллельных процессов.

При работе с ETL исходные данные желательно разделять на меньшие фрагменты равного размера. Равный размер очень важен; если размер фрагментов не будет совпадать, то в результате придется дожидаться завершения работы одного из процессов (работающего с самой большой частью). Например, на графике ниже показано, что четыре процесса работают над секциями одинакового размера; эти четыре процесса одновременно закончат обработку данных за январь 2008 г. и перейдут к данным февраля 2008 г. Но если бы секции данных были разного размера, три процесса завершили бы работу и ожидали завершения четвертого процесса, который работал бы значительно дольше. Общее время выполнения определяется временем обработки самого большого из фрагментов данных.

Чтобы создать диапазоны секций равного размера, используйте в качестве механизма секционирования временной период или измерения (например, географическое расположение). Если первичный ключ является инкрементальным значением, например, IDENTITY или другим значением с приращением, можно воспользоваться функцией остатка от деления. Если столбцы, подходящие для секционирования по ним, отсутствуют, создайте хэш на основе значения строк и выполните секционирование по значению хэша. Дополнительные сведения о хэшировании и секционировании см. в техническом документе Оптимизация числа различных объектов служб Analysis Services; хотя документ посвящен числу различных объектов в службах Analysis Services, в нем также подробно рассматривается техника секционирования по хэшу.

Несколько дополнительных советов по секционированию:

Применяйте секционирование в целевой таблице. Это позволит параллельно запускать несколько версий одного и того же пакета, вставляющих данные в различные секции целевой таблицы. При использовании секционирования очень полезна инструкция SWITCH. Она не только повышает скорость параллельной загрузки, но и позволяет эффективно перемещать данные. Дополнительные сведения см. в статье электронной документации по SQL Server Эффективная передача данных с помощью переключения секций.

В соответствии с вышесказанным, пакеты следует разрабатывать таким образом, чтобы они принимали параметр, указывающий обрабатываемую секцию. Это позволит запустить одновременно несколько экземпляров пакета с различными значениями параметра и секции; при этом выполнение задачи ускорится за счет параллелизма
Запустить несколько выполнений можно из командной строки с помощью команды START. Краткий пример кода, запускающего параллельно несколько инструкций robocopy, можно найти в технической статье Образец скрипта Robocopy для пользовательской синхронизации баз данных служб Analysis Services.

Большинство современных серверов — это многоядерные SMP-системы. Для полной загрузки процессоров на таком сервере необходимо применение высокого уровня параллелизма. Оно обеспечивается секционированием задачи и одновременным выполнением многих процессов. Дополнительные сведения о том, как это реализуется, см. в статье Мировой рекорд производительности SSIS ETL.

Минимизируйте количество журналируемых операций.

При вставке данных в целевую базу данных SQL Server по возможности минимально используйте журналируемые операции. Если данные вставляются в базу данных в режиме полного журналирования, файл журнала будет увеличиваться очень быстро, так как каждая вставляемая в таблицу строка будет также попадать и в журнал.

Поэтому при разработке пакетов служб интеграции учитывайте следующее:

Постарайтесь обрабатывать все потоки данных в массовом режиме, а не построчно. В массовом режиме минимизируется число записей, добавляемых в файл журнала. Это сокращение освободит ресурсы ввода-вывода для прочих операций вставки и минимизирует ограничения по производительности, вызываемые записью в журнал.
Если необходимо выполнять операции удаления, организуйте данные таким образом, чтобы можно было обрезать (TRUNCATE) таблицу вместо выполнения операции DELETE. В последнем случае для каждой удаляемой строки в журнал будет вноситься запись, в то время как при обрезании данные из таблицы просто удаляются и в журнал вносится единственная запись о факте выполнения TRUNCATE. Вопреки устоявшемуся мнению, инструкция TRUNCATE может участвовать в транзакции.
Используйте инструкцию SWITCH и секционирование. Если необходимо переместить секции, можно использовать инструкцию SWITCH (чтобы включить новую секцию или исключить самую старую), при этом в журнал вносится минимальное количество записей.
Будьте осторожны при использовании инструкций DML; при совмещении инструкций DML с инструкциями INSERT минимальное журналирование прекращается.

Правильно планируйте и распределяйте выполнение задачи.

Разбив задачу на удобные в обработке фрагменты, следует решить, где и когда эти фрагменты будут выполняться. Самое важное при этом — не допустить, чтобы одна долго выполняющаяся задача задерживала выполнение всего процесса ETL.

Хороший вариант управления выполнением — создание очереди приоритетов для пакета с последующим выполнением многих экземпляров этого пакета (с различными значениями параметра секции). Очередь может быть просто таблицей SQL Server. Поток управления каждого из пакетов должен содержать следующий простой цикл:

1. Выбрать актуальный фрагмент данных из очереди:

«Актуальный» означает еще необработанный фрагмент, для которого уже выполнены все фрагменты, от которых она зависит.

Если такие объекты в очереди отсутствуют, завершить выполнение пакета.
2. Выполнить необходимую операцию над фрагментом данных.3. Пометить фрагмент в очереди как «готовый».4. Вернуться к началу цикла.

Выбор части данных из очереди и пометку части данных как «готовой» (шаги 1 и 3 выше) можно реализовать в виде хранимой процедуры.

Очередь служит центральным механизмом контроля и координации; она определяет порядок выполнения и не позволяет двум пакетам обрабатывать один фрагмент данных. После создания очереди можно просто начать запускать дополнительные копии DTEXEC для увеличения параллелизма.

10 самых полезных функций SQL Server 2008 для администратора баз данных

AlexeiK — Tue, 26 Jan 2010 10:45:00 GMT

Автор: Майк Вайнер (Mike Weiner)Соавтор: Бурцин Пэйтел (Burzin Patel)Редакторы: Любор Коллар (Lubor Kollar), Кевин Кокс (Kevin Cox), Билл Эммерт (Bill Emmert), Грег Хузмайер (Greg Husemeier), Пол Бурпо (Paul Burpo), Джозеф Сак (Joseph Sack), Дэнни Ли (Denny Lee), Санджай Мишра (Sanjay Mishra), Линдси Аллен (Lindsey Allen), Марк Суза (Mark Souza)

Microsoft SQL Server 2008 содержит ряд улучшений и новых функциональных возможностей, расширяющих функциональность предыдущих версий. Администрирование и обслуживание баз данных, поддержание управляемости, доступности, безопасности и производительности — все это входит в обязанности администратора базы данных. В этой статье описаны десять самых полезных новых функций SQL Server 2008 (в алфавитном порядке), облегчающих работу администратора БД. Помимо краткого описания, для каждой из функций приведены возможные ситуации ее применения и важные рекомендации по использованию.

Монитор активности

При устранении проблем, связанных с производительностью, или отслеживании работы сервера в реальном времени администратор обычно запускает ряд скриптов или проверяет соответствующие источники сведений, чтобы собрать общие данные о выполняющихся процессах и выявить причину проблемы. Монитор активности SQL Server 2008 объединяет такие сведения, предоставляя наглядную информацию по выполняющимся и недавно выполнявшимся процессам. Администратор БД может как просматривать высокоуровневые сведения, так и проанализировать любой из процессов более детально и ознакомиться со статистикой ожидания, что облегчает выявление и разрешение проблем.

Чтобы открыть монитор активности, щелкните правой кнопкой мыши на имени зарегистрированного сервера в обозревателе объектов, затем выберите Монитор активности или же воспользуйтесь стандартным значком на панели инструментов в среде SQL Server Management Studio. Монитор активности предлагает администратору раздел обзора, внешне похожий на Диспетчер задач Windows, а также компоненты детального просмотра отдельных процессов, ожидания ресурсов, ввода-вывода в файлы данных и последних ресурсоемких запросов, как показано на рис. 1.

Рис. 1: Вид представления Монитора активности SQL Server 2008 в среде Management Studio

Примечание. Монитор активности использует параметр периодичности обновления данных, который можно изменить щелчком правой кнопки мыши. При выборе частого обновления данных (раз в менее чем 10 секунд) производительность высоконагруженной рабочей системы может понизиться.

С помощью монитора активности администратор также может выполнять следующие задачи:

· Приостанавливать и возобновлять работу монитора активности одним щелчком правой кнопки мыши. Это позволяет администратору «сохранить» сведения о состоянии на определенный момент времени, они не будут обновлены или перезаписаны. Но не забывайте, что при обновлении данных вручную, развертывании или сворачивании раздела старые данные будут обновлены и утеряны.

· Щелкните правой кнопкой мыши элемент строки, чтобы отобразить полный текст запроса или графический план выполнения с помощью пункта меню «Последние ресурсоемкие запросы».

· Выполнять трассировку приложением Profiler или завершать процессы в представлении «Процессы». События приложения Profiler включают события RPC:Completed, SQL:BatchStarting и SQL:BatchCompleted, а также Audit Login и Audit Logout.

Монитор активности также позволяет отслеживать активность любого локального или удаленного экземпляра SQL Server 2005, зарегистрированного в среде SQL Server Management Studio.

Аудит [SQL Server]

Возможность отслеживать происходящие события и регистрировать сведения о них, включая сведения о пользователях, обращающихся к объектам, а также времени и содержании вносимых изменений, помогает администратору обеспечить соблюдение стандартов соответствия нормативным или организационным требованиям по безопасности. Кроме того, понимание происходящих в среде событий также может помочь при разработке плана по снижению рисков и поддержания безопасности в среде.

В SQL Server 2008 (только выпуски Enterprise и Developer) SQL Server Audit реализует автоматизацию, позволяющую администратору и другим пользователям подготавливать, сохранять и просматривать аудиты различных компонентов серверов и баз данных. Функция предусматривает возможность аудита с детализацией уровня сервера или базы данных.

Существуют группы действий аудита на уровне сервера, например, следующие:

· FAILED_LOGIN_GROUP отслеживает неудачные попытки входа в систему.

· BACKUP_RESTORE_GROUP сообщает, когда создавалась резервная копия базы данных или выполнялось ее восстановление.

· DATABASE_CHANGE_GROUP проводит аудит времени создания, изменения или удаления базы данных.

Группы действий аудита на уровне базы данных включают следующие:

· DATABASE_OBJECT_ACCESS_GROUP вызывается при каждом выполнении инструкции CREATE, ALTER или DROP для объекта базы данных.

· DATABASE_OBJECT_PERMISSION_CHANGE_GROUP вызывается при использовании инструкций GRANT, REVOKE или DENY для объектов базы данных.

Существуют и другие действия аудита, например, SELECT, DELETE и EXECUTE. Дополнительные сведения, в том числе полный список всех групп и действий аудита, см. в разделе Группы действий и действия аудита SQL Server.

Результаты аудита можно направить для последующего просмотра в файл или журнал событий (системный журнал или журнал событий безопасности Windows). Данные аудита создаются с применением Расширенных событий — еще одной новой функции SQL Server 2008.

Аудиты SQL Server 2008 позволяют администратору ответить на вопросы, на которые раньше было очень сложно ответить постфактум, например, «Кто удалил этот индекс?», «Когда была изменена хранимая процедура?», «Какое внесенное изменение может мешать пользователю получить доступ к этой таблице?» и даже «Кто выполнил инструкцию SELECT или UPDATE для таблицы [dbo.Payroll]?».

Дополнительные сведения об использовании аудита SQL Server и примеры его реализации см. в разделе Руководство по обеспечению соответствия требованиям в SQL Server 2008.

Сжатие резервных копий

Администраторы БД долгое время предлагали включить эту функцию в SQL Server. Теперь это сделано, и как раз вовремя! В последнее время по ряду причин, например, в связи с возросшей длительностью хранения данных и необходимостью физического хранения большего объема данных, размеры баз данных стали расти экспоненциально. При резервном копировании большой базы данных необходимо выделение значительного дискового пространства для файлов резервной копии, а также выделение для операции существенного временного промежутка.

При использовании сжатия резервных копий SQL Server 2008 файл резервной копии сжимается по мере его записи, благодаря чему требуется не только меньше дискового пространства, но и меньше операций ввода-вывода, а резервное копирование занимает меньше времени. В ходе лабораторных испытаний с реальными пользовательскими данными во многих случаях наблюдалось уменьшение размера файла резервной копии на 70-85%. Кроме того, испытания показали, что длительность операций копирования и восстановления сократилась примерно на 45%. Следует отметить, что дополнительная обработка при сжатии увеличивает загрузку процессоров. Чтобы отделить во времени ресурсоемкий процесс копирования от других процессов и минимизировать его влияние на их работу, можно воспользоваться другой описанной в этом документе функцией — Resource Governor.

Сжатие включается путем добавления предложения WITH COMPRESSION в команду BACKUP (дополнительные сведения см. в разделе Электронная документация по SQL Server) или установкой этого параметра на странице Параметры диалогового окна Резервное копирование базы данных. Чтобы не требовалось вносить изменения во все существующие сценарии резервного копирования, реализован глобальный параметр, включающий сжатие всех создаваемых на экземпляре сервера резервных копий по умолчанию. (Этот параметр доступен на странице Настройки базы данных диалогового окна Свойства сервера; его также можно установить, выполнив хранимую процедуру sp_configure со значением параметра backup compression default, равным 1). Команда создания резервной копии требует явного задания параметра сжатия, а команда восстановления автоматически распознает сжатую резервную копию и распаковывает ее при восстановлении.

Сжатие резервных копий — исключительно полезная функция, сберегающая дисковое пространство и время. Дополнительные сведения о настройке сжатия резервных копий см. в техническом примечании Настройка производительности сжатия резервных копий в SQL Server 2008. Примечание. Создание сжатых резервных копий поддерживается только в выпусках SQL Server 2008 Enterprise и Developer, однако все выпуски SQL Server 2008 позволяют восстанавливать сжатые резервные копии.

Серверы централизованного управления

Часто администратор БД управляет сразу многими экземплярами SQL Server. Возможность централизации управления и администрирования многими экземплярами SQL в единой точке позволяет экономить существенные усилия и время. Реализация серверов централизованного управления, доступная в среде SQL Server Management Studio посредством компонента «Зарегистрированные серверы», позволяет администратору выполнять различные административные операции над многими серверами SQL Servers из единой консоли управления.

Серверы централизованного управления позволяют администратору зарегистрировать группу серверов и выполнять над ними, как над единой группой, например, следующие операции:

· Многосерверное выполнение запросов: теперь из одного источника можно выполнить скрипт на многих серверах SQL Server, данные будут возвращены этому источнику, причем раздельно выполнять вход в каждый из серверов не требуется. Это может быть особенно полезным в случае, когда необходимо просмотреть или сравнить данные с нескольких серверов SQL Server, не выполняя распределенный запрос. Кроме того, при условии поддержки синтаксиса запроса предыдущими версиями SQL Server, запускаемый из редактора запросов SQL Server 2008 запрос может выполняться и на экземплярах SQL Server 2005 и SQL Server 2000. Дополнительные сведения см. в блоге рабочей группы по управляемости SQL Server в разделе Выполнение многосерверных запросов в среде SQL Server 2008.

· Импорт и определение политик на многих серверах: в рамках функциональности Управления на основе политик (еще одной новой функции SQL Server 2008, также описанной в этой статье), SQL Server 2008 обеспечивает возможность импорта файлов политик в отдельные группы серверов централизованного управления и позволяет определять политики на всех серверах, зарегистрированных в определенной группе.

· Управление службами и вызов диспетчера конфигурации SQL Server: инструмент «серверы централизованного управления» помогаает создать центр управления, в котором администратор БД может просмотреть и даже изменить (при наличии соответствующих разрешений) состояние служб.

· Импорт и экспорт зарегистрированных серверов: серверы, зарегистрированные в серверах централизованного управления (Central Management Servers), могут экспортироваться и импортироваться при их передаче между администраторами или различными установленными экземплярами SQL Server Management Studio. Эта возможность служит альтернативой импорту или экспорту администратором его собственных локальных групп в SQL Server Management Studio.

Не забывайте, что разрешения применяются с помощью проверки подлинности Windows, поэтому права и разрешения пользователей могут различаться на различных серверах, зарегистрированных в группе сервера централизованного управления. Дополнительные сведения см. в разделе Администрирование нескольких серверов с помощью серверов централизованного управления и в блоге Кимберли Трипп (Kimberly Tripp): Центральные серверы управления SQL Server 2008 — знакомы ли вы с ними?

Сборщик данных и хранилище данных управления

Настройка производительности и диагностика занимают много времени и могут требовать профессиональных навыков работы с SQL Server, а также понимания внутренней структуры баз данных. Системный монитор Windows (Perfmon), профилировщик SQL Server Profiler и динамические административные представления решали часть этих задач, но они нередко оказывали влияние на работу сервера, были трудоемки в применении или задействовали методы сбора разрозненных данных, затрудняющие их последующее объединение и интерпретацию.

Чтобы предоставить понятные сведения о производительности системы, позволяющие предпринять конкретные меры, в SQL Server 2008 реализовано полностью расширяемое средство сбора и хранения данных о производительности — сборщик данных. Оно содержит несколько непосредственно готовых к работе агентов сбора данных, централизованное хранилище данных о производительности, так называемое хранилище данных управления, и несколько подготовленных заранее отчетов для представления собранных данных. Сборщик данных — это масштабируемое средство, обеспечивающее сбор и объединение данных из различных источников, таких как динамические административные представления, монитор производительности Perfmon и запросы Transact-SQL, в соответствии с полностью настраиваемой частотой сбора данных. Сборщик данных можно расширить, реализовав сбор данных по любому измеряемому атрибуту приложения.

Еще одна полезная функция хранилища данных управления — это возможность его установки на любом сервере SQL Server с последующим сбором данных с одного или нескольких экземпляров SQL Server. При этом минимизируется влияние на производительность рабочих систем, а также улучшается масштабируемость в контексте отслеживания и сбора данных со многих серверов. При лабораторных испытаниях наблюдаемая потеря пропускной способности при выполнении агентов и работе хранилища данных управления на нагруженном сервере (с применением рабочей нагрузки OLTP) составила приблизительно 4%. Потеря производительности может изменяться в зависимости от периодичности сбора данных (упомянутое испытание велось при расширенной рабочей нагрузке, с передачей данных в хранилище каждые 15 минут), она также может резко увеличиваться во время периодов сбора данных. В любом случае следует ожидать некоторого уменьшения доступных ресурсов, так как процесс DCExec.exe использует определенный объем памяти и ресурсы ЦП, а запись в хранилище данных управления повысит нагрузку на подсистему ввода-вывода и потребует выделения пространства в месте расположения файлов данных и журнала.На диаграмме (рис. 2) показан типичный отчет сборщика данных.

Рис. 2: Вид отчета сборщика данных SQL Server 2008

В отчете показана деятельность SQL Server за время периода сбора данных. В нем собраны и отражены такие события, как ожидания, использование ЦП, ввода-вывода и памяти, а также статистика по ресурсоемким запросам. Администратор может также перейти к детальному рассмотрению элементов отчетов, сконцентрировавшись на отдельном запросе или операции, чтобы исследовать, определить и устранить проблемы, связанные с производительностью. Эти возможности сбора данных, их хранения и создания отчетов позволяют реализовать упреждающее отслеживание состояния серверов SQL Server в среде. При необходимости они позволяют возвращаться к историческим данным, чтобы понять и оценить изменения, повлиявшие на производительность за отслеживаемый период. Сборщик данных и хранилище данных управления поддерживаются во всех выпусках SQL Server 2008, кроме SQL Server Express.

Сжатие данных

Простота управления базой данных значительно облегчает выполнение рутинных задач администрирования. По мере роста размеров таблиц, индексов и файлов и распространения очень больших баз данных (VLDB) управление данными и работа с громоздкими файлами становятся все более сложными. Кроме того, растущие с объемом запрашиваемых данных потребности в памяти и обеспечении физической пропускной способности ввода-вывода также усложняют деятельность администраторов и дорого обходятся организации. Вследствие этого во многих случаях администраторам и организациям приходится либо расширять память или gпропускную способность ввода-вывода серверов, либо смиряться с понижением производительности.

Сжатие данных, представленное в SQL Server 2008, помогает разрешить эти проблемы. Эта функция позволяет администратору избирательно сжимать любые таблицы, секции таблиц или индексы, благодаря чему уменьшается занимаемое пространство на диске и в памяти, а также размер операций ввода-вывода. Сжатие и распаковка данных нагружают процессор; тем не менее, во многих случаях дополнительная нагрузка на процессор более чем компенсируется выигрышем в объемах ввода-вывода. В конфигурациях, в которых ввод-вывод является «узким местом», сжатие данных также может обеспечить рост производительности.

В некоторых лабораторных испытаниях включение сжатия данных обеспечивало экономию 50-80% дискового пространства. Экономия пространства значительно различалась: если в данных содержалось мало повторяющихся значений, или значения использовали все выделяемые для указанного типа данных байты, экономия была минимальной. При этом производительность многих рабочих нагрузок не увеличивалась. Однако при работе с данными, содержащими много числовых данных и много повторяющихся значений, отмечались значительная экономия дискового пространства и рост производительности, составляющий от нескольких процентов до 40-60% для некоторых образцов рабочих нагрузок запросов.

SQL Server 2008 поддерживает два типа сжатия: сжатие строк, при котором сжимаются отдельные столбцы таблицы, и сжатие страниц, при котором страницы данных сжимаются с помощью сжатия строк, префиксов и словарного сжатия. Достигаемая степень сжатия сильно зависит от типов данных и содержимого базы данных. В общем, при использовании сжатия строк уменьшается дополнительная нагрузка на операции приложений, однако уменьшается и степень сжатия, то есть выигрывается меньше места. В то же время сжатие страниц приводит к большей дополнительной нагрузке на приложение и загрузке процессора, но и экономит значительно больше пространства. Сжатие страниц является надмножеством сжатия строк, то есть если объект или секция объекта сжимаются с помощью сжатия страниц, к ним также применяется и сжатие строк. Кроме того, SQL Server 2008 поддерживает формат хранения vardecimal из SQL Server 2005 с пакетом обновления 2 (SP2). Следует учитывать, что, поскольку этот формат является подмножеством сжатия строк, он считается устаревшим и будет исключен из будущих версий продукта.

И сжатие строк, и сжатие страниц может включаться для таблицы или индекса в оперативном режиме, нисколько не нарушая доступность данных для приложений. В то же время сжать или распаковать отдельную секцию секционированной таблицы в оперативном режиме без ее отключения невозможно. Испытания показали, что наилучшим оказывается совмещенный подход, при котором сжимаются лишь несколько наибольших таблиц: при этом достигается отличное отношение экономии дискового пространства (значительной) к потере производительности (минимальной). Поскольку у операции сжатия, как и у операций создания или перестроения индекса, также имеются требования к доступному дисковому пространству, проводить сжатие следует с учетом этих требований. Минимум свободного пространства во время процесса сжатия потребуется, если сжатие начать с объектов наименьшего размера.

Сжатие данных можно провести с помощью инструкций Transact-SQL или мастера сжатия данных. Чтобы определить возможное изменение размера объекта при его сжатии, можно воспользоваться системной хранимой процедурой sp_estimate_data_compression_savings или мастером сжатия данных. Сжатие базы данных поддерживается только в выпусках SQL Server 2008 Enterprise и Developer. Оно реализуется исключительно в самих базах данных и не требует внесения каких-либо изменений в приложения.

Дополнительные сведения об использовании сжатия см. в документе Создание сжатых таблиц и индексов.

Управление на основе политик

Во многих бизнес-сценариях необходимо поддерживать определенные конфигурации или соблюдать политики либо на определенном сервере SQL Server, либо многократно, в группе серверов SQL Server. Администратор или организация могут потребовать применения особой схемы именования всех создаваемых пользовательских таблиц или хранимых процедур или требовать определенные изменения применялись к конфигурации на многих серверах.

Управление на основе политик (PBM) предоставляет администратору широкий набор возможностей по управлению средой. Политики можно создавать и выполнять проверку на соответствие им. Если цель проверки (например, ядро базы данных, база данных, таблица или индекс SQL Server) не соответствует требованиям, администратор может автоматически перенастроить ее в соответствии с этими требованиями. Также существует ряд режимов определения политик (многие из которых автоматизированы), упрощающих проверку соответствия требованиям политик, регистрацию в журнале нарушений политики и отправку уведомлений, и даже выполняющих откат изменений для обеспечения соответствия требованиям политики. Дополнительные сведения о режимах определения и об их сопоставлении с аспектами (понятием управления на основе политик (PBM), также обсуждаемым в этом блоге) см. в разделе Блог об управлении SQL Server на основе политик.

Политики можно экспортировать и импортировать в виде XML-файлов для их определения и применения на многих экземплярах серверов. Кроме того, в среде SQL Server Management Studio и в представлении зарегистрированных серверов политики можно определять на многих серверах, зарегистрированных в локальной группе серверов или в группе сервера централизованного управления.

В предыдущих версиях SQL Server может быть реализована не вся функциональность управления на основе политик. Тем не менее, функцию составление отчетов политики можно использовать на серверах SQL Server 2005 и SQL Server 2000. Дополнительные сведения об использовании управления на основе политик см. в разделе Администрирование серверов с помощью управления на основе политик в электронной документации по SQL Server. Дополнительные сведения о самой технологии политик с примерами см. в разделе Руководство по обеспечению соответствия в SQL Server 2008.

Прогнозируемая производительность и параллелизм

Многие администраторы сталкиваются со значительными трудностями при поддержке серверов SQL Servers с постоянно изменяющимися рабочими нагрузками и обеспечении предсказуемого уровня производительности (или минимизации расхождений в планах запросов и производительности). Неожиданные изменения производительности при выполнении запросов, изменения планов запросов и/или общие связанные с производительностью проблемы могут быть вызваны рядом причин, в том числе повышением нагрузки от выполняющихся на сервере SQL Server приложений или обновлением версии самой базы данных. Предсказуемость выполняемых на сервере SQL Server запросов и операций значительно облегчает достижение и поддержание целей по уровню доступности, производительности и/или непрерывности бизнес-деятельности (выполнение соглашений об уровне обслуживания и уровне операционной поддержки).

В SQL Server 2008 некоторые функции изменены для увеличения прогнозируемости производительности. Так, внесены определенные изменения в структуры планов SQL Server 2005 (закрепление планов) и добавлена возможность управления эскалацией блокировок на уровне таблиц. Оба улучшения способствуют более прогнозируемому и упорядоченному взаимодействию между приложением и базой данных.

Во-первых, структуры планов (Plan Guide):

В SQL Server 2005 было реализовано улучшение стабильности и предсказуемости запросов с помощью новой на тот момент функции — «структур планов», содержавших указания для выполнения запросов, которые нельзя было изменить непосредственно в приложении. Дополнительные сведения см. в техническом документеПринудительное использование планов запросов . Хотя подсказка в запросе USE PLAN является очень мощной функцией, она поддерживала только операции SELECT DML и часто была неудобна в применении из-за чувствительности структур планов к форматированию.

В SQL Server 2008 механизм структур планов расширен в двух направлениях: во-первых, расширена поддержка подсказки в запросе USE PLAN, которая теперь совместима со всеми инструкциями DML (INSERT, UPDATE, DELETE, MERGE); во-вторых, введена новая функция закрепления планов, позволяющая непосредственно создавать структуру плана (закрепление) любого плана запроса, существующего в кэше планов SQL Server, как показано в следующем примере.

sp_create_plan_guide_from_handle

@name = N'MyQueryPlan',

@plan_handle = @plan_handle,

@statement_start_offset = @offset;

У структуры планов, созданной любым способом, имеется область базы данных; она хранится в таблице sys.plan_guides. Структуры планов лишь влияют на процесс выбора плана запроса оптимизатором, но не избавляют от необходимости компиляции запроса. Также добавлена функция sys.fn_validate_plan_guide, для проверки существующих структур планов SQL Server 2005 и обеспечения их совместимости с SQL Server 2008. Закрепление планов доступно в выпусках SQL Server 2008 Standard, Enterprise и Developer.

Во-вторых, эскалация блокировок:

Эскалация блокировки часто вызывало проблемы блокировки, а иногда даже взаимоблокировку. Устранять эти проблемы приходилось администратору. В предыдущих версиях SQL Server можно было управлять эскалацией блокировок (флаги трассировки 1211 и 1224), но это было возможно только для детализации на уровне экземпляра. Для одних приложений это устраняло проблему, а для других вызывало еще большие проблемы. Другим недостатком алгоритма эскалации блокировок в SQL Server 2005 было то, что блокировки секционированных таблиц укрупнялись напрямую до уровня таблиц, а не до уровня секций.

SQL Server 2008 предлагает решение обеих проблем. В нем реализован новый параметр, позволяющий управлять эскалацией блокировки на уровне таблиц. С помощью команды ALTER TABLE можно выбрать отключение эскалации или эскалацию до уровня секций для секционированных таблиц. Обе эти возможности улучшают масштабируемость и производительность без нежелательных побочных эффектов, затрагивающих другие объекты в экземпляре. Эскалация блокировки задается на уровне объекта базы данных и не требует внесения каких-либо изменений в приложения. Оно поддерживается во всех выпусках SQL Server 2008.

Resource Governor

Поддерживать устойчивый уровень обслуживания, предотвращая бесконтрольные запросы и обеспечивая выделение ресурсов для важнейших рабочих загрузок, раньше было сложно. Отсутствовала возможность гарантировать выделение определенного объема ресурсов набору запросов, отсутствовало управление приоритетами доступа. У всех запросов были равные права на доступ ко всем доступным ресурсам.

Новая функция SQL Server 2008 — «Регулятор ресурсов»(Resource Governor) — помогает справиться с этой проблемой, давая возможность дифференцировать рабочие нагрузки и распределять ресурсы в соответствии с потребностями пользователей. Ограничения регулятора ресурсов легко перенастраиваются в реальном времени при минимальном воздействии на выполняющиеся рабочие нагрузки. Распределение рабочих нагрузок по пулу ресурсов настраивается на уровне соединения, причем этот процесс полностью прозрачен для приложений.

На диаграмме ниже показан процесс выделения ресурсов. В данном сценарии настраиваются три пула рабочих нагрузок (рабочие нагрузки Admin, OLTP и Report), после чего пулу рабочих нагрузок OLTP присваивается высший приоритет. В то же время настраиваются два пула ресурсов (пул Pool и пул Application) с заданными ограничениями по объему памяти и времени процессора (ЦП). На последнем этапе рабочая нагрузка Admin назначается пулу Admin, а рабочие нагрузки OLTP и Report назначаются пулу Application.

Ниже приведены особенности, которые следует учитывать при использовании регулятора ресурсов.

- Регулятор ресурсов использует учетные данные входа, имя узла или имя приложения в качестве «идентификатора пула ресурсов», поэтому использование для приложения одного имени входа при определенных количествах клиентов, приходящихся на один сервер, может усложнить создание пулов.

- Не поддерживается группировка объектов на уровне базы данных, при которой доступ к ресурсам регулируется на основе объектов базы данных, к которым осуществляется доступ.

- Регулятор ресурсов позволяет управлять ресурсами только в пределах одного экземпляра SQL Server. Для управления несколькими экземплярами SQL Server или процессами на сервере из единого источника следует рассмотреть возможность применения Диспетчера системных ресурсов Windows.

- Настраивать можно только использование ресурсов процессора и памяти. Управление ресурсами ввода-вывода не реализовано.

- Динамическое переключение рабочих нагрузок между пулами ресурсов после соединения невозможно.

- Регулятор ресурсов поддерживается только в выпусках SQL Server 2008 Enterprise и Developer и может использоваться только для ядра базы данных SQL Server; управление службами SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS) и SQL Server Reporting Services (SSRS) не поддерживается.

Прозрачное шифрование данных (TDE)

Многие организации уделяют очень большое внимание вопросам безопасности. Существует множество различных слоев, защищающих один из самых ценных активов организации — ее данные.Чаще всего организации успешно защищают используемые данные с помощью мер физической защиты, брандмауэров и строгих политик ограничения доступа. Однако, в случае утери физического носителя с данными, например, диска или ленты с резервной копией, все перечисленные меры обеспечения безопасности оказываются бесполезными, так как злоумышленник может просто восстановить базу данных и получить полный доступ к данным.SQL

Server 2008 предлагает решение этой проблемы путем прозрачного шифрования данных (TDE). При шифровании TDE данные в операциях ввода-вывода шифруются и дешифруются в реальном времени; файлы данных и журналов шифруются с помощью ключа шифрования базы данных (DEK). DEK — это симметричный ключ, защищаемый сертификатом, который хранится в базе данных master сервера, или асимметричный ключ, защищаемый модулем расширенного управления ключами (EKM).

Функция TDE защищает «неактивные» данные, поэтому данные в файлах MDF, NDF, и LDF невозможно просматривать с помощью редактора шестнадцатеричных данных или каким-либо другим способом. Однако активные данные, например, результаты выполнения инструкции SELECT в среде SQL Server Management Studio, останутся видимыми для пользователей, у которых имеются права на просмотр таблицы. Кроме того, поскольку функция TDE реализована на уровне базы данных, база данных может задействовать индексы и ключи для оптимизации запросов. TDE не следует путать с шифрованием на уровне столбцов — это отдельная функция, позволяющая шифровать даже активные данные.

Шифрование базы данных — одноразовый процесс, который можно запустить командой Transact-SQL или из среды SQL Server Management Studio, после чего он выполняется в фоновом потоке. Состояние шифрования или дешифровки можно отслеживать с помощью динамического административного представления sys.dm_database_encryption_keys. Во время проведенных лабораторных испытаний шифрование базы данных размером 100 Гб с применением алгоритма шифрования AES_128 заняло около часа. Хотя накладные расходы при использовании TDE определяются в основном рабочей нагрузкой приложения, в некоторых из проведенных испытаний эти дополнительные расходы составили менее 5%. Следует учитывать одну особенность, которая может повлиять на производительность: если TDE используется в любой из баз данных на экземпляре, то также шифруется и системная база данных tempDB. Наконец, при одновременном использовании различных функций необходимо учитывать следующее:

· При использовании сжатия резервных копий для сжатия зашифрованной базы данных размер сжатой резервной копии будет большим, чем без использования шифрования, так как зашифрованные данные сжимаются плохо.

· Шифрование базы данных не влияет на сжатие данных (строчное или страничное).

TDE позволяет организации обеспечить соответствие требованиям нормативных стандартов и общему уровню защиты данных. TDE поддерживается только в выпусках SQL Server 2008 Enterprise и Developer; его активация не требует внесения изменений в существующие приложения. Дополнительные сведения см. в разделе Шифрование данных в выпуске SQL Server 2008 Enterprise или в обсуждении в Руководстве по обеспечению соответствия требованиям в SQL Server 2008 на тему Прозрачное шифрование данных.

Подводя итог, можно сказать, что в SQL Server 2008 предлагаются функции, усовершенствования и возможности, облегчающие работу администратора базы данных. 10 самых популярных из них описаны здесь, но в SQL Server 2008 есть и много других возможностей, упрощающих жизнь администраторов и других пользователей. Списки «10 лучших функций» по другим направлениям работы с SQL Server можно найти в других статьях «Лучшие 10 ... в SQL Server 2008» на этом сайте. Полный список функций и их подробное описание см. в электронной документации по SQL Server и на обзорном веб-сайте SQL Server 2008.

10 рекомендаций по созданию крупномасштабного реляционного хранилища данных

AlexeiK — Tue, 26 Jan 2010 10:34:00 GMT

Авторы:: Стюарт Озер (Stuart Ozer) при участии Према Мехры (Prem Mehra) и Кевина Кокса (Kevin Cox)

Технические редакторы: Любор Коллар (Lubor Kollar), Томас Кейзер (Thomas Kejser), Дэнни Ли (Denny Lee), Джимми Мэй (Jimmy May), Майкл Рэдман (Michael Redman), Санджэй Мишра (Sanjay Mishra)

Построение крупномасштабного реляционного хранилища данных представляет собой сложную задачу. В этойтой статье описан ряд методов проектирования, которые могут помочь в создании эффективного реляционного хранилища данных большого объема с помощью SQL Server. В большинстве крупномасштабных хранилищ данных применяется секционирование таблиц и индексов, и поэтому многие из приводимых рекомендаций относятся к секционированию. Большинство этих советов основаны на опыте построения крупных хранилищ данных на базе SQL Server 2005.

Рекомендуется секционировать большие таблицы фактов

Рекомендуется секционировать таблицы фактов размером 50–100 ГБ и более.
Секционирование может упростить управление данными и часто приводит к повышению производительности.

Быстрое, более детальное сопровождение индексовБолее гибкие варианты резервного копирования и восстановления.
Ускоренная загрузка и удаление данных.

Ускорение работы запросов, ограниченных одной секцией.
Обычно таблицу фактов секционируют по ключу даты.

Позволяет применять метод скользящего окна.

Позволяет применять методику «устранения» секций.

Постройте кластеризованный индекс на ключе даты в таблице фактов

Это поддерживает использование эффективных запросов для заполнения кубов или получения среза исторических данных.
Если данные загружаются пакетами в отведенное для заливки данных время, то для кластеризованного индекса на таблице фактов нужно использовать параметры ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCKS = OFF. Это поможет ускорить операции просмотра таблиц во время запроса и избежать избыточной активности блокировок во время масштабных операций обновления.
Постройте некластеризованные индексы для каждого внешнего ключа. Это помогает точечным запросам извлекать строки на основе избирательного предиката измерения. Используйте файловые группы для административных задач, в том числе резервного копирования и восстановления, частичного обеспечения доступности базы данных и т. п.

Будьте внимательны с выбором фрагмента секционирования

Большинство клиентов используют для секционирования месяц, квартал или год.
Для эффективного удаления необходимо удалять по одной полной секции за один раз.
Загрузка происходит быстрее, если за один раз загружается полная секция.

Привлекательным вариантом может оказаться создание ежедневных секций для ежедневной нагрузки.
Однако следует помнить, что таблица может содержать не более 1000 секций.

Выбор степени секционирования влияет на параллелизм запросов.

Для SQL Server 2005:

Для запросов, затрагивающих одну секцию, параллелизм может достигать значения MAXDOP (максимальной степени параллелизма).
В запросах, затрагивающих несколько секций, используется один поток на секцию, вплоть до значения MAXDOP.

Для SQL Server 2008:

Параллельные потоки количеством до MAXDOP пропорционально распределяются пропорционально при сканировании секций; на одну секцию может приходиться несколько потоков даже при необходимости сканирования нескольких секций.

Если необходимо добиться степени параллелизма, соответствующей MAXDOP, рекомендуется избегать таких схем секционирования, при которых запросы часто затрагивают лишь 2 или 3 секции (предполагается, что MAXDOP равно или больше 4).

Правильно проектируйте таблицы измерения

Используйте целочисленные суррогатные ключи для всех измерений, кроме измерения даты. Для суррогатных ключей измерения используйте минимально возможные целые числа. Это поможет сохранить малый размер таблицы фактов.
Используйте для ключа даты осмысленное целочисленное значение, получаемое на основе типа данных DATETIME (например, 20060215).

Не используйте суррогатный ключ для измерения даты.
Удобно писать запросы, в которых предложение WHERE относится к этому столбцу, что обеспечит устранение секций в таблице фактов.

Постройте кластеризованный индекс на суррогатном ключе для каждой таблицы измерения и некластеризованный индекс на бизнес-ключе (потенциально объединенный с эффективной датой строки) для поддержки поиска по суррогатному ключу во время загрузки.
Постройте некластеризованные индексы для других столбцов измерения, в которых часто выполняется поиск.
Избегайте секционирования таблиц измерений.
Избегайте принудительного установления связей по внешним ключам между таблицами фактов и измерений — это ускорит загрузку данных. Можно создать ограничения внешнего ключа с параметром NOCHECK для документирования связей, но не следует обеспечивать строгое выполнение таких ограничений. Проверять целостность данных можно с помощью преобразований Transform Lookup или же можно проверять целостность данных в источнике данных.

Пишите эффективные запросы для устранения секций

Всегда, когда это возможно, используйте в предикате запроса (условие WHERE) ключ секционирования (ключ измерения даты) таблицы фактов.

Используйте для сопровождения данных метод скользящего окна

Поддерживайте движущееся временное окно для оперативного доступа к таблицам фактов. Загружайте самые новые данные и выгружайте самые старые.
Всегда сохраняйте пустые секции с обоих концов диапазона секций, чтобы гарантировать, что разбиение секций (перед загрузкой новых данных) и слияние секций (после выгрузки старых данных) не повлекут перемещения данных.
Не следует выполнять разбиение или слияние заполненных секций. Разбиение или слияние заполненных секций может оказаться крайне неэффективным, поскольку в таком случае объем операций с журналом может возрасти в 4 раза и, кроме того, это приведет к интенсивной работе механизма блокировок.
Создавайте промежуточную таблицу загрузки в той же файловой группе, где располагается загружаемая секция.
Создавайте промежуточную таблицу выгрузки в той же файловой группе, где располагается удаляемая секция.
Быстрее всего загружать сразу полную новую секцию, но это возможно только в случае, когда размер секции совпадает с периодом загрузки данных (например, используется одна секция для каждого дня и данные загружаются один раз в день).
Если размер секции не совпадает с периодом загрузки данных, следует проводить добавочную загрузку только самой последней секции.
Различные варианты загрузки массовых данных в секционированную таблицу обсуждаются в техническом документе http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/loading_bulk_data_partitioned_table.mspx.
Всегда выгружайте секции по одной.

Загружайте исходные данные эффективным образом

Во время начальной загрузки данных используйте модели восстановления SIMPLE или BULK LOGGED.
Создайте секционированную таблицу фактов с кластеризованным индексом.
Создайте неиндексированные промежуточные таблицы для каждой из секций и разделите файлы исходных данных, предназначенные для заполнения каждой из секций.
Заполняйте промежуточные таблицы параллельно.

Используйте несколько инструкций BULK INSERT, программу BCP или задачи служб SSIS.

Если подсистема ввода-вывода не является «узким местом», создайте столько параллельно выполняющихся сценариев загрузки, сколько в системе установлено процессоров. Если пропускная способность ввода-вывода ограничена, используйте одновременно меньшее число скриптов.
Установите нулевой размер пакета для загрузки.
Установите нулевой размер фиксации для загрузки.
Используйте TABLOCK.
Если источником данных служат расположенные на одном сервере «плоские» файлы, используйте инструкцию BULK INSERT. Если данные принудительно отправляются с удаленных компьютеров, используйте программу BCP или службы SSIS.

Постройте кластеризованный индекс для каждой промежуточной таблицы, а затем создайте соответствующие ограничения CHECK.
Переключите все секции в секционированную таблицу с помощью инструкции SWITCH.
Постройте для секционированной таблицы некластеризованные индексы.
На 64-процессорном сервере 1 ТБ данных можно загрузить менее чем за с хранилищем SAN с пропускной способностью 14 ГБ/с (для неиндексированной таблицы). Дополнительные сведения см. в записи блога SQLCAT http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx.

Эффективно удаляйте старые данные

Когда возможно, используйте переключение секций.
Если нужно удалить несколько миллионов строк из несекционированных индексированных таблиц:

Не используйте конструкцию DELETE FROM ...WHERE ...

При этом возникнут значительные проблемы, связанные с блокировками и ведением журналов.
В случае отмены удаления откат займет длительное время.

Как правило, более быстрым будет:

Вставить записи в неиндексированную таблицу с помощью инструкции INSERT.
Создать индекс (индексы) для таблицы.
Переименовать новую таблицу, чтобы она заменила исходную.

В качестве альтернативного способа можно организовать удаление небольшими порциями, циклически выполняя инструкцию

DELETE TOP (1000) ... ;

COMMIT

В качестве еще одной альтернативы можно пометить строки как удаленные, а само удаление отложить и выполнить в период пониженной нагрузки.

Управляйте статистикой вручную

Статистика по секционированным таблицам ведется для таблицы в целом.
После загрузки новых данных обновляйте вручную статистику по крупным таблицам фактов.
После повторного построения индекса для секции обновляйте статистику вручную.
Если статистика обновляется регулярно, после периодической загрузки данных, то автоматическое создание статистики (параметр AUTOSTATS) для этой таблицы можно отключить.
Это важно для оптимизации запросов, при которых может потребоваться чтение лишь самых новых данных.
Также повысить производительность можно, обновляя статистику по таблицам измерений небольшого размера после добавочной загрузки данных. Чтобы получить более точные планы запросов, используйте параметр FULLSCAN для обновления статистики по таблицам измерения.

Выбирайте эффективные стратегии резервного копирования

Для очень больших баз данных резервное копирование базы данных целиком может занимать значительное время.

Например, резервное копирование базы данных размером 2 ТБ на массив RAID-5 из 10 дисков по сети SAN может занять 2 часа (при скорости 275 МБ/с).

Отличным вариантом резервного копирования может быть создание моментальных снимков в SAN.
Сократите объем данных, подлежащих регулярному резервному копированию.

Файловые группы для секций с историческими данными можно пометить как READ ONLY.
Создавайте резервную копию файловой группы лишь один раз при ее переводе в состояние «только для чтения».
Проводите регулярное резервное копирование только для файловых групп, доступных и для чтения, и для записи.

Учтите, что инструкции RESTORE для восстановления файловых групп, доступных только для чтения, не могут выполняться параллельно.

, типы данных, хранилище данных, база данных, дизайн, индекс, зеркальное отображение, оптимизация, секции, производительность, точность, обработка, запрос, масштабируемость, безопасность, службы отчетов, службы интеграции

Whitepaper About Star Join Optimizations

Microsoft SQL Server Development Customer Advisory Team — Wed, 16 Dec 2009 20:20:03 GMT

When we talk to Data Warehouse customers – they often raise questions about the star joins improvements SQL Server 2008. Star join is a set of optimizations that provide some interesting performance gains in data warehouse workload. Instead of digging into details here on SQL CAT we want to draw your attention to this nice whitepaper about the subject: Using Star Join and Few-Outer-Row Optimizations to Improve Data Warehousing Queries Happy reading....(read more)

Monitoring free space in tempdb transaction log

Microsoft SQL Server Development Customer Advisory Team — Mon, 14 Dec 2009 13:58:36 GMT

Author: Thomas Kejser Reviewers: Peter Byrne, Sunil Agarwal, Prem Mehra, Peter Scharlock, Lindsey Allen, Mark Souza As part of your database monitoring, you may be keeping track of the free space in the transaction log. One reason to do this, is to detect a rogue query consuming too much transaction log space. If you have such monitoring set up, you need to be a bit careful about tempdb . Recall that unused space in the transaction log in a FULL and BULK LOGGED recovery mode database is reclaimed...(read more)

Performance Comparison between Data Type Conversion Techniques in SSIS 2008

Microsoft SQL Server Development Customer Advisory Team — Tue, 01 Dec 2009 13:59:00 GMT

Authors : Sedat Yogurtcuoglu and Thomas Kejser Technical Reviewers : Kevin Cox, Denny Lee, Carl Rabeler, Dana Kaufman, Jesse Fountain, Alexei Khalyako, Dana Kaufman Overview Data type conversion is one of the most common ETL tasks used in Microsoft® SQL Server® Integration Services packages. There are several ways to perform these conversions: you can use a Data Conversion transformation or a Derived Column transformation, or you can perform the conversion on the source component itself. This technical...(read more)

Working with the New SQL Server 2008 Collations and Earlier Versions of the Native Data Provider

Mike Weiner — Wed, 25 Nov 2009 19:06:00 GMT

Writer: Fernando Caro

Technical Reviewers: Jimmy Wu, Sanjay Mishra, Qingsong Yao, Juergen Thomas, Burzin Patel, Wanda He, Tres London

Published: October 2009

Applies to: SQL Server 2008

Summary: Collations are essential in setting up a database and for database operations. They are used at the server and database level, but they can also be used at the column or expression level. With SQL Server 2008, new Windows collations (denoted by *_100 in the collation name) were added that are in full alignment with the Windows Vista and Windows Server 2008 operating systems. Some of these new collations are not supported with earlier versions of the native data provider during connection to a SQL Server 2008 database, if both the client and the server environment are not in Unicode. It is therefore important to understand these collation scenarios and corresponding solutions.

For further details please download the whitepaper from: http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Pre-SQLServer2008_NativeDataProviders_and_NewCollations.docx

Как справиться с PAGELATCH при большом количестве одновременных операций INSERT

AlexeiK — Wed, 25 Nov 2009 13:45:00 GMT

Авторы: Thomas Kejser, Lindsey Allen, Arvind Rao и Michael Thomassy

При участии и с рецензиями: Mike Ruthruff, Lubor Kollar, Prem Mehra, Burzin Patel, Michael Thomassy, Mark Souza, Sanjay Mishra, Peter Scharlock, Stuart Ozer, Kun Cheng и Howard Yin

Введение

Недавно, мы проводили лабораторные испытания в Microsoft Enterprise Engineering Center, при которых использовалась большая рабочая нагрузка, характерная для OLTP систем. Целью этой лабораторной работы было взять Microsoft SQL Server c интенсивной рабочей нагрузкой и посмотреть, что случится при увеличении числа процессоров с 64 до 128 (примечание: эта конфигурация была ориентирована на релиз Microsoft SQL Server 2008 R2). Рабочая нагрузка представляла собой бльшое количесво одновеменных операций вставки, направляемых в несколько больших таблиц.

Как только мы начали масштабировать нагрузку до 128 процессорных ядер, сразу же в статистике ожиданий стали доминировать блокировоки PAGELATCH_UP и PAGELATCH_EX. Средняя продолжительность ожиданияй была десятки миллисекунд, и таких ожиданий было очень много. Такое их количество оказалось для нас неожиданностью, ожидалось, что их продолжительность не будет превышать несколько миллисекунд.

В данной технической заметке будет описано, как мы сначала диагностировали подобную проблему и как для разрешения подобной проблемы можно использовать секционированые таблицы.

Диагностика проблемы

Когда в sys.dm_os_wait_stats наблюдается большое число ожиданий PAGELATCH, с помощью sys.dm_os_waiting_tasks можно определить сессию и ресурс, который задача ожидает, например, с помощью этого сценария:

SELECT session_id, wait_type, resource_description
FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'PAGELATCH%'

Пример результата:

session_id	wait_type	resource_description
42	PAGELATCH_EX	7:1:122
46	PAGELATCH_EX	7:1:122
48	PAGELATCH_EX	7:1:122
53	PAGELATCH_EX	7:1:122

В столбце resource_description указаны местоположения страниц, к которым ожидают доступ сессии, местоположение представлено в таком формате:

<database_id>:<file_id>:<page_id>

Опираясь на значения в столбце resource_description, можно составить довольно сложный запрос, который предоставит выборку всех попавших в список ожидания страниц:

SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms

, s.name AS schema_name , o.name AS object_name ,
i.name AS index_name
FROM sys.dm_os_buffer_descriptors bd JOIN (
SELECT *

, CHARINDEX(':', resource_description) AS file_index
, CHARINDEX(':', resource_description

, CHARINDEX(':', resource_description)) AS page_index

, resource_description AS rd
FROM sys.dm_os_waiting_tasks wt
WHERE wait_type LIKE 'PAGELATCH%'
)AS wt
     ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)
     AND bd.file_id = SUBSTRING(wt.rd, wt.file_index, wt.page_index)
   AND bd.page_id = SUBSTRING(wt.rd, wt.page_index, LEN(wt.rd))
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.partition_id
JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id

JOIN sys.objects o ON i.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id

Запрос показал, что ожидаемые страницы относятся к кластеризованному индексу, определённому первичным ключом таблицы с представленной ниже структурой:

CREATE TABLE HeavyInsert (

ID INT PRIMARY KEY CLUSTERED
, col1 VARCHAR(50)
) ON [PRIMARY]

Что происходит, почему возникает очередь ожиданий к страницам данных индекса - всё это будет рассмотрено в этой технической заметке.

Основная информация

Чтобы определить, что происходит с нашей большой OLTP-нагрузкой, важно понимать, как SQL Server выполняет вставку в индекс новой строки. При необходимости вставки в индекс новой строки, SQL Server будет следовать следующему алгоритму внесения изменений:

1. В журнале транзакций создаётся запись о том, что строка изменилась.
2. Осуществляется поиск в В-дереве местонахождения той страницы, куда должна будет попасть новая запись.
3. Осуществляется наложение на эту страницу краткой блокировки PAGELATCH_EX, чтобы воспрепятствовать другим изменениям на этой странице.
4. Осуществляется добавление строки в страницу и, если это необходимо, осуществляется пометка этой страницы как «грязной».
5. Осуществляется снятие краткой блокировки со страницы.

В итоге, страница будет сброшена на диск процессом контрольной точкой или отложенной записи.

Однако, что произойдет если все вставляемые строки попадают на одну и ту же страницу? В данном случае можно наблюдать рост очереди к этой странице. Даже учитывая, что краткая блокировка весьма непродолжительна, она может стать причиной конкуренции при высокой параллельной рабочей нагрузке. У нашего клиента, первый и единственный столбец в индексе являлся монотонно возрастающим ключом. Из-за этого, каждая новая вставка шла на ту же самую страницу в конце В-дерева, пока эта страница не была заполнена. Рабочие нагрузки, которые используют в качестве первичного ключа IDENTITY или другие столбцы с последовательно увеличивающимися значениями, также могут столкнуться с подобной проблемой, если одновременно выполняемая нагрузка достаточно высока.

Решение

Всегда, когда несколько потоков получают синхронный доступ к одному и тому же ресурсу, может проявиться описанная выше проблема. Стандартное решение состоит в том, чтобы создать больше ресурсов конкурентного доступа. В нашем случае, таким конкурентным ресурсом является последняя страница В-дерева.

Один из способов снизить конкуренцию за одну страницу состоит в том, чтобы выбрать в качестве первого столбца индекса другой, не увеличивающийся монотонно столбец. Однако, для нашего клиента это потребовало бы внесения изменений на прикладном уровне в клиентских системах. Мы должны были найти другое решение, которое могло бы ограничиться только изменениями в базе данных.

Помните, что местом конкуренции является одна страница в В-дерева. Если бы только было возможно использовать для этого несколько В-деревьев для одной таблицы!. К счастью, такая возможность есть, это: Секционированные таблицы и индексы. Таблица может быть секционирована таким способом, чтобы новые строки размещались в нескольких секциях.

Сначала нужно создать функцию и схему секционирования:

CREATE PARTITION FUNCTION pf_hash (INT) AS RANGE LEFT FOR VALUES (0,1,2)
CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY])

Представленный выше пример использует четыре секции. Число необходимых секций зависит от числа активных процессов, выполняющих операции INSERT в описанную выше таблицу. Есть некоторая сложность в секционировании таблицы с помощью хэш-столбца,например в том, что всякий раз, когда происходит выборка строк из таблицы, будут затронуты все секции. Это означает, что придётся обращаться более чем к одному В-дереву, т.е. не будет отброшенных оптимизатором за ненадобностью ненужных секций. Связанная сэтим дополнительная нагрузка на процессоры и некоторое увеличение времени ожиданий процессоров, побуждает минимизировать число планируемых секций (их должно быть минимальное количество, при котором не наблюдается PAGELATCH). В рассматриваемом нами случае, в системе нашего клиента имелось достаточно много резерва в утилизации процессоров, так что было вполне возможно допустить небольшую потерю времени для инструкций SELECT, и при этом увеличить до необходимых объёмов норму инструкций INSERT.

Далее нам требуется столбец, который поможет распределить вставки по четырем секциям. Такого столбца изначально в сценарии Microsoft Enterprise Engineering Center не было. Однако, его всегда достаточно просто создать. Используя тот факт, что столбец ID монотонно увеличивается с приращением равным единице, и здесь легко применима довольно простая хеш-функция:

CREATE TABLE HeavyInsert_Hash(

ID INT NOT NULL , col1 VARCHAR(50) , HashID AS ID % 4 PERSISTED NOT NULL)

С помощью столбца HashID, вставки в четыре секции будут выполняться циклически. Создаём кластеризованный индекс следующим образом:

CREATE UNIQUE CLUSTERED INDEX CIX_Hash
ON HeavyInsert_Hash (ID, HashID) ON ps_hash(HashID)

Используя новую схему таблицы с секционированием вместо первоначального варианта таблицы, мы сумели избавиться от очередей PAGELATCH и повысить скорость вставки. Этого удалось достичь за счёт балансировки одновременных вставок между несколькими секциями, где каждая секция имеет своё В-дерево. Нам удалось повысить производительность вставки на 15 процентов, и избавиться от большой очереди PAGELATCH к горячей странице индекса одной таблицы. При этом у нас остался достаточно большой резерв процессоров, что делает возможным дальнешую отимизацию, если мы захотим применить аналогичный прием для другой таблицы, тоже с высокой нормой вставки.

Строго говоря, суть этой уловки в оптимизации логической схемы первичного ключа таблицы. Однако, потому что ключ просто стал длиннее на величину хеш-функции относительно изначального ключа, дубликатов для столбца ID удалось избежать.

Уникальные индексы по единственному столбцу таблицы зачастую становятся причиной проблем с очередями PAGELATCH. Но даже если эту проблему удастся устранить, у таблицы может оказаться другой, некластеризованный индекс, который будет испытвать аналогичную проблему. Как правило, проблема наблюдается для уникальных ключей на единственном столбце, где каждая вставка попадает на одну и ту же страницу. Если и у других таблиц индексы испытывают высокую конкуренцию с PAGELATCH, можно применить тот же прием с секционированием к индексам таких таблиц, используя такой же хэш-ключ в качестве первичного ключа.

Не всегда возможно внести изменения в приложение, особенно, если оно является продуктом третьих фирм. Но если изменение запросов возможно, становится доступной их оптимизация за счёт добавления к ним условий фильтрации по предикатам первичного ключа.

Пример: Чтобы отбросить ненужные секции, можно внести следующие изменения в сценарий:

SELECT * FROM HeavyInsert_Hash

WHERE ID = 42

Который после изменений будет выглядеть так:

SELECT * FROM HeavyInsert_Hash

WHERE ID = 42 AND HashID = 42 % 4

Исключение оптимизатором ненужных секций по значению хэша не будет вам ничего стоить, если только не считать большой платой за это увеличение на один байт каждой строки кластеризованного индекса.

Вариант стратегии быстрого и надежного резервного копирования/восстановления VLDB по сети

AlexeiK — Tue, 24 Nov 2009 19:06:00 GMT

Автор: Томас Грохсер (Thomas H. Grohser)

При содействии: Линдсей Аллен (Lindsey Allen)

Техническая экспертиза статьи: Sanjay Mishra, Lubor Kollar, Stuart Ozer, Thomas Kejser, Juergen Thomas, James Podgorski, Burzin Patel

Резюме: Размер баз данных непрерывно растёт, так же, как и растут требования к надежности и доступности баз. Одновременно с этим как никогда важным становится требование быстрого и надежного восстановления данных. Этот документ посвящён проблемам проектирования устойчивого резервного копирования и решений по восстановлению очень больших баз данных (VLDB). В этой статье на реальном примере демонстрируется как лучше всего использовать функциональность SQL Server 2008 для осуществления резервного копирования и восстановления, которыми обладает SQL Server 2008, а также создание планов резервного копирования и восстановления VLDB по сети.

Создание 2-террабайтной резервной копии на локальном диске и быстрое восстановление из нее достаточно быстрый процесс, однако он не предоставляет адекватной защиты от форс-мажорной ситуации. С другой стороны, создание резервной копии базы данных по чети на сетевой ресурс предоставляет достаточную защиту от от форс-мажора, но копирование данных по обычной 1-гигабайтной сети займет очень много времени. Когда мы только изучали данную ситуацию, для базового сравнения мы провели создание резервной копии 2-террабайтной базы по 1-гигабайтной сети на сервер, который находился на удалении 10 миль. Процесс занял 24 часа, что было абсолютно недопустимо. Необходимо было создать некое решение, которое позволило бы избежать узких мест в производительности процесса создания резервной копии и дало бы возможность соблюсти требования сервисного соглашения ( SLA).

Собирая решение по кусочкам и проводя бесконечные тесты, нам удалось сократить время создания копии 2-террабайтной базы до 36 минут. Решение, которое мы назвали «многопоточным резервированием по сети» , использует восемь 1-гигабайтных сетевых соединений. Jumbo Frames были настроены на каждой сетевой карте, все сетевые карты были объеденены в один 10-гигабитный канал, который перекинулся ко второму серверу. После этих оптимизаций создание резервной копии заняло 2 часа 15 минут. Использование компресии резервной копии дало возможность сократить время до финальных 36 минут.

Детальное описание проекта можете прочитать во вложении.

PowerPivot for SharePoint Installation Guides DRAFT

Microsoft SQL Server Development Customer Advisory Team — Fri, 20 Nov 2009 15:18:00 GMT

The installation of PowerPivot for SharePoint can be a little complex at times - especially the PowerPivot for SharePoint multi-server farm scenario. In order to help with this, the Analysis Services team and the SQLCAT team came together and created four PowerPivot for SharePoint Installation Guides for your review. These are DRAFT guides that you can review and provide feedback on. Meanwhile, happy installing! For more information, please go to PowerPivot Server Installation ....(read more)

PowerPivot Jump Start!

Microsoft SQL Server Development Customer Advisory Team — Tue, 10 Nov 2009 23:41:00 GMT

As you may already know, the new name for Project Gemini is PowerPivot and the official names are PowerPivot for Excel and PowerPivot for SharePoint. For information and to sign up for notification on the release dates, check out the official PowerPivot web site at http://www.powerpivot.com/ Shortly after the SharePoint conference, our Analysis Services teammates independently created their own very interesting blogs: Rob Collie’s PowerPivotPro.com : Rob is one of the founding engineers behind PowerPivot...(read more)

SQL Server Reporting Services Disaster Recovery Case Study

denny.lee — Fri, 23 Oct 2009 16:01:00 GMT

Authors: Ayad Shammout (CareGroup Healthcare), Denny Lee

Technical Reviewers: Lukasz Pawlowski, Sanjay Mishra, Prem Mehra, Michael Thomassy, Lindsey Allen, Burzin Patel, Andy Wu, Thomas Kejser, Carl Rabeler, Quoc Bui

Applies to: SQL Server 2005, SQL Server 2008

Introduction

There are many ways to perform disaster recovery with Microsoft SQL Server Reporting Services (SSRS). Based upon customer experience and internal testing, this technical note provides guidance around best practices to design and manage robust end-to-end disaster recovery (DR). This DR method will involve both automatic and manual failover in the form of content switches, SQL Server failover clustering, and database mirroring. This case study focuses on the lessons learned from CareGroup Healthcare System.

Environment Setup

Figure 1 shows a basic disaster recovery scenario. The left side (in green) represents the primary data center, while the right side (in orange) represents the disaster recovery (DR) site. In general the two sites should be identical or at least very similar to each other so that the DR site will be able to handle the load of the primary data center; all of this should be transparent to the users.

Figure 1: Primary data center and the disaster recovery site

Primary Data Center

The primary data center contains the Reporting Services Web service servers (there is only one for the purpose of this diagram); the report catalogs are placed on a separate server (RSDB). For more information about how to design a scale out Reporting Services environment, see the Building and Deploying Large Scale SQL Server Reporting Services Environments Technical Note Series. As a general rule, you should set up the report catalogs (RSDB) in a failover cluster, because the report catalogs are central to the Reporting Services environment. For more information about configuring and optimizing report catalogs, see Report Server Catalog Best Practices.

Disaster Recovery Site

The disaster recovery site should closely duplicate the primary data center Reporting Services environment. It should be located in another geographic location such that if the primary data center experiences a resource disruption (for example, in connectivity or power), all traffic can be redirected to the alternate site with minimal disruption to service. In noncritical scenarios, you can utilize fewer resources (for example, less powerful servers, fewer servers) but for mission-critical systems, we highly recommend a server-for-server duplication of resources.

Network Setup

To ensure connectivity from the clients to the primary data center and the disaster recovery site, a common technique is to use a content switch to load-balance traffic within the individual sites as well as between the global sites. In the case of CareGroup Healthcare, a Cisco GSS is used as the content switch. As well, there is direct fiber network connectivity between the primary data center and the disaster recovery site to ensure minimal latencies for any communication between the two centers. If the primary site goes down for any reason, the content switch transparently redirects all client traffic to the disaster recovery set of Reporting Services servers. If the content switch is unavailable, the IP address can be changed at the DNS level. This latter change is a manual switch with a slightly longer network outage, which is due to the DNS cache clearing the old IP address and pointing to the new one.

Database Setup

For the CareGroup Healthcare environment, the primary RSDB instance within the primary data center is named Boston\sql4 and is a SQL Server cluster using SQL Server 2008 Enterprise, with the content switch pointing to the alias sql4. An active/passive failover cluster is used; this allows other active database instances to be located on the server in the passive node. This technique works well if the passive node is not overutilized such that it can handle the Reporting Services workload if the Reporting Services active node goes offline. It may make sense to go to an active/active cluster if there is enough traffic, concurrent users, and/or resources, though the discussion of the tradeoffs between idle machines and high availability scenarios is outside the scope of this technical note. Each of these clustering techniques has unique advantages; ultimately, it is important to ensure that the report catalogs are consistently running with minimal downtime. For more information about how to best configure a SQL Server clustering environment, see SQL Server 2008 Failover Clustering.

To ensure that the RSDB within the disaster recovery site is up-to-date, a common technique is to use asynchronous database mirroring. For the CareGroup Healthcare environment, the mirrored RSDB instance is named Montréal\sql4 and is a SQL Server cluster using SQL Server 2008 Enterprise. Note that while the domain names are different (Montréal vs. Boston in this case), the SQL Server clusters for the primary data center and the disaster recovery site have the same name (more on this later). As noted in Report Server Catalog Best Practices, the key database to keep in sync is the report server (RSDB) database, because it stores all of the report metadata, including report definitions, report history and snapshots, and scheduling information. All Reporting Services operations must connect to this database to access its metadata to perform their functions. The reason asynchronous database mirroring is commonly chosen is that asynchronous mirroring has minimal or no impact on response time performance. In addition, asynchronous mirroring works well because RSDB metadata is not frequently updated. For more information about database mirroring, see SQL Server Replication: Providing High Availability using Database Mirroring and Asynchronous Database Mirroring with Log Compression in SQL Server 2008.

The following table describes an example endpoint setup for these database mirrors.

Mirror Setup

Boston\sql4 (Primary)

Montréal\sql4 (DR site)

Name

Principal

Partner

Listener Port

50212

Encryption

Yes

Role

Partner

Initializing Database Mirror

A relatively easy way to initialize a database mirroring setup is to:

1) Make full and transaction log backups of the Reporting Services databases on the principal server.

2) Copy the backups over to the disaster recovery site, restoring each Reporting Services database in no-recovery mode.

3) Set up the failover partner on the mirror (that is, the DR site) before you set up the failover partner on the principal server.

Failover Scenarios

There are three main disaster recovery scenarios that can occur within this Reporting Services disaster recovery environment:

· Reporting Services servers within the primary data center go offline.

· The active server for the RSDB in the primary data center failover cluster goes offline.

· The entire cluster for the RSDB in the primary data center goes offline.

Please note that this note does not explicitly cover the scenarios concerning the Reporting Source databases. While most of the information here is applicable to these databases (such as data marts, operational data stores, transactional systems, and data warehouses), they have their own disaster recovery requirements and procedures that should be separate from the reporting infrastructure.

Reporting Services Servers within the Primary Data Center Go Offline

As noted in Figure 2, both Reporting Services servers point to the same cluster of databases. In this case, they both point to the active/passive database cluster Boston\sql4.

Figure 2: Reporting Services servers within the Primary Data Center go offline

In this scenario where the Reporting Services servers within the primary data center go offline, the hardware content switch will detect a network connectivity issue and automatically fail over to the disaster recovery site. By default, the Reporting Services farm connects to the report catalog in the primary data center because it has the most up-to-date information (provided that the network is fast and responsive). Therefore in most cases, the client connectivity to the Reporting Services environment in the DR site is minimally impacted (clients might need to be closed and restarted to establish a new network connection, or they could receive a broken link/page in the browser and need to refresh or restart their report execution).

Active RSDB Server in the Primary Data Center Failover Cluster Goes Offline

If the active server in the primary data center failover cluster goes offline, the active/passive setup of the SQL Server failover cluster will kick in. The active server will automatically fail over to the passive instance of the node with little to no service interruption. The report server may report that it cannot connect to the report server database, but after the passive node comes online, it will automatically re-establish the connection. All report server connectivity will continue as previously performed except that now the passive instance has become the active instance.

Figure 3: Active cluster for RSDB in the primary data center goes offline

Entire RSDB Cluster in the Primary Data Center Goes Offline

If the primary data center RSDB SQL Server cluster goes offline, the disaster recovery solution is to manually fail over to the RSDB on the disaster recovery site to continue serving up reports. But note that this is a manual failover process, which requires manual configuration of the Reporting Services servers in the Reporting Services farm to point to the RSDB instance within the disaster recovery site. It is important to be aware that, with asynchronous mirroring, manual failover by definition forces the service to allow data loss. With Reporting Services, however, the data loss should be minimal because report metadata is not frequently updated and snapshots can be re-created. By configuring the primary data center and disaster recovery site RSDBs with identical instance names (recall that both instances are named sql4 but that they are in different domains), the manual failover is simplified because the Reporting Services servers will connect to an identically named instance of the RSDB (different domain, but same name).

Figure 4: Entire cluster for RSDB in the primary data center goes offline

Primary Data Center Outage Scenarios

If the entire primary data center goes offline for either a planned or unplanned outage, the content switch will automatically fail over to the Reporting Services farm in the disaster recovery site, but you will need to perform a manual failover to ensure that the Reporting Services servers can connect to the copy of the RSDB in the disaster recovery site.

Planned Outages

For planned outages:

1) The content switch will perform the task of suspending the primary IP addresses and activating the DR site IP addresses so that all connections will be redirected to the disaster recovery site.

2) You will need to execute a script on the primary database to manually switch to the mirrored database. After the partner database becomes the primary, the same script can be re-executed to switch back to the original primary database. Here is an example:

-- Script to manually fail over to the mirror database

-- Example: switch from Boston\sql4 to Montreal\sql4

USE [master]

GO

-- Database ReportingServer

ALTER DATABASE ReportingServer SET PARTNER FAILOVER

GO

Unplanned Outages

For unplanned outages:

1) The content switch will perform the task of suspending the primary IP addresses and activating the DR site IP addresses so that all connections will be redirected to the disaster recovery site.

2) You will need to run a manual failover script to force the service to switch with possible data loss. The script needs to run on the mirrored server in the case of loss of the principal server. Here is an example:

-- Script to force database fail over of Boston\sql4 to Montreal\sql4

-- in case of SQL Server instance failure

USE [master]

GO

-- Database ReportingServer

ALTER DATABASE ReportingServer

SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

GO

After this script is run, the mirror server is the principal server and the mirror itself is suspended.

For more information about Reporting Services configuration for the database connection, see Configuring a Report Server Database Connection.

Break/Rebuild the Mirror

After the system goes down, it is undesirable to have the active system push data to the passive system (it cannot anyways). So it is important to break the mirror with the primary data center server and then re-establish it after the primary data center (in this example, Boston) server is back online. Note that the disaster recovery (in this example, Montréal) server is the one that is now active, so you will need to manually transfer the data from the DR site server back to the primary data center server (in this example, Montréal to Boston). To fully break the mirror, execute the following on the primary server:

ALTER DATABASE ReportingServer SET PARTNER OFF

RESTORE DATABASE ReportingServer WITH RECOVERY

To rebuild the mirror, follow the original steps in the Initializing Database Mirror section above.

Detail Logistics

It is important to understand that the Reporting Services report jobs, schedules, and subscriptions will not be mirrored between the RSDB from the primary data center and the disaster recovery site. This occurs because SQL Server Agent jobs are stored in the msdb database, which is not mirrored. A common method to resolve this issue is to script all of these Reporting Services jobs in the primary site and then use these scripts to re-create them on the disaster recovery site (with the jobs disabled); activate these jobs only if the system fails over. In the case of Reporting Services database migration, it is not necessary to perform this operation, as noted in How to: Migrate a Reporting Services Installation.

Note that logins created on the primary site may not be replicated to the disaster recovery site. To work around this problem, we recommend storing all of these logins into their own database and mirroring this database to the DR site as well. Then create a script to generate the logins within the DR site to re-establish the logins. For more information, see How to transfer logins and passwords between instances of SQL Server.

Conclusion

With these guidelines, you can prepare your Reporting Services environment for disaster recovery scenarios. The key here is that you will need to use a combination of hardware components, network connectivity, SQL Server database mirroring, and architecture to create a disaster recovery infrastructure to support an enterprise SQL Server Reporting Services environment. As noted, this case study has used specific techniques such as active/passive clustering and asynchronous database mirroring to support their disaster recovery requirements. Your situation may be different, but this technical note should provide you with the basic framework for Reporting Services disaster recovery. The hope is that you will never need to use it, but at least now you are ready in case you do.

Использование SQL Server 2008 в среде Hyper-V. Рекомендации и вопросы производительности

AlexeiK — Thu, 22 Oct 2009 11:40:00 GMT

Авторы: Линдсей Аллен (Lindsey Allen), Майк Рутраф (Mike Ruthruff), Прем Мехра (Prem Mehra)
Технические редакторы: Синди Гросс (Cindy Gross), Бурзин Пател (Burzin Patel), Денни Ли (Denny Lee), Майкл Томасси (Michael Thomassy), Санджай Мишра (Sanjay Mishra), Савитха Падманабхан (Savitha Padmanabhan), Тони Вельм (Tony Voellm), Боб Уорд (Bob Ward)

Основанная на технологии гипервизора функция виртуализации Hyper-V™ представляет собой тонкий слой ПО, связывающий оборудование и ОС Windows Server® 2008, который позволяет одновременно запускать на сервере несколько операционных систем без их модификации. Hyper-V — это мощная технология виртуализации, обеспечивающая консолидацию слабо загруженных корпоративных серверов, что позволяет снизить совокупную стоимость владения при хранении и повысить качество обслуживания.
Hyper-V дает возможность обойти ограничения, связанные с наличием оборудования, и использовать больше разнообразных типов сред разработки и тестирования.
Определение объема оборудования, необходимого для консолидации имеющихся рабочих нагрузок и обеспечения задела для роста, как правило, довольно проблематично. Добавление виртуализации усложняет проблемы планирования ресурсных затрат в неоднородной среде. Данный документ призван помочь в решении этих проблем. Основное внимание уделено следующим двум важным аспектам запуска Microsoft® SQL Server® в среде Hyper-V:

• Дополнительные затраты системных ресурсов, связанные с работой SQL Server в среде Hyper-V
• Масштабируемость Hyper-V при работе с SQL Server 2008
В этом документе описан ряд опробованных тестовых конфигураций, представляющих разнообразные возможные сценарии функционирования SQL Server в среде Hyper-V. В документе изложены полученные результаты и наблюдения, а также представлены рекомендации. Результаты тестов показали, что SQL Server 2008 обеспечивает при работе в среде Hyper-V стабильную производительность и масштабируемость. Мы уверены, что при соответствующих рабочих нагрузках Windows Server 2008 Hyper-V представляет собой надежную платформу для работы SQL Server 2008. Запуск производственных процессов в среде Hyper-V допустим при условии, что рабочая нагрузка не превышает возможностей гостевой виртуальной машины Hyper-V.

Using Filtered Statistics with Partitioned Tables.

AlexeiK — Tue, 20 Oct 2009 20:26:00 GMT

Author: Alexei Khalyako

Reviewers and contributors: Eric Hanson, Dimitri Artemov, Burzin Patel, Thomas Kejser

Introduction

We’ve worked with a customer on architectural design review and preparing a migration of a big Data Warehouse from SQL Server 2005 to SQL Server 2008. Base on the customer’s requests, there were various advantages of the migration to SQL Server 2008 identified. One of the requests was related to control of the query resource consumption.

The main role of the Data Warehouse in the customer’s scenario is to collect Call Data records, which have to be analyzed later and used by several other business applications. So, in general once per day new data come into a several Terabyte partitioned table and then business apps and business users were running report-type of queries. Some of the relatively small reports were taking very long times. As DBA started analyzing those queries they recognized that the difference between REQUIRED and REQUESTED memory by the query was huge (like amount of required memory was 18Mb and requested – 10 GB)!

As the part of optimization we were able to lower the appetite of those queries by using Resource Governor. We even managed to get more of the heavy queries executed at the same time. However this didn’t address the other question – why queries were requesting way more memory?

Analyzing query plans we found out that the Statistics were disabled on this table. The reason for this was that customer had to load every day very big amount of data into the new partition. Statics was becoming not valid and should have been updated. Update process was starting on the entire table (which is several Tb) and by the tome update was finished – the new portion of the data was arriving. And so on, and so on... Customer decided to disable and delete statistics on the entire table.
Another important moment to note: customer stated that most of the report queries run against only one partition in partitioned table.

Base on the AdwentureWorks database and the table SalesOrderHeader I created a little sample to illustrate this situation. I made a partitioned table SalesOrderHistory partitioned by OrderDate by date and covering one month 2008-07.
The table structure looks like:

CREATE TABLE [SalesOrderHistory](
              [SalesOrderID] [int] NOT NULL,
              [RevisionNumber] [tinyint] NOT NULL,
              [OrderDate] [datetime] NOT NULL,
             [DueDate] [datetime] NOT NULL,
              [ShipDate] [datetime] NULL,
              [Status] [tinyint] NOT NULL,
              [SalesOrderNumber] [nvarchar](25) NOT NULL,
              [CustomerID] [int] NOT NULL,
              [ShipToAddressID] [int] NULL,
              [BillToAddressID] [int] NULL,
              [CreditCardApprovalCode] [varchar](15) NULL,
              [SubTotal] [money] NOT NULL,
              [TaxAmt] [money] NOT NULL,
              [Freight] [money] NOT NULL,
              [TotalDue] [money] NOT NULL,
             [Comment] [nvarchar](max) NULL,
              [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
              [ModifiedDate] [datetime] NOT NULL)
              ON day_scheme ([OrderDate])

The query will look like a:

Select b.Name, a.SalesorderId, a.OrderDate from SalesOrderHistory a
Inner Join Store b on a.CustomerID = b.CustomerID
WHERE a.OrderDate = '2008-07-13 00:00:00.000'and a.SalesOrderID = 43659
Group by b.Name, a.SalesorderId, a.OrderDate

Query executed in 5 sec.

Let’s see the plan with no statistics on the table:

So, we see the warning that statistics is missing, if we check the estimated number of rows in the output of SELECT is 393 rows

If we run DMV to check memory requested and granted to this query we will see following:

select session_id,requested_memory_kb,required_memory_kb,granted_memory_kb,query_cost

from sys.dm_exec_query_memory_grantswhere session_id >50

session_id	requested_memory_kb	required_memory_kb	granted_memory_kb	query_cost
52	1360	1280	1360	5,737777

Now we will try to fix it. In SQL 2008 we’ve got new Filtered Statistics. In order to learn more about this feature please read http://msdn.microsoft.com/en-us/library/ms190397.aspx Having in mind that customer told us that 80% of the queries are hitting only one partition we may apply filtered statistics to improve those queries performance. I can try to create different statistics per each column which is included in SELECT statement:

CREATE STATISTICS Day0713_SalesOrderID on SalesOrderHistory (SalesOrderID )where OrderDate = '2008-07-13 00:00:00.000'

CREATE STATISTICS Day0713_OrderDate on SalesOrderHistory (OrderDate) where OrderDate = '2008-07-13 00:00:00.000'

CREATE STATISTICS Day0713_CustomerID on SalesOrderHistory (CustomerID ) where OrderDate = '2008-07-13 00:00:00.000'

After creating those statistics and executing the same query as mentioned above we see that the query execution time was few milliseconds and the plan looked much better:

Let’s check how much memory this query requested now and how much was granted:

select session_id,requested_memory_kb,required_memory_kb,granted_memory_kb,query_cost

from sys.dm_exec_query_memory_grantswhere session_id >50

session_id	requested_memory_kb	required_memory_kb	granted_memory_kb	query_cost
52	1024	512	1024	5,678132

We see that comparable to running the same query with no statistics now with filtered statistics on the table enabled has the query much lower expectations on how much memory it may need.

One of the great advantages of the filtered statistics is that if we switch in new partition into the partitioned table filtered statistics built on the older partitions does not become invalid and you don’t need to update it. You only need to create new filtered statistics for the new partition which is much faster process comparable to if you need to update or build statistics for entire table. In case of the real data in the customer’s lab we were able to get even better results using filtered statistics on much bigger amount of data. If you have similar workload, where most of the queries are going against single partition in partitioned table you may want to play with the filtered indexes and check if it will help you increasing performance of your queries.

Are you going to SQL PASS Nov. 3rd- Nov. 5th?

Microsoft SQL Server Development Customer Advisory Team — Mon, 19 Oct 2009 13:47:00 GMT

Are you going to SQLPASS in Seattle on Nov 3 rd -Nov 5 th ? If you are going or even thinking about it, then read about how you can spend some quality time with the SQLCAT team. We will be there with our famous ugly lime green shirts, so you won’t miss us. The new exciting addition the SQLCAT will partake in this upcoming PASS is the SQL Server Clinic. The SQL Server Clinic will be open every day during PASS starting after the keynote until 6:00pm. In the SQL Server Clinic will be the powerful combination...(read more)

Deploying a Business Intelligence Solution Using SharePoint, Reporting Services, and PerformancePoint Monitoring Server with Kerberos

CarlRabeler — Tue, 13 Oct 2009 21:23:00 GMT

Author: Carl Rabeler

Reviewers: Prem Mehra, Troy Starr, Kevin Donovan, Burzin Patel, Doron Bar-Caspi, Prash Shirolkar, John Wyss

This technical note describes how we designed and implemented a business intelligence solution that utilized a server farm containing Microsoft® Office SharePoint® Server 2007, Microsoft Office PerformancePoint® Server 2007 Monitoring Server, and Microsoft SQL Server® 2008 Reporting Services in SharePoint mode, all running on Windows Server® 2008 R2 and with all servers and applications configured for Kerberos authentication. In this technical note, we discuss the design requirements for this business intelligence solution, its logical architecture, the challenges we faced in architecting and implementing this solution, and our resolutions to these challenges.

Note: The business intelligence solution discussed in this technical note was configured and tested in the SQL CAT customer lab and is currently being deployed (substantially as discussed in this article) at a customer site.

Design Requirements

The design requirements for this business intelligence solution are:

· Deploy the solution into a Windows Server 2008 R2 domain.Deploy all server applications in this solution onto servers running Windows Server 2008 R2.

· Deploy a server farm running Office SharePoint Server 2007 (SharePoint), which includes the SharePoint Web front-end (WFE) and the application server, on one server and the SQL Server (SQL Server) database on another server.

· Configuring the SharePoint services for later scale-out to multiple SharePoint WFE front-end servers and multiple SharePoint application servers

· Deploy SQL Server 2008 Reporting Services (Reporting Services) on a server, integrate Reporting Services with SharePoint, and deploy its databases to a shared metadata database server

· Deploy Microsoft Office PerformancePoint Server 2007 Monitoring Server (PerformancePoint) on the SharePoint application server, deploying its Monitoring System database to the shared metadata database server, and configuring PerformancePoint for later scale-out to a dedicated application server.

· Utilize SQL Server and SQL Server Analysis Services (Analysis Services) data sources (the data source servers) for the Reporting Services reports and PerformancePoint dashboards.

· Design reports using Report Builder 2.0 and PPS Dashboard Designer.

· Execute Reporting Services reports and view PPS dashboards utilizing Windows® Internet Explorer® 7 or Internet Explorer 8.

· Configure the MOSS application server to authenticate business users retrieving business intelligence data into their Reporting Services reports and PPS dashboards from the the data source servers using the business user’s authentication credentials.

Solution Architecture

The following two diagrams represent the solution architectures for the initial deployment and for eventual scale-out. The discussion in the remainder of this document describes the challenges we faced and our resolutions in implementing this solution architecture.

Initial Deployment Architecture

In this initial deployment architecture, the SharePoint WFE and application server along with the PerformancePoint Server are located on a single application server (called the MOSS App Svr in the following diagram), with a single dedicated server on which Reporting Services is installed in SharePoint mode (called the SSRS Svr in the following diagram), and with a single server on which SQL Server is installed that contains all of the metadata for SharePoint, PerformancePoint and Reporting Services (called the Metadata Svr in the following diagram).

Full Scale-Out Deployment Architecture

In this full scale-out deployment architecture, the components in the previous diagram are scaled out to the following components:

· Multiple SharePoint WFE servers (called WFE (1) through WFE (n) in the following diagram).

· Multiple SharePoint application servers (called MOSS App Svr (1) through MOSS App Srv (n) in the following diagram).

· A dedicated SharePoint metadata server (called the MOSS DB Svr in the following diagram).

· A dedicated PerformancePoint Server application server (called the PPS App Svr in the following diagram).

· A dedicated PerformancePoint Server metadata server (called the PPS Mntr Sys DB Svr in the following diagram).

· Multiple Reporting Services application servers (called SSRS Svr (1) through SSRS Svr (n) in the following diagram).

· A dedicated Reporting Services metadata server (called the SSRS DB Svr in the following diagram).

Challenges Encountered and Resolutions

In designing and deploying this solution, we encountered a number of installation challenges. Some were unique to Kerberos, and others were related to software installation. These challenges and our resolutions are discussed next.

Note: This technical note assumes a basic understanding of Kerberos; a full discussion of Kerberos architecture and step-by-step configuration procedures is beyond the scope of this article. For more information about Kerberos architecture, see Microsoft TechNet. It contains has many articles, including the following one: Kerberos. For step-by-step guidance, see the articles that are mentioned in context in this technical note. We include our comments on lessons learned from our use of these articles. There is no single article for this combination of components.

Designing and Configuring for Kerberos Authentication

To enable the middle-tier applications (MOSS, PPS, and SSRS) on the Application server to seamlessly authenticate users in accessing report and dashboard data on the data source servers, Kerberos is required. If it is properly configured, the client computer obtains the Kerberos ticket for the user from the Kerberos Key Distribution Center (KDC) based on properly registered Service Principal Names (SPNs), and then it presents this ticket to the SharePoint server farm when it establishes a connection.

Note: The KDC is a network service that supplies session tickets and temporary session keys to users and computers within an Active Directory® domain. The KDC runs on each domain controller as part of Active Directory Domain Services.

The application servers in the SharePoint server farm, if they are properly configured, impersonate this user when then retrieve the requested data from the appropriate SQL data server (SQL Server or Analysis Services). This is the classic double-hop scenario, which requires Kerberos to securely impersonate a user credentials when connecting to a service on another computer. In order for the Internet Information Services (IIS) service or the Reporting Services service to impersonate the user during connection to the appropriate SQL Data server, the service account under which the IIS service or the Reporting Services service is running must have permission to impersonate, and the computers involved must be authorized for delegation.

Note: For more information about troubleshooting with respect to Kerberos and delegation, see Kerberos authentication and troubleshooting delegation issues.

On the application server in our solution, there are multiple IIS Web applications within MOSS (Central Administration, a portal site, a My Site, and an SSP Administration site) as well as the PPS web application and the SharePoint WFE. These Web applications utilize different identities via dedicated application pools, and these Web applications listen on both default and nondefault ports (that is, ports other than TCP port 80 and SSL port 443). In addition, the WFE forwards the SSRS requests to the SSRS server. In order for impersonation of the user’s Kerberos credentials to work properly, the identity for each Web application must have an SPN registered. Furthermore, the appropriate SPN must be utilized when the client connects to a particular IIS Web application. In addition, the SSRS service on the SSRS server along with the Analysis Services and SQL Server services on the SQL Data servers must each have properly registered SPNs. To implement Kerberos authentication for this environment, we utilized the approach described in the following Microsoft TechNet article: Configure Kerberos authentication (Office SharePoint Server), supplemented by the Excel® Services documentation in the following Knowledge Base article: How to configure SharePoint Server 2007 and Excel Services for Kerberos authentication and PPS-specific information in the following Microsoft TechNet article: Configure Monitoring Server for Kerberos delegation.

Note: One of things we really like about the Configure Kerberos authentication (Office SharePoint Server) TechNet article is its step-by-step approach to verifying that Kerberos is configured and working properly. However, you should be aware that the Event ID in the Event log for a successful login using Kerberos has changed with Windows Server 2008 and Windows Vista®. It is now Event ID 4624 (see Knowledge Base article 947226); it was Event ID 540 in Windows Server 2003 (as documented in the article).

Kerberos Challenge 1:

As pointed out in the Configure Kerberos authentication (Office SharePoint Server) article, Internet Explorer does not, by default, include port numbers in its Kerberos ticket requests. If port numbers are not included, impersonation is attempted using the Kerberos ticket associated with the application pool identity for the Web application listening on port 80 (or port 443 if SSL is involved). Because in our solution architecture we have Web applications listening on both nondefault and default port numbers, this default behavior does not result in the appropriate Kerberos ticket being retrieved for Web applications listening on nondefault ports – resulting in authentication errors.

To enable Internet Explorer to include port numbers in every request, you can change a registry setting on every client. For more information about this behavior and registry key, see Knowledge Base article 908209. While this Knowledge Base article refers only to Internet Explorer 6.0, the behavior and the registry key changes also apply to Internet Explorer 7 and 8. For the customer environment for which we were designing this solution, modifying the registry on each client (even by means of a global policy) was not acceptable. Without the port numbers in the Kerberos ticket request, only one SPN can be registered for each host name.

Our challenge was to design a solution that enabled an SPN for each Web application in IIS such that a unique Kerberos ticket is retrieved for each Web application on the application server if the application is listening on a nondefault port.

Kerberos Resolution 1:

To enable a unique SPN and Kerberos ticket for each IIS Web application listening on a nondefault port, we did the following:

Defined unique host names in DNS for each IIS Web application on the Application server.
Configured each Web application with its own application pool identity (service account).
Registered four SPN variations for each unique host name using the service account identity for that Web application.

To illustrate, we began by creating host names in DNS for the SharePoint portal site (hostname), for Central Administration (hostnameca), and PerformancePoint (hostnamepps). Then we registered SPNs for the short host name as well as the fully qualified domain name (FQDN) (hostname.mydomain.com), registering these names both with and without the port number.

Note: We registered SPNs both with and without port numbers to ensure that our Kerberos configuration worked successfully whether Internet Explorer submitted the port numbers or not.

hostname

setspn -A HTTP/hostname mydomain\mossportalpool
setspn -A HTTP/hostname:80 mydomain\mossportalpool
setspn -A HTTP/hostname.mydomain.com mydomain\mossportalpool
setspn -A HTTP/hostname.mydomain.com:80 mydomain\mossportalpool

hostnameca

setspn -A HTTP/hostnameca mydomain\mossfarmadmin
setspn -A HTTP/hostnameca:11111 mydomain\mossfarmadmin
setspn -A HTTP/hostnameca.mydomain.com mydomain\mossfarmadmin
setspn -A HTTP/hostnameca.mydomain.com:11111 mydomain\mossfarmadmin

hostnamepps

setspn -A HTTP/hostnamepps mydomain\ppsapppool
setspn -A HTTP/hostnamepps:40000 mydomain\ppsapppool
setspn -A HTTP/hostnamepps.mydomain.com mydomain\ppsapppool
setspn -A HTTP/hostnamepps.mydomain.com:40000 mydomain\ppsapppool

This approach resolved the Internet Explorer issue with connecting to a Web application running on a nondefault port. With this approach, when Internet Explorer connects to a virtual hostname for the appropriate Web application (rather than the physical machine name), DNS refers the user to the application server, and it uses the SPN registered for the virtual hostname. As a result, the appropriate Kerberos ticket is retrieved (rather than the Kerberos ticket for the application listening on port 80). Using our example, when Internet Explorer connects to hostnameca or hostnameca:11111, the SPN for the mossfarmadmin is used, and if Internet Explorer connects to hostnamepps or hostnamepps:40000, the SPN for ppsapppool is used.

Note: We also registered SPNs for the service account for the SQL Server relational engine, Analysis Services, and Reporting Services services. Only two SPNs were registered for these services because connectivity to these services is not made by Internet Explorer; instead, the middle-tier services on the application servers connect directly to these services.

Kerberos Challenge 2:

Our solution to Kerberos Challenge 1 required that we configure IIS to utilize different application pool identities for each Web application: for example, hostname, hostnameca, and hostnamepps each used a different application pool identity. In IIS 7.5 with Windows Server 2008 R2 (as well as with IIS 7.0 in Windows Server 2008), IIS utilizes kernel mode authentication by default. Kernel mode authentication runs under the machine account of the IIS server. For more information, see Security Authentication <authentication> and Advanced Settings Dialog Box – Windows Authentication Feature. This change to kernel mode authentication is generally a good thing, because performance is improved and the configuration for Kerberos is simplified in many scenarios; authentication is done under the LocalSystem account, regardless of the application pool identity. However, in a Web farm environment (such as in our scenario), we needed to use the application pool for authentication to ensure the correct Kerberos ticket would be utilized for the appropriate Web service when it was scaled out to multiple application and front-end servers.

Kerberos Resolution 2:

There are two ways to resolve this problem:

· Set the useAppPoolCredentials attribute in the system.webServer/security/authentication/Windows-Authentication configuration section of the ApplicationHost.config file to “True”.

· Disable Kernel Mode authentication.

We chose the second option. As mentioned previously in this technical note, we used this TechNet article: Configure Monitoring Server for Kerberos delegation to help us configure PPS. This article specifically recommends that we configure kernel mode authentication to use application pool credentials with IIS 7.0 and Windows Server 2008. As a result, this was our initial approach to resolving this issue. This solution worked well with Internet Explorer 7. However, with Internet Explorer 8, we encountered an additional challenge: The first time a user connects to a Web application in MOSS that is configured with Kerberos and that uses an application pool identity, the user is repeatedly prompted for authentication when navigating for the first time through nonroot portions of the SharePoint site. We discovered that if we disabled kernel mode authentication, we did not see this behavior.

After discussing the situation with the developers on the SharePoint and Internet Explorer teams, we discovered that we had run into a recently discovered incompatibility with kernel mode authentication when using an application pool identity. The incompatibility was traced back to performance enhancements in Internet Explorer 8 that impact SharePoint 2007. We ran both functional and performance tests in our environment using Internet Explorer 7 and Internet Explorer 8, with kernel mode authentication disabled for all of the Web applications that used dedicated application pool identities, and we determined that this configuration functioned properly and performed well. As a result, our recommended solution is to disable kernel mode authentication with SharePoint 2007 to seamlessly support a mix of Internet browsers.

Kerberos Challenge 3:

The resolutions to our first two Kerberos challenges performed as expected when the SharePoint farm consisted of a single SharePoint server (with either a local or a remote SQL Server database). However, we encountered an issue with this approach when we scaled out to multiple front-end Web servers and multiple application servers. When we scaled out, we changed the name of our Web applications for Network Load Balancing (NLB) to a virtual name, which NLB then directed to a specific physical server. For example, we changed the name for our SharePoint portal site from hostname to kerbportal.mydomain.net and changed our SPNs accordingly. We followed the steps in Update a Web application URL and IIS bindings (Office SharePoint Server) to update the Web application URL and IIS bindings. When we did so, we were unable to view our deployed Reporting Services reports and PerformancePoint dashboards. We received errors stating that the published PPS dashboards and SSRS reports were not able to find the underlying dashboard and report locations. For example, reports that were deployed to http://hostname/ReportServer were still trying to connect to that location, but the newly configured location for the report server was http://kerbportal.mydomain.com/ReportServer. However, the deployed report was not configured to use this new URL path.

Note: For more information about this problem, see section 8.4 of Reporting Services SharePoint Integration Troubleshooting.

Kerberos Resolution 3:

The solution was quite straightforward after we identified the problem. Namely, we needed to configure virtual host names in DNS that would eventually be utilized in NLB, but until it was time to scale out, we pointed these virtual host names in DNS to the single SharePoint server.

So, for example, for the SharePoint portal site, create a DNS entry for a virtual host called (for example) kerbportal that points to the SharePoint server. Later, you can update this entry in DNS to point to the NLB server. Then, make sure to specify kerbportal.mydomain.com in the Host Header field when you create the portal Web site in the Central Administration application in SharePoint. Similarly, configure virtual names for your My Site and SSP Administration sites. Following this approach, adjust the SPNs that you register to utilize the virtual host names that you will use when you later scale out. If you use this approach, you will not have to reconfigure your SPNs when you later scale out, and you will not have to redeploy your Reporting Services reports or your PPS dashboards.

For more information, see Plan for host-named site collections (Office SharePoint Server).

Installing Software Components

During the installation of the various software components, we also encountered a number of installation challenges. While none of these challenges were showstoppers, we present these resolutions to ensure that you can easily resolve these issues if you encounter them. The challenges we faced and how we resolved them are discussed in the following sections.

Installing the SQL Server Reporting Services Add-in on the SharePoint Server on Windows Server 2008 R2

When we installed the SQL Server Reporting Services Add-in (rsSharepoint.msi) on the SharePoint server, we followed the steps listed in SQL Server Books Online: How to: Install or Uninstall the Reporting Services Add-in. When we attempted to install the Reporting Services Add-in (rsSharepoint.msi) on the SharePoint server on Windows Server 2008 R2, the Reporting Services Add-in did not complete successfully, and the installer rolled back the attempted installation. When we reviewed the Application log in Event Viewer, we discovered numerous “Insufficient SQL database permission” errors. We reran the Reporting Services Add-in from a command prompt with elevated permissions, but we received the same errors. To resolve this issue, we followed the “How to Install Reporting Services Add-in in Files-Only Mode” section in How to: Install or Uninstall the Reporting Services Add-in. These steps enabled us to successfully install the Reporting Services Add-in. We verified the successful installation of the Reporting Services Add-in by reviewing the RS_SP_<N>.log file in C:\Users\<login>\AppData\Local\Temp.

Configuring SQL Server Reporting Services in SharePoint Mode for Windows Authentication with User Access Control Enabled

When we configured Reporting Services integration in SharePoint, we followed the steps listed in SQL Server Books Online: How to: Configure Report Server Integration in SharePoint Central Administration. When we configured report server integration settings in Central Administration, we were unable to select Windows Authentication to specify that connections be made using Windows integrated authentication. While this initially seemed to indicate a Kerberos problem, we checked and double-checked our Kerberos configuration and found no errors. After some research on the Internet using Bing, we found the following article Troubleshooting Server and Database Connection Problems that suggested that User Account Control (UAC) might be the problem – although the problem described in this article was not exactly the same as the one we were experiencing. We closed the Central Administration site in Internet Explorer and reconnected with elevated permissions, and then we were successfully able to select Windows Authentication for our SharePoint integration with Reporting Services.

Installing Prerequisites for PerformancePoint Monitoring Server on the SharePoint Server with SQL Server 2008

When we installed the prerequisites for PerformancePoint Monitoring Server on the SharePoint Server with SQL Server 2008, we followed the steps listed in the following TechNet article: Install PerformancePoint Monitoring Server with SQL Server 2008. However, we were unable to install the SQLServer2005_XMO component (XMO 9.0), which is a prerequisite for a fully-functional PPS installation. After some research on the Internet using Bing, we discovered Knowledge Base article 970496, which explained that there was a known incompatibility between the SQL Server Reporting Services Add-in and XMO 9.0. This bug prevents the installation of PerformancePoint Planning Server (not Monitoring Server) and the importing of key performance indicators (KPIs) from SQL Server when using Monitoring Server. However, further research revealed that this bug was fixed in both the Cumulative Update Package 5 for SQL Server 2005 Service Pack 3 and the Cumulative Update Package 15 for SQL Server 2005 Service Pack 2. Details of the fix can be found in Knowledge Base article 972694. By using the newest version of XMO 9.0, we were able to install the SQL Server 2005 Management Objects Collection and subsequently import KPIs from SQL Server.

Use the Application Pool for Root PPS Site and Subsites

When we installed PerformancePoint Monitoring Server, we followed the steps listed in the following TechNet article: Configure Monitoring Server for Kerberos delegation. As discussed previously, we configured a unique application pool and identity for the PPSMonitoringPreview and PPMonitoringWebService subsites, as well as a unique virtual host name and port number for the PPS Web application. The virtual host name enables us to use the appropriate Kerberos ticket for the PPS service account. However, when we used Internet Explorer to connect to the PPSMonitoring site (HTTP://ppsmonitoring.mydomain.com:40000), we were unable to connect to either the WebService or the Preview subsites. Because we were reasonably confident of our Kerberos configuration at this point, we immediately looked at the identity for the application pool for the root site as well as these two subsites. We discovered that while we had configured the application pool identity for each of the subsites to the PPS service account, the root site was not similarly configured – instead, it was configured to use the NetworkService account. As soon as we changed the identity of the application pool for the root site to the PPS service account, PPS worked as expected. Upon rereading the TechNet article, we noted that this step was not clearly stated.

SharePoint with Report Builder 1.0, Not Report Builder 2.0

During our testing of this solution, we created some reports using Report Builder 2.0 to display the user identity when connecting to our SQL Data servers in order to verify that Kerberos was configured and working properly. When we attempted to open one of these reports using the Report Builder ClickOnce application, we received the following error: “System.IO.StreamReader: The Report Element was not found.”

After a quick bit of research on the Internet using Bing, we learned that by default, the ClickOnce application opens Report Builder 1.0 rather than Report Builder 2.0. The solution is to install the add-in, Microsoft® SQL Server® 2008 Report Builder 2.0 ClickOnce for SharePoint in order to make Report Builder 2.0 ClickOnce available from a SharePoint site.

Note: This add-in is separate from the ClickOnce version of Report Builder 2.0 in SQL Server 2008 PCU1.

You then need to configure the default ClickOnce application in Report Manager and in SharePoint Central Administration to open Report Builder 2.0 rather than Report Builder 1.0. The following ReadMe and SQL Server Books Online articles explain the problem and solution:

· Microsoft SQL Server 2008 Report Builder 2.0 ClickOnce for SharePoint Readme

· How to: Set Report Builder 2.0 as the Default ClickOnce Report Builder Version

Summary

With proper planning, you can successfully you can successfully deploy a server farm using Microsoft Office SharePoint Server 2007, Microsoft SQL Server 2008 Reporting Services, and Microsoft Office PerformancePoint Server 2007 Monitoring Server, and you can configure Kerberos authentication for user authentication throughout the server farm. The use of virtual host names for each Web application with a unique application pool identity enables you to specify unique SPNs for each of these Web applications such that Internet Explorer clients can seamlessly connect to the resources within the server farm using their own authentication credentials.

Using Replication for High Availability and Disaster Recovery

Nicholas Dritsas — Tue, 22 Sep 2009 16:29:00 GMT

Writers: Manaphan Huntrakoon, Nicholas Dritsas

Technical Reviewers: Burzin Patel, Sanjay MishraPublished: August 2009 Summary: An essential part of IT mission is maintaining high availability and disaster recovery capability. This technical case study shows how a company can use SQL Server 2008 database replication to eliminate single points of failure in data centers, and to enable fast recovery from a possible disaster at its primary data center. These strategies and solutions will be of interest to database administrators, senior IT managers, project leads, and architects.Introduction: An international company has deployed a number of SQL Server instances spread out over different locations in Asia, the United States and Europe. The main problem they wanted to resolve was the high availability of data critical to their main application. In the event this data ever became unavailable, the financial repercussions would have been substantial. While the company had many ways and methods to alleviate this problem, they were unsure of which SQL Server high availability/disaster recovery technology would address their problem plus provide a strategic platform for the future. They engaged with Microsoft to help in the design of an architecture best fit for their needs, budget, expectations and skill set. In this document, we will be going through the steps and processes the project team went through to evaluate and decide on the best choices and options.
For more details, please look in this white paper: Using Replication for High Availability and Disaster Recovery in SQL Server 2008.

Resolving PAGELATCH Contention on Highly Concurrent INSERT Workloads

tkejser — Tue, 22 Sep 2009 09:50:00 GMT

Authors: Thomas Kejser, Lindsey Allen, Arvind Rao and Michael Thomassy

Contributors and reviewers: Mike Ruthruff, Lubor Kollar, Prem Mehra, Burzin Patel, Michael Thomassy, Mark Souza, Sanjay Mishra, Peter Scharlock, Stuart Ozer, Kun Cheng and Howard Yin

Introduction

Recently, we performed a lab test that had a large OLTP workload in the Microsoft Enterprise Engineering Center. The purpose of this lab was to take an intensive Microsoft SQL Server workload and see what happened when we scaled it up from 64 processors to 128 processors. (Note: This configuration is supported as part of the Microsoft SQL Server 2008 R2 release.). The workload had highly concurrent insert operations going to a few large tables.

As we began to scale this workload up to 128 cores, the wait stats captured were dominated by PAGELATCH_UP and PAGELATCH_EX. The average wait times were tens of milliseconds, and there were a lot of waits. These waits were not expected, or they were expected to be a few milliseconds only.

In this TechNote we will describe how we first diagnosed the problem and how we then used table partitioning to work around it.

Diagnosing the Problem

When you see large waits for PAGELATCH in sys.dm_os_wait_stats, you will want to do the following. Start your investigation with sys.dm_os_waiting_tasks and locate a task waiting for PAGELATCH, like this:

SELECT session_id, wait_type, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%'

Example Output:

The resource_description column lists the exact page being waited for in the format: <database_id>:<file_id>:<page_id>.

Using the resource_description column, you can now write this rather complex query that looks up all these waiting pages:

SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms
, s.name AS schema_name
, o.name AS object_name
, i.name AS index_name
FROM sys.dm_os_buffer_descriptors bd
JOIN (
SELECT *
, CHARINDEX(':', resource_description) AS file_index
, CHARINDEX(':', resource_description
  , CHARINDEX(':', resource_description)) AS page_index
, resource_description AS rd
FROM sys.dm_os_waiting_tasks wt
WHERE wait_type LIKE 'PAGELATCH%'
) AS wt
    ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)
    AND bd.file_id = SUBSTRING(wt.rd, wt.file_index, wt.page_index)
    AND bd.page_id = SUBSTRING(wt.rd, wt.page_index, LEN(wt.rd))
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.partition_id
JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id
JOIN sys.objects o ON i.object_id = o.object_id

JOIN sys.schemas s ON o.schema_id = s.schema_id

The query shows that the page we are waiting for is in a clustered index, enforcing the primary key, of a table with this structure:

CREATE TABLE HeavyInsert (
ID INT PRIMARY KEY CLUSTERED
, col1 VARCHAR(50)
) ON [PRIMARY]

What is going on here, why are we waiting to access a data page in the index?

Background Information

To diagnose what was happening in our large OLTP workload, it’s important to understand how SQL Server handles the insertion of a new row into an index. When a new row is inserted into an index, SQL Server will use the following algorithm to execute the modification:

Record a log entry that row has been modified.
Traverse the B-tree to locate the correct page to hold the new record.
Latch the page with PAGELATCH_EX, preventing others from modifying it.
Add the row to the page and, if needed, mark the page as dirty.
Unlatch the page.

Eventually, the page will also have to be flushed to disk by a checkpoint or lazy write operation.

However, what happens if all the inserted rows go to the same page? In that case, you can see a queue building up on that page. Even though a latch is a very lightweight semaphore, it can still be a contention point if the workload is highly concurrent. In this customer case, the first, and only, column in the index was a continuously increasing key. Because of this, every new insert went to the same page at the end of the B-tree, until that page was full. Workloads that use IDENTITY or other sequentially increasing value columns as primary keys may run into this same issue at high concurrency too.

Solution

Whenever many threads need synchronized access to a single resource, contention can occur. The solution is typically to create more of the contended resource. In this case, the contended resource is the last page in the B-tree.

One way to avoid contention on a single page is to choose a leading column in the index that is not continually increasing. However, this would have required an application change in the customer’s system. We had to look for a solution that could be implemented within in the database.

Remember that the contention point is a single page in a B-tree. If only there was a way to get more B-trees in the table. Fortunately, there IS a way to get this: Partition the table. The table can be partitioned in such a way that the new rows get spread over multiple partitions.

First, create the partition function and scheme:

CREATE PARTITION FUNCTION pf_hash (TINYNT) AS RANGE LEFT FOR VALUES (0,1,2)

CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY])

This example uses four partitions. The number of partitions you need depends on the amount of INSERT activity happening on the table. There is a drawback to hash-partitioning the table like this: Whenever you select rows from the table, you have to touch all partitions. This means that you need to access more than one B-tree – you will not get partition elimination. There is a CPU cost and latency cost to this, so keep the number of partitions as small as possible (while still avoiding PAGELATCH). In our particular customer case, we had plenty of spare CPU cycles, so we could afford to sacrifice some time on SELECT statements, as long as it helped us increase the INSERT rate.

Second, you need a column to partition on, one that spreads the inserts over the four partitions. There was no column available in the table for this in the Microsoft Enterprise Engineering Center scenario. However, it is easy to create one. Taking advantage of the fact that the ID column is constantly increasing in increments of one, here is a simple hash function of the row:

CREATE TABLE HeavyInsert_Hash(
ID INT NOT NULL
, col1 VARCHAR(50)
, HashID AS CAST(ABS(ID % 4) AS TINYINT) PERSISTED NOT NULL)

With the HashID column, you can cycle the inserts between the four partitions. Create the clustering index in this way:

CREATE UNIQUE CLUSTERED INDEX CIX_Hash
ON HeavyInsert_Hash (ID, HashID) ON ps_hash(HashID)

By using this new, partitioned table instead of the original table, we managed to get rid of the PAGELATCH contention and increase the insertion rate, because we spread out the high concurrency across many pages and across several partitions, each having its own B-tree structure. We managed to increase the INSERT rate by 15 percent for this customer, with the PAGELATCH waits going away on the hot index in one table. But even then, we had CPU cycles to spare, so we could have optimized further by applying a similar trick to other table with high insert rates.

Strictly speaking, this optimization trick is a logical change in the primary key of the table. However, because the new key is just extended with the hash value of the original key, duplicates in the ID column are avoided.

The single column unique indexes on a table are typically the worst offender if you are experiencing PAGELATCH contention. But even if you eliminate this, there may be other, nonclustered indexes on the table that suffer from the same problem. Typically, the problem occurs with single column unique keys, where every insert ends up on the same page. If you have other indexes in the table that suffer from PAGELATCH contention, you can apply this partition trick to them too, using the same hash key as the primary key.

Not all applications can be modified, something that is a challenge for ISVs. However, if you DO have the option of modifying the queries in the system, you can add an additional filter to queries seeking on the primary key.

Example: To get partition elimination, change this:

SELECT * FROM HeavyInsert_Hash
WHERE ID = 42

To this:

SELECT * FROM HeavyInsert_Hash
WHERE ID = 42 AND HashID = CAST(ABS(42 % 4) AS TINYINT)

With partition elimination, the hash partitioning trick is almost a free treat. You will still add one byte to each row of the clustered index.

Accelerating Microsoft adCenter with Microsoft SQL Server 2008 Analysis Services

denny.lee — Sat, 19 Sep 2009 23:12:00 GMT

How Microsoft adCenter takes advantage of groundbreaking performance and scalability enhancements and innovative design tools available with Microsoft SQL Server 2008 Analysis Services

For more information, please refer to the whitepaper Accelerating Microsoft adCenter with Microsoft SQL Server 2008 Analysis Services.

Executive Summary

As an Internet platform for online advertising campaigns on Microsoft® Bing™, Microsoft adCenter offers customers a wide range of intelligence and analytics tools to target, reach, and engage the right audience with the right ad at the right time. A complex, multi-faceted Online Analytical Processing (OLAP) environment based on SQL Server® 2008 Analysis Services drives the adCenter intelligence solutions. This large-scale environment includes numerous data marts that consolidate information from various sources through SQL Server 2008 Integration Services. The adCenter data marts, based on SQL Server 2008 Enterprise Edition, maintain up to two terabytes of data in relational databases, equivalent to one month of Web activities at a current accumulation rate of approximately 60 to 70 gigabytes per day. The OLAP cubes built on top of these relational stores hold 13 months of data and are up to three terabytes in size.

Prior to SQL Server 2008, adCenter relied on Microsoft SQL Server 2005 as the technological foundation of its intelligence and analytics infrastructure. Among other benefits, SQL Server 2005 enabled adCenter to take full advantage of the 64-bit platform, the Unified Dimensional Model (UDM), and Multidimensional Expressions (MDX). However, it was difficult to sustain processing and query execution performance levels during peak usage as scalability became a limiting factor. At 500 users per cube, adCenter began to notice performance issues, challenging a continually growing user base. By upgrading to SQL Server 2008, adCenter was able to break through existing limitations without the need to replace server hardware or redesign infrastructure. The performance and scalability improvements are vital for keeping adCenter agile, competitive, and ready for further growth.

In comparison to its predecessor, SQL Server 2008 offers better hardware utilization, optimized cube processing, and substantially faster MDX query execution, and it promotes efficient UDM designs and code quality in analytics solutions. These are key factors that enable adCenter to increase its return on investment (ROI) in server hardware by allowing more users per server, while at the same time improving user experience and service level agreements (SLAs). For the most critical data marts, adCenter SLAs now state that 80 percent of the queries must return results in 3 seconds or less. Moreover, adCenter gained new options to track service level compliance and establish baselines for Analysis Services capacity planning. By exploiting the new monitoring capabilities in addition to Analysis Services traces, such as Management Data Warehouse (MDW) and new performance counters for Analysis Services-related system resources, adCenter can locate root causes of performance issues faster than with traces alone. This ultimately increases the productivity of OLAP developers when troubleshooting performance issues and optimizing analytics solutions.

The upgrade to SQL Server 2008 also allows adCenter to prepare for future OLAP infrastructure optimizations and relational data-warehousing technologies, such as SQL Server code-named Project "Madison." The adCenter Data Mart team has developed concrete plans to assess a load-balanced scale-out data warehouse appliance for the largest data marts and data warehouses. adCenter also plans to increase the size of the relational data stores to a massive volume of more than 150 terabytes as soon as Madison becomes available. This will allow adCenter to maintain between five and six months of Web activity data online, assuming an increase in data collection rates of one terabyte per day. For adCenter, the upgrade to SQL Server 2008 represents an important step toward the future.

The purpose of this technical white paper is to share Microsoft knowledge, experiences, and recommendations related to the architecture and design of SQL Server 2008 Analysis Services for high performance and scalability in a large-scale OLAP environment. This paper is not intended to serve as a procedural guide. Although many organizations have similar requirements, each enterprise environment also has unique needs, making it necessary to adapt the information discussed in this paper.

This white paper assumes that readers are IT professionals and technical decision makers, already familiar with Windows Server®, Active Directory®, and SQL Server. Specifically, knowledge about SQL Server 2008 Analysis Services, Integration Services, and Business Intelligence Development Studio is helpful. Detailed product information is available in the SQL Server 2008 TechCenter at http://technet.microsoft.com/en-us/sqlserver/default.aspx.

Note: For security reasons, the sample names of forests, domains, servers, databases, and other internal resources used in this paper do not represent real resource names used within Microsoft and are for illustration purposes only.

For more information, please refer to the whitepaper Accelerating Microsoft adCenter with Microsoft SQL Server 2008 Analysis Services.

Configure a Sharepoint Server Farm for minimal downtime during software updates

Lindsey.allen — Fri, 18 Sep 2009 21:32:00 GMT

This article describes how to update Office SharePoint Server farms with minimal downtime. By following the procedures in this article, you can update servers to add the latest service pack or cumulative update without incurring significant downtime for users. This article describes a step-by-step process for Office SharePoint Server farms that incorporate SQL Server database mirroring.

It often takes several teams or roles in an organization to configure, upgrade, and maintain a server farm environment. To implement this solution, you must confer with network engineers, SQL Server database administrators, and all affected SharePoint farm administrators.

This article is written for IT professionals who maintain server farms that are running Microsoft Office SharePoint Server 2007 with Service Pack 2 (SP2) .

The complete step by step instruction can be found here: http://technet.microsoft.com/en-us/library/ee514459.aspx

Looking deeper into SQL Server using Minidumps

Microsoft SQL Server Development Customer Advisory Team — Fri, 11 Sep 2009 15:43:24 GMT

Author: Thomas Kejser Reviewers and Contributors: Bob Ward, Michael Thomassy, Juergen Thomas, Hermann Daeubler, Mark Souza, Lubor Kollar, Henk van der Valk (Unisys) and Peter Scharlock For advanced troubleshooting and understanding of SQL Server, you can sometimes benefit from creating a dump file of the sqlservr.exe process. What is a dump? It is a file containing a snapshot of the running process – and parts or all of the memory space of that process. The snapshot also contains the call stack of...(read more)