Chief Executive of Kensington and Chelsea council, Nicholas Holgate has resigned over its response to the Grenfell Tower tragedy.