March 31, 2008 at 9:34 pm
Hello
I have a question about having indexes on a different set of spindles. I have a customer that has set up their server in preparation for me to install their DB. They have a data partition(ok) a log partition (wonderful) and an index partition(what???). I have never separated indexes from the data file. I have a reasonable idea of how to do it, but I am not sure why. The database will probably crawl up to 30 or 40 GB in the next couple of years, so it is not an extremely large db and it will live on their SAN. So my questions are:
Why would you separate the indexes from the data?
If the db is on a SAN is there really any benefit?
Which indexes should I separate, or more importantly which should I not separate?
Thanks in advance for your help.
Steve
April 1, 2008 at 8:15 am
There are a couple of reasons to put indexes on another disk array.
Most likely, they wanted another set of read and write heads for the index searches. This can be of great benefit if you have a lot of queries running against two different indexes - if you put the indexes on different arrays, they will not contend for disk resources.
As for choosing which and when - you need to determine index usage and disk contention and separate the ones that fight each other for disk resources.
April 1, 2008 at 9:13 am
Michael is right, but I'd also think about separating the logs from the data onto separate spindles (logs - sequential access, data-random), or even tempdb onto separate spindles since it's used for all ORDER BY, GROUP BY, worktables, etc.
The idea is to spread the load and have two sets of spindles working at the same time, speeding up performance. Be sure that the spindles aren't shared on the SAN as sometimes logical LUNs result in physical shared spindles.
April 1, 2008 at 9:23 am
One other note, we do this regularly because, as Steve state, sharing the load across multiple access points is a simple win. However, you now need to be careful about how you place your data. Creating a clustered index, you want to point it at the data filegroup, not the index filegroup since it stores the data. It's a silly detail to miss, but awfully easy. Then you suddenly find that your careful disk distribution isn't working at all since the indexes & data are mixed up again.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2008 at 9:31 am
I have to agree with Grant and Steve. In our DW project, I have seperated the system tables ([PRIMARY]) from the data [DATA], default), non-clustered indexes ([Indexes]), and indexed views ([ViewData]). In the development environment these are (unfortunately) on the same disks, however, when we move to our production environment, these will be on their own sets of disks on our SAN (as will the transaction logs).
😎
April 1, 2008 at 9:36 am
And to really muck up the environment some more, add a filegroup for storing text or (MAX) columns too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2008 at 9:45 am
That is really good information. Thanks
How about this variable as well. Our DC (of which we have 3 now) typically houses many DBs. These would probably be considered small < 50GB and most around 2-3GB.
Would that change anything that has been recommended so far?
Steve
April 1, 2008 at 9:57 am
If DC means Domain Controllers, my advice is to get the databases of them and onto their own dedicated server(s).
😎
April 1, 2008 at 9:59 am
Sorry
TOO many acronyms.
DC=Data Center
Steve
April 1, 2008 at 10:01 am
Not for me. In general, the suggestions so far won't hurt performance on a 2gb database and they will help performance on larger databases. It's easy and free, so why not?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply