July 18, 2011 at 2:44 am
We have software that uses a SQL Server 2008 Virtual Machine. We have full access to the SQL Server VM but NOT the underlying VM host. The host and the disk infrastructure is managed by BT (who outsource this instance to another thrid party). So I cannot make changes to the anything from the host upwards.
The VM has a C: drive with enough space for OS and program installations and an E: drive with plenty of space for the databases and growth. I am also told that the underlying disks of V Host are RAID 10 with about 6 or 8 disks.
The application we have installed (3rd party) is setup within one filegroup and we experience slow down on certain tables that contain around 30 million rows of data. Indexing this table actually creates a total index size larger that the data itself.
I've never used filegroups before despite using SQL now for about 4 years on/off but am more of an accidental DBA then a seasoned professional so please go easy with me.
With no access to the underlying infrastructure I wondered...
- Would a second/third filegroup on the E: drive help. If I moved clustered indexes to the secondard and other indexes to the third. I suspect because of the underlying infrastructure I wouldn't see any difference???
- I have access to an iSCSI NAS over the LAN (Gigabit), if I put the third filegroup on that would there be any performance increasse, also if for some reason the iSCSI disconnected and all the non clustered indexes were missing, how would SQL behave?
- If none of the above helps can filegroups be used to partition the large tables so that only the last months worth of data is in the main filegroup? Some of the applications reporting functionality uses this table so I can't remove the historical data from the tables but for the most part the table is mainly written to and only the last months worth of data is accessed frequently.
Any advice welcome
Thanks in advance
Mark
July 18, 2011 at 2:52 am
With no access to the underlying infrastructure I wondered...
- Would a second/third filegroup on the E: drive help. If I moved clustered indexes to the secondard and other indexes to the third. I suspect because of the underlying infrastructure I wouldn't see any difference???
- I have access to an iSCSI NAS over the LAN (Gigabit), if I put the third filegroup on that would there be any performance increasse, also if for some reason the iSCSI disconnected and all the non clustered indexes were missing, how would SQL behave?
- If none of the above helps can filegroups be used to partition the large tables so that only the last months worth of data is in the main filegroup? Some of the applications reporting functionality uses this table so I can't remove the historical data from the tables but for the most part the table is mainly written to and only the last months worth of data is accessed frequently.
Any advice welcome
Thanks in advance
Mark
File groups have no impact on the actual disk setup , they are a logical partitioning of the database files.
Having files located on Networked storage is not going to help performance , in addition to normal I/O latency you would also have to contend with Network latency. Unless you plan to have a clustered setup with SAN.
Partitioning the huge table into different files as part of the same files group or different filegroups will help ease the performance issue , if the historical data is needed for only query purposes. I.e it needs to be part of a read only file group with aligned indexes.
July 18, 2011 at 3:23 am
Peawet (7/18/2011)
- I have access to an iSCSI NAS over the LAN (Gigabit), if I put the third filegroup on that would there be any performance increasse, also if for some reason the iSCSI disconnected and all the non clustered indexes were missing, how would SQL behave?
Likely there will be a performance decrease, and if the filegroup disappears, any query that needs the data will throw a severity 24 error and disconnect. A restart of the database would be needed to persuade SQL that the file is back.
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
July 18, 2011 at 3:50 am
OK so using iSCSI isn't going to help. Nothing I hadn't expected there.
Does a readonly filegroup improve performance?
Mark
July 18, 2011 at 3:57 am
Peawet (7/18/2011)
Does a readonly filegroup improve performance?
As opposed to what? A normal filegroup. Can do, just depends on how it's used and where it's put. Main point with a read-only filegroup is that SQL takes no locks. However if it was read-write with no writes the locks would be taken but wouldn't ever be blocked.
Typically to get performance improvements from multiple filegroups they need to be on separate physical IO channels and the bottleneck needs to be IO (not CPU, not locking)
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
July 19, 2011 at 11:51 pm
Just on the filegroup side of things.
If you have a single LUN sitting behind e:\ then filegroups will not give you more throughput, except!
If you pump vast amounts of data into your DB and you run into contention on PFS and Lazywriters.
If you add a filegroup and I assume you'd hook up a file to it. You get the benefit of more IO threads.
This is only really needed if you push large amounts of IO down your sql's throat.
The other benefit of a filegroup is that if you create an index on it it will be less fragmentated then dumping everything in the same file.
Like the others are saying it is a logical partition for the same DB but there are a few good things that comes with more files.
Depending on your system it might not be needed though.
Cheers
Jannie
July 20, 2011 at 1:02 am
Jannie-186227 (7/19/2011)
If you add a filegroup and I assume you'd hook up a file to it. You get the benefit of more IO threads.
SQL uses all threads to write to all data files, so adding files won't get you more threads.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply