1

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:

  1. 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?

  2. 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

  3. 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.
Improve this question
0

2 Answers 2

Reset to default
2

Community wiki answer collecting information left in comments by Dan Guzman and Aaron Bertrand.

The space requirements for this table will mostly be driven by the size of the MessageContent column. The default behavior of the deprecated ntext type is that the column data will be stored in separate LOB data pages of 8K each.

So with 18,700 rows, a minimum of about 150MB will be needed with values larger than 8K requiring additional pages. nvarchar(MAX) will store smaller values in-row and reduce space requirements considerably if most values are under 8K.

If you are in danger of hitting your hosting limits, have you considered a different design, where you store these big messages in flat files on the file system, and just the path in the database? Typically hosted file system storage is much cheaper than hosted database storage.

Perhaps your server is remote and over a slow network connection. The high elapsed time may be due to low network bandwidth and latency. Nothing much you can do on the database side in that case.

Improve this answer
2

  1. 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.

  2. 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.

  3. 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 & A that has some excellent discussion of LOB data - - LOB_DATA, slow table scans, and some I/O questions

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.