TheBugSlayer
Programmer
Hi guys,
We have a mission-critical database with a very large audit table; more than 200 million rows and growing as I type. This table is one of the bottlenecks of our system. Most user activity on the front-end is audited and a large number of checks are performed against the audit table. The server (2012 EE) is on Always-On Availability and as such the database is replicated instantly to another server through Active Sync.
I restored a backup to a different database on the server containing the replica of the database with the aim of testing partitioning as part of the solution. The transaction date and time clustered index was partitioned and queries that took 5 minutes to run are now running in 7 seconds. See the partition function below:
Now that the test proved mostly successful, I am face with the issue of moving the data between partitions as it ages. Apparently the only way to achieve this is to split, merge or switch partitions. My question is, what is the best approach for moving data between partitions? Is there a way to automate the process or perform the data movement dynamically? I have seen some code online and I have saved a few links but I am not satisfied, between the scant articles and my lack of experience in the matter. I don't want to create a solution that I won't be able to maintain or one that becomes a problem over time.
Thank you for your advice.
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).
We have a mission-critical database with a very large audit table; more than 200 million rows and growing as I type. This table is one of the bottlenecks of our system. Most user activity on the front-end is audited and a large number of checks are performed against the audit table. The server (2012 EE) is on Always-On Availability and as such the database is replicated instantly to another server through Active Sync.
I restored a backup to a different database on the server containing the replica of the database with the aim of testing partitioning as part of the solution. The transaction date and time clustered index was partitioned and queries that took 5 minutes to run are now running in 7 seconds. See the partition function below:
Code:
CREATE PARTITION FUNCTION TransactionTimePF (datetime)
AS RANGE LEFT FOR VALUES ('2013-12-31', '2014-06-30','2014-09-30')
GONow that the test proved mostly successful, I am face with the issue of moving the data between partitions as it ages. Apparently the only way to achieve this is to split, merge or switch partitions. My question is, what is the best approach for moving data between partitions? Is there a way to automate the process or perform the data movement dynamically? I have seen some code online and I have saved a few links but I am not satisfied, between the scant articles and my lack of experience in the matter. I don't want to create a solution that I won't be able to maintain or one that becomes a problem over time.
Thank you for your advice.
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).
