April 15, 2015 at 8:35 am
We are running SQL Server 2014 Enterprise Edition (64-Bit) on Windows 2012 R2 Standard (64-Bit).
1. When to create indexes, before or after data is added? Please address Clustered and Non-Clustered Indexes.
2. To move indexes to it's own filegroup, is it best to create the NON-Clustered Indexes on the separate filegroup with code similar to the example below?
CREATE NONCLUSTERED INDEX IX_Employee_OrganizationLevel_OrganizationNode
ON HumanResources.Employee (OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = ON)
ON TransactionsFG1;
GO
I have read the following links that states that if you create the Clustered Index on a separate filegroup, it would also move the base table to that particular filegroup. (So I take it that you ONLY can move NON-CLustered Indexes to a separate filegroup.)
Placing Indexes on Filegroups:
https://technet.microsoft.com/en-us/library/ms190433(v=sql.105).aspx
By default, indexes are stored in the same filegroup as the base table on which the index is created. A nonpartitioned clustered index and the base table always reside in the same filegroup. However, you can do the following:
• Create nonclustered indexes on a filegroup other than the filegroup of the base table.
Move an Existing Index to a Different Filegroup:
https://msdn.microsoft.com/en-us/library/ms175905.aspx
Limitations and Restrictions
• If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup.
• You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using Management Studio. To move these indexes use the CREATE INDEX statement with the (DROP_EXISTING=ON) option in Transact-SQL.
I am just looking for some best practices, recommendations and things to think about.
Thanks.
April 15, 2015 at 8:45 am
1. If you're about to bulk load data, creating indexes afterwards will speed up the load process. Otherewise, create the indexes upfront.
2. The clustered index IS the table, so it can't reaside on a different filegroup from the table. Generally speaking, moving nonclustered indexes to a different filegroup will hardly improve performance. If you can have different filegroups on different physical disks, I suggest that you focus on performance based on the whole workload and move critical objects (the most accessed ones) to their own filegroup. Having (nonclustered) indexes on a separate filegroup is a widespread habit in the Oracle world, but I question its validity even in that realm: it's at best a premature optimization.
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply