I was reading up on a simple 2D game and that a sprite sheet holds all the sprites and that the game just reads the whole thing and renders only specific part of the sprite sheet based on the pixel position.

Well, you are warm. If the source code of the game is written correctly then a game engine ( game engine is layered between game source code and hardware) will handle the memory issues involved in your hardware. The whole sprite sheet should be held in memory somewhere and therefore ready at an instant under calls from the game source code. The game engine will do the actual lower level memory management and render the sprite to screen once a call is made from the game source code to do this.

Games in the 1980s thru 1990s relied on game source code to handle memory issues and most other areas of the game. The huge difference now is that most popular and profitable games usually have a game engine layer between the game source code and the hardware, whereas more primitive games decades ago relied totally or mostly on game source code. As game source code became larger and more complex, the need for a game engine to standardize lower level coding even across many games became a stronger demand. Since art assets often have a lot of lower level coding to handle them, a game engine to standardize the methods of game development in lower level coding will free the developer to spend more time on upper level coding such as gameplay functionality. Some developers will insist that this is an over-simplification, but I drew the principles this way for the sake of clarity in explaining the boundaries which might be there. Such boundaries can be a good thing because compartmentalization or modules of coding generally make everything better for the developer, especially a team, and avoid much of the "spaghetti" coding which is so very difficult to extend, integrate, and debug.

Clinton