0

On our side we have a database on SQL Server 2016 (v13.0.7024.30). In this database, we have a table with about 14 million rows.

We receive a lot of messages containing a (non-unique) timestamp, non-unique as multiple messages can come in on the same moment in time.

The queries we execute are mainly on the basis of that timestamp (e.g. give us all the messages from the past 24 hours).

We have defined an index on the timestamp:

existing index

We see an improvement in the performance with this index (compared to before, when we did not had the index), when doing queries to get all columns (87 in total) and rows after a certain time (e.g. past 24 hours).

Queries look like:

SELECT [Column1],...,[Column87] 
FROM [dbo].[myTable] 
WHERE [Timestamp] > '2023-04-17 00:00:00'

Our question: is there a way to improve the performance even more, by creating another / better index?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Marab
  • 27
  • 3
  • 1
    Depends on your scenario and constraints, but if you have a good amount of data a clustered columnstore is potentially valuable. It takes more time to build but once built it can help speed up query execution especially if you are trying to filter a good amount of data out. Batch mode is generally faster, but if you push lots of data back to the client, it can negate that benefit as that is generally slower than batch mode. – Conor Cunningham MSFT Apr 18 '23 at 12:35
  • 1
    probably. pls post your table schema – Mitch Wheat Apr 18 '23 at 12:35
  • 1
    I would ask if you *need* all 87 columns in your query. It's unlikely your index is going to be covering with a table *that* wide, unless `Timestamp` is your clustered index? – Thom A Apr 18 '23 at 12:36
  • If your data is very wide, and you don't need ALL columns, it might help to create NC index on timestamp as well and only include the columns you're mostly fetching. – siggemannen Apr 18 '23 at 12:36
  • How many rows are you expecting with a clause like `[Timestamp] > '2023-04-17 00:00:00'`? *If* it's a small amount (we're talking 10's here, maybe a few 100) then having a `NONCLUSTERED` index on `Timestamp` only will likely result in a seek on the index followed by a key lookup. if we're talking 1,000's or more, though, then the chance of a full scan on the CI become more probable; key lookups can become *very* costly. – Thom A Apr 18 '23 at 12:51
  • Is there any plan to have data to be purged say after some threshold, or your meed is to have it in table ( old records)? If we manage to purge old records the perfoamce might increase – sshet Apr 18 '23 at 12:51
  • `Queries look like:...` Please include the average total number of records returned from your query as well as the frequency the query is executed. Next add the following above your query 1 time: `SET STATISTICS IO ON` and include the results in your question. Finally please include the full execution plan of the query. This should be able to tell us if there are other reasons or factors for any slowness like maybe missing or inaccurate statistics for example. ↓ See below ↓: – Igor Apr 18 '23 at 12:55
  • [Include the actual Execution Plan](https://stackoverflow.com/a/7359705/1260204), you could use [Paste the Plan](https://www.brentozar.com/pastetheplan/) and share the link in your question. – Igor Apr 18 '23 at 12:55
  • Aside... `WHERE [Timestamp] > '2023-04-17 00:00:00'` is sensitive to the current `SET DATEFORMAT` setting. If you want to avoid conversion errors when encountering `DMY` sessions you should stick a `T` in there, `'2023-04-17T00:00:00'`, which works with all variants. – AlwaysLearning Apr 18 '23 at 13:53

0 Answers0