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 |
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
 Posted - 2005-11-02 : 23:28:04
|
| I have enherited an extremely busy system, by far the busiest SQL Server box I have ever worked on. Currently the system uses a single filegroup and I was wondering what the opinion was these days of using multiple filegroups in an effort to improve performance. The system is now getting to a size where standard maintenance jobs interfere with the application (blocking) to a degree that we really cannot run them at all.If you have any success or horror stories with using multiple SQL Server filegroups I'd like to hear them.Any whitepapers or other information on managing a high volume, large (not quite VLDB) database would also be welcome.a little more info on the setup:1. Pretty high end HP DL 580 box w/4 CPUs, 3+ ghz chips.2. Hyperthreading is turned on, however parallelism is limited to 4 CPU only3. 8GB RAM in the box, AWE/PAE enabled, SQL Server gets 6GB for buffer cache. Target and Total memory is the same value - doesn't appear to be starved for memory. Buffer cache hit ratio is 99.5% and up. We are not swapping out at all.4. The application we are running requires that we have a locally installed SQL Server database. We cannot get around that unfortunately. The application is occasionally very CPU hungry, but the box seems to handle this ok.5. Disk is a mid-range SAN from EMC. HBAs are 2GB variety, disks are fast. We only occasionally have disk queuing issues, but never on the drives associated with SQL Server.6. We have several LUNs carved out in the SAN that are presented to the SQL Box. All LUNs are RAID 10, I am not sure about how many spindles per LUN, but we aren't having bad disk queueing problems so I am not too worried about this.7. Win2k3 enterprise edition, SQL SErver 2000 EE Sp3a w/818 hotfix.-ec |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-03 : 08:26:01
|
"The application we are running requires that we have a locally installed SQL Server database"Can I get a ticket to the public execution of the Vendor please?  Sorry, no useful advise I can offer on multiple SQL Server filegroups, but I am interested in the outcome.Kristen |
 |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-11-03 : 13:01:52
|
From the description, it looks like multiple filegroups will not help you. The clincher is this:quote:
5. Disk is a mid-range SAN from EMC. HBAs are 2GB variety, disks are fast. We only occasionally have disk queuing issues, but never on the drives associated with SQL Server.
Also, you have a 99.5% buffer cache hit ratio.In SQL Profiler, are you seeing many table scan events? Or in perfmon, does the table scans/sec counter seem "high"? I put that in quotes, since I am not sure if there can be a "good" value for table scans, especially if you have large numbers of small code tables that have 100 or less rows. Those should be scanned every time. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-03 : 14:55:58
|
Filegroups would help you if you were having queueing issues, which you have stated repeatedly that you are not. It does not help with concurrent processing. One of my servers is that exact same server with 32GB of RAM hooked up to an EMC CX700.  The DL580 is a great, dependable little server. Here are some questions for you that will help a little:1. What is your standard cache hit ratio. Give the total category and the categories for each subgroup?2. Are any of your counters messed up during the daily maintenance process?3. Is the nightly maintenance process the only time you have issues?4. What are you doing for your nightly maintenance process? What is the schedule?I'm assuming you have already used profiler to identify your top long running, heavy read, and heavy write queuries and looked for indexing issues. Is this correct?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-11-04 : 14:15:10
|
| Are you seeing any GAM/SGAM contention (see fn_virtualfilestats)? You might benefit from multiple files in the default filegroup. Multiple FileGroups might be a solution if you see this type of contention, but multiple files usually gets the job done, too.Jon-Like a kidney stone, this too shall pass. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-11-04 : 18:19:06
|
quote:
Originally posted by derrickleggettHere are some questions for you that will help a little:1. What is your standard cache hit ratio. Give the total category and the categories for each subgroup?2. Are any of your counters messed up during the daily maintenance process?3. Is the nightly maintenance process the only time you have issues?4. What are you doing for your nightly maintenance process? What is the schedule?
Thanks for the reply Derrick. Here are the answers to your questions:1. Here is the breakdown from SQLServer:Cache Manager Cache Hit RatioTotal: 97.311Adhoc: 99.995Cursors: 97.408Execution Contexts: 97.156Misc. Normalized Trees: 71.623Prepared SQL Plans: 99.162Procedure Plans: 64.519Repl Plans: 0.00Trigger Plans: 99.9732. I'll have to get a recent trace of the data during our maint window to answer this question accurately. 3. We have an ongoing performance problem it seems. Anytime any maintenence occurs (even tlog backups, or automated collection of stats) the system has some issues. The problems don't just occur during the maint window though.4. Pretty standard schedule now that I have my hands on the system. We do tlog backups every 15min. We do full backups every other day at midnight. We do differential backups every day at noon. We also take a differential backup at midnight on the days when we skip the fulls. Over the weekend we collect stats (I might make this a daily job, but for now it seems ok), we also run an optimization job on the weekends. The weekend jobs are ok, it is the stuff we have to do daily that is hurting us right now. One of the problems I am looking to remedy is the inconsistent full backups. I didn't mention this earlier, but sometimes they run for 4 or 5 hours and sometimes for 30 to 40 hours. I have to get this more consistent so I can schedule around it. We will be implementing SQL litespeed to reduce the backup window and backup file footprint. EDIT:I should also note that this particular application ran fine when it was a little smaller than it currently is. Now that the size is approaching 450GB it is a little tempermental. That is why I am looking at the little things that can be done to squeeze out more performance. The filegroups idea is just one of the avenues that I am investigating.-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-11-04 : 18:24:15
|
quote:
Originally posted by bakerjon
Are you seeing any GAM/SGAM contention (see fn_virtualfilestats)? You might benefit from multiple files in the default filegroup. Multiple FileGroups might be a solution if you see this type of contention, but multiple files usually gets the job done, too.
Here is the fn_virtualfilestats output--Data fileSELECT * FROM :: fn_virtualfilestats(8, 1)GO--Log fileSELECT * FROM :: fn_virtualfilestats(8, 2)GODbId FileId TimeStamp NumberReads NumberWrites BytesRead BytesWritten IoStallMS ------ ------ ----------- -------------------- -------------------- -------------------- -------------------- -------------------- 8 1 442403937 30427913 23098609 1839738658816 263972274176 179110019(1 row(s) affected)DbId FileId TimeStamp NumberReads NumberWrites BytesRead BytesWritten IoStallMS ------ ------ ----------- -------------------- -------------------- -------------------- -------------------- -------------------- 8 2 442403953 245368 7816427 151036684288 128513612288 8786(1 row(s) affected) I am not familiar with how to decipher fn_virtualfilestats output so I'll have to do some reading up on it. I'm guessing that you want to take a look at the delta between to executions of this query though so this one bit of data probably isn't that helpful. btw, the system was bounced on wednesday night (11/2).-ec |
|
|
|
|
|
|
|
|
Advice on using multiple filegroups