Category: sql server manageability
Ash159753 on Wed, 29 Mar 2017 14:43:30
I am looking for partition strategy on certain existing tables in SQL Server DB. There are thousand of queries written on these tables, I am looking for a solution where I can partition these tables but I don't have to make any changes to existing tables in order to improve its performance.
if someone can help providing ideas/solution, it would be greatly appreciated.
Please let me know if you need any additional information on this.
Davy_Wang on Thu, 30 Mar 2017 08:17:56
To partition the existing tables , first create one or more filegroups and partition function and schema . The column type is based on what you need .Last create clustered index on your table .
alter database YOURDATABASE add filegroup YourFileGroup alter database YOURDATABASE add file (name= YourFileName ,filename= YourFilePath ,size=YourSize,
filegrowth= GrowthSize) to filegroup YourFileGroup CREATE PARTITION FUNCTION YourFunctionName ( ColumnType ) AS RANGE LEFT FOR
VALUES ( SetValue ) CREATE PARTITION SCHEME YourPartitionScheme AS PARTITION YourFunctionName TO ( YourFileGroup ) go CREATE CLUSTERED INDEX YourClustName ON YourTableName ( YourColumn ASC ) ON YourPartitionScheme ( YourColumn )
After that you could go to storage tab of table properties to make sure the table is partitioned.
For more information, please refer to this article .
Ash159753 on Fri, 31 Mar 2017 19:48:54
Thank you very much for your reply!
I think creating partition is not much of problem but the real challenge is that we have thousands of queries written on existing 70 to 60 tables, if we do partition based on date range then how will existing query know to go to next partition if not found data from the first partition?
It is not practically possible to make changes those many queries to make use of partition strategies so the challenge is that how do we achieve performance in this situation.
Hilary Cotter on Fri, 31 Mar 2017 19:53:50
As long as your queries align themselves with your partition SQL Server will know. for example if you partition on date and you query on a data range which is found in one partition, the query optimizer will only consult that partition.
If your queries are on name and you partition on date, your queries will likely go against all partitions.
Ash159753 on Mon, 03 Apr 2017 13:52:06
Great! Thank you Hilary!
This is what I was hoping for , so if first partition did not find data by query , SQL Server DB will direct the query call to rest of the partitions.
I think this will definitely help somewhat on the existing query for performance and any new query after partition will be written , we can encourage to use partition based query.
Your reply is valuable.
Davy_Wang on Fri, 07 Apr 2017 08:04:55
Glad to hear that you have resolved the issue. Please make corresponding replies as answer as it would benefit others when they are reading this thread.
If you have any other questions, please let me know.