November 1, 2011 at 1:35 pm
we have two data files in the same filegroup "PRIMARY" in the database.
We i run sp_helpindex, all the indexes "located on PRIMARY", i wanna know which data file they are created, how am i able to find out ?
And sp_helpconstraint only shows the relationship, is there a way to find out constraints physical location ?
ddfg
November 1, 2011 at 1:58 pm
They'll be in both. If there are multiple files in a filegroup, SQL spreads data across all of them.
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
November 1, 2011 at 2:01 pm
Gail is spot on with her answer.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 1, 2011 at 2:09 pm
Gail: I was trying to find a reference I could quote for that same answer. I know the data, but I can't remember where I read it. Any reference? (Search engine "sql server tables files" is just giving me page after page of how to import flat files.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 1, 2011 at 2:14 pm
Gus - try this one
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/17/948.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 1, 2011 at 2:16 pm
GSquared (11/1/2011)
Gail: I was trying to find a reference I could quote for that same answer. I know the data, but I can't remember where I read it. Any reference? (Search engine "sql server tables files" is just giving me page after page of how to import flat files.)
Try this: http://msdn.microsoft.com/en-us/library/ms190433.aspx (searched "Where is the index stored when 1 filegroup spans 2 files")
I think the idea is that SQL Server defines the index's storage by FILEGROUP, therefore making it impossible to determine how much of the index resides on either physical file.
Jared
Jared
CE - Microsoft
November 1, 2011 at 2:24 pm
jared-709193 (11/1/2011)
GSquared (11/1/2011)
Gail: I was trying to find a reference I could quote for that same answer. I know the data, but I can't remember where I read it. Any reference? (Search engine "sql server tables files" is just giving me page after page of how to import flat files.)Try this: http://msdn.microsoft.com/en-us/library/ms190433.aspx (searched "Where is the index stored when 1 filegroup spans 2 files")
I think the idea is that SQL Server defines the index's storage by FILEGROUP, therefore making it impossible to determine how much of the index resides on either physical file.
Jared
Be careful with the approach of placing an index on a different filegroup (as mentioned in the article) when dealing with large databases and piecemeal restores. In that type of setup, where filegroup backups are performed and piecemeal restores are plausible, the indexes should be in the same filegroup.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 1, 2011 at 2:29 pm
SQLRNNR (11/1/2011)
jared-709193 (11/1/2011)
GSquared (11/1/2011)
Gail: I was trying to find a reference I could quote for that same answer. I know the data, but I can't remember where I read it. Any reference? (Search engine "sql server tables files" is just giving me page after page of how to import flat files.)Try this: http://msdn.microsoft.com/en-us/library/ms190433.aspx (searched "Where is the index stored when 1 filegroup spans 2 files")
I think the idea is that SQL Server defines the index's storage by FILEGROUP, therefore making it impossible to determine how much of the index resides on either physical file.
Jared
Be careful with the approach of placing an index on a different filegroup (as mentioned in the article) when dealing with large databases and piecemeal restores. In that type of setup, where filegroup backups are performed and piecemeal restores are plausible, the indexes should be in the same filegroup.
Can you elaborate on that? We currently have our largest database partitioned with the data into separate filegroups for each month and the same for the partitioned indexes into their own "index" monthly filegroups; i.e. 25 filegroups. Why is this bad? (also, old months have their corresponding 2 filegroups read only)
Thanks,
Jared
Jared
CE - Microsoft
November 1, 2011 at 2:31 pm
jared-709193 (11/1/2011)
GSquared (11/1/2011)
Gail: I was trying to find a reference I could quote for that same answer. I know the data, but I can't remember where I read it. Any reference? (Search engine "sql server tables files" is just giving me page after page of how to import flat files.)Try this: http://msdn.microsoft.com/en-us/library/ms190433.aspx (searched "Where is the index stored when 1 filegroup spans 2 files")
I think the idea is that SQL Server defines the index's storage by FILEGROUP, therefore making it impossible to determine how much of the index resides on either physical file.
Jared
That covers multiple filegroups and spreading data on them. Found that no problem. Was looking for reference on multiple files within a file group.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 1, 2011 at 2:31 pm
SQLRNNR (11/1/2011)
Gus - try this onehttp://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/17/948.aspx
That's good. I was hoping for something on MSDN, but at least that allows testing of the concept.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 1, 2011 at 2:35 pm
GSquared (11/1/2011)
SQLRNNR (11/1/2011)
Gus - try this onehttp://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/17/948.aspx
That's good. I was hoping for something on MSDN, but at least that allows testing of the concept.
Found it! http://msdn.microsoft.com/en-us/library/ms187087.aspx
Thanks,
Jared
Jared
CE - Microsoft
November 1, 2011 at 2:55 pm
jared-709193 (11/1/2011)
I think the idea is that SQL Server defines the index's storage by FILEGROUP, therefore making it impossible to determine how much of the index resides on either physical file.
It is possible to determine, but there's no real reason to do so, SQL considers multiple files in one filegroup to be all part of a whole and anything placed on a filegroup will be spread across all the files in it.
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
November 1, 2011 at 3:20 pm
Thanks guys. So, those indexes will be created across both data files.
What about constraints ? Where are they stored ?
ddfg
November 1, 2011 at 4:06 pm
Primary key and unique constraints are enforced by indexes, so, exactly the same as normal indexes.
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
November 1, 2011 at 5:42 pm
jared-709193 (11/1/2011)
SQLRNNR (11/1/2011)
jared-709193 (11/1/2011)
GSquared (11/1/2011)
Gail: I was trying to find a reference I could quote for that same answer. I know the data, but I can't remember where I read it. Any reference? (Search engine "sql server tables files" is just giving me page after page of how to import flat files.)Try this: http://msdn.microsoft.com/en-us/library/ms190433.aspx (searched "Where is the index stored when 1 filegroup spans 2 files")
I think the idea is that SQL Server defines the index's storage by FILEGROUP, therefore making it impossible to determine how much of the index resides on either physical file.
Jared
Be careful with the approach of placing an index on a different filegroup (as mentioned in the article) when dealing with large databases and piecemeal restores. In that type of setup, where filegroup backups are performed and piecemeal restores are plausible, the indexes should be in the same filegroup.
Can you elaborate on that? We currently have our largest database partitioned with the data into separate filegroups for each month and the same for the partitioned indexes into their own "index" monthly filegroups; i.e. 25 filegroups. Why is this bad? (also, old months have their corresponding 2 filegroups read only)
Thanks,
Jared
As long as you understand that both filegroups should be restored during your piecemeal restore as you are coming back online from a disaster. To get back online for that section of the database you now need to restore two filegroups in lieu of one. Depending on size - this could be bad. Main point is that you must be aware that you should have the data and indexes restored during a piecemeal restore. We can maintain SLA by not having the entire database restored so long as critical facets are restored and the app can access them in a timely manner.
I have not found it any more efficient (performance wise) to have the indexes in a separate filegroup than in the same filegroups as the data. Testing is a must.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply