April 6, 2017 at 7:23 am
it's me again...
another test, I would like to go deeper on this issue
same database, but with 13K + filegroups/datafile
now it's running and stuck in "fixups" phase.
check session with adam mechanic's who_is_active
(http://sqlblog.com/blogs/adam_machanic/archive/2011/04/06/who-is-active-options-a-month-of-monitoring-part-6-of-30.aspx)
with
@get_outer_command = 1,
@get_task_info =2,
@get_locks = 1,
@get_additional_info=1
found this in locks info:
<Database name="master">
<Objects>
<Object name="sysbrickfiles" schema_name="sys">
<Locks>
<Lock resource_type="KEY" index_name="clst" request_mode="X" request_status="GRANT" request_count="9823" />
<Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
</Locks>
</Object>
</Objects>
</Database>
I suppose that request_count coincides with fg/df number (it increases with each refresh).
Try to select sysbrickfiles using DAC connection i got a lock, as aspected.
the first thing I think about is "lock escalation", this can explain why with 4K+ database fg this phase is really fast (seconds) but if u overtaking the limit of 5K+ u go in lock escalation.
I know it's not a good idea, but I could disable lock escalation for all sys base tables... 😎
what do you think about?
April 6, 2017 at 9:00 am
I still think the most likely culprit is that you have 13K files to write to. How many physical disks are you writing to? I'd be willing to bet the biggest issue is going to be Disk I/O with that many files.
If you don't have 13K different disks you are writing to, you will have a lot of locking occuring at the disk level. The disk can only handle so many writes per second and if you have 1 process writing to disk (for example with a restore of a single file per disk), it can write things quickly. But as soon as you tell it you want to write to more than 1 location at the same time, the OS tries to write to both locations at once on the same disk. If this is a spinning platter disk, it will get written to the buffer (which is usually quite small... I believe most nomral desktops have 8 MB buffers) and when that is full it will get put to disk or when the head is at a good writing position it will write to disk. Now you start trying to write to 13,000 different locations on the disk at once, once that buffer gets full, your processes that want to write to disk need to wait for the read/write head to dump the buffer onto the disk before it can accept another request. And if that buffer has it jumping all over the place to do a write, you will have slow writes.
SSD's are similar except that they don't need to move a read/write head around. They still can only handle so many requests per second and a sequential write is going to be a LOT faster than random writing.
I would expect that disk IO is going to be a lot bigger culprit than any table locks you get.
But as for the table locks, I would expect those as you have 13,000 insert/update commands going against that table. I could be mistaken, but I think that the X lock (exclusive lock) would not be due to lock escalation, but would be related to the insert into that table.
I would seriously look into the 13,000 data files/file groups and get that number down to something more reasonable/manageable. If reducing that number is not realistic, I'd try to get sign off from the higher ups for the acceptable downtime as I doubt you are going to get a fast restore when doing that many data files/file groups.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 6, 2017 at 9:10 am
That seems to be a totally bad idea. The issue is you have an unreasonable number of files and going to 13K is still unreasonable. Seems like it would make more sense to figure out better ways to work on multitenant issues and whatever anyone was trying to accomplish with all those files. I think you said you inherited this but you certainly have something concrete to present to management on why this setup is bad since the recovery time is ridiculous and affects everyone using that system. Are they willing to let everyone know that? And make sure you have those discussions in email.
But I am curious - which fixup portion? There are many and the last one was a fixup portion. It's all really just due to having too many files.
Sue
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply