Storing in a global variable using inline assembly
The address you are looking for is a symbol known by the linker. Then
you can have the linker put the value of the symbol straight into your
code: no need to load from memory. In other words, you use the ldi
instruction to load the required address as an immediate value. You
should also reverse the order of the struct definition, since you are
storing SPL at the lowest address.

typedef struct { uint8_t sp_l, sp_h; } struct_t;    

struct_t arr[MAX_STRUCTS];

void some_inline_assembly_function()
{
  asm volatile(
    // Determine where to store the new SP_H and SP_L register
    "ldi r26, lo8(arr)     \n\t"  // X is loaded with the symbol arr
    "ldi r27, hi8(arr)     \n\t"  // which is the address of arr[0].sp_l
    // Here you may want to add 2 * an array index.

    // This part stores the stack pointer low and high byte indirectly
    // using the X registers (r26,27), which is what I want.
    "in    r0, __SP_L__    \n\t" 
    "st    x+, r0          \n\t" 
    "in    r0, __SP_H__    \n\t"
    "st    x+, r0          \n\t"
  ::);
}


Notice the lo8() and hi8() assembler macros: they split the address
into low and high bytes. Actually the assembler translates them into
R_AVR_LO8_LDI and R_AVR_HI8_LDI relocations records, and the
linker does the real job.

Edit: I think I just realized why you were thinking of using lds
in the first place...

Instead of using array indices, you may want to access the array through
a pointer (declared struct_t *pointer). When programming in C or C++,
a pointer identifier behaves, for most purposes, just like an array
identifier. However, at the assembly level, using a pointer involves an
extra indirection, as you have to read the contents of the pointer from
RAM. In this case you would indeed be using lds instructions:

; Copy the global 'pointer' into the X pointer register.
lds r26, pointer    ; low byte
lds r27, pointer+1  ; high byte


As a side note, if you are programming in C++, then you have to declare
extern "C" any identifier that you want to use both in C++ and in
assembly. Otherwise the C++ compiler will
mangle its name and you
would need to use the mangled name in the assembly source.