Discussion on database management and optimization (MS SQL Server)

Database management

Talking about database management —————— The first thing to say is database design, a good database design concept can avoid many databases caused by the trouble and the cost of late maintenance

1, the first thing to consider is the database permissions and security, first of all conditional priority is to deploy the database in the database cluster, of course, the amount of data is not very expensive to consider from the cost can be used is not recommended

Permissions This is mainly the login user rights and the structure of a reasonable collocation, preferably a schema corresponding to a user name, convenient late specification and maintenance

2, say the permissions of the database is the main storage of data, how to save data, how to standardize, how to establish the index, the amount of data in the late, how to partition, I believe these are many small partners in the heart of the puzzle

(1) How to store data and specification data? As a database designer, first of all in the data volume problem do not stop at the current amount of data, but as far as possible to maximize the amount of evaluation data, even more than 10 times times the amount of evaluation data, the database design, and then standardize the data: to refine the data to be stored, to minimize sink in the data, It then makes a reasonable index of the frequently-operated tables. In this way for the early database to build such a vast project even completed. Of course, my library is not very detailed, for example, each table must have a unique primary key or a mixed primary key, the database file is placed there and so I don't say it all.

Of course, here's a small thing to tell you a little common sense: the best design Database manager can not be physically deleted is that we often say delete, instead of deleting the flag instead of delete. In this, don't ask me why, okay, everybody use it and you know it.

(2) How to build an index? This people do not think that is who will build an index is good, small where to tell everyone is not so, we think billions of data how? Query is not a headache? Xiaohe told us that nearly billion of data query seconds of things, of course, not all here, but in accordance with a certain criteria to query a small amount of data. You know why it's so cool? is to build my index well. Often see a lot of people on the Internet to say that the volume of millions is very large, I said, you can study the index, not what the partition even more said the data warehouse can solve the problem.

(3) When is the partition?? How do I partition it? Generally a table of data more than 10G can be partitioned, so that the query will be faster, but also easy to manage.

The partitioning method mainly has three kinds of range partition, hash partition (hash partition), compound partition

A range partition is the partitioning of a range of values in a data table, depending on the extent of a value, deciding which partition to store the data on. For example, according to the sequence number partition, according to the Business record creation date partition and so on. The downside is that it may cause uneven distribution of maintenance costs in the late

A hash partition (hash partition) is a type of partition that distributes data evenly by specifying the partition number, because the partitions are identical in size by hashing on the I/O device.

Composite partitioning sometimes we need to partition the data in each partition in a few table spaces, so we're going to use a composite partition. A composite partition is a partitioning method that uses a range partition and then uses a hash partition within each partition, such as partitioning the records of an item transaction by time, then dividing the data in each partition by three sub-partitions, and storing the data in three specified tablespace

The small database of what's going on down here.!!!!!!

Here are some of the providers of database tuning direction see this article's partners can have some direction

1, storage partition disk into NTFS format, NTFS faster than FAT32, and look at your data file size, more than 1G you can use multi-database files, so that the access load can be dispersed across multiple physical hard disks or disk arrays.

2, tempdb tempdb should also be on a separate physical hard disk or disk array, it is recommended to put on RAID 0, so that it has the highest performance, do not set it to the maximum value let it automatically grow

3. log file Log files should also be separated from the data files on different drive or disk arrays, which can also improve the hard disk I/O performance.

4, partitioned view is to divide your data horizontally on the cluster server, it is suitable for large-scale oltp,sql cluster, if your database is not access particularly large not recommended to use.

5, cluster index your table must have a cluster index, when using the cluster index query, the chunk query is the fastest, such as with between, should be a physical continuous, you should try to reduce its updaet, should be for this can make it physical discontinuous.

6, non-clustered index non-clustered index independent of the physical order, design it must have a high degree of selectivity, can improve the query speed, but when the table update these non-clustered index will affect the speed, and occupy a large space, if you are willing to use space and modify time for speed can be considered.

7. Indexed views If you index on a view, the result set of the view is stored, which can improve performance for a particular query, but it also severely reduces performance when the UPDATE statement is used, typically in a data warehouse with relatively stable data.

8, maintenance Index After you have built the index, regular maintenance is important, using DBCC SHOWCONTIG to observe page density, scan density, and so on, timely use DBCC INDEXDEFRAG to organize the table or view index, when necessary with DBCC Dbreindex to rebuild the index can be a good result.

Whether you are using a few tables 1, 2, 3 points can improve a certain performance, 5, 6, 8 points you have to do, as for 4, 7 points to see your needs.

Discussion on database management and optimization (MS SQL Server)