SQL Server Supplements-table partitioning

Recent modifications to the performance aspects of the company database. The results are now being built.

The company has a lot of database problems, briefly listed below:

1. The data sheet document is missing.

2. Database of more than 900 tables, nearly half are backup and some reports, no library processing

3. Large number of tables manually exported by year create a history library

4. Field type abuse, no database optimization

If there is a brother who is unfortunately connected to life, said the chilling point, this is basically a mess. Fortunately, the world will be done in fine, step by step optimization.

My optimization step is to analyze business code, complete database document, and transformation Business system, optimization database

Because the business is too complex, not easy to redo, so still take a more tragic approach.

After this is a study and practice of the process it.

Scenarios that use Table partitioning:

In fact, the company's data volume is really not much, the annual data is about 1 million, and the home can not compare. But because the database design is really bad, query performance simply can't bear to look straight.

In addition to building indexes, I also processed the partitioning of dates.

Here are the commands used to record the alternate

--========================================
--Create file groups

New, file group, properties--Select Database

--========================================
--Create TABLE partition function
create partition function fnpartition (int) as Range right
For values (100,200,300)

SELECT * FROM Sys.partition_functions
--========================================
--Create a table partition schema
create PARTITION Scheme Schemaforpartition
As Partition fnpartition
to ([Primary],[primary],[primary],[primary])
SELECT * FROM Sys.partition_schemes

--========================================
--Modifying table partitions
ALTER TABLE Base_user drop constraint pk_base_user_userid--Delete the primary key for an existing table (the primary key is stripped from the only re-organizer with full-text indexing)
ALTER TABLE [dbo]. Base_user with NOCHECK ADD
CONSTRAINT pk_base_user_id PRIMARY KEY CLUSTERED
(
[userid]--Note ID Here are the columns of your original primary key
) on Schemaforpartition ([UserID])--Create a primary key onto the PS_PRODUCT_SCHEME_MTB partition function

SELECT *, $PARTITION. fnpartition ([UserID]) as PF from Base_user
--=============================================
--what filegroups exist in the partition after partitioning
Alter PARTITION scheme schemaforpartition next used ' primary '
--Add a split point
Alter PARTITION function fnpartition () split range (400)
--=============================================
--merge split points
Alter PARTITION function fnpartition () merge range (100)

Reference:

http://kb.cnblogs.com/page/73921/

Http://www.cnblogs.com/sienpower/archive/2011/12/31/2308741.html

SQL Server Supplements-table partitioning