August 16, 2016 at 4:37 am
Morning All,
Is there any performance gain in moving static data tables to read-only filegroups.
I heard somewhere (through a MS YouTube Exam prep) that you can save on Locking by marking filegroups as readonly.
One of my databases has plenty of look up tables, probably totalling 1GB in size, over about 30 tables.
Is it worth moving to a separate filegroup and marking as read only.
I am not seeing any locking problems as it stands right now, but just wondered.
Cheers,
Alex
August 16, 2016 at 6:19 am
You will see some performance gains, but they're not astronomical.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 16, 2016 at 6:49 am
When SQL Server constructs an execution plan, it takes a lot of things into consideration and it attempts to come up with a good balance. If it is known that the underlying tables of query cannot be written to, then perhaps this will influence the execution plan in some way that leads to better performance. Give it a try.
But really the first thing to look into is whether these lookup tables are properly indexed.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply