October 14, 2014 at 8:35 am
I have a big database and 75% space is taken by a table around 490 GB. Now I am told to partition the table. I think, partitioning the table involves adding file group. Can't I just create a file group and put it in different LUN or need to go thru whole partition process.
October 14, 2014 at 2:44 pm
Depends on what you want to achieve.
You can also partition with all partitions to the same filegroup.
What are the reasons why you want to partition this table?
Archiving automation?
Performance?
-- Gianluca Sartori
October 15, 2014 at 5:50 am
For the performance reason. they want to partition 2 big table. Should I just create different file group and put it to different LUN? will that help for backup and rebuild index job?
October 15, 2014 at 6:17 am
smtzac (10/15/2014)
For the performance reason. they want to partition 2 big table.
Then don't waste your time. Partitioning is not for performance reasons. It's not a magic go-faster feature.
https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2014 at 6:22 am
Hello GilaMonster,
it won't help for backup, restore, DBCC CHECKDB, Rebuild Index job?
October 15, 2014 at 6:32 am
Index rebuild, maybe, depends on how you partition and whether you need to rebuild all partitions or not. Bear in mine that online rebuilds can't be done at the partition level before SQL 2014, so you're forced to chose between online rebuilds of the entire index or offline rebuilds of the partition. Rebuilds at the partition level don't update statistics, so if you do rebuild by partition, you need to add an extra step to update stats.
Backup and restore, not unless you're going to do file and filegroup backups and restores (and are completely familiar with them, are happy with the increased complexity and have practiced how to do partial restores, and are running the DB in full recovery model), and can partition in such a way that you can restore only part of the table in a disaster and have the application not break.
CheckDB, no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply