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 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...
>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.
>
>

Can I tell how often the clustered index pages are spliting?

Greetings

For performance reasons I am trying to determine how often the pages in
the index split due to inserts. Are there any records in the 'sys' tables,
etc. Thanx"Robert" <stop.spam@.boeing.com> wrote in message news:<Hsw479.Mnx@.news.boeing.com>...
> Greetings
> For performance reasons I am trying to determine how often the pages in
> the index split due to inserts. Are there any records in the 'sys' tables,
> etc. Thanx

I don't think this is stored in any system table, but you can use the
Performance Monitor counter SQLServer:AccessMethods, Page Splits/sec
to capture the information.

Simon|||Many, many thanx. I found it.

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0402102355.4236cb34@.posting.google.c om...
> "Robert" <stop.spam@.boeing.com> wrote in message
news:<Hsw479.Mnx@.news.boeing.com>...
> > Greetings
> > For performance reasons I am trying to determine how often the pages
in
> > the index split due to inserts. Are there any records in the 'sys'
tables,
> > etc. Thanx
> I don't think this is stored in any system table, but you can use the
> Performance Monitor counter SQLServer:AccessMethods, Page Splits/sec
> to capture the information.
> Simon

Can I split a field based on a character?

Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.

Don't let blindman see this post; he'll pull out the complete works of E.F. Codd!!! :D

Seriously, do a google on fn_Split(). I think you will find it will suit your purposes.

Regards,

hmscott|||check that site,I think that is the best and simple way to do.
http://www.sqlteam.com/item.asp?ItemID=2652|||The requirements are very close to the ones of generating a remittance advice when running Claims Reimbursement...It's been awhile, but I had to deal with the same scenario. First, I stored Dx codes separately from Claim Details. I did have a reference from ClaimDxCodes back to Claim Details table to retain dependency of CPT codes on diagnosis codes (Dx). When printing the Remittance Advice I transformed the data to present it in the format that you're trying to accomplish, using Crystal (the original app was written in VB4 with SQL 6.5). Later, when I recoded the reporting piece for ActiveReports control, I created a sub-report to display the Dx codes as a comma-separated list. But the concept remained the same, - ClaimHeader-->>ClaimDetail-->>ClaimDxCodes. Do not store Dx codes in the same table as your CPT codes, or your claim header info! Doing so violates the fundamentals of relational database principles, and B. Lindman WILL pull out the complete works of Codd at you...Let the massacre begin!..|||That's got me out of a tight spot, thanks very much.
Cheers, Simon.|||I'm really a nice guy. Really.