PRIMARY KEY NONCLUSTERED (filegroup issue) | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

PRIMARY KEY NONCLUSTERED (filegroup issue)

We have decided to break our database into two filegroups each running on a seperate disk spindle. filegroups: PRIMARY, SECONDARY The SECONDARY filegroup will contain only Indexes. The PRIMARY will have the rest of evrything Here’s my question: If I used this T-SQL:
ALTER TABLE [dbo].[tblOne] ADD CONSTRAINT [PK_tblOne] PRIMARY KEY CLUSTERED
(
[tblOne_id]
) ON [SECONDARY]
GO The leaf level on the CLUSTERED index is data pages. So, would the entire data in the tblOne reside on my SECONDARY Disk spindle?
OR, would the index pages stay on the SECONDARY disk spindle and the data pages remain on the PRIMARY disk spindle.
(THe second option was what I was wanting to get) Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.
Filegroup with Cluster Index has table too.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
I want to have indexes on one disk and the tables on another option 1:
Leave all CLUSTERED indexes on the data disk
Create all NON CLUSTERED indexes on the index disk option 2:
Create ALL indexes on the index disk
Leave the rest on the data disk In option1, will I have two copies of my data?
quote:Originally posted by LuisMartin Filegroup with Cluster Index has table too.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.
HERE’s a restatement of my question: I have two filegroups: PRIMARY, SECONDARY Now, when I run this T-SQL statement, what is actually stored on the PRIMARY and what goes on to SECONDARY , literally from the page level.
Where is the ‘constraint’, the indexpage and the datapage ? ALTER TABLE [dbo].[tblBusAssetResource] ADD CONSTRAINT [PK_tblBusAssetRes] PRIMARY KEY CLUSTERED
(
[BusAssetRes_id]
) ON [SECONDARY]
GO Now, when I run this T-SQL statement, what is actually stored on the PRIMARY and what goes on to SECONDARY, literally from the page level.
Where is the indexpage and the datapage ?
CREATE CLUSTERED INDEX [IX_Person_ID] ON [dbo].[tblBusAssetResource]([Person_id], [BusAssetRes_id]) ON [SECONDARY]
GO Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.
The index plus the data is stored on the secondary filegroup. Nothing is put on the primary filegroup (other than additions to the system tables) A clustered index can’t be separated from the data Cheers
Twan
True. The only way is having all non-cluster index, but this is not recomendable in terms of performance.
If you want to gain performance and manteinanse separating database in two group, put the non cluster index in one groupfile and look for tables most used (with cluster), and put those tables in same filegroup. After that, run Performance Monitor finding balance in Average Disk Queue length for both disk. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
]]>