Design Federated database servers to achieve the high performance level required for large Web sites, multi-tier systems typically balance the processing load of each layer across multiple servers. Microsoft®sql server™2000 Scales the database processing load across a group of servers by horizontally partitioning the sql server data. These servers are independent of each other, but can also collaborate with each other to process database requests from the application, a group of collaboration servers called consortia. The federated database tier can achieve very high levels of performance only when the application sends each SQL statement to a member server that has most of the data needed for the statement. This is referred to as the data configuration SQL statement required to use the statement. Using the required data configuration SQL statements is not unique to the federation server, as is the same requirement in a clustered system. Although the server federation is the same as the image presented to the application by a single database server, there is an internal difference in how the database service tier is implemented.
| Single Server Tier |
Federated Server Layer |
| There is an instance of SQL Server on the production server. |
There is an instance of SQL Server on each member server. |
| The production data is stored in a database. |
Each member server has a member database. Data is distributed between member databases. |
| Typically, each table is a single entity. |
The tables in the original database are partitioned horizontally into member tables. A member database has a member table, and a distributed partitioned view makes it appear that each member server has a full copy of the original table. |
| All connections to a single server and all SQL statements are handled by the same instance of SQL Server. |
The application layer must be able to configure the SQL statement on a member server that contains most of the data referenced by the statement. |
Although the goal is to design a federation of database servers to handle all workloads, this can be done by designing a set of distributed partitioned views that distribute data between different servers.
Database DesignThe database design consists of two components: logical design and physical design. Logical database design involves using database components such as tables and constraints to model business requirements and data without having to consider how or where to physically store the data. Physical database design involves mapping logical design to physical media, leveraging available hardware and software capabilities to physically access and maintain data as quickly as possible, including building indexes. It is difficult to change these components after design, so it is important to properly design the database in the early stages of database application development, to model the business requirements and take advantage of hardware and software features. To achieve the optimization of SQL Server database, we should first have a good database design scheme. In practice, many SQL Server scenarios tend to be poorly designed to cause poor performance. Implementing a good database design must consider these issues:
1.1 Normalization of logical librariesIn general, the logical database design meets the normalized Top 3 standards: 1.1th specification: Columns that do not have duplicate groups or multiple values. 2.2nd specification: Each non-critical field must rely on the primary key and cannot rely on some parts of the 1 modular primary keywords. 3.3rd specification: 1 non-critical fields cannot be dependent on another 1 non-critical fields. The design that adheres to these rules produces fewer columns and more tables, thereby reducing data redundancy and reducing the number of pages used to store data. However, table relationships may need to be handled through complex mergers, which can degrade system performance. To some extent, the non-standard can improve the performance of the system, the non-standard process can be based on the performance of different considerations in a number of different methods, but the following methods are often validated by practice to improve performance. 1. If the normalized design produces many 4-way or more road-merging relationships, consider adding duplicate attributes (columns) 2 to the Database Entity (table). Commonly used computed fields, such as totals, maximum values, and so on, can be considered for storage in a database entity. For example, the plan management system of a project has a schedule, its field: Project number, plans for the year, two plans, adjustment plans, replenishment plans, and the total number of plans (planned for the Year + two plans + adjustment plan + replenishment plan) are frequently used in queries and reports, and in the case of a large amount of records, it is necessary Add the total number of plans to the table as 1 separate fields. Triggers can be used here to maintain data consistency at the client. 3. Redefine the entity to reduce the expense of external property data or row data. The corresponding non-standard types are: (1) Dividing 1 entities (tables) into 2 tables (dividing all the attributes into 2 groups). This separates the frequently accessed data from the less-visited data. This method requires that the primary keyword be copied in each table. The resulting design facilitates parallel processing and produces a table with fewer columns. (2) Dividing 1 entities (tables) into 2 tables (dividing all rows into 2 groups). This approach applies to entities that will contain large amounts of data (tables). History is often preserved in applications, but history is rarely used. It is therefore possible to separate the frequently accessed data from the less visited historical data. And if a data row is accessed as a subset by a logical workgroup (department, Sales partition, geographic area, and so on), this approach can also be beneficial.
1.2 Generating the physical databaseTo choose the basic physical implementation strategy correctly, you must understand the operational characteristics of the database access format and hardware resources, mainly memory and disk subsystem I/O. This is a wide range of topics, but the following guidelines may help. 1. The data types associated with each table column should reflect the minimum storage space required for the data, especially for indexed columns. For example, you can use the smallint type not to use the integer type, so that the index field can be read faster, and can be placed on 1 data pages more rows of data, thus reducing I/O operations. 2. The performance can be improved by placing 1 tables on a physical device and then placing its nonclustered index on 1 different physical devices through the SQL Server segment. In particular, the system uses a number of intelligent disk controllers and data separation technology, the benefits are more obvious. 3. Split a frequently used large table with SQL Server segments and place it on a database device of 2 separate intelligent disk controllers, which can also improve performance. Data separation can also improve performance because multiple heads are being looked up. 4. Using SQL Server segments to store data from text or image columns on 1 separate physical devices can improve performance. 1 dedicated intelligent controllers can further improve performance.
Query Optimization
There are a number of reasons why queries are slow, often as follows:1, no index or no index (this is the most common problem of query slow, is the defect of program design) 2, I/O throughput is small, resulting in a bottleneck effect. 3. No computed columns were created to cause the query to be not optimized. 4, less than 5 memory, slow network speed 6, query out the amount of data too large (you can use multiple inquiries, other methods to reduce data) 7, lock or deadlock (this is also the most common query slow problem, is the defect of program design) 8, sp_lock,sp_ Who, the active user view, is due to read and write competitive resources. 9, returned the unnecessary row and column 10, the query statement is not good, did not optimize
you can refine your query by: 1, the data, log, index on the different I/O devices, increase the reading speed, before the Tempdb should be placed on the RAID0, SQL2000 not support. The larger the amount of data (size), the more important I/O is. 2, longitudinal, transverse partition table, reduce the size of the table (Sp_spaceuse) 3, upgrade hardware 4, according to query conditions, establish index, optimize the index, optimize access mode, limit the data volume of the result set. Note that the fill factor should be appropriate (preferably with the default value of 0). The index should be as small as possible, using a small number of bytes of Lie Jian index Good (reference to the creation of the index), not to a limited number of characters Jianjian a single index such as Gender field 5, improve network speed; 6, to expand the server's memory, Windows 2000 and SQL Server 2000 can support 4-8g memory. Configure virtual Memory: The virtual memory size should be configured based on the services that are running concurrently on the computer. Run microsoft SQL Server? When 2000 , consider setting the virtual memory size to 1.5 times the physical memory installed on your computer. If you have additional Full-text indexing installed, and you intend to run the Microsoft search service to perform full-text indexes and queries, consider configuring the virtual memory size to be at least 3 times the physical memory installed on your computer. Configure the  SQL server max server memory configuration option to the 1.5 Times of physical memory (half of the virtual memory size setting). 7, increase the number of server cpu; However, it must be understood that parallel processing of serial processing requires resources such as memory. The use of parallel or serial stroke is the automatic evaluation of MsSQL selection. A single task can be run on a processor by breaking it into multiple tasks. For example, delays in sorting, linking, scanning, and GROUP by words, SQL SERVER determines the optimal level of parallelism based on the load of the system, and complex queries that consume a large number of CPUs are best suited for parallel processing. However, the update operation is Update,insert and delete cannot be processed in parallel. 8, if you are using like to query, the simple use of index is not good, but full-text indexing, space consumption. like ' a% ' using index like'%a ' do not use the index for like '%a% ' queries, the query time is proportional to the total length of the field value, so you cannot use the CHAR type, but the VARCHAR. A full-text index that is long for a field's value. 9, DB server and application server separation; OLTP and OLAP separation 10, distributed partitioned views can be used to implement a federation of database servers. A consortium is a set of separately managed servers, but they work together to share the processing load of the system. This mechanism for creating a federation of database servers through partitioned data can expand a group of servers to support the processing needs of large multi-tier Web sites. For more information, see Designing a federated database server. (Reference SQL Help file '
