August 20, 2011 at 5:51 am
1. There were 76 non-clustred Index created in sql 2000 as of now data pages and index pages are same MDF file. after upgrade to sql 2008. I want sperate all the index into sperate NDF file. please provide the script for how to move all the existing index another file? and what are the steps should I followed?
2. Production server having RAID 1+0 disk configuration and two hard disk,total 300GB,
For the best pratices, please suggestion me as follows for disk configuration
1. MDF, NDF file on disk "D"
2. LDF file on disk "E"
3. Tempdb, templog on disk "F"
5. Backup purpose on disk "G"
Thanks
ananda
August 20, 2011 at 6:39 am
Here are come SSC articles that can help out.
http://www.sqlservercentral.com/search/?q=move+index+filegroup&t=a&t=s
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 20, 2011 at 6:39 am
76 nonclustered indexes? First thing I'd do is remove unnecessary/redundant indexes. That's way more than should probably be on a table except in OLAP/datawarehouse.
To move the indexes you use CREATE INDEX ... ON <new filegroup name> WITH DROP_EXISTING
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
August 20, 2011 at 6:45 am
you just need to rebuild the indexes on a new data file. So, steps would basically be:
create new filegroup, perhaps "indexFG"
create new datafile on that filegroup
rebuild indexes on new filegroup (alter index indexname rebuild on XXX)
now when you say, "all existing indexes" you need to reconognize that your clustered indexes contain the actual data pages, so if you relocate those, you are relocating the data. you might want to just relocate your non-clustered indexes.
I did a similar task to the one you described and used a script created by Alexander Karmanov. I know he has it posted on the web somewhere, but since I cant seem to find it, I'm sure he wouldnt mind me reposting. I remember that I did have to perform some tweaking of his code to suit my needs
/*******************************************************************************
** Name: 02a. Tables moved to proper filegroups.sql
** Description: Script moves tables to proper filegroup if it's not aligned
with a list which is derived from the model(s).
Script structure:
- A list (database, table, filegroup) is created.
- Existence of all filegroups is checked - quits if there is an
assumed filegroup that does not exist in the database.
- Every table in the database is checked against that list.
- For those having different filegroup a script is built
and preserved: that script re-creates existing indexes
(creates with drop existing)
I.e. it repeats existing indexes
for the table with the new filegroup specified.
Since some of the non-clustered indexes may be located in
a different filegroup, they are moved to the table's filegroup.
See index (re-)creation script description below.
- Those scripts are executed within a transaction one-by-one.
Index (re-)creation script structure:
1) compose drop index commands list create index commands list
after existing indexes
for non-clustered indexes which are located not in the desired
filegroup or if the clustered index for the table is not in the
desired filegroup.
2) compose create index (with drop existing) commands for the clustered
indexes that are to be moved. For tables without any clustered
index the PK supporting index is made clustered and recreated with other.
3) compose create index commands list after existing indexes
for non-clustered indexes which are located not in the desired
filegroup or if the clustered index for the table is not in the
desired filegroup. The index creation command does not include
filegroup name, so it will be created in the same filegroup
as the table it belongs to.
Those commands are saved in a table with following structure:
- record id (identity)
- table name
- sequence number (the same as topics listed just above)
- index name
- sql command
The very structure manipulation script will go through that table
in order of table name then sequence number then index name
(latter is just for convinience - to analyze the log).
There will be a transaction for each table - too costly to make
it around everything.
Script comments verbosely its execution process at the run time.
Script is assumed to be a part of incremental build fixig
defect XXXXX - DatabaseB database discrepancies and a similar
one for DatabaseA. They differ only with the table content.
Script is also is assumed to be a tool for operations team
parameterized by the table with tables/filegroups
(see statements inserting data into #desired_layout table variable).
In order to get all the messages in a printable form the script
output must be directed to text or a file.
NOTE: since that script can't move tables with BLOB attribute,
such tables must be moved before by a separate script(s).
**
** Input Parameters: database/table/filegroup mapping in a form
of insert statements (see configurable part below).
** Returns: a table with database/table/index names with filegroup names
for each object: original one, configured(desired) one and the final one,
status of the operation for each index is specified.
** Modification History
** Sr Date Modified By Reason
** 1 05/01/2007 Alexander Karmanov Initial version
** 2 07/17/2007 Alexander Karmanov Added DatabaseA database tables,
added explicit option setting.
** 3 11/14/2007 Alexander Karmanov Reconfigured script:
for tables in XXXXXXXXX repository
specified PRIMARY filegroup instead of YYYYYYYYYYYYYYY.
*******************************************************************************/
use master
go
set nocount on
declare
@script_name sysname
select
@script_name = '02a. Tables moved to proper filegroups.sql'
print replicate('-', 80)
print 'Script name: ' + @script_name
print 'Server: ' + @@servername
print 'Database: ' + db_name()
print 'User: ' + suser_sname()
print 'Start time: ' + cast(getdate() as varchar)
print replicate('-', 80)
go
-- These six SET options must be set to ON
set ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON
-- This option must be set to OFF.
set NUMERIC_ROUNDABORT OFF
/******************************************************************************
Here is a configurable part:
mapping between tables and filegroups
*******************************************************************************/
-- declare a table with desired database tables layout
if object_id('tempdb..#desired_layout') is not null drop table #desired_layout
create table #desired_layout (database_name sysname, table_name sysname, filegroup_name sysname)
insert into #desired_layout (database_name, table_name, filegroup_name)
-- DatabaseA database
select 'DatabaseA', 'system_conformance', 'PRIMARY' union all
select 'DatabaseA', 'system_status', 'PRIMARY' union all
select 'DatabaseA', 'table_a_1', 'SrvA_DbA__DatabaseA' union all
-- DatabaseB database
select 'DatabaseB', 'status_reason_type', 'SrvA_DbB_provider' union all
select 'DatabaseB', 'table_a_1_item', 'SrvA_DbB_DatabaseB' union all
select 'DatabaseB', 'table_a_2', 'SrvA_DbB_DatabaseB' union all
select 'DatabaseB', 'unit_of_measure', 'SrvA_DbB_DatabaseB' union all
select 'DatabaseB', 'workflow_status', 'SrvA_DbB_DatabaseB'
/******************************************************************************
End of configurable part,
below goes the actual code
*******************************************************************************/
go
use tempdb
go
-- a table with the column list for all the indexes in the source database is prepared,
-- but is left emtpy - it's needed to be created prior to a helper UDF
if object_id('tempdb..index_columns') is not null drop table index_columns
create table index_columns (database_name sysname, table_name sysname, index_name sysname,
column_name sysname, key_no int,
is_index_clustered bit, is_index_unique bit, is_column_descending bit)
go
-- a helper function converting a table with column names into a comma-separated list
if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list
go
create function dbo.f_get_column_list (
@database_name sysname,
@table_name sysname,
@index_name sysname
)
returns varchar(8000)
as
begin
declare @col_list varchar(8000)
select @col_list = isnull(@col_list + ', ', '') +
column_name + ' ' +
case is_column_descending when 1 then 'DESC' else '' end
from index_columns
where database_name = @database_name
and table_name = @table_name
and index_name = @index_name
order by key_no
return @col_list
end
go
-- after that point the only 'go' command is the very final one - many variables are reused.
declare
@sql varchar(8000),
@crlf char(2),
@rowcnt int
select
@crlf = char(13) + char(10),
@sql = ''
-- check for existence of all the filegroups mentioned in the layout table, exit if there is any problem:
-- get a list of existing filegroups for all the databases:
if object_id('tempdb..#existing_filegroups') is not null drop table #existing_filegroups
create table #existing_filegroups (database_name sysname, filegroup_name sysname)
select @sql = @sql + @crlf + 'select ''' + database_name + ''' as database_name, groupname as group_name from ' +
database_name + '.dbo.sysfilegroups union all'
from #desired_layout
group by database_name
select
@sql = replace (@sql + ';', 'union all;', '')
insert into #existing_filegroups (database_name, filegroup_name)
exec(@sql)
(select @rowcnt = count(distinct d.filegroup_name)
from #desired_layout d
left join #existing_filegroups e
on d.database_name = e.database_name
and d.filegroup_name = e.filegroup_name
where e.database_name is null)
if @rowcnt > 0
begin
select 'In ' + upper(d.database_name) + ' database there is no filegroup ''' + upper(d.filegroup_name) + ''''
as 'Filegroups that does not exist in the target database:'
from #desired_layout d
left join #existing_filegroups e
on d.database_name = e.database_name
and d.filegroup_name = e.filegroup_name
where e.database_name is null
group by d.database_name, d.filegroup_name
print 'Script is aborted because there is at least one filegroup that does not exist (see above).'
return
end
print 'Check for the missing filegroups completed successfully - all the filegroups mentioned in the list do exist.'
-- get the existing database/table/index/filegroup layout
if object_id('tempdb..#existing_layout') is not null drop table #existing_layout
create table #existing_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,
filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)
select @sql = null
select @sql = isnull(@sql + @crlf + 'union all' + @crlf, '') + 'select ''' + database_name + ''' as database_name, ' + @crlf +
'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' + @crlf +
'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' + @crlf +
'g2.groupname as table_filegroup_name ' + @crlf +
' from ' + database_name + '.dbo.sysobjects o ' + @crlf +
' join ' + database_name + '.dbo.sysindexes i ' + @crlf +
' on o.id = i.id ' + @crlf +
' join ' + database_name + '.dbo.sysfilegroups g ' + @crlf +
' on i.groupid = g.groupid ' + @crlf +
' left join (select c.id, o2.name ' + @crlf +
' from ' + database_name + '.dbo.sysconstraints c ' + @crlf +
' join ' + database_name + '.dbo.sysobjects o2 ' + @crlf +
' on c.constid = o2.id ' + @crlf +
' where o2.xtype in (''PK'', ''UQ'')) k ' + @crlf +
' on o.id = k.id ' + @crlf +
' join ' + database_name + '.dbo.sysindexes i2 ' + @crlf +
' on o.id = i2.id ' + @crlf +
' and i2.indid < 2 ' + @crlf +
' join ' + database_name + '.dbo.sysfilegroups g2 ' + @crlf +
' on i2.groupid = g2.groupid ' + @crlf +
' where o.xtype = ''U'' ' + @crlf +
' and i.name not like ''\_WA%'' escape ''\'' ' + @crlf +
' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' + @crlf +
' and i.indid < 255 ' + @crlf
from #desired_layout
group by database_name
insert into #existing_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)
exec(@sql)
-- compose table with commands changing the database structure
if object_id('tempdb..#sql') is not null drop table #sql
create table #sql (rowid int identity (1,1), database_name sysname, table_name sysname,
index_name sysname, seq_number int, sql varchar(7000))
-- get all the indexes that must be dropped:
-- 1. indexes that are not clustered, are not created as PK or UNIQUE constraint.
-- 2. either:
-- 2a. index is located not in a desired filegroup
-- 2b. clustered index on the table (index belongs to) is located not in a desired filegroup
-- All those indexes got sequence code of 1 meaning it will be executed first.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct e.database_name, e.table_name, e.index_name, 1 as seq_number,
sql = 'use ' + e.database_name + '; drop index dbo.' + e.table_name + '.' + e.index_name
from #existing_layout e
join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where e.is_pk_or_uk = 0
and e.ind_id > 1
and (
e.filegroup_name <> d.filegroup_name
or
e.table_filegroup_name <> d.filegroup_name
)
-- get all the indexes that must be created as clustered with drop_existing option:
-- 1. all the indexes that are clustered or PK or unique
-- (all the PK indexes on the tables that don't have clustered key get here as PK/UQ so the distinction needs to be done -
-- when the CLUSTERED word is included.)
-- All those indexes got sequence code of 2 meaning they will be executed when all the dependant indexes are dropped
-- The very command is to be added later - now just identifiers.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct e.database_name, e.table_name, e.index_name, 2 as seq_number,
sql = 'use ' + e.database_name + '; create %unique% %clustered% index ' + e.index_name + ' on dbo.' + e.table_name +
'(%column_list%) with fillfactor = 90, drop_existing on ' + quotename(d.filegroup_name)
from #existing_layout e
join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where
e.is_pk_or_uk = 1 -- check for PK or unique key
or e.ind_id = 1 -- indid in sysindexes means clustered key on table
-- get all the indexes that are dropped in the first step - with the new destination filegroup and
-- all the parameters like fillfactor, and so on.
-- no need to analyze layouts, just take those that were marked as to be dropped and
-- compose create index statement for them.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct s.database_name, s.table_name, s.index_name, 3 as seq_number,
sql = 'use ' + s.database_name + '; create %unique% nonclustered index ' + s.index_name + ' on dbo.' + s.table_name +
'(%column_list%) with fillfactor = 90 on ' + quotename(d.filegroup_name)
from #sql s
join #desired_layout d
on s.database_name = d.database_name
and s.table_name = d.table_name
where seq_number = 1
-- get attributes, column lists from the existing database
select @sql = null
select @sql = isnull(@sql + @crlf, '') +
'use ' + database_name + @crlf +
'insert into tempdb..index_columns (database_name, table_name, index_name, column_name, ' + @crlf +
' key_no, is_index_clustered, is_index_unique, is_column_descending)' + @crlf +
'select ''' + database_name + ''', o.name as table_name, i.name as index_name, c.name as column_name, k.keyno, ' + @crlf +
' INDEXPROPERTY (o.id, i.name, ''isclustered'') as is_index_clustered,' + @crlf +
' INDEXPROPERTY (o.id, i.name, ''isunique'') as is_index_unique,' + @crlf +
' INDEXKEY_PROPERTY(o.id, i.indid, k.keyno, ''isdescending'') as is_column_descending' + @crlf +
' from ' + database_name + '.dbo.sysobjects o' + @crlf +
' join ' + database_name + '.dbo.sysindexes i' + @crlf +
' on o.id = i.id' + @crlf +
' join ' + database_name + '.dbo.sysindexkeys k' + @crlf +
' on i.id = k.id' + @crlf +
' and i.indid = k.indid' + @crlf +
' join ' + database_name + '.dbo.syscolumns c' + @crlf +
' on k.colid = c.colid' + @crlf +
' and k.id = c.id' + @crlf +
' where ' + @crlf +
' o.xtype = ''U''' + @crlf +
' and INDEXPROPERTY (i.id, i.name, ''isstatistics'') = 0' + @crlf +
''
from #desired_layout
group by database_name
exec(@sql)
-- replace %column_list% placeholders in the index creation statements with proper column list
update #sql
set sql = replace(sql, '%column_list%', dbo.f_get_column_list(database_name, table_name, index_name))
-- specify proper clustered option for indexes:
-- for indexes that are clustered already:
update s
set sql = replace(sql, '%clustered%', 'clustered')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
and ic.is_index_clustered = 1
-- for indexes that are PK in table without any clustered index - we make PK indexes clustered,
-- so it will be rebuilt in the new (desired) filegroup.
update s
set sql = replace(sql, '%clustered%', 'clustered')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
join #existing_layout e
on s.database_name = e.database_name
and s.table_name = e.database_name
and e.ind_id = 0 -- that means the table is a heap right now (no clustered index)
-- for the rest set it as nonclustered
update s
set sql = replace(sql, '%clustered%', 'nonclustered')
from #sql s
-- specify proper unique option for indexes:
-- for indexes that are clustered already:
update s
set sql = replace(sql, '%unique%', 'unique')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
and ic.is_index_unique = 1
-- for the rest set it as non-unique
update s
set sql = replace(sql, '%unique%', '')
from #sql s
-- at this point all the commands are prepared in #sql table
-- and all we need is just to execute them one-by-one.
-- Actions for a table are wrapped into a transaction,
-- so if there is any error no table indexes structures
print cast(getdate() as varchar) + ': Starting processing the tables'
declare
@rowid int,
@database_name sysname,
@table_name sysname,
@index_name sysname,
@seq_number int
declare t cursor static for
select distinct database_name, table_name
from #sql
order by database_name, table_name
open t
fetch next from t into @database_name, @table_name
while @@fetch_status = 0
begin
print cast(getdate() as varchar) + ': Processing table ' + @table_name + ' in ' + @database_name + ' database'
begin tran
declare i cursor static for
select rowid, index_name, sql, seq_number
from #sql
where database_name = @database_name
and table_name = @table_name
order by seq_number, index_name
open i
fetch next from i into @rowid, @index_name, @sql, @seq_number
while @@fetch_status = 0
begin
if @seq_number = 1 print cast(getdate() as varchar) + ': Dropping index ' + @table_name + '.' + @index_name
else if @seq_number = 2 print cast(getdate() as varchar) + ': Moving index ' + @table_name + '.' + @index_name + ' to another filegroup'
else if @seq_number = 3 print cast(getdate() as varchar) + ': Re-creating index ' + @table_name + '.' + @index_name
print cast(getdate() as varchar) + ': A command to be executed: ' + @sql
exec(@sql)
-- if there is an error - stop processing that table, rolling back transaction for it and reporting failure for it
if @@error <> 0
begin
rollback
print cast(getdate() as varchar) + ': Command failed to execute'
break
end
print cast(getdate() as varchar) + ': Command successfully completed'
fetch next from i into @rowid, @index_name, @sql, @seq_number
end
close i
deallocate i
-- if we have uncommitted transaction here that means no error occured in th loop on the indexes
-- and there is no transaction rollback issued above - i.e. everything is fine for that table,
-- report success on it (and commit it of course):
if @@trancount > 0
begin
commit
print cast(getdate() as varchar) + ': Successfully processed table ' + @table_name + ' in ' + @database_name + ' database'
end
fetch next from t into @database_name, @table_name
end -- of loop on t cursor
close t
deallocate t
-- get the tables layout after the changes (similar to what was done before the changes:
-- get the existing database/table/index/filegroup layout
if object_id('tempdb..#updated_layout') is not null drop table #updated_layout
create table #updated_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,
filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)
select @sql = null
select @sql = isnull(@sql + @crlf + 'union all' + @crlf, '') + 'select ''' + database_name + ''' as database_name, ' + @crlf +
'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' + @crlf +
'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' + @crlf +
'g2.groupname as table_filegroup_name ' + @crlf +
' from ' + database_name + '.dbo.sysobjects o ' + @crlf +
' join ' + database_name + '.dbo.sysindexes i ' + @crlf +
' on o.id = i.id ' + @crlf +
' join ' + database_name + '.dbo.sysfilegroups g ' + @crlf +
' on i.groupid = g.groupid ' + @crlf +
' left join (select c.id, o2.name ' + @crlf +
' from ' + database_name + '.dbo.sysconstraints c ' + @crlf +
' join ' + database_name + '.dbo.sysobjects o2 ' + @crlf +
' on c.constid = o2.id ' + @crlf +
' where o2.xtype in (''PK'', ''UQ'')) k ' + @crlf +
' on o.id = k.id ' + @crlf +
' join ' + database_name + '.dbo.sysindexes i2 ' + @crlf +
' on o.id = i2.id ' + @crlf +
' and i2.indid < 2 ' + @crlf +
' join ' + database_name + '.dbo.sysfilegroups g2 ' + @crlf +
' on i2.groupid = g2.groupid ' + @crlf +
' where o.xtype = ''U'' ' + @crlf +
' and i.name not like ''\_WA%'' escape ''\'' ' + @crlf +
' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' + @crlf +
' and i.indid < 255 ' + @crlf
from #desired_layout
group by database_name
insert into #updated_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)
exec(@sql)
-- report difference between the previously existing layout, what was the intent and what we've got after the update:
print 'Script execution status report (see the grid if it is not displayed below):'
select
e.database_name + '.' + e.table_name + '.' + e.index_name as object,
e.filegroup_name as original_fileroup,
isnull(d.filegroup_name, 'NOT CONFIGURED') as desired_new_filegroup,
isnull(u.filegroup_name, 'OBJECT DISAPPEARED!!!') as actual_new_filegroup,
case
when d.filegroup_name is null then 'NOT CONFIGURED'
when u.filegroup_name is null then 'OBJECT WAS LOST'
when d.filegroup_name <> u.filegroup_name then 'NO UPDATE HAPPENED'
else 'ok'
end as update_status
from #existing_layout e
left join #updated_layout u
on e.database_name = u.database_name
and e.table_name = u.table_name
and e.index_name = u.index_name
left join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where e.ind_id > 0 -- no need to show items for the heap (which item is not actually an index anyway).
-- If such heap table has a PK it is shown in a separate line (with ind_id > 1)
order by e.database_name, e.table_name, e.index_name
go
-- clean-up
use tempdb
if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list
if object_id('tempdb..index_columns') is not null drop table index_columns
go
-- to release any database we switched to
use master
go
-- the end of the script
go
-- script output end printed
print replicate('-', 80)
print 'Completion time: ' + cast(getdate() as varchar)
print replicate('-', 80)
go
August 20, 2011 at 9:18 am
Can you just confirm something, are all 4 drive letters simly paritions on the same mirrored pair of discs?
From what you said in the original post it sounds like you only have two PHYSICAL discs in the server, and they are configured as RAID 1 (Raid 1+0 needs more than two), and then they have been logically partitioned in to 4 drives that Windows sees.
If so moving indexes to differerent NDF files, and moving files around partitions is not likely to gain you much at all.
Can you post the full hardware spec and database sizes?
Mike
August 20, 2011 at 10:21 am
Mike John (8/20/2011)
Can you just confirm something, are all 4 drive letters simly paritions on the same mirrored pair of discs?From what you said in the original post it sounds like you only have two PHYSICAL discs in the server, and they are configured as RAID 1 (Raid 1+0 needs more than two), and then they have been logically partitioned in to 4 drives that Windows sees.
If so moving indexes to differerent NDF files, and moving files around partitions is not likely to gain you much at all.
Can you post the full hardware spec and database sizes?
Mike
Mike, I had the same questions - but to expand on this, are the LUNs coming from an Enterprise SAN? If so, how many spindles are supporting those LUNs?
The other question I have is: are you actually experiencing issues that you believe this will resolve?
Just for clarity, I have a 1.3TB database in a single MDF file and no issues with IO performance. The LUN for the data files (more than 1 database, but the others are all much smaller and minimally accessed) is presented from a EMC/DMX SAN with more than 50 spindles backing that LUN. Because of how the LUN is created - it is virtually separated into 4 separate groups which is more or less equivalent to splitting the data file across 4 physical LUNs in a single filegroup.
So - I would recommend before going through the hassle of splitting everything out, you need to understand the SAN architecture and how it is configured before making the determination that splitting out between index and data filegroups - or splitting into multiple files in a filegroup - will actually help.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 20, 2011 at 11:11 am
Couldn't agree more!
I may have missed it but I did not see SAN mentioned at all so was assuming (dangerous!) a simple pair of mirrored local discs.
(Need input)
Mike
August 20, 2011 at 11:19 am
Yes - definitely need more input, and I wasn't assuming SAN or DAS...
I see this a lot, and the assumption by the person is that splitting out data and indexes will solve their problems - but, in most cases it is either unnecessary overhead or will actually cause more performance issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 20, 2011 at 11:37 am
Jeffrey Williams-493691 (8/20/2011)
Yes - definitely need more input, and I wasn't assuming SAN or DAS...I see this a lot, and the assumption by the person is that splitting out data and indexes will solve their problems - but, in most cases it is either unnecessary overhead or will actually cause more performance issues.
Splitting out data and non-clustered indexes and placing the 2 filegroups in separate disks is a Microsoft best practice, from what I remember reading somewhere.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 20, 2011 at 3:26 pm
Marios Philippopoulos (8/20/2011)
Jeffrey Williams-493691 (8/20/2011)
Yes - definitely need more input, and I wasn't assuming SAN or DAS...I see this a lot, and the assumption by the person is that splitting out data and indexes will solve their problems - but, in most cases it is either unnecessary overhead or will actually cause more performance issues.
Splitting out data and non-clustered indexes and placing the 2 filegroups in separate disks is a Microsoft best practice, from what I remember reading somewhere.
Which does not automatically make it a good thing to do.
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
August 21, 2011 at 6:01 am
GilaMonster (8/20/2011)
Marios Philippopoulos (8/20/2011)
Jeffrey Williams-493691 (8/20/2011)
Yes - definitely need more input, and I wasn't assuming SAN or DAS...I see this a lot, and the assumption by the person is that splitting out data and indexes will solve their problems - but, in most cases it is either unnecessary overhead or will actually cause more performance issues.
Splitting out data and non-clustered indexes and placing the 2 filegroups in separate disks is a Microsoft best practice, from what I remember reading somewhere.
Which does not automatically make it a good thing to do.
From http://msdn.microsoft.com/en-us/library/ms190433(v=SQL.100).aspx:
By creating the nonclustered index on a different filegroup, you can achieve performance gains if the filegroups are using different physical drives with their own controllers. Data and index information can then be read in parallel by the multiple disk heads. For example, if Table_A on filegroup f1 and Index_A on filegroup f2 are both being used by the same query, performance gains can be achieved because both filegroups are being fully used without contention.
I'm not sure in what scenario the above would not be true?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 21, 2011 at 6:15 am
When most of my queries use covering indexes
When my data is in cache already (which hopefully is most of the time)
When a query is only using the clustered indexes of the tables it's querying.
When those two drives share the same IO channel at any point
To pull that off and have it show performance improvements you need to do a lot more thought and a lot more design than just 'tables on filegroup 1, indexes on filegroup 2'
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
August 21, 2011 at 8:29 am
GilaMonster (8/21/2011)
...To pull that off and have it show performance improvements you need to do a lot more thought and a lot more design than just 'tables on filegroup 1, indexes on filegroup 2'
Indeed, but I think most of us started with the scenario as pointed in this thread.
It is a first step and IMHO a good one to work on experience with the topology and science of when and how to split data and indexes.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 21, 2011 at 9:04 am
ALZDBA (8/21/2011)
It is a first step and IMHO a good one to work on experience with the topology and science of when and how to split data and indexes.
Depends.
There are multiple reasons why one might want to split data over filegroups. If only looking at performance, data from indexes is easy to do, but all too likely not to show measureable performance gains.
Is it going to hurt? Unlikely
Is it going to help performance? Maybe
The problem I have with that blanket recommendation is that it's too simplistic, which, for a change, BoL does actually mention. Continuing the quote from where Marios left off
However, if Table_A is scanned by the query but Index_A is not referenced, only filegroup f1 is used. This creates no performance gain.
Because you cannot predict what type of access will occur and when it will occur, it could be a better decision to spread your tables and indexes across all filegroups. This would guarantee that all disks are being accessed because all data and indexes are spread evenly across all disks, regardless of which way the data is accessed. This is also a simpler approach for system administrators.
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
August 21, 2011 at 10:53 am
GilaMonster (8/21/2011)
ALZDBA (8/21/2011)
It is a first step and IMHO a good one to work on experience with the topology and science of when and how to split data and indexes.Depends.
There are multiple reasons why one might want to split data over filegroups. If only looking at performance, data from indexes is easy to do, but all too likely not to show measureable performance gains.
Is it going to hurt? Unlikely
Is it going to help performance? Maybe
The problem I have with that blanket recommendation is that it's too simplistic, which, for a change, BoL does actually mention. Continuing the quote from where Marios left off
However, if Table_A is scanned by the query but Index_A is not referenced, only filegroup f1 is used. This creates no performance gain.
Because you cannot predict what type of access will occur and when it will occur, it could be a better decision to spread your tables and indexes across all filegroups. This would guarantee that all disks are being accessed because all data and indexes are spread evenly across all disks, regardless of which way the data is accessed. This is also a simpler approach for system administrators.
Ah, indeed. I didn't read over the full bol topic. Now I can place your statement better.
There is indeed more to this topic than just straight forward data on 1 and indexes on 2.
Thanks for the elaboration.
OP will by now understand there is more to challenge than just this split 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply