Move database file: indexes

Showing posts with label indexes. Show all posts

Friday, March 30, 2012

moving clustered indexes

Hi,
I have quite a few clustered indexes that I need to move theminto a new file
created in a new filegroup. How can I move them without losing any data?
regards,
Hi,
Data are contained in the leaf pages of the clustered index, moving the
clustered index moves the data
to the new file group. So you cannot move the clustered index alone.
How to move the index to a new file group
create clustered index idx_clus on Table (Column) with drop existing
on new_file_group
Thanks
Hari
MCDBA
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:#$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>
|||Hari is correct in the how-to... Be aware that this process may take a long
time... It will write to the transaction log, so be backing up the log
during the process to keep the log from growing huge... And users will be
locked out of the tables during the process.
Backup everything both before and after, just to be safe..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:%23$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>

moving clustered indexes

Hi,
I have quite a few clustered indexes that I need to move theminto a new file
created in a new filegroup. How can I move them without losing any data?
regards,Hi,
Data are contained in the leaf pages of the clustered index, moving the
clustered index moves the data
to the new file group. So you cannot move the clustered index alone.
How to move the index to a new file group
---
create clustered index idx_clus on Table (Column) with drop existing
on new_file_group
Thanks
Hari
MCDBA
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:#$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>|||Hari is correct in the how-to... Be aware that this process may take a long
time... It will write to the transaction log, so be backing up the log
during the process to keep the log from growing huge... And users will be
locked out of the tables during the process.
Backup everything both before and after, just to be safe..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:%23$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>

moving clustered indexes

Hi,
I have quite a few clustered indexes that I need to move theminto a new file
created in a new filegroup. How can I move them without losing any data?
regards,Hi,
Data are contained in the leaf pages of the clustered index, moving the
clustered index moves the data
to the new file group. So you cannot move the clustered index alone.
How to move the index to a new file group
---
create clustered index idx_clus on Table (Column) with drop existing
on new_file_group
Thanks
Hari
MCDBA
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:#$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>|||Hari is correct in the how-to... Be aware that this process may take a long
time... It will write to the transaction log, so be backing up the log
during the process to keep the log from growing huge... And users will be
locked out of the tables during the process.
Backup everything both before and after, just to be safe..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:%23$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>

Moving BD to new schema...

Good morning to everyone!

I have a 3Gig SQL database that currently has all Indexes, Logs, and Data on one drive. We have a new server that will be put in place on Wednesday and thankfully we will be putting the Indexes and TransLogs on different drives from the actual Data. Does anyone have a recommendation on his/her preferred way of doing this and what are some of the advantages/disadvantages some of you may have encountered? This isn't homework. I am finally getting the hang of manipulating and working with our database and will need to accomplish the server switch in about two weeks. Or if you have a recommended reading on this - please point me to it and I'll get to practicing!

Thanks to everyone!
Tiffanieput indices on different drives than the data. you can create seperate filegroups and put indices for one set of tables on one drive and data on the other and do the vice versa for another set of tales. put T-Logs on a completely seperate drive from the data and indices. I do not want to start the RAID debate.|||Thanks Sean,

We are putting everything on their own drives, but I was wondering how everyone goes about pointing the tables to the new locations of the I&Ts? No raid discussion from me - server is already built I just get to play with moving everything and making sure it works. Basically we're planning to fully backup on the old server and restore on the new. Somehow I need to adjust the paths though?

Tiffanie|||Monday... erased.|||tables that are frequently joined, you might to keep oon seperate filegroups.

I do not think you can use ALTER TABLE to change filegroups.

I am not sure you are going tobe able to do a backup and restore. You may have to script your whole database in the EM (easy to do). and adjust your create table statements with ON PRIMARY or SECONDARY etc... specified. Then you will have to move the data over without using a backup.

The indices are easy. Just drop and recreate. You can use the EM or write some code that writes some code against sysindexes to do the job.|||Thanks Sean - I'll play around with that.
Have a terrific day!
Tiffanie

Friday, March 9, 2012

move indexes via ALTER INDEX

I was checking out an indexes property via SQL 2005 Management Studio
and it looks like I can move the index to other filegroups via the
drop down.
I checked the BOL ALTER INDEX and I didnt see an argument for this
action. I see stuff about PARTITION, but I think that is for row
partitioning??
I want to move existing indexes from the PRIMARY filegroup to a new
file group just for indexes, which is called INDEXES.
Can this be done via ALTER INDEX or some other way?
TIA
RobCheck out the CREATE INDEX ... WITH DROP_EXISTING syntax where you specify
the filegroup to create the new index on.

--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"rcamarda" <robert.a.camarda@.gmail.comwrote in message
news:1173752583.908449.87100@.30g2000cwc.googlegrou ps.com...

Quote:

Originally Posted by

>I was checking out an indexes property via SQL 2005 Management Studio
and it looks like I can move the index to other filegroups via the
drop down.
I checked the BOL ALTER INDEX and I didnt see an argument for this
action. I see stuff about PARTITION, but I think that is for row
partitioning??
I want to move existing indexes from the PRIMARY filegroup to a new
file group just for indexes, which is called INDEXES.
Can this be done via ALTER INDEX or some other way?
TIA
Rob
>

Move indexes to new filegroup

If a new filegroup is created on a new disk drive, is there a way to migrate
a specific clustered index to the new filegroup?
Regards,
Jamie
I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
Regards,
Jamie
"Tibor Karaszi" wrote:

> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH =
> 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>

Move indexes to new filegroup

