Moving Existing Table Indexes to new files from Primary file, improve performance

Question

Moving Existing Table Indexes to new files from Primary file, improve performance

tmbasha

SSC Rookie

Points: 42
More actions
June 12, 2008 at 11:21 pm

#189116

Hi,
Current my entire data reside on one primary file, it abt 350 GB with all required indexes, Data was daily increasing, i planned to improve the performance my moving high transaction tables indexes to new file, is this improve performance. please help me in this regard.
Thanks
Basha

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Simply moving to a new file will not improve performance.

The file should be located on a different disk(s) so that IO can distributed over the disks and this will help your performance.

This is just a general pointer and how exactly to distribute differs from case to case.

check out this site and and the web for more details.

"Keep Trying"

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

It's unlikely to increase performance, unless the new file is on a different physical drive and IO is your bottelneck. It usually isn't

You will probably get better return on your time by looking at the slow queries and modifying the query or the indexes it uses.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 3

What everyone else said is very correct. Multiple disks with multiple files and file groups is the way to go.

The one thing I would add is that I've read (can't find the article now despite looking, it was something from MS) that splitting up files & filegroups, even without multiple drives, does increase performance a tad.

Regardless of performance, it offers backup & recovery mechanisms that might prove useful. I did find one of these articles. There's another linked from this one to one by Kimberly Tripp.

"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

noeld SSC Guru Points: 96590 More actions · Answer 4

Another thing to consider is the posibility of "archiving" old data. It has been my experience that in many occassions we let the tables grow with data that is almost never queried. See if you can create "historic" tables and move old stuff out of the main ones. Some other times this is not possible though.

Just my $0.02

* Noel

tmbasha SSC Rookie Points: 42 More actions · Answer 5

Hi,

Thanks all of you, for your valuable suggestions. Below i am providing the estimation size of different tables with index also which resides on single file i.e. Primary.... may be growth rate of data is very high for upcoming years. So, i am proceeding with moving the below index data to new file group on different location.

S.No.TableData Size(GB)Index Size(GB)Total No of Indexes

1.X129234

2.X22082

3.X33753

4.X4247

5.X51533

6.X6114

7.X70114

8.X81003

noeld SSC Guru Points: 96590 More actions · Answer 6

tmbasha (6/15/2008)
Hi,
Thanks all of you, for your valuable suggestions. Below i am providing the estimation size of different tables with index also which resides on single file i.e. Primary.... may be growth rate of data is very high for upcoming years. So, i am proceeding with moving the below index data to new file group on different location.
S.No.TableData Size(GB)Index Size(GB)Total No of Indexes
1.X129234
2.X22082
3.X33753
4.X4247
5.X51533
6.X6114
7.X70114
8.X81003

... 53 indexes in one table? ... 🙁

* Noel

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

noeld (6/16/2008)
tmbasha (6/15/2008)
S.No.TableData Size(GB)Index Size(GB)Total No of Indexes
3.X33753
... 53 indexes in one table? ... 🙁

Ouch. 3 GB data and 7GB indexes. I hope it's a read-only table

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tmbasha SSC Rookie Points: 42 More actions · Answer 8

This is the table which stores an Order of 93 columns, And this DB is design develop 10 years ago.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

tmbasha (6/16/2008)
This is the table which stores an Order of 93 columns

Still... I'd be quite willing to bet that SQL is only using a small % of those indexes. Check the index usage DMV (sys.dm_db_index_usage_stats), see if you have any unused ones that can be removed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tmbasha SSC Rookie Points: 42 More actions · Answer 10

Which columns do i need to verify for checking less usage of indexes from below query

SELECT * FROM sys.dm_db_index_usage_stats

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 11

Check the number of seeks, scans and lookups. If they're all 0, the index is not been used. Number of updates shows how many times SQL had to update the index due to data change.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass