March 19, 2008 at 12:09 pm
SQL SERVER 2005
Hello,
I would like to set each cpu available per each file group.
How can I do?
Thank
March 19, 2008 at 1:28 pm
Can you please elaborate the question in detail? I didn't get it?
SQL DBA.
March 19, 2008 at 3:14 pm
Hello,
thank to reply,
It was asked for me to create a database following some "best practice",
one of these was to set the number of files to match the number of CPUs that are configured for the instance.
But I need more help:
it means:
in the case of a server with 4 CPU (or cores), can I to set up a database with 4 files for filegroup to increase performance?
Is it right?
Another question is: can I mapping a CPU with a database file? eg CP1 with file2, CPU2 with file 4 and so on?
More explanation is welcome.
March 19, 2008 at 4:54 pm
net (3/19/2008)
Hello,thank to reply,
It was asked for me to create a database following some "best practice",
one of these was to set the number of files to match the number of CPUs that are configured for the instance.
But I need more help:
it means:
in the case of a server with 4 CPU (or cores), can I to set up a database with 4 files for filegroup to increase performance?
Is it right?
Another question is: can I mapping a CPU with a database file? eg CP1 with file2, CPU2 with file 4 and so on?
More explanation is welcome.
To my knowledge, the only scenario where defining as many data files as CPUs is advisable is for tempdb. That is because of the proportional fill algorithm used to populate the tempdb data files.
Not sure if it makes sense to do the same for any other kind of database.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 20, 2008 at 12:21 am
That best practice is for TempDB only, and it's only really necessary if you have high volumes of temp table creation and/or are seeing blocking on the allocation structures in TempDB.
The idea is that you create one file (not filegroup) per CPU (although fewer files do work also, eg 8 files for 16 CPUs), all in the primary filegroup of TempDB. They need to all be the same size, and autogrow should be disabled.
It's not recomended for any DB other than TempDB.
For other databases your decision to split into multiple files/filegroups should be based on IO performance and backup/recovery strategies.
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
March 20, 2008 at 5:04 am
If you are doing this in SQL 2000 you need to set the trace flag 1118 so the different tempdb files are used evenly but in SQL 2005 you don't need to bother with this.
Microsoft best practice would be to have each of the tempdb files on seperate drives as well but this is impossible in most circumstances unless you company has bottomless pockets or a good SAN!
Buxton69
March 20, 2008 at 8:14 am
Buxton69 (3/20/2008)
If you are doing this in SQL 2000 you need to set the trace flag 1118 so the different tempdb files are used evenly but in SQL 2005 you don't need to bother with this.Microsoft best practice would be to have each of the tempdb files on seperate drives as well but this is impossible in most circumstances unless you company has bottomless pockets or a good SAN!
Buxton69
I didn't know about trace flag 1118 in SQL 2000! I have never used it!
I was under the impression that tempdb data files would be filled evenly as long as they are each set to the same size and autogrowth is turned off. Are you sure 1118 is needed on top of that?
From the little I have read on the web it seems to do mostly with contention on allocation pages, but is not related to the proportional-fill process.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 20, 2008 at 8:34 am
I'm sure it was something I read somewhere that you had to do this as well, maybe on this forum, but can't find an article specifically saying that you need to do this to ensure transactions are distributed evenly amongst the seperate files, in that case I withdraw my comment, I'll check my facts next time!
March 20, 2008 at 9:02 am
No worries, it wasn't meant like that... 🙂
I was concerned that maybe I had missed something all this time.
This flag is good to know, something new I learned this morning.
Thanks!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 20, 2008 at 9:32 am
I was more worried that I couldn't find where I had read it but I can't find it on any microsoft KBs so I assume that where ever I read it it was wrong which is also good to know as I had assumed that I needed to put the flag on.
🙂
March 20, 2008 at 9:46 am
I googled "trace flag 1118" and this was at the top of the list:
PRB: Concurrency enhancements for the tempdb database
support.microsoft.com/kb/328551
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 21, 2008 at 3:39 am
Hello all,
thank for your contribution.
March 25, 2008 at 4:10 am
Marios Philippopoulos (3/20/2008)
I googled "trace flag 1118" and this was at the top of the list:PRB: Concurrency enhancements for the tempdb database
support.microsoft.com/kb/328551
But none relating to the proportional-fill process.
March 25, 2008 at 4:28 am
Going back to the OP, it is not possible to dedicate a given CPU to a given file or file group. In a multi-CPU or multi-core environment, there are advantages that can sometimes be gained in having the database split in up to the same number of files as there are cores.
For large databases (over 0.5 TB), you can get worthwhile performance improvements by splitting your user databases into multiple file groups. There is a lot of advice from Microsoft about the scenarios where this can help. For smaller systems the benefit of splitting things is mostly not worth the time taken to do it.
The main exception is tempdb. If you have high concurrent use of tempdb, it can be worth splitting this across multiple files in the same filegroup.
However, if your I-O processor cannot handle the extra work that SQL will give it after you have split your database, you may even find performance gets worse.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 25, 2008 at 4:39 am
Buxton69 (3/25/2008)
Marios Philippopoulos (3/20/2008)
I googled "trace flag 1118" and this was at the top of the list:PRB: Concurrency enhancements for the tempdb database
support.microsoft.com/kb/328551
But none relating to the proportional-fill process.
From what I've read, Traceflag 1118 prevents single-page allocations to tables. Means that all tables will be at least 1 extent in size (8 pages or 64kb). With it on, SQL will never create mixed extents and thus any concurency issues around the SGAM will be removed.
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 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply