Hi...
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
ForchHi
"Forch" wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] had
been dropped and re-created at some point, although my tests on a SP2 machine
have not replicated this!
John|||Are you sure that Field3 and Field4 have the same data definition in
both databases?
Are you sure the clustered index has the same definition in both
databases?
Are you sure Field3 comes before Field4 in the table definition in both
databases?
If you have table modification (such as changing a column's definition)
you could try to reindex the clustered index and recreate the
nonclustered index.
HTH,
Gert-Jan
Forch wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
Showing posts with label indexes. Show all posts
Showing posts with label indexes. Show all posts
Tuesday, March 20, 2012
Can not change index INCLUDE fields order....
Hi...
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], &
#91;Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], &
#91;Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
ForchHi
"Forch" wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases
;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3],
[Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4],
[Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE.
No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in th
e
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] ha
d
been dropped and re-created at some point, although my tests on a SP2 machin
e
have not replicated this!
John|||Are you sure that Field3 and Field4 have the same data definition in
both databases?
Are you sure the clustered index has the same definition in both
databases?
Are you sure Field3 comes before Field4 in the table definition in both
databases?
If you have table modification (such as changing a column's definition)
you could try to reindex the clustered index and recreate the
nonclustered index.
HTH,
Gert-Jan
Forch wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases
;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3],
[Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4],
[Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE.
No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in th
e
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], &
#91;Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], &
#91;Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
ForchHi
"Forch" wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases
;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3],
[Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4],
[Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE.
No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in th
e
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] ha
d
been dropped and re-created at some point, although my tests on a SP2 machin
e
have not replicated this!
John|||Are you sure that Field3 and Field4 have the same data definition in
both databases?
Are you sure the clustered index has the same definition in both
databases?
Are you sure Field3 comes before Field4 in the table definition in both
databases?
If you have table modification (such as changing a column's definition)
you could try to reindex the clustered index and recreate the
nonclustered index.
HTH,
Gert-Jan
Forch wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases
;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3],
[Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4],
[Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE.
No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in th
e
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
Can not change index INCLUDE fields order....
Hi...
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
Forch
Hi
"Forch" wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] had
been dropped and re-created at some point, although my tests on a SP2 machine
have not replicated this!
John
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
Forch
Hi
"Forch" wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] had
been dropped and re-created at some point, although my tests on a SP2 machine
have not replicated this!
John
Sunday, March 11, 2012
Can Indexes LIve on another server?
I know I can split up data and indexes onto different file groups, but can I
then move the index file group to another server? if so, what are the pros &
cons?
thanks in advance!!Yes, you can certainly have a filegroup which lives on another physical
resource. There are two very strong di
vantages to this:
(a) network latency unless you are on gig+ ethernet...
(b) reliance on multiple machines to be up for one server to be considered
"available." How do you plan to recycle machines after applying a service
pack or hotfix?
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:E7BF3CB9-6974-4BBD-AD9F-94D81D04EC4C@.microsoft.com...
>I know I can split up data and indexes onto different file groups, but can
>I
> then move the index file group to another server? if so, what are the pros
> &
> cons?
> thanks in advance!!|||what are the performance gains/issues?
I am looking at splitting up a 1 tb data file into a 300gb data file and a
700gb index file. I have a gb network, but I am worried about administrating
mutiple machines.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Yes, you can certainly have a filegroup which lives on another physical
> resource. There are two very strong divantages to this:
> (a) network latency unless you are on gig+ ethernet...
> (b) reliance on multiple machines to be up for one server to be considered
> "available." How do you plan to recycle machines after applying a service
> pack or hotfix?
>
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:E7BF3CB9-6974-4BBD-AD9F-94D81D04EC4C@.microsoft.com...
>
>|||Why not just additional drive(s) to the same server?
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:29753642-4A9E-4DC3-975B-578EBFD963AC@.microsoft.com...
> what are the performance gains/issues?
> I am looking at splitting up a 1 tb data file into a 300gb data file and a
> 700gb index file. I have a gb network, but I am worried about
> administrating
> mutiple machines.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Incorrect. olny local drives can be used for this purpose or special SAN
that you probably don't have.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uMTRslkrFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Yes, you can certainly have a filegroup which lives on another physical
> resource. There are two very strong divantages to this:
> (a) network latency unless you are on gig+ ethernet...
> (b) reliance on multiple machines to be up for one server to be considered
> "available." How do you plan to recycle machines after applying a service
> pack or hotfix?
>
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:E7BF3CB9-6974-4BBD-AD9F-94D81D04EC4C@.microsoft.com...
>|||And only enterprize edition supports VIA SANs. others will work if a SAN
drive is presented by SAN driver as local.
however another tip here is:
BOL
Note When running on Windows NT, SQL Server performance can be improved
further if the databases are created on disks formatted using NTFS and,
specifically, 64-KB extent sizes. In Windows 2000, setting the file system
cluster size to 64-KB extent size using the /A format option will also
improve performance. For more information about formatting an NTFS disk, see
the Windows NT or Windows 2000 documentation.
"Farmer" <someone@.somewhere.com> wrote in message
news:ez99%23ilrFHA.3068@.TK2MSFTNGP14.phx.gbl...
> Incorrect. olny local drives can be used for this purpose or special SAN
> that you probably don't have.
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uMTRslkrFHA.1028@.TK2MSFTNGP12.phx.gbl...
>|||Am I reading that you have 700gb of indexing on 300gb of tables?
Consider if you are indexing on too many columns:
http://msdn.microsoft.com/library/d.../>
indexes.asp
Also, look into the extent of index fragmentation:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:29753642-4A9E-4DC3-975B-578EBFD963AC@.microsoft.com...
> what are the performance gains/issues?
> I am looking at splitting up a 1 tb data file into a 300gb data file and a
> 700gb index file. I have a gb network, but I am worried about
> administrating
> mutiple machines.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> Incorrect. olny local drives can be used for this purpose or special SAN
> that you probably don't have.
Uh, we have multiple Clariions. But thanks for the tip.|||And here's the full story on creating database files on network drives.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Farmer" <someone@.somewhere.com> wrote in message news:ez99%23ilrFHA.3068@.TK2MSFTNGP14.phx.
gbl...
> Incorrect. olny local drives can be used for this purpose or special SAN
> that you probably don't have.
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:uMTRslkrFHA.1028@.TK2MSFTNGP12.phx.gbl...
>|||Well, we are currently delivering on Del 2650's with a powervault loaded wit
h
16 - 15k 73gb hard drives configured into one raid-5 drive. The search times
on this 1tb table are very fast, but I am looking to scale it out. I would
like to seperate the indexes and the data onto different disks, but then
started wondering if I could store the data on one server, and say store the
indexes on 3-4 other servers that point to the data servers.
My research has found that 70-80% of query times is caught up in the indexes
looking for the right keys to use to go get the data with. I have 26 indexes
built on my table that covers 95% of the queries uses according to our log
files. I would like to spread out the work done in the index search to a fe
w
specialized servers if possible. I dont think that MS can do this, but I can
probably build something myself that could.
thanks!!
"Aaron Bertrand [SQL Server MVP]" wrote:
> Uh, we have multiple Clariions. But thanks for the tip.
>
>
then move the index file group to another server? if so, what are the pros &
cons?
thanks in advance!!Yes, you can certainly have a filegroup which lives on another physical
resource. There are two very strong di
vantages to this:(a) network latency unless you are on gig+ ethernet...
(b) reliance on multiple machines to be up for one server to be considered
"available." How do you plan to recycle machines after applying a service
pack or hotfix?
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:E7BF3CB9-6974-4BBD-AD9F-94D81D04EC4C@.microsoft.com...
>I know I can split up data and indexes onto different file groups, but can
>I
> then move the index file group to another server? if so, what are the pros
> &
> cons?
> thanks in advance!!|||what are the performance gains/issues?
I am looking at splitting up a 1 tb data file into a 300gb data file and a
700gb index file. I have a gb network, but I am worried about administrating
mutiple machines.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Yes, you can certainly have a filegroup which lives on another physical
> resource. There are two very strong di
vantages to this:> (a) network latency unless you are on gig+ ethernet...
> (b) reliance on multiple machines to be up for one server to be considered
> "available." How do you plan to recycle machines after applying a service
> pack or hotfix?
>
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:E7BF3CB9-6974-4BBD-AD9F-94D81D04EC4C@.microsoft.com...
>
>|||Why not just additional drive(s) to the same server?
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:29753642-4A9E-4DC3-975B-578EBFD963AC@.microsoft.com...
> what are the performance gains/issues?
> I am looking at splitting up a 1 tb data file into a 300gb data file and a
> 700gb index file. I have a gb network, but I am worried about
> administrating
> mutiple machines.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Incorrect. olny local drives can be used for this purpose or special SAN
that you probably don't have.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uMTRslkrFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Yes, you can certainly have a filegroup which lives on another physical
> resource. There are two very strong di
vantages to this:> (a) network latency unless you are on gig+ ethernet...
> (b) reliance on multiple machines to be up for one server to be considered
> "available." How do you plan to recycle machines after applying a service
> pack or hotfix?
>
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:E7BF3CB9-6974-4BBD-AD9F-94D81D04EC4C@.microsoft.com...
>|||And only enterprize edition supports VIA SANs. others will work if a SAN
drive is presented by SAN driver as local.
however another tip here is:
BOL
Note When running on Windows NT, SQL Server performance can be improved
further if the databases are created on disks formatted using NTFS and,
specifically, 64-KB extent sizes. In Windows 2000, setting the file system
cluster size to 64-KB extent size using the /A format option will also
improve performance. For more information about formatting an NTFS disk, see
the Windows NT or Windows 2000 documentation.
"Farmer" <someone@.somewhere.com> wrote in message
news:ez99%23ilrFHA.3068@.TK2MSFTNGP14.phx.gbl...
> Incorrect. olny local drives can be used for this purpose or special SAN
> that you probably don't have.
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uMTRslkrFHA.1028@.TK2MSFTNGP12.phx.gbl...
>|||Am I reading that you have 700gb of indexing on 300gb of tables?
Consider if you are indexing on too many columns:
http://msdn.microsoft.com/library/d.../>
indexes.asp
Also, look into the extent of index fragmentation:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:29753642-4A9E-4DC3-975B-578EBFD963AC@.microsoft.com...
> what are the performance gains/issues?
> I am looking at splitting up a 1 tb data file into a 300gb data file and a
> 700gb index file. I have a gb network, but I am worried about
> administrating
> mutiple machines.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> Incorrect. olny local drives can be used for this purpose or special SAN
> that you probably don't have.
Uh, we have multiple Clariions. But thanks for the tip.|||And here's the full story on creating database files on network drives.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Farmer" <someone@.somewhere.com> wrote in message news:ez99%23ilrFHA.3068@.TK2MSFTNGP14.phx.
gbl...
> Incorrect. olny local drives can be used for this purpose or special SAN
> that you probably don't have.
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:uMTRslkrFHA.1028@.TK2MSFTNGP12.phx.gbl...
>|||Well, we are currently delivering on Del 2650's with a powervault loaded wit
h
16 - 15k 73gb hard drives configured into one raid-5 drive. The search times
on this 1tb table are very fast, but I am looking to scale it out. I would
like to seperate the indexes and the data onto different disks, but then
started wondering if I could store the data on one server, and say store the
indexes on 3-4 other servers that point to the data servers.
My research has found that 70-80% of query times is caught up in the indexes
looking for the right keys to use to go get the data with. I have 26 indexes
built on my table that covers 95% of the queries uses according to our log
files. I would like to spread out the work done in the index search to a fe
w
specialized servers if possible. I dont think that MS can do this, but I can
probably build something myself that could.
thanks!!
"Aaron Bertrand [SQL Server MVP]" wrote:
> Uh, we have multiple Clariions. But thanks for the tip.
>
>
Sunday, February 12, 2012
Can I make my delete run faster?
On our OLTP 24/6 system we have a table with over 40,000,000 rows in
it, the need is to remove half of these rows. There are 7 indexes on
the table (one clustered, six non-clustered). Since the IT director
won't let me move it to a faster server, I have to delete the records
month by month. The data is not referential (I didn't design the db) so
I can't use cascading deletes and have to use a delete trigger:
CREATE TRIGGER [Delete From Joining Tables] ON dbo.Consignments
FOR DELETE
AS
SET NOCOUNT ON
IF NOT EXISTS(SELECT * FROM INSERTED)
BEGIN
delete from tbl1 where exists (select * from deleted as d where
d.[master id] = tbl1.[master id])
delete from tbl2 where exists (select * from deleted as d where
d.[master id] = tbl2.[master id])
delete from tbl3 where exists (select * from deleted as d where
d.[master id] = tbl3.[master id])
delete from tbl4 where exists (select * from deleted as d where
d.[master id] = tbl4.[master id])
. .. .. .. .. .. .. .. .. ..
. .. .. .. .. .. .. .. .. ..
(up to 17 tables)
END
The trigger works fine and is deleting the relevant data in the above
tables, however I am wondering if there is some way I can speed up the
deletes. I have tried using a while loop and deleting in piecemeal
however this doesn't seem to be any faster than using the following
statement:
delete from [consignments]
where [Date] >= '20011201' and [Date] <= '20011207'
Currently on our high spec'd dev server (10 disks, quad procs,
enterprise edn) I am experiencing odd timings, sometimes it takes 5
minutes to delete 1million rows, yet it can take over 30 mins to delete
1.5million rows. There is no one else accessing this server. I created
a further index on [master id] and [Date] no visible increase in
deletes. I have ran DBCC INDEXDEFRAG on each index in the table and
Updated the stats, no joy also. So, any ideas you SQL MVP's?
Should I be using index hints? (I have read the SQL Server can do this
efficiently on its own.)
Cheers
qhScott
Why do you need a trigger?
Write stored procedure instead
Don't run the transaction to delete all rows at once. Divide the tran into
small ones
SET ROWCOUNT 100000
WHILE 1 = 1
BEGIN
DELETE FROM MyTable WHERE ........
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
END
SET ROWCOUNT 0
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1116427283.912954.281370@.g49g2000cwa.googlegroups.com...
> On our OLTP 24/6 system we have a table with over 40,000,000 rows in
> it, the need is to remove half of these rows. There are 7 indexes on
> the table (one clustered, six non-clustered). Since the IT director
> won't let me move it to a faster server, I have to delete the records
> month by month. The data is not referential (I didn't design the db) so
> I can't use cascading deletes and have to use a delete trigger:
> CREATE TRIGGER [Delete From Joining Tables] ON dbo.Consignments
> FOR DELETE
> AS
> SET NOCOUNT ON
> IF NOT EXISTS(SELECT * FROM INSERTED)
> BEGIN
> delete from tbl1 where exists (select * from deleted as d where
> d.[master id] = tbl1.[master id])
> delete from tbl2 where exists (select * from deleted as d where
> d.[master id] = tbl2.[master id])
> delete from tbl3 where exists (select * from deleted as d where
> d.[master id] = tbl3.[master id])
> delete from tbl4 where exists (select * from deleted as d where
> d.[master id] = tbl4.[master id])
> .. .. .. .. .. .. .. .. .. ..
> .. .. .. .. .. .. .. .. .. ..
> (up to 17 tables)
> END
> The trigger works fine and is deleting the relevant data in the above
> tables, however I am wondering if there is some way I can speed up the
> deletes. I have tried using a while loop and deleting in piecemeal
> however this doesn't seem to be any faster than using the following
> statement:
> delete from [consignments]
> where [Date] >= '20011201' and [Date] <= '20011207'
> Currently on our high spec'd dev server (10 disks, quad procs,
> enterprise edn) I am experiencing odd timings, sometimes it takes 5
> minutes to delete 1million rows, yet it can take over 30 mins to delete
> 1.5million rows. There is no one else accessing this server. I created
> a further index on [master id] and [Date] no visible increase in
> deletes. I have ran DBCC INDEXDEFRAG on each index in the table and
> Updated the stats, no joy also. So, any ideas you SQL MVP's?
> Should I be using index hints? (I have read the SQL Server can do this
> efficiently on its own.)
> Cheers
> qh
>|||If you are deleting half of the rows in this table, then I assume this is a
one time deal and not a daily operation. Most of the time spent grinding
away is SQL Server logging the deletes to the transaction log. In addition
to deleting the records in batches of say 100,000 at a time, consider
changing the recovery model, perhaps temporarily, of the database to
"simple" and executing a "checkpoint" between each iteration of deletes.
This will minimize the amount of transaction logging. You may also want to
delete indexes from the table prior to the mass delete and then re-apply
them again afterward, especially the primary / clustered index. Also,
possibly temporary setting the database to SINGLE_USER mode could help.
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1116427283.912954.281370@.g49g2000cwa.googlegroups.com...
> On our OLTP 24/6 system we have a table with over 40,000,000 rows in
> it, the need is to remove half of these rows. There are 7 indexes on
> the table (one clustered, six non-clustered). Since the IT director
> won't let me move it to a faster server, I have to delete the records
> month by month. The data is not referential (I didn't design the db) so
> I can't use cascading deletes and have to use a delete trigger:
> CREATE TRIGGER [Delete From Joining Tables] ON dbo.Consignments
> FOR DELETE
> AS
> SET NOCOUNT ON
> IF NOT EXISTS(SELECT * FROM INSERTED)
> BEGIN
> delete from tbl1 where exists (select * from deleted as d where
> d.[master id] = tbl1.[master id])
> delete from tbl2 where exists (select * from deleted as d where
> d.[master id] = tbl2.[master id])
> delete from tbl3 where exists (select * from deleted as d where
> d.[master id] = tbl3.[master id])
> delete from tbl4 where exists (select * from deleted as d where
> d.[master id] = tbl4.[master id])
> .. .. .. .. .. .. .. .. .. ..
> .. .. .. .. .. .. .. .. .. ..
> (up to 17 tables)
> END
> The trigger works fine and is deleting the relevant data in the above
> tables, however I am wondering if there is some way I can speed up the
> deletes. I have tried using a while loop and deleting in piecemeal
> however this doesn't seem to be any faster than using the following
> statement:
> delete from [consignments]
> where [Date] >= '20011201' and [Date] <= '20011207'
> Currently on our high spec'd dev server (10 disks, quad procs,
> enterprise edn) I am experiencing odd timings, sometimes it takes 5
> minutes to delete 1million rows, yet it can take over 30 mins to delete
> 1.5million rows. There is no one else accessing this server. I created
> a further index on [master id] and [Date] no visible increase in
> deletes. I have ran DBCC INDEXDEFRAG on each index in the table and
> Updated the stats, no joy also. So, any ideas you SQL MVP's?
> Should I be using index hints? (I have read the SQL Server can do this
> efficiently on its own.)
> Cheers
> qh
>|||JT wrote:
> If you are deleting half of the rows in this table, then I assume
this is a
> one time deal and not a daily operation.
Yes, for political reasons, I can't simply create a new table and copy
the rows we want then rename the new/old table. Therefore on a w
end
(only time db isn't being used) I need to run a script that can remove
over 15,000,000 in a few hours. I should be able to do it now, however
I want to see if I can make it faster.
I'm using a trigger to ensure that I don't have any redundant data in
the other tables. Yes RI should have been setup in the design stage
however, in a perfect world, etc...
Thanks for the reply.
Scott|||Hi
This can be modified as:
delete tbl4
FROM tbl4
INNER JOIN deleted as d ON d.[master id] = tbl4.[master id]
this will improve the performance
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Scott" wrote:
> On our OLTP 24/6 system we have a table with over 40,000,000 rows in
> it, the need is to remove half of these rows. There are 7 indexes on
> the table (one clustered, six non-clustered). Since the IT director
> won't let me move it to a faster server, I have to delete the records
> month by month. The data is not referential (I didn't design the db) so
> I can't use cascading deletes and have to use a delete trigger:
> CREATE TRIGGER [Delete From Joining Tables] ON dbo.Consignments
> FOR DELETE
> AS
> SET NOCOUNT ON
> IF NOT EXISTS(SELECT * FROM INSERTED)
> BEGIN
> delete from tbl1 where exists (select * from deleted as d where
> d.[master id] = tbl1.[master id])
> delete from tbl2 where exists (select * from deleted as d where
> d.[master id] = tbl2.[master id])
> delete from tbl3 where exists (select * from deleted as d where
> d.[master id] = tbl3.[master id])
> delete from tbl4 where exists (select * from deleted as d where
> d.[master id] = tbl4.[master id])
> .. .. .. .. .. .. .. .. .. ..
> .. .. .. .. .. .. .. .. .. ..
> (up to 17 tables)
> END
> The trigger works fine and is deleting the relevant data in the above
> tables, however I am wondering if there is some way I can speed up the
> deletes. I have tried using a while loop and deleting in piecemeal
> however this doesn't seem to be any faster than using the following
> statement:
> delete from [consignments]
> where [Date] >= '20011201' and [Date] <= '20011207'
> Currently on our high spec'd dev server (10 disks, quad procs,
> enterprise edn) I am experiencing odd timings, sometimes it takes 5
> minutes to delete 1million rows, yet it can take over 30 mins to delete
> 1.5million rows. There is no one else accessing this server. I created
> a further index on [master id] and [Date] no visible increase in
> deletes. I have ran DBCC INDEXDEFRAG on each index in the table and
> Updated the stats, no joy also. So, any ideas you SQL MVP's?
> Should I be using index hints? (I have read the SQL Server can do this
> efficiently on its own.)
> Cheers
> qh
>
it, the need is to remove half of these rows. There are 7 indexes on
the table (one clustered, six non-clustered). Since the IT director
won't let me move it to a faster server, I have to delete the records
month by month. The data is not referential (I didn't design the db) so
I can't use cascading deletes and have to use a delete trigger:
CREATE TRIGGER [Delete From Joining Tables] ON dbo.Consignments
FOR DELETE
AS
SET NOCOUNT ON
IF NOT EXISTS(SELECT * FROM INSERTED)
BEGIN
delete from tbl1 where exists (select * from deleted as d where
d.[master id] = tbl1.[master id])
delete from tbl2 where exists (select * from deleted as d where
d.[master id] = tbl2.[master id])
delete from tbl3 where exists (select * from deleted as d where
d.[master id] = tbl3.[master id])
delete from tbl4 where exists (select * from deleted as d where
d.[master id] = tbl4.[master id])
. .. .. .. .. .. .. .. .. ..
. .. .. .. .. .. .. .. .. ..
(up to 17 tables)
END
The trigger works fine and is deleting the relevant data in the above
tables, however I am wondering if there is some way I can speed up the
deletes. I have tried using a while loop and deleting in piecemeal
however this doesn't seem to be any faster than using the following
statement:
delete from [consignments]
where [Date] >= '20011201' and [Date] <= '20011207'
Currently on our high spec'd dev server (10 disks, quad procs,
enterprise edn) I am experiencing odd timings, sometimes it takes 5
minutes to delete 1million rows, yet it can take over 30 mins to delete
1.5million rows. There is no one else accessing this server. I created
a further index on [master id] and [Date] no visible increase in
deletes. I have ran DBCC INDEXDEFRAG on each index in the table and
Updated the stats, no joy also. So, any ideas you SQL MVP's?
Should I be using index hints? (I have read the SQL Server can do this
efficiently on its own.)
Cheers
qhScott
Why do you need a trigger?
Write stored procedure instead
Don't run the transaction to delete all rows at once. Divide the tran into
small ones
SET ROWCOUNT 100000
WHILE 1 = 1
BEGIN
DELETE FROM MyTable WHERE ........
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
END
SET ROWCOUNT 0
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1116427283.912954.281370@.g49g2000cwa.googlegroups.com...
> On our OLTP 24/6 system we have a table with over 40,000,000 rows in
> it, the need is to remove half of these rows. There are 7 indexes on
> the table (one clustered, six non-clustered). Since the IT director
> won't let me move it to a faster server, I have to delete the records
> month by month. The data is not referential (I didn't design the db) so
> I can't use cascading deletes and have to use a delete trigger:
> CREATE TRIGGER [Delete From Joining Tables] ON dbo.Consignments
> FOR DELETE
> AS
> SET NOCOUNT ON
> IF NOT EXISTS(SELECT * FROM INSERTED)
> BEGIN
> delete from tbl1 where exists (select * from deleted as d where
> d.[master id] = tbl1.[master id])
> delete from tbl2 where exists (select * from deleted as d where
> d.[master id] = tbl2.[master id])
> delete from tbl3 where exists (select * from deleted as d where
> d.[master id] = tbl3.[master id])
> delete from tbl4 where exists (select * from deleted as d where
> d.[master id] = tbl4.[master id])
> .. .. .. .. .. .. .. .. .. ..
> .. .. .. .. .. .. .. .. .. ..
> (up to 17 tables)
> END
> The trigger works fine and is deleting the relevant data in the above
> tables, however I am wondering if there is some way I can speed up the
> deletes. I have tried using a while loop and deleting in piecemeal
> however this doesn't seem to be any faster than using the following
> statement:
> delete from [consignments]
> where [Date] >= '20011201' and [Date] <= '20011207'
> Currently on our high spec'd dev server (10 disks, quad procs,
> enterprise edn) I am experiencing odd timings, sometimes it takes 5
> minutes to delete 1million rows, yet it can take over 30 mins to delete
> 1.5million rows. There is no one else accessing this server. I created
> a further index on [master id] and [Date] no visible increase in
> deletes. I have ran DBCC INDEXDEFRAG on each index in the table and
> Updated the stats, no joy also. So, any ideas you SQL MVP's?
> Should I be using index hints? (I have read the SQL Server can do this
> efficiently on its own.)
> Cheers
> qh
>|||If you are deleting half of the rows in this table, then I assume this is a
one time deal and not a daily operation. Most of the time spent grinding
away is SQL Server logging the deletes to the transaction log. In addition
to deleting the records in batches of say 100,000 at a time, consider
changing the recovery model, perhaps temporarily, of the database to
"simple" and executing a "checkpoint" between each iteration of deletes.
This will minimize the amount of transaction logging. You may also want to
delete indexes from the table prior to the mass delete and then re-apply
them again afterward, especially the primary / clustered index. Also,
possibly temporary setting the database to SINGLE_USER mode could help.
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1116427283.912954.281370@.g49g2000cwa.googlegroups.com...
> On our OLTP 24/6 system we have a table with over 40,000,000 rows in
> it, the need is to remove half of these rows. There are 7 indexes on
> the table (one clustered, six non-clustered). Since the IT director
> won't let me move it to a faster server, I have to delete the records
> month by month. The data is not referential (I didn't design the db) so
> I can't use cascading deletes and have to use a delete trigger:
> CREATE TRIGGER [Delete From Joining Tables] ON dbo.Consignments
> FOR DELETE
> AS
> SET NOCOUNT ON
> IF NOT EXISTS(SELECT * FROM INSERTED)
> BEGIN
> delete from tbl1 where exists (select * from deleted as d where
> d.[master id] = tbl1.[master id])
> delete from tbl2 where exists (select * from deleted as d where
> d.[master id] = tbl2.[master id])
> delete from tbl3 where exists (select * from deleted as d where
> d.[master id] = tbl3.[master id])
> delete from tbl4 where exists (select * from deleted as d where
> d.[master id] = tbl4.[master id])
> .. .. .. .. .. .. .. .. .. ..
> .. .. .. .. .. .. .. .. .. ..
> (up to 17 tables)
> END
> The trigger works fine and is deleting the relevant data in the above
> tables, however I am wondering if there is some way I can speed up the
> deletes. I have tried using a while loop and deleting in piecemeal
> however this doesn't seem to be any faster than using the following
> statement:
> delete from [consignments]
> where [Date] >= '20011201' and [Date] <= '20011207'
> Currently on our high spec'd dev server (10 disks, quad procs,
> enterprise edn) I am experiencing odd timings, sometimes it takes 5
> minutes to delete 1million rows, yet it can take over 30 mins to delete
> 1.5million rows. There is no one else accessing this server. I created
> a further index on [master id] and [Date] no visible increase in
> deletes. I have ran DBCC INDEXDEFRAG on each index in the table and
> Updated the stats, no joy also. So, any ideas you SQL MVP's?
> Should I be using index hints? (I have read the SQL Server can do this
> efficiently on its own.)
> Cheers
> qh
>|||JT wrote:
> If you are deleting half of the rows in this table, then I assume
this is a
> one time deal and not a daily operation.
Yes, for political reasons, I can't simply create a new table and copy
the rows we want then rename the new/old table. Therefore on a w
end(only time db isn't being used) I need to run a script that can remove
over 15,000,000 in a few hours. I should be able to do it now, however
I want to see if I can make it faster.
I'm using a trigger to ensure that I don't have any redundant data in
the other tables. Yes RI should have been setup in the design stage
however, in a perfect world, etc...
Thanks for the reply.
Scott|||Hi
This can be modified as:
delete tbl4
FROM tbl4
INNER JOIN deleted as d ON d.[master id] = tbl4.[master id]
this will improve the performance
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Scott" wrote:
> On our OLTP 24/6 system we have a table with over 40,000,000 rows in
> it, the need is to remove half of these rows. There are 7 indexes on
> the table (one clustered, six non-clustered). Since the IT director
> won't let me move it to a faster server, I have to delete the records
> month by month. The data is not referential (I didn't design the db) so
> I can't use cascading deletes and have to use a delete trigger:
> CREATE TRIGGER [Delete From Joining Tables] ON dbo.Consignments
> FOR DELETE
> AS
> SET NOCOUNT ON
> IF NOT EXISTS(SELECT * FROM INSERTED)
> BEGIN
> delete from tbl1 where exists (select * from deleted as d where
> d.[master id] = tbl1.[master id])
> delete from tbl2 where exists (select * from deleted as d where
> d.[master id] = tbl2.[master id])
> delete from tbl3 where exists (select * from deleted as d where
> d.[master id] = tbl3.[master id])
> delete from tbl4 where exists (select * from deleted as d where
> d.[master id] = tbl4.[master id])
> .. .. .. .. .. .. .. .. .. ..
> .. .. .. .. .. .. .. .. .. ..
> (up to 17 tables)
> END
> The trigger works fine and is deleting the relevant data in the above
> tables, however I am wondering if there is some way I can speed up the
> deletes. I have tried using a while loop and deleting in piecemeal
> however this doesn't seem to be any faster than using the following
> statement:
> delete from [consignments]
> where [Date] >= '20011201' and [Date] <= '20011207'
> Currently on our high spec'd dev server (10 disks, quad procs,
> enterprise edn) I am experiencing odd timings, sometimes it takes 5
> minutes to delete 1million rows, yet it can take over 30 mins to delete
> 1.5million rows. There is no one else accessing this server. I created
> a further index on [master id] and [Date] no visible increase in
> deletes. I have ran DBCC INDEXDEFRAG on each index in the table and
> Updated the stats, no joy also. So, any ideas you SQL MVP's?
> Should I be using index hints? (I have read the SQL Server can do this
> efficiently on its own.)
> Cheers
> qh
>
Subscribe to:
Posts (Atom)