1. Storage
Partition the hard disk into NTFS format. NTFS is faster than FAT32, And you can view the size of your data file. You can use multi-database files for 1 GB or more, in this way, the access load can be distributed to multiple physical hard disks or disk arrays.
2. tempdb
Tempdb should also be placed on a separate physical hard disk or disk array. We recommend that you place it on RAID 0 so that it has the highest performance. Do not set the maximum value for it to automatically increase.
3. Log Files
Log Files should also be separated from data files on different disks or disk arrays, which can also improve hard disk I/O performance.
4. Partition View
It is to split your data horizontally on the cluster server. It is suitable for large-scale OLTP and SQL clusters. It is not recommended if your database is not highly accessible.
5. Cluster Index
Your table must have a cluster index. When you use a cluster index for query, the block query is the fastest. If you use between, it should be physically continuous, you should try to reduce the updaet on it, because this can make it physically discontinuous.
6. Non-Cluster Index
Non-cluster indexes have nothing to do with the physical order. When designing a non-cluster index, it must have a high degree of selectivity, which can improve the query speed. However, during table update, these non-cluster indexes will affect the speed and occupy a large amount of space, if you are willing to exchange space and modification time, you can consider the speed.
7. Index View
If an index is created on a view, the result set of the view is stored, and the query performance can be improved significantly, however, when the update statement is used, it also seriously reduces the performance and is generally used in a data warehouse with relatively stable data.
8. Maintain Indexes
Regular maintenance is very important after you build indexes. You can use dbcc showcontig to observe page density and scan density, and use dbcc in a timely manner.
Indexdefrag is used to sort out the index of a table or view. If necessary, using dbcc dbreindex to reconstruct the index can be very effective.
No matter how many tables you use, such as tables 1, 2, and 3, you can improve performance. You must do so at, 6, or 8, I personally do not recommend this.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
