Large table size, affecting usage I have the following database table, that saves all outgoing emails to students:

    CREATE TABLE [dbo].[tblEmailsSent](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [Sent] [datetime] NULL,
        [SentByUser] [nvarchar](50) NULL,
        [ToEmail] [nvarchar](150) NULL,
        [StudentID] [int] NULL,
        [SubjectLine] [nvarchar](200) NULL,
        [MessageContent] [ntext] NULL,
        [ReadStatus] [bit] NULL,
        [Folder] [nvarchar](50) NULL,
     CONSTRAINT [PK_tblMessages] PRIMARY KEY CLUSTERED 
    (
        [id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
     IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO        


The table currently stores 18,700 records. 

I have a number of issues, which I guess might be related: 


The table, for some reason, weighs 460 MB - which means I am pretty much hitting the limit of the hosting package. I wouldn't have thought that 18,700 rows would weigh so much. Is there something that can be done about this?
When I load the top X rows from the table, the response is VERY slow. We are talking about a run time of a little more than 2 minutes (in SSMS), for a simple query that looks like this:

SELECT top 500  * 
FROM tblEmailsSent
ORDER BY id desc

The following query gives zero records, when I know for a fact (ie, i can see in the table,) that emails were sent during the day of the 18th:

 SELECT id FROM tblEmailsSent
 WHERE  convert(datetime,[sent],103) = convert(datetime,'18/11/2016',103)



I am using Microsoft SQL Server 2005 - 9.00.5000.00.

I am using ntext because i am using Unicode (for Hebrew support).

SET STATISTICS TIME ON for query 2 returns:


(500 row(s) affected). 

SQL Server Execution Times:
    CPU time = 141 ms,  elapsed time = 112347 ms.


A SELECT AVG(DATALENGTH(MessageContent)) query returns 23,363.



I changed the MessageContent column to nvarchar(MAX), and ran query 2 again:


SQL Server Execution Times:
    CPU time = 47 ms,  elapsed time = 116726 ms.


...only a few seconds different from before.

The server is remote. It generally responds fairly quickly, but these results for such a simple query are really very very slow.

Using SET STATISTICS IO ON returns:


Table 'tblEmailsSent'.
 Scan count 1, logical reads 17, physical reads 0, read-ahead reads 354, 
 lob logical reads 3308, lob physical reads 660, lob read-ahead reads 0.
SQL Server Execution Times:
 CPU time = 62 ms,  elapsed time = 107245 ms.
Try rebuilding all indexes on your table to see if you can reclaim free space due to fragmentation.  If you can move to SQL Server 2016 SP1, you could take advantage of compression.
Are you sure the actual query is taking 2 minutes or is it SSMS that is taking 2 minutes to display the results.  The grid in SSMS is notoriously slow.  Before running your query, run SET STATISTICS TIME ON.  Run your query and look in messages for:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.


That would be the actual amount of execution time for the query.
select convert(datetime,'18/11/2016',103) returns midnight as far as time, so unless those emails were sent at exactly midnight, you won't get a match.  If you want to see emails for that date, use 

SELECT id FROM tblEmailsSent
 WHERE  convert(date,[sent],103) = convert(date,'18/11/2016',103)



Posting the xml for the "actual" execution plan "might" provide us with additional information. I wouldn't think the order desc would be causing any real problem - add SET IO STATISTICS ON before you run the query to see how much logical vs physical I/O you're doing.

One thing to do as an experiment would be to change the select to just retrieve ID only - how long does that run.  Add the next column - how long does that run and so on.  Perhaps your LOB reads are really where the slow down is - if that's the case, I'm not sure what you'd be able to do about that.  Starting with a 'minimum number of columns and adding additional columns on each subsequent run may provide some insight as to what is bogging down your query.

Also, check out this Q &amp; A that has some excellent discussion of LOB data - - LOB_DATA, slow table scans, and some I/O questions