If a new filegroup is created on a new disk drive, is there a way to migrate
a specific clustered index to the new filegroup?
--
Regards,
JamieUse CREATE INDEX with the DROP EXISTING clause. Example:
USE master
GO
DROP DATABASE FgTest
GO
CREATE DATABASE FgTest
ON PRIMARY
( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH =1024KB ),
FILEGROUP [ForData]
( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
USE FgTest
CREATE TABLE t(c1 int identity, c2 char(5))
CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
INSERT INTO t (c2)
SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
GO
EXEC sp_helpindex t
CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
EXEC sp_helpindex t
USE master
DROP DATABASE FgTest
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> If a new filegroup is created on a new disk drive, is there a way to migrate
> a specific clustered index to the new filegroup?
> --
> Regards,
> Jamie|||I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH => 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> > If a new filegroup is created on a new disk drive, is there a way to migrate
> > a specific clustered index to the new filegroup?
> > --
> > Regards,
> > Jamie
>|||Try adding UNIQUE to the index definition, since it comes from a primary key constraint:
CREATE UNIQUE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:6FF49395-56FF-453F-8E67-34AEFD91408D@.microsoft.com...
> I'm having trouble with the syntax. Index is created and dropped with
> ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
> [PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
> (
> [SupplierXrefKey] ASC
> ) ON [PRIMARY]
>
> IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id => OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
> ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
> WHEN I RUN:
> CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
> (SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
> I get an error:
> Server: Msg 1907, Level 16, State 1, Line 1
> Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
> does not match the constraint being enforced by the existing index.
> DDRIVE is the additional filegroup I created for this table and which exists
> on a drive other than the primary filegroup drive.
> --
> Regards,
> Jamie
>
> "Tibor Karaszi" wrote:
>> Use CREATE INDEX with the DROP EXISTING clause. Example:
>> USE master
>> GO
>> DROP DATABASE FgTest
>> GO
>> CREATE DATABASE FgTest
>> ON PRIMARY
>> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH
>> =>> 1024KB ),
>> FILEGROUP [ForData]
>> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
>> FILEGROWTH = 1024KB )
>> LOG ON
>> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
>> FILEGROWTH = 10%)
>> GO
>> USE FgTest
>> CREATE TABLE t(c1 int identity, c2 char(5))
>> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
>> INSERT INTO t (c2)
>> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
>> GO
>> EXEC sp_helpindex t
>> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
>> EXEC sp_helpindex t
>> USE master
>> DROP DATABASE FgTest
>> GO
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>> > If a new filegroup is created on a new disk drive, is there a way to migrate
>> > a specific clustered index to the new filegroup?
>> > --
>> > Regards,
>> > Jamie
>>

Move indexes to new filegroup

If a new filegroup is created on a new disk drive, is there a way to migrate
a specific clustered index to the new filegroup?
--
Regards,
JamieUse CREATE INDEX with the DROP EXISTING clause. Example:
USE master
GO
DROP DATABASE FgTest
GO
CREATE DATABASE FgTest
ON PRIMARY
( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH =
1024KB ),
FILEGROUP [ForData]
( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXS
IZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MA
XSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
USE FgTest
CREATE TABLE t(c1 int identity, c2 char(5))
CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
INSERT INTO t (c2)
SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
GO
EXEC sp_helpindex t
CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
EXEC sp_helpindex t
USE master
DROP DATABASE FgTest
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> If a new filegroup is created on a new disk drive, is there a way to migra
te
> a specific clustered index to the new filegroup?
> --
> Regards,
> Jamie|||I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierIt
emXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupp
lierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
--
Regards,
Jamie
"Tibor Karaszi" wrote:

> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE
= UNLIMITED, FILEGROWTH =
> 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MA
XSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB ,
MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>|||Try adding UNIQUE to the index definition, since it comes from a primary key
constraint:
CREATE UNIQUE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:6FF49395-56FF-453F-8E67-34AEFD91408D@.microsoft.com...[vbcol=seagreen]
> I'm having trouble with the syntax. Index is created and dropped with
> ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
> [PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
> (
> [SupplierXrefKey] ASC
> ) ON [PRIMARY]
>
> IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
> OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplier
ItemXrefs')
> ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSu
pplierItemXrefs]
> WHEN I RUN:
> CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
> (SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
> I get an error:
> Server: Msg 1907, Level 16, State 1, Line 1
> Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
> does not match the constraint being enforced by the existing index.
> DDRIVE is the additional filegroup I created for this table and which exis
ts
> on a drive other than the primary filegroup drive.
> --
> Regards,
> Jamie
>
> "Tibor Karaszi" wrote:
>

Monday, February 20, 2012

Moving indexes to their own disk drive

I am trying to find out if it is possible to move indexes to a separate filegroup/disk drive during database restore. I am trying this to see if it improves performance. Also if I cannot move the indexes during restore, how would I move them afterwards to a different filegroup/disk drive? Thanks in advance for all the help.Just recreate indexes:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ] --<<<<<<<<<<<<<<<<<<<<<<|||Look up ALTER TABLE

I think you can ALTER the contraint (The indexes) and change the file group

Never done it though...let me look into it...|||I was meesing with

USE Northwind
GO

CREATE TABLE myTable99 (Col1 int IDENTITY(1,1),Col2 char(1))
GO

CREATE INDEX myTable99_IX1 ON myTable99(Col1)
GO

ALTER TABLE myTable99 NOCHECK CONSTRAINT myTable99_IX1 ON Secondary
GO

DROP TABLE myTable99
GO

But can't get it to work...

Seems like you have to drop and recreate...I would have thought there might have been a move or alter or something...

How big is the database?|||Will that SQL allow me to move the existing index to a different drive, and can you show me an example. Thanks.|||One thing to remember, you can not move a clustered index to a drive (filegroup) that is different from the filegroup the data is on. In short, a clustered index is the data. As for an example, suppose you have two filegroups named UserData and IndexData. For table Customers you would have:

create (unique) index indexname on Customers (fields) on IndexData

alter table Customers add constraint pk_customers primary key (keyfield(s)) on UserData|||I can't find a way to move the index across filegroups...

Even EM does a drop and recreate...|||if this is going to be a one time thing then i would invest the time and do it right and completely recreate the index on the new disk.
this would give you a fresh index on the new disk

and kids
dont forget your fill factor.

moving indexes to other filegroup

Thanks again for the help/suggestions.
Jake
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%239qSYtQBHHA.4992@.TK2MSFTNGP03.phx.gbl...
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:4554D0B8.8090503@.realsqlguy.com...
> I'll go further. Splitting indexes off to a separate file group is almost
> always a waste of time. Especially since you note that you will be moving
> to new hardware. You should be moving to 64bit SQL Server on 64bit
> Windows. On this platform, the large amounts of memory available for cache
> will make your physical disk access patterns mostly unpredictable. If you
> dedicate a volume for your indexes, most likely that volume will remain
> idle because the indexes on are either completely cached, or completely
> unused.
> Instead, when you add a volume to your server, dedicate it to Logs, TempDB
> or just add an additional file to your main file group on the new volume.
> SQL Server will automatically spread the IO evenly across all the files in
> the file group.
> David

moving indexes to other filegroup

Hello,
I currently have the indexes stored in the primiray filegroup and am
wondering if there is a way to move them to a secondary filegroup and then
put this secondary filegroup on a different drive to improve performance. I
am running sql2k5. Thanks in advance.
JakeJake Smythe wrote:
> Hello,
> I currently have the indexes stored in the primiray filegroup and am
> wondering if there is a way to move them to a secondary filegroup and then
> put this secondary filegroup on a different drive to improve performance. I
> am running sql2k5. Thanks in advance.
> Jake
>
>
Have you looked at the syntax for CREATE INDEX in Books Online? It's
pretty well documented there how to put an index on a different filegroup.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Jake
You don't say how many drives the current database uses and if you are
moving the indexes to a different drive array or not? You would need to
analyse the index usage by your queries to determine if you would gain
benefit from moving the indexes, it could be that you move the data from some
tables by moving (dropping/creating) the clustered index and leave a
combination of different data/clustered indexes and non-clustered indexes on
both.
It could just be that if you added an extra file to primary filegroup or if
you added the disc(s) into a RAID array the performance increase may be more
substantial. Another thing worth looking at is if moving tempdb to the new
drive and leaving your database alone would help.
If you have not split the spindles that the log files and data files use,
then I would ignore any thought of extra file/filegroups and split them onto
the new disc(s) (possibly with the exception of if your database is read
only!!)
John
"Jake Smythe" wrote:
> Hello,
> I currently have the indexes stored in the primiray filegroup and am
> wondering if there is a way to move them to a secondary filegroup and then
> put this secondary filegroup on a different drive to improve performance. I
> am running sql2k5. Thanks in advance.
> Jake
>
>|||John and Tracy,
Thanks for the replies. We currently have the data on one array with
raid 5 and we have the logs on another array using raid 1. We're going to be
moving to a cluster environment within the next couple of months so I
thought that if we have a third array for the indexes (suggestion on raid?)
we could boost performance even more. We currently have some replication on
some of these databases as well. So I was looking at a way to move the
clustered and non-clustered indexes to another filegroup that would be on
this third array via TSQL if possible. We have sql2k5 std. Is moving the
indexes to another drive array worth it? I did look at the create index on
bol. My assumption is that I would need to drop all of the existing indexes
and recreate them pointing to the new filegroup. If this is the case how can
I get the scripts just to generate clustered and non-clustered indexes from
existing indexes within a database? Any suggestions would be greatly
appreicated
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5E8BB7AA-D27A-4EFB-8654-430D7FF308A8@.microsoft.com...
> Hi Jake
> You don't say how many drives the current database uses and if you are
> moving the indexes to a different drive array or not? You would need to
> analyse the index usage by your queries to determine if you would gain
> benefit from moving the indexes, it could be that you move the data from
> some
> tables by moving (dropping/creating) the clustered index and leave a
> combination of different data/clustered indexes and non-clustered indexes
> on
> both.
> It could just be that if you added an extra file to primary filegroup or
> if
> you added the disc(s) into a RAID array the performance increase may be
> more
> substantial. Another thing worth looking at is if moving tempdb to the new
> drive and leaving your database alone would help.
> If you have not split the spindles that the log files and data files use,
> then I would ignore any thought of extra file/filegroups and split them
> onto
> the new disc(s) (possibly with the exception of if your database is read
> only!!)
> John
>
> "Jake Smythe" wrote:
>> Hello,
>> I currently have the indexes stored in the primiray filegroup and am
>> wondering if there is a way to move them to a secondary filegroup and
>> then
>> put this secondary filegroup on a different drive to improve performance.
>> I
>> am running sql2k5. Thanks in advance.
>> Jake
>>
>>|||Jake Smythe wrote:
> John and Tracy,
> Thanks for the replies. We currently have the data on one array with
> raid 5 and we have the logs on another array using raid 1. We're going to be
> moving to a cluster environment within the next couple of months so I
> thought that if we have a third array for the indexes (suggestion on raid?)
> we could boost performance even more. We currently have some replication on
> some of these databases as well. So I was looking at a way to move the
> clustered and non-clustered indexes to another filegroup that would be on
> this third array via TSQL if possible. We have sql2k5 std. Is moving the
> indexes to another drive array worth it? I did look at the create index on
> bol. My assumption is that I would need to drop all of the existing indexes
> and recreate them pointing to the new filegroup. If this is the case how can
> I get the scripts just to generate clustered and non-clustered indexes from
> existing indexes within a database? Any suggestions would be greatly
> appreicated
>
Jake, I've dumped a script at
http://realsqlguy.com/downloads/01_MakeZlogClustersNonUnique.sql that
I've used to generate DROP and CREATE statements for indexes. You'll
need to modify it to suit your needs, but it should be pretty close to
working for you.
To be honest, I've never had a performance problem required splitting
indexes off into a seperate filegroup. Usually identifying and tweaking
bad queries and/or missing indexes does more to help performance than
anything.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4554D0B8.8090503@.realsqlguy.com...
> Jake Smythe wrote:
>> John and Tracy,
>> Thanks for the replies. We currently have the data on one array with
>> raid 5 and we have the logs on another array using raid 1. We're going to
>> be moving to a cluster environment within the next couple of months so I
>> thought that if we have a third array for the indexes (suggestion on
>> raid?) we could boost performance even more. We currently have some
>> replication on some of these databases as well. So I was looking at a way
>> to move the clustered and non-clustered indexes to another filegroup that
>> would be on this third array via TSQL if possible. We have sql2k5 std. Is
>> moving the indexes to another drive array worth it? I did look at the
>> create index on bol. My assumption is that I would need to drop all of
>> the existing indexes and recreate them pointing to the new filegroup. If
>> this is the case how can I get the scripts just to generate clustered and
>> non-clustered indexes from existing indexes within a database? Any
>> suggestions would be greatly appreicated
> Jake, I've dumped a script at
> http://realsqlguy.com/downloads/01_MakeZlogClustersNonUnique.sql that I've
> used to generate DROP and CREATE statements for indexes. You'll need to
> modify it to suit your needs, but it should be pretty close to working for
> you.
> To be honest, I've never had a performance problem required splitting
> indexes off into a seperate filegroup. Usually identifying and tweaking
> bad queries and/or missing indexes does more to help performance than
> anything.
>
I'll go further. Splitting indexes off to a separate file group is almost
always a waste of time. Especially since you note that you will be moving
to new hardware. You should be moving to 64bit SQL Server on 64bit Windows.
On this platform, the large amounts of memory available for cache will make
your physical disk access patterns mostly unpredictable. If you dedicate a
volume for your indexes, most likely that volume will remain idle because
the indexes on are either completely cached, or completely unused.
Instead, when you add a volume to your server, dedicate it to Logs, TempDB
or just add an additional file to your main file group on the new volume.
SQL Server will automatically spread the IO evenly across all the files in
the file group.
David|||Guys,
Thanks for the responses. I wasn't sure it was really worth the headache
but figured I'd ask :) Thanks again for the help/suggestions.
Jake
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%239qSYtQBHHA.4992@.TK2MSFTNGP03.phx.gbl...
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:4554D0B8.8090503@.realsqlguy.com...
>> Jake Smythe wrote:
>> John and Tracy,
>> Thanks for the replies. We currently have the data on one array with
>> raid 5 and we have the logs on another array using raid 1. We're going
>> to be moving to a cluster environment within the next couple of months
>> so I thought that if we have a third array for the indexes (suggestion
>> on raid?) we could boost performance even more. We currently have some
>> replication on some of these databases as well. So I was looking at a
>> way to move the clustered and non-clustered indexes to another filegroup
>> that would be on this third array via TSQL if possible. We have sql2k5
>> std. Is moving the indexes to another drive array worth it? I did look
>> at the create index on bol. My assumption is that I would need to drop
>> all of the existing indexes and recreate them pointing to the new
>> filegroup. If this is the case how can I get the scripts just to
>> generate clustered and non-clustered indexes from existing indexes
>> within a database? Any suggestions would be greatly appreicated
>>
>> Jake, I've dumped a script at
>> http://realsqlguy.com/downloads/01_MakeZlogClustersNonUnique.sql that
>> I've used to generate DROP and CREATE statements for indexes. You'll
>> need to modify it to suit your needs, but it should be pretty close to
>> working for you.
>> To be honest, I've never had a performance problem required splitting
>> indexes off into a seperate filegroup. Usually identifying and tweaking
>> bad queries and/or missing indexes does more to help performance than
>> anything.
> I'll go further. Splitting indexes off to a separate file group is almost
> always a waste of time. Especially since you note that you will be moving
> to new hardware. You should be moving to 64bit SQL Server on 64bit
> Windows. On this platform, the large amounts of memory available for cache
> will make your physical disk access patterns mostly unpredictable. If you
> dedicate a volume for your indexes, most likely that volume will remain
> idle because the indexes on are either completely cached, or completely
> unused.
> Instead, when you add a volume to your server, dedicate it to Logs, TempDB
> or just add an additional file to your main file group on the new volume.
> SQL Server will automatically spread the IO evenly across all the files in
> the file group.
> David

moving indexes to other filegroup

Moving indexes to another filegroup trouble...

I am trying to place all the clustered indexes to de data filegroup of my database and de non clustered indexes to de index filegroup of my database.

But in some databases I cannot change the location in the storage tab of the index property window because it is greyed out. The strange thing is that in some other databases it is possible, and some other partially. I even found a table with two non clustered indexes, of which one was possible to change, and the other not! Changing the compatibility mode of the database does not help.

So I will not get too happy about this.

1) Does anyone know why this behaviour is?
2) Does anyone has a script or tool (preferably free) which I can use to automate this task?

This all is in SQL Server 2005 Developer and using Management Studio.

Thanks in advance!

You can move an index by using the DROP_EXISTING clause.

Code Snippet

CREATE TABLE myTable

(ID int, NC varchar(3))

CREATE CLUSTERED INDEX myCIX ON myTable (ID)

CREATE NONCLUSTERED INDEX myIX ON myTable (NC)

CREATE CLUSTERED INDEX myCIX ON myTable (ID)

WITH (DROP_EXISTING=ON) ON [OtherFG]

CREATE NONCLUSTERED INDEX myIX ON myTable (NC)

WITH (DROP_EXISTING=ON) ON [OtherFG]

sp_help myTable

DROP TABLE myTable

For constraints check out the MOVE TO clause for the DROP constraint statement.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||See this http://www.mssqltips.com/tip.asp?tip=1112 too|||

Thanks, but wat I wanted to know, if somewone has an script that looks for all the indexes and creates move scripts or moves the indexes itself. I already knew how to do it, but with a database with 50-100 tables, handlabour is not cheap.

Moving indexes to another filegroup trouble...

I am trying to place all the clustered indexes to de data filegroup of my database and de non clustered indexes to de index filegroup of my database.

So I will not get too happy about this.

1) Does anyone know why this behaviour is?
2) Does anyone has a script or tool (preferably free) which I can use to automate this task?

This all is in SQL Server 2005 Developer and using Management Studio.

Thanks in advance!

You can move an index by using the DROP_EXISTING clause.

Code Snippet

CREATE TABLE myTable

(ID int, NC varchar(3))

CREATE CLUSTERED INDEX myCIX ON myTable (ID)

CREATE NONCLUSTERED INDEX myIX ON myTable (NC)

CREATE CLUSTERED INDEX myCIX ON myTable (ID)

WITH (DROP_EXISTING=ON) ON [OtherFG]

CREATE NONCLUSTERED INDEX myIX ON myTable (NC)

WITH (DROP_EXISTING=ON) ON [OtherFG]

sp_help myTable

DROP TABLE myTable

For constraints check out the MOVE TO clause for the DROP constraint statement.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||See this http://www.mssqltips.com/tip.asp?tip=1112 too|||

moving indexes onto another partition

Hi
From several weeks I suffer poor performance of my SQL Serv (as backend to
the web application).
I heard that sometimes splitting database to different hard disk could raise
performance. But in my case I have got only one HDD devided to 2 partition -
my question is: if it is good idea to migrate tables or indexes to anther
partition - does it make sens?
Or maybe thare is another solution to my problem - connected mostly with HDD
queue legth. So I could say that all the delay in answers are connected with
poor HDD performance.
Best Regards
Darek T.It does not help to move the indexes to the other partition: it is still the
same physical disk drive.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Dariusz Tomon" wrote:

> Hi
> From several weeks I suffer poor performance of my SQL Serv (as backend to
> the web application).
> I heard that sometimes splitting database to different hard disk could rai
se
> performance. But in my case I have got only one HDD devided to 2 partition
-
> my question is: if it is good idea to migrate tables or indexes to anther
> partition - does it make sens?
> Or maybe thare is another solution to my problem - connected mostly with H
DD
> queue legth. So I could say that all the delay in answers are connected wi
th
> poor HDD performance.
>
> Best Regards
> Darek T.
>
>|||Use SQL profiler to run a trace and identify the query that is slow. The
vast majority of performance problems can be fixed by rewriting queries or
adding indexes.
Beyond that, you can only add physical drives to distribute the read/writes.
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:O$Q3lgRdGHA.1656@.TK2MSFTNGP02.phx.gbl...
> Hi
> From several weeks I suffer poor performance of my SQL Serv (as backend to
> the web application).
> I heard that sometimes splitting database to different hard disk could
> raise performance. But in my case I have got only one HDD devided to 2
> partition - my question is: if it is good idea to migrate tables or
> indexes to anther partition - does it make sens?
> Or maybe thare is another solution to my problem - connected mostly with
> HDD queue legth. So I could say that all the delay in answers are
> connected with poor HDD performance.
>
> Best Regards
> Darek T.
>

moving indexes onto another partition

Hi
From several weeks I suffer poor performance of my SQL Serv (as backend to
the web application).
I heard that sometimes splitting database to different hard disk could raise
performance. But in my case I have got only one HDD devided to 2 partition -
my question is: if it is good idea to migrate tables or indexes to anther
partition - does it make sens?
Or maybe thare is another solution to my problem - connected mostly with HDD
queue legth. So I could say that all the delay in answers are connected with
poor HDD performance.
Best Regards
Darek T.It does not help to move the indexes to the other partition: it is still the
same physical disk drive.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Dariusz Tomon" wrote:
> Hi
> From several weeks I suffer poor performance of my SQL Serv (as backend to
> the web application).
> I heard that sometimes splitting database to different hard disk could raise
> performance. But in my case I have got only one HDD devided to 2 partition -
> my question is: if it is good idea to migrate tables or indexes to anther
> partition - does it make sens?
> Or maybe thare is another solution to my problem - connected mostly with HDD
> queue legth. So I could say that all the delay in answers are connected with
> poor HDD performance.
>
> Best Regards
> Darek T.
>
>|||Use SQL profiler to run a trace and identify the query that is slow. The
vast majority of performance problems can be fixed by rewriting queries or
adding indexes.
Beyond that, you can only add physical drives to distribute the read/writes.
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:O$Q3lgRdGHA.1656@.TK2MSFTNGP02.phx.gbl...
> Hi
> From several weeks I suffer poor performance of my SQL Serv (as backend to
> the web application).
> I heard that sometimes splitting database to different hard disk could
> raise performance. But in my case I have got only one HDD devided to 2
> partition - my question is: if it is good idea to migrate tables or
> indexes to anther partition - does it make sens?
> Or maybe thare is another solution to my problem - connected mostly with
> HDD queue legth. So I could say that all the delay in answers are
> connected with poor HDD performance.
>
> Best Regards
> Darek T.
>

Moving indexes on another physical drive

I have SQL Server, which quite often have 200 transaction/second. It is
expected what the load, during this year, would be increased up to 1000
transactions per second. The database relatively small, can be loaded in
memory. All tables which often used by queries pinned in memory, with
'pintable' option. IO system - DELL CX300, with write cache enabled. I have 3
disks, which can be used for SQL Server files. Currently I use one 2 of them,
1 for data file and 1 for log file. I try to find how to use another disk to
speed up queries and to have more even distribution of transaction times. I
think to move indexes on another physical drive, so data would be on one
disk, and indexes on another. I guess it would reduce average and maximum
wait time on latches during checkpoints. Is it true, what can be expected
from this movement of indexes?
Another idea which I have - use two drives for data, one for log. In this
case I would create filegroup whih would include two data files on different
drives, and data would be evenly distributed across drives. Is it better in
terms of performance and to have more even distribution of transaction times?
May be there is better way to use 3dr drive?
Hi
Have you looked at your disk queues?
Are your data and logs, on seperate RAID-10 volumes, and each RAID-10 volume
on different spindles?
On some of our larger systems, we have seen pinning a table actually
degraded performance as the tables were taking space that could have better
been used for dirty buffers.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"andsm" wrote:

> I have SQL Server, which quite often have 200 transaction/second. It is
> expected what the load, during this year, would be increased up to 1000
> transactions per second. The database relatively small, can be loaded in
> memory. All tables which often used by queries pinned in memory, with
> 'pintable' option. IO system - DELL CX300, with write cache enabled. I have 3
> disks, which can be used for SQL Server files. Currently I use one 2 of them,
> 1 for data file and 1 for log file. I try to find how to use another disk to
> speed up queries and to have more even distribution of transaction times. I
> think to move indexes on another physical drive, so data would be on one
> disk, and indexes on another. I guess it would reduce average and maximum
> wait time on latches during checkpoints. Is it true, what can be expected
> from this movement of indexes?
> Another idea which I have - use two drives for data, one for log. In this
> case I would create filegroup whih would include two data files on different
> drives, and data would be evenly distributed across drives. Is it better in
> terms of performance and to have more even distribution of transaction times?
> May be there is better way to use 3dr drive?
|||Hi
You can move heavy quering table/s to another physical disk , please note
physical not logical.
You are right , separating LOG and DATA files will improve the performance
of the database.
If your database is small and the tables are small as well why do you want
at least at this moment to move the tabes/indexes, did you start to get slow
response?
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7988A160-301C-4BE2-A7FC-9B9090E62831@.microsoft.com...
> I have SQL Server, which quite often have 200 transaction/second. It is
> expected what the load, during this year, would be increased up to 1000
> transactions per second. The database relatively small, can be loaded in
> memory. All tables which often used by queries pinned in memory, with
> 'pintable' option. IO system - DELL CX300, with write cache enabled. I
have 3
> disks, which can be used for SQL Server files. Currently I use one 2 of
them,
> 1 for data file and 1 for log file. I try to find how to use another disk
to
> speed up queries and to have more even distribution of transaction times.
I
> think to move indexes on another physical drive, so data would be on one
> disk, and indexes on another. I guess it would reduce average and maximum
> wait time on latches during checkpoints. Is it true, what can be expected
> from this movement of indexes?
> Another idea which I have - use two drives for data, one for log. In this
> case I would create filegroup whih would include two data files on
different
> drives, and data would be evenly distributed across drives. Is it better
in
> terms of performance and to have more even distribution of transaction
times?
> May be there is better way to use 3dr drive?
|||More importantly than what the others have suggested, when you say current
activity is 200 TPS and future activity is 1,000 TPS, that is a pretty big
jump.
What is the nature of this activity? 50% Reads/50% Writes? 75% Reads/25%
Writes? 25% Reads/75% Writes?
Can you tell what the current I/O OPS are? What is the projection?
A single physical disk (1 spindle, dedicated) can achieve perhaps 100-200
I/O OPS, depending on the R/W and physical disk metrics. For this much
activity, you do not want to drive this disk much above 65% on average.
Every transaction will involve a serial log write and a random data write,
eventually. You should have at least a sufficient number of spindles to
cover your anticipated I/O OPS, both for data and log activity.
For example, if you feel you have 300 I/O OPS, you should have at least 2;
however, especially with a dedicated storage solution, you should stripe at
least six spindles per LUN, at a minimum. If you can't achieve the minimum,
then you may consider a technique called Disk Plaiding, which is striping
your LUN at the hardware level, but then do as you suggest, and multi-file
each SQL Server filegroup. This way you have hardware striping and DBMS
striping across those (a Plaid...checkerboard pattern) of distributed data.
The LUN striping should be Raid, of course, or mirrored sets of
Hyper-volumes, called Meta-volumes. This is effectively RAID 10, but know
that hyper-volumes are only a slice of a disk and any single disk (1
spindle) could be shared out, hyper by hyper, to several hosts. You must
take this into account when trying to count I/O OPS per spindle.
You should really work with your Storage Management to communicate the
capacity requirements. Barring that, contact your storage technical rep.
Be careful though, many will tell you that it doesn't matter and try to give
you one big volume. It matters and they are full of hooey. All of the
major storage providers and Microsoft publish white papers on how to set up
external storage for DBMS support. Use those.
Sincerely,
Anthony Thomas

"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7988A160-301C-4BE2-A7FC-9B9090E62831@.microsoft.com...
I have SQL Server, which quite often have 200 transaction/second. It is
expected what the load, during this year, would be increased up to 1000
transactions per second. The database relatively small, can be loaded in
memory. All tables which often used by queries pinned in memory, with
'pintable' option. IO system - DELL CX300, with write cache enabled. I have
3
disks, which can be used for SQL Server files. Currently I use one 2 of
them,
1 for data file and 1 for log file. I try to find how to use another disk to
speed up queries and to have more even distribution of transaction times. I
think to move indexes on another physical drive, so data would be on one
disk, and indexes on another. I guess it would reduce average and maximum
wait time on latches during checkpoints. Is it true, what can be expected
from this movement of indexes?
Another idea which I have - use two drives for data, one for log. In this
case I would create filegroup whih would include two data files on different
drives, and data would be evenly distributed across drives. Is it better in
terms of performance and to have more even distribution of transaction
times?
May be there is better way to use 3dr drive?

Moving indexes on another physical drive

I have SQL Server, which quite often have 200 transaction/second. It is
expected what the load, during this year, would be increased up to 1000
transactions per second. The database relatively small, can be loaded in
memory. All tables which often used by queries pinned in memory, with
'pintable' option. IO system - DELL CX300, with write cache enabled. I have 3
disks, which can be used for SQL Server files. Currently I use one 2 of them,
1 for data file and 1 for log file. I try to find how to use another disk to
speed up queries and to have more even distribution of transaction times. I
think to move indexes on another physical drive, so data would be on one
disk, and indexes on another. I guess it would reduce average and maximum
wait time on latches during checkpoints. Is it true, what can be expected
from this movement of indexes?
Another idea which I have - use two drives for data, one for log. In this
case I would create filegroup whih would include two data files on different
drives, and data would be evenly distributed across drives. Is it better in
terms of performance and to have more even distribution of transaction times?
May be there is better way to use 3dr drive?Hi
Have you looked at your disk queues?
Are your data and logs, on seperate RAID-10 volumes, and each RAID-10 volume
on different spindles?
On some of our larger systems, we have seen pinning a table actually
degraded performance as the tables were taking space that could have better
been used for dirty buffers.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"andsm" wrote:
> I have SQL Server, which quite often have 200 transaction/second. It is
> expected what the load, during this year, would be increased up to 1000
> transactions per second. The database relatively small, can be loaded in
> memory. All tables which often used by queries pinned in memory, with
> 'pintable' option. IO system - DELL CX300, with write cache enabled. I have 3
> disks, which can be used for SQL Server files. Currently I use one 2 of them,
> 1 for data file and 1 for log file. I try to find how to use another disk to
> speed up queries and to have more even distribution of transaction times. I
> think to move indexes on another physical drive, so data would be on one
> disk, and indexes on another. I guess it would reduce average and maximum
> wait time on latches during checkpoints. Is it true, what can be expected
> from this movement of indexes?
> Another idea which I have - use two drives for data, one for log. In this
> case I would create filegroup whih would include two data files on different
> drives, and data would be evenly distributed across drives. Is it better in
> terms of performance and to have more even distribution of transaction times?
> May be there is better way to use 3dr drive?|||Hi
You can move heavy quering table/s to another physical disk , please note
physical not logical.
You are right , separating LOG and DATA files will improve the performance
of the database.
If your database is small and the tables are small as well why do you want
at least at this moment to move the tabes/indexes, did you start to get slow
response?
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7988A160-301C-4BE2-A7FC-9B9090E62831@.microsoft.com...
> I have SQL Server, which quite often have 200 transaction/second. It is
> expected what the load, during this year, would be increased up to 1000
> transactions per second. The database relatively small, can be loaded in
> memory. All tables which often used by queries pinned in memory, with
> 'pintable' option. IO system - DELL CX300, with write cache enabled. I
have 3
> disks, which can be used for SQL Server files. Currently I use one 2 of
them,
> 1 for data file and 1 for log file. I try to find how to use another disk
to
> speed up queries and to have more even distribution of transaction times.
I
> think to move indexes on another physical drive, so data would be on one
> disk, and indexes on another. I guess it would reduce average and maximum
> wait time on latches during checkpoints. Is it true, what can be expected
> from this movement of indexes?
> Another idea which I have - use two drives for data, one for log. In this
> case I would create filegroup whih would include two data files on
different
> drives, and data would be evenly distributed across drives. Is it better
in
> terms of performance and to have more even distribution of transaction
times?
> May be there is better way to use 3dr drive?|||More importantly than what the others have suggested, when you say current
activity is 200 TPS and future activity is 1,000 TPS, that is a pretty big
jump.
What is the nature of this activity? 50% Reads/50% Writes? 75% Reads/25%
Writes? 25% Reads/75% Writes?
Can you tell what the current I/O OPS are? What is the projection?
A single physical disk (1 spindle, dedicated) can achieve perhaps 100-200
I/O OPS, depending on the R/W and physical disk metrics. For this much
activity, you do not want to drive this disk much above 65% on average.
Every transaction will involve a serial log write and a random data write,
eventually. You should have at least a sufficient number of spindles to
cover your anticipated I/O OPS, both for data and log activity.
For example, if you feel you have 300 I/O OPS, you should have at least 2;
however, especially with a dedicated storage solution, you should stripe at
least six spindles per LUN, at a minimum. If you can't achieve the minimum,
then you may consider a technique called Disk Plaiding, which is striping
your LUN at the hardware level, but then do as you suggest, and multi-file
each SQL Server filegroup. This way you have hardware striping and DBMS
striping across those (a Plaid...checkerboard pattern) of distributed data.
The LUN striping should be Raid, of course, or mirrored sets of
Hyper-volumes, called Meta-volumes. This is effectively RAID 10, but know
that hyper-volumes are only a slice of a disk and any single disk (1
spindle) could be shared out, hyper by hyper, to several hosts. You must
take this into account when trying to count I/O OPS per spindle.
You should really work with your Storage Management to communicate the
capacity requirements. Barring that, contact your storage technical rep.
Be careful though, many will tell you that it doesn't matter and try to give
you one big volume. It matters and they are full of hooey. All of the
major storage providers and Microsoft publish white papers on how to set up
external storage for DBMS support. Use those.
Sincerely,
Anthony Thomas
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7988A160-301C-4BE2-A7FC-9B9090E62831@.microsoft.com...
I have SQL Server, which quite often have 200 transaction/second. It is
expected what the load, during this year, would be increased up to 1000
transactions per second. The database relatively small, can be loaded in
memory. All tables which often used by queries pinned in memory, with
'pintable' option. IO system - DELL CX300, with write cache enabled. I have
3
disks, which can be used for SQL Server files. Currently I use one 2 of
them,
1 for data file and 1 for log file. I try to find how to use another disk to
speed up queries and to have more even distribution of transaction times. I
think to move indexes on another physical drive, so data would be on one
disk, and indexes on another. I guess it would reduce average and maximum
wait time on latches during checkpoints. Is it true, what can be expected
from this movement of indexes?
Another idea which I have - use two drives for data, one for log. In this
case I would create filegroup whih would include two data files on different
drives, and data would be evenly distributed across drives. Is it better in
terms of performance and to have more even distribution of transaction
times?
May be there is better way to use 3dr drive?

Moving indexes on another physical drive

I have SQL Server, which quite often have 200 transaction/second. It is
expected what the load, during this year, would be increased up to 1000
transactions per second. The database relatively small, can be loaded in
memory. All tables which often used by queries pinned in memory, with
'pintable' option. IO system - DELL CX300, with write cache enabled. I have
3
disks, which can be used for SQL Server files. Currently I use one 2 of them
,
1 for data file and 1 for log file. I try to find how to use another disk to
speed up queries and to have more even distribution of transaction times. I
think to move indexes on another physical drive, so data would be on one
disk, and indexes on another. I guess it would reduce average and maximum
wait time on latches during checkpoints. Is it true, what can be expected
from this movement of indexes?
Another idea which I have - use two drives for data, one for log. In this
case I would create filegroup whih would include two data files on different
drives, and data would be evenly distributed across drives. Is it better in
terms of performance and to have more even distribution of transaction times
?
May be there is better way to use 3dr drive?Hi
Have you looked at your disk queues?
Are your data and logs, on seperate RAID-10 volumes, and each RAID-10 volume
on different spindles?
On some of our larger systems, we have seen pinning a table actually
degraded performance as the tables were taking space that could have better
been used for dirty buffers.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"andsm" wrote:

> I have SQL Server, which quite often have 200 transaction/second. It is
> expected what the load, during this year, would be increased up to 1000
> transactions per second. The database relatively small, can be loaded in
> memory. All tables which often used by queries pinned in memory, with
> 'pintable' option. IO system - DELL CX300, with write cache enabled. I hav
e 3
> disks, which can be used for SQL Server files. Currently I use one 2 of th
em,
> 1 for data file and 1 for log file. I try to find how to use another disk
to
> speed up queries and to have more even distribution of transaction times.
I
> think to move indexes on another physical drive, so data would be on one
> disk, and indexes on another. I guess it would reduce average and maximum
> wait time on latches during checkpoints. Is it true, what can be expected
> from this movement of indexes?
> Another idea which I have - use two drives for data, one for log. In this
> case I would create filegroup whih would include two data files on differe
nt
> drives, and data would be evenly distributed across drives. Is it better i
n
> terms of performance and to have more even distribution of transaction tim
es?
> May be there is better way to use 3dr drive?|||Hi
You can move heavy quering table/s to another physical disk , please note
physical not logical.
You are right , separating LOG and DATA files will improve the performance
of the database.
If your database is small and the tables are small as well why do you want
at least at this moment to move the tabes/indexes, did you start to get slow
response?
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7988A160-301C-4BE2-A7FC-9B9090E62831@.microsoft.com...
> I have SQL Server, which quite often have 200 transaction/second. It is
> expected what the load, during this year, would be increased up to 1000
> transactions per second. The database relatively small, can be loaded in
> memory. All tables which often used by queries pinned in memory, with
> 'pintable' option. IO system - DELL CX300, with write cache enabled. I
have 3
> disks, which can be used for SQL Server files. Currently I use one 2 of
them,
> 1 for data file and 1 for log file. I try to find how to use another disk
to
> speed up queries and to have more even distribution of transaction times.
I
> think to move indexes on another physical drive, so data would be on one
> disk, and indexes on another. I guess it would reduce average and maximum
> wait time on latches during checkpoints. Is it true, what can be expected
> from this movement of indexes?
> Another idea which I have - use two drives for data, one for log. In this
> case I would create filegroup whih would include two data files on
different
> drives, and data would be evenly distributed across drives. Is it better
in
> terms of performance and to have more even distribution of transaction
times?
> May be there is better way to use 3dr drive?|||More importantly than what the others have suggested, when you say current
activity is 200 TPS and future activity is 1,000 TPS, that is a pretty big
jump.
What is the nature of this activity? 50% Reads/50% Writes? 75% Reads/25%
Writes? 25% Reads/75% Writes?
Can you tell what the current I/O OPS are? What is the projection?
A single physical disk (1 spindle, dedicated) can achieve perhaps 100-200
I/O OPS, depending on the R/W and physical disk metrics. For this much
activity, you do not want to drive this disk much above 65% on average.
Every transaction will involve a serial log write and a random data write,
eventually. You should have at least a sufficient number of spindles to
cover your anticipated I/O OPS, both for data and log activity.
For example, if you feel you have 300 I/O OPS, you should have at least 2;
however, especially with a dedicated storage solution, you should stripe at
least six spindles per LUN, at a minimum. If you can't achieve the minimum,
then you may consider a technique called Disk Plaiding, which is striping
your LUN at the hardware level, but then do as you suggest, and multi-file
each SQL Server filegroup. This way you have hardware striping and DBMS
striping across those (a Plaid...checkerboard pattern) of distributed data.
The LUN striping should be Raid, of course, or mirrored sets of
Hyper-volumes, called Meta-volumes. This is effectively RAID 10, but know
that hyper-volumes are only a slice of a disk and any single disk (1
spindle) could be shared out, hyper by hyper, to several hosts. You must
take this into account when trying to count I/O OPS per spindle.
You should really work with your Storage Management to communicate the
capacity requirements. Barring that, contact your storage technical rep.
Be careful though, many will tell you that it doesn't matter and try to give
you one big volume. It matters and they are full of hooey. All of the
major storage providers and Microsoft publish white papers on how to set up
external storage for DBMS support. Use those.
Sincerely,
Anthony Thomas
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7988A160-301C-4BE2-A7FC-9B9090E62831@.microsoft.com...
I have SQL Server, which quite often have 200 transaction/second. It is
expected what the load, during this year, would be increased up to 1000
transactions per second. The database relatively small, can be loaded in
memory. All tables which often used by queries pinned in memory, with
'pintable' option. IO system - DELL CX300, with write cache enabled. I have
3
disks, which can be used for SQL Server files. Currently I use one 2 of
them,
1 for data file and 1 for log file. I try to find how to use another disk to
speed up queries and to have more even distribution of transaction times. I
think to move indexes on another physical drive, so data would be on one
disk, and indexes on another. I guess it would reduce average and maximum
wait time on latches during checkpoints. Is it true, what can be expected
from this movement of indexes?
Another idea which I have - use two drives for data, one for log. In this
case I would create filegroup whih would include two data files on different
drives, and data would be evenly distributed across drives. Is it better in
terms of performance and to have more even distribution of transaction
times?
May be there is better way to use 3dr drive?

Moving indexes from one server to another

I moved my Sql Server Database from one sever to another ,Is there any way t
o
regenerate the indexes on the server we moved to without having to recreate
them?
TIA,
Vinny"Vinny Vinn" <VinnyVinn@.discussions.microsoft.com> wrote in message
news:67FC6662-F011-49DB-B623-A66CE2075F3A@.microsoft.com...
> I moved my Sql Server Database from one sever to another ,Is there any way
to
> regenerate the indexes on the server we moved to without having to
recreate
> them?
> TIA,
> Vinny
>
The indexes are objects stored in the database itself. When you moved the
database to the new server, the indexes should have come with you.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi,
As soon as you Restore the database using RESTORE DATABASE or SP_ATTACH_DB
automatically data and all the objects will be restored.
Thanks
Hari
SQL Server MVP
"Vinny Vinn" <VinnyVinn@.discussions.microsoft.com> wrote in message
news:67FC6662-F011-49DB-B623-A66CE2075F3A@.microsoft.com...
>I moved my Sql Server Database from one sever to another ,Is there any way
>to
> regenerate the indexes on the server we moved to without having to
> recreate
> them?
> TIA,
> Vinny
>

Friday, March 30, 2012

moving clustered indexes

moving clustered indexes

moving clustered indexes

Moving BD to new schema...

Friday, March 9, 2012

move indexes via ALTER INDEX

Move indexes to new filegroup

Move indexes to new filegroup

Move indexes to new filegroup

Monday, February 20, 2012

Moving indexes to their own disk drive

moving indexes to other filegroup

moving indexes to other filegroup

moving indexes to other filegroup

Moving indexes to another filegroup trouble...

Moving indexes to another filegroup trouble...

moving indexes onto another partition

moving indexes onto another partition

Moving indexes on another physical drive

Moving indexes on another physical drive

Moving indexes on another physical drive

Moving indexes from one server to another

Move database file

Blog Archive

About Me