Tile mode advantages are:
edit: you could just say "tilemaps are smaller" to sum up these advantages, but...
tile reuse: a tile can be used multiple times in a tilemap, which saves VRAM
smaller patterns: tiles can be assigned different palettes to reduce their pixel size (with multiple palettes it's possible to do some pretty impressive images with only 4 colors per tile...)
faster screen updates: since a tilemap is much smaller than an equivalent bitmap, you can update the screen with much less CPU/DMA time than it would take for a similar-sized bitmap
larger virtual screens: also due to the smaller size of the tilemap, you can construct large virtual screens to scroll through; this also reduces the frequency with which you need to update the display.
edit: less loading time: blah blah smaller is good blah blah
These advantages don't really apply so much to things like FMV (except maybe low-color animation), fractal generation, or other types of dynamically-generated displays, but they're great for conventional backgrounds.
The main disadvantages are that it takes more work to make sure your graphics are suitable for a tiled display, and that there is no framebuffer, making framebuffer effects like blurring and similar distortions impossible without somehow generating a bitmap of the display and switching to bitmap mode (short of writing a VDP2 emulator, I suppose you could still accomplish this in some limited situations by taking a screenshot of your map editor...)
As for the exact use, you'll have to consult the SGL and/or VDP2 manuals and/or SGL/SBL samples since I don't have them handy, but basically how it works is that you have (alternate terms in parentheses since I don't recall which Sega uses):
pattern (aka tile, character) data: the actual pixels of the tile stored in VRAM, usually stored as references to a palette instead of raw RGB. Multiple palette sizes are supported by VDP2, and IIRC each background layer can have its own palette settings. I think different tile sizes are possible as well.
palette data (aka CRAM, CLUT): contains the actual RGB color entries for each palette. VDP2 has a dedicated color RAM to which these must be written.
tilemap (aka name table, attribute table): a table that describes the tiles that make up the displayed image, along with which palette they use, whether they are flipped along either axis, and their priority (i.e. whether they appear above/below sprites or other backgrounds). This replaces the framebuffer; that is you build the image with tiles instead of individual pixels.
So when creating the display graphics, you split them up into tiles of your chosen tile size, then use a map editor (a generic one such as
OpentUME, or a custom-made level editor for a game) to assemble the tiles into the desired display. The map editor generates map data files that contain the tilemaps. This is more work than just saving a bitmap, but one advantage is that the same editor can be used for "effect" tiles, i.e. an invisible (to the player) layer of virtual tiles that tell the game engine what a tile does. This can be virtually any effect you can write the code for, from simple solid/empty (which of course can be creatively combined to create invisible barriers, fake walls, and other oddities
😉) to low gravity, damage, instant death, and so on.
In terms of code, you need to transfer the pattern and tilemap data to VRAM, put the palette into CRAM, and then set up the tilemap mode with the appropriate VRAM addresses. I can't give you the details of this since my computer's stuffed in a box somewhere. Anyway, If I've done a bad job of explaining the basic concept, I'd suggest browsing some SNES graphics tutorials... hopefully someone who has a more sizable clue about the details (Takashi?) could break it down further if needed.
HTH.