May 6, 2002 at 5:33 am
we have a database with one single datafile and logfile. Currently we are in a process of fine tuning the performance of SQL. Before doing this I just tried a small test on one of our servers...
Created a database and a table with 6 fields with an index. The table and index were on 2 datafiles. Now I had an another database on the same server with both table and index on single datafile. I inserted 50000 rows on both databases...The table with index on different file was comparitively slow alomost one second. and update with 8 conditions resulted the same result...how exactly the tables and indexes should be arranged and what are the other configuration that I should make to know that indeed there is a performance gain..
levi
Jesus My Saviour
May 6, 2002 at 6:23 am
I try to stick with a single mdf/ldf unless I run into space limitations - just easier to administer. Testing filegroup performance is about like another test - come up with a standard test, run it enough times to get a reliable number, change one thing, try it again.
Andy
May 6, 2002 at 8:01 am
Having tables and indexes in different filegroups doesn't help you a whole lot unless they are on different physical drive sets. Is this the case?
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
May 6, 2002 at 9:57 am
From my experience, a table and its indexes should be on a single filegroup with multiple files. Heavily used tables should be on different filegroups and possibly on different physical drives if the demands for the tables are great enough.
May 6, 2002 at 7:08 pm
We do this on a few servers, started out as a test and found it gives an improvement on reads more so than writes. You should with proper planning of the physical files sizes be able to get the speed to equalize with the single file group file. The biggest problem to overcome is to make sure the physical file does not have to grow and when you have two files growin gon the same drive it does slow the growth and thus the inserts a bit. Not sure what other factors with doing this on the same drive as we never tested to a major level. Test reads based on the indexes is were your looking for performance gains with this method. Also we only have done this with fiels over 2GB as smaller fiels showed no improvements.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 7, 2002 at 4:11 am
I just went through and found an article by Andy about the MS Sqlserver 2000 Performance Tuning book... I bought the book today and hope this should help me in many ways....
levi
Jesus My Saviour
May 7, 2002 at 6:18 am
I think you'll find the book helpful. Unfortuneately it always comes down to having to test your theories, even if based on the book!
Antares, I find the info about the 2g file pretty interesting. Speculation on why?
Andy
May 7, 2002 at 6:52 am
Like I said in previous postings we generally only move the data into filegroups for specific reasons. 1 is that the data is static in some tables and not in others. Be setting the static files in a physical group (and this does help with these some no mater the size) the file has higher access speeds due to the fact it can remain contiguous on the Hard Drive. Less file fragmentation does help speed things up. My theory is that the index suffers on reads when the file is not longer contiguos even if the index itself is not fragmented internally to the file itself. I also further believe that the pointers inside the file to the data does not neccessarily mean that the index is all stored relative to the other items. It seems thou and most likely based on the system specs that accessing the internal data to a file did not bennefit measureably on files under 2GB (although I may still do this on smaller files). We have one large database I decided that I tested on that has a single table with 9GB of data and 1GB of Index space. Whe I first began I was looking at a way to speed access to the indexes and started looking at filegroups. What I decided to do as a test was seperate the table into 1 filegroup and it's indexes into their own filegroups. What I found was that a query that was taking a minute or more was running now in at 50 secs or better. I surmised then that the files fragmentation (both internal and external) was affecting performance. So then we went to our next databases which were 4, 3.5, 3.2, and 2.6 GB in size. All showed a similar but not near as great improvement as the first. Now for us there are inserts but these transactions are fewer than all our other databases and we were more worried with read than write access as these databases collect several sources for reporting. We tried this with smaller databases but the performance increase was just not there. We did find things we ha to consider thou.
1) Filegrowth, grow the file so fragementation on the physical drive is limited.
2) Reads versus writes on the same drive, higher write DBs can suffer if more than one file has to grow as opposed to a single file.
3) Write versus static. Long term staticts updates and fragmentation seem to remain limited for these tables if in their own fielgroup.
One other things we did to improve access on a single drive are to run a defragmenetation utility one and a while which helps regular DBs as well.
For the future I want to get a defragmentor than will allow me to choose the order of the files to see if that has any bennefit.
Again the 2GB just seems relative to our systems hardware and abilities more than an actual guideline. Have not tried this on my local machine to see what would happen under testing and may see if I can pinpoint something specific in the future.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 7, 2002 at 9:00 am
It's non clustered indexes that if create on different disk gets benifitted(conditional: if the data required to read or write (disk I/O) is not enough for the disk.)
Clustered index should be created on the same filegroup.
Cheers,
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
May 7, 2002 at 10:48 am
Clustered indexes cannot be created elsewhere in relation to the table as this sorts the table and the leaf pages are stored with the table. Sorry I should have been more specific yes. I just assumed...and you know what they say.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 7, 2002 at 11:01 am
If you get time to investigate more would make a great article (or two).
Andy
May 7, 2002 at 4:07 pm
Antares has some good points. Another item I had read was each filegroup uses a separate thread, so this can also speed up filegroups, but probably only if you have different physical drives.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply