Descending indexes are really helpful if we want order by on more than one column and at least two columns are required in opposite direction in order by.  If all columns are required in same direction, index can be scanned in backward direction even if the index is created in ascending order and data required in descending order. The only cache here is backward scans can’t use parallelism.

For more details on backward scan, please refer this article of Itzik Ben-Gan.

But, the real problem with descending indexes is they tend to become fragmented quickly as generally the order of inserting data in OLTP environment is in ascending order.   I have tried to reproduce the same with below example.

--Script to generate test data 
use tempdb 
go 
if Object_id('dbo.studentAttendance') is not null 
  drop table dbo.studentAttendance 
go 
create table dbo.studentAttendance 
  ( 
     attendanceID   bigint identity(1, 1), 
     studentID      int, 
     attendanceDate date, 
     isPresent      bit, 
     constraint pk_studentattendance primary key clustered(attendanceID) 
  ) 
go 

if Object_id('dbo.students') is not null 
  drop table dbo.students 
go 
create table dbo.students 
  ( 
     studentID int, 
     name      nvarchar(100), 
     constraint pk_students primary key clustered(studentID) 
  ); 
with l0 as (select 1 as C union all select 1), 
     l1 as (select 1 as C from l0 as A, l0 as B), 
     l2 as (select 1 as C from l1 as A, l1 as B), 
     l3 as (select 1 as C from l2 as A, l2 as B), 
     num as (select Row_number() over(order by C) as N from l3) 

insert into students 
            (studentID, 
             name) 
select N as StudentID, 
       N'Student' + Cast(N as nvarchar(100)) 
from   num 
where  N <= 100  

go

First we create index in ascending order and populates data in sequence as it generally happens in real life scenario.

use tempdb
go
create index ix_studentattendance_studentid_attendancedate on dbo.studentattendance(attendanceDate asc, studentID asc) 
go 

set nocount on 
begin tran 
declare @i int 
select @i = -365 
while ( @i < 0 ) 
  begin 
      insert into dbo.studentAttendance 
                  (studentID, 
                   attendanceDate, 
                   isPresent) 
      select studentID, 
             Dateadd(dd, @i, Sysdatetime()), 
             ( studentID%2 ) 
      from   dbo.students 

      select @i = @i + 1 
  end 
commit

Now, if we check fragmentation of index we created using below results

Query used to check fragmentation

select Object_name(ps.OBJECT_ID) as TableName, 
       i.name                    as IndexName, 
       avg_fragmentation_in_percent, 
       fragment_count, 
       page_count 
from   sys.Dm_db_index_physical_stats(Db_id(), Object_id('studentAttendance'), null, null, 'LIMITED') as ps 
inner join sys.indexes as i on ps.[object_id] = i.[object_id] and ps.index_id = i.index_id  

Now, we drop index, truncate the table and create index again using one of the keys in descending order

use tempdb
go
drop index ix_studentattendance_studentid_attendancedate on dbo.studentattendance 
go 
truncate table dbo.studentattendance 
go 
create index ix_studentattendance_studentid_attendancedate on dbo.studentattendance(attendanceDate desc, studentID asc) 
go 

set nocount on 
begin tran 
declare @i int 
select @i = -365 
while ( @i < 0 ) 
  begin 
      insert into dbo.studentAttendance 
                  (studentID, 
                   attendanceDate, 
                   isPresent) 
      select studentID, 
             Dateadd(dd, @i, Sysdatetime()), 
             ( studentID%2 ) 
      from   dbo.students 

      select @i = @i + 1 
  end 
commit  
go

If we check the fragmentation now, we can see the fragmentation is really high for the nonclustered  index

With help of below query, we can find out all descending indexes in database

select object_name(si.object_id) as TableName
, si.name as IndexName
, sc.name as DescColumnName
from sys.indexes si
inner join sys.index_columns sic on sic.object_id = si.object_id and sic.index_id = si.index_id
inner join sys.columns sc on sc.object_id = sic.object_id and sc.column_id = sic.column_id
where sic.is_descending_key = 1

If you use sp_help, descending indexes will be shown with (-) after column name

Summary:-

Descending index can cause the fragmentation for normal OLTP operations , so we should examine the each descending indexes  and check if its really used in query. On the contrary, if you have index on any decreasing value column, creating descending index  helps in controlling fragmentation.