Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
eversm
Starting Member
8 Posts |
 Posted - 2003-04-16 : 10:32:49
|
| I have a EMC clarion disk storage unit and I wanted to boost DB performance by locating data into filegoups that would span over seperate drives on the clarion. I also have a table that has approximately 40 GB of data and the indices are about 48GB. The first step I took was to move the indices for this huge table to a new filegroup that used a different set of drives on the clarion. My thought here would be that there would be less contention for the drives and cause the DB to scream. Early feedback is that inquiries to the table is a bit sluggish compared to the past. WHY??????? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-16 : 12:09:49
|
| Well you need to run the Performance Monitor to determine what the problem is. You should take a look at the various disk counters.Tara |
 |
|
|
eversm
Starting Member
8 Posts |
Posted - 2003-04-17 : 15:18:38
|
| I believe the essence of my inquiry is this: Does moving data in this fashion truely improve query performance? Is it better to split both data and indices to the same filegroup? Or if I continue on this approach, can I create additional filegroups for indices and data of my largest data tables? I have two tables that make up over 75% of my entire 130GB database, including indices. From your experience, is this the way to go about this?Thanks so much for your input!Mike |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-17 : 16:32:21
|
You have 2 tables thare about 97 gb?Now that's a database! What are you storing in there? Web hits?Anyway, how many physical drives do you have?Personally I'd get moreI would also look into partitioned views, placing each partition on a separate physical drive. I wonder if the index for each partition should reside in it's own filegroup on a drive different than the data, or on the same drive would be better.Nigel would be the one to ask I bet.Brett8-)Edited by - x002548 on 04/21/2003 10:42:09 |
|
|
|
eversm
Starting Member
8 Posts |
Posted - 2003-04-17 : 17:47:04
|
| We process about 1/4 million invoice records monthly and this generates about 1/2 million general ledger transactions. I have the primary data stripped over 5 physical drives and now, the indices for the general ledger table in a fiilegroup that is again spread over a different set of physical drives. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-04-17 : 17:58:15
|
| Are the two sets of physical drives on separate SCSI or Fibre Channel Channels?If not, you'll not gain anything by putting them on different disks becuase they have to come across the same "pipe" basically.You'll probably want to gotohttp://www.sql-server-performance.com and look up their bottleneck and benchmarking articles.They tell you how to find bottlenecks and what performance counters to look at.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
eversm
Starting Member
8 Posts |
Posted - 2003-04-21 : 10:12:44
|
| I found some very useful information on the performance link, thanks! I still have not found an answer to my question about whether or not to put both indecies and data into the same filegroup or seperate filegroups, would produce better performance.I just believe that with our clarion SAN, six way database server and 4 Gb of memory, this DB should scream! I'm also beginning to look at data purges to move historical data out of the huge tables. I'm interested in anyones recommendations for using the OLAP features of SQL 2000.I want to thank all of you for your support! I've found your input very valuable!Mike |
|
|
|
|
|
|
|
|
Filegroups