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 |
|
catparks
Starting Member
18 Posts |
 Posted - 2004-08-23 : 11:13:53
|
I am experiencing some performance problems on a SQL database, and am looking into filegroups as a possible solution. (1 part of the solution.) I came across a statement that read: "It is not possible to move files to a different filegroup after the files have been added to the database". If this is true, it blows my plan because I want to move existing tables to a new filegroup. I also read that filegroups are not the only answer to performance problems. I included more detailed information below to describe the other steps we are taking. We have not implemented the new hardware. I am also log shipping the database, in case that has an effect on any suggestions. Suggestions/comments/opinions are appreciated. =============================We are using Microsoft's SQL 2000 Server SP3a to house the back-end database. This server resides on a Dell PowerEdge 6650 running Microsoft Windows 2000 Advanced Server with SP3 with the following specs: Quad Intel Xeon 1.6GHz CPUs4 GB of RAMRAID 1+0 Array (130 GB)Utilizing this hardware platform we are encountering degraded performance levels quite frequently. Using the performance monitoring tool in Microsoft Windows 2000 we were able to identify the loads being put on the system.% Processor Time (Processor)This counter measures the amount of available time one or more processors spends on a process. While our server maintains an overall average of around 60%, it quite frequently spikes above 75%.Available MBytes (Memory)This counter measures the amount of unused physical memory. This number fluctuates between 200MB and 300MB on the server.% Idle Time (PhysicalDisk)This performance monitor counter is used the measure the amount of time where the physical disks are not being written or read from. Our server quite frequently is at 0 for this counter.Avg. Disk Read Queue Length (PhysicalDisk)This counter identifies if the physical disks are backlogged with requests for data reads. This number appears to be the worst of all counters measured on the server.The disk I/O issues plaguing our database server appear to be the root of our problem. To resolve this we purchased a fiber channel SAN using a Dell/EMC CX300 SAN array and a McData 8 port fiber channel switch. Along with a fiber channel adapter for the server, this can replace the slower RAID technology currently in use.With the improved storage capabilities we feel the need to also maximize our processing power on the database server. To accomplish this we purchased a Dell PowerEdge 7250 with four 1.5GHz Itanium 2 processors and 16 GB of memory. We will run this new platform with a Windows 2003 Enterprise Server (64 bit) operating system and Microsoft SQL 2000 Server (64 bit) Enterprise Edition for the database. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-23 : 12:25:27
|
| If you want to move a table to a different filegroup, you have to do it manually. You'll need to write some scripts that create the table on the other filegroup but with a temporary name, then move the data over to this new table, move constraints and indexes over too, then drop the source table, then rename the temporary table to the source table's name.Tara |
 |
|
|
|
|
|
SQL Filegroups