Clustered key index, currently on

Hi, currently I have a unique clustered index key on

GpsDatetime (datetime) asc
ImeiNumber (bigint) asc
Id (primary key) asc

Ideally I would like to get rid of id, but I needed it to be unique

99% of my queries are asking for latest Data, should I be ordering by desc for date and id?

A lot of my queries also ask for EventId, I was wondering if I should include this in the clustered key, or does it have too many already?

The table in question has 100 million rows and grows at a rate of 20 records per second
websssAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The best effect this clustered index has is on read performance, by keeping data with same ImeiNumber together on near pages physically on disc, no matter if it is inserted in one go or over long times.

That's not true.  ImeiNumber is the second key column, not the first, so those numbers are together only for identical datetimes.  That's likely useless unless they are very odd querying patterns that use a lot of a specific date and specific/range of imei numbers, which I don't think is logical to assume.
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
>>A lot of my queries also ask for EventId, I was wondering if I should include this in the clustered key, or does it have too many already?

Create NON Clustered Index for this kind of requirement. Also check if you want to include column in there. You can also check for Filtered Index.

Keep Clustered index as small as possible and it got readjusted every time we Insert/Update/Delete.

For uniqueness you can create Unique Key. Note it will include 1 NULL value.
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Ideally I would like to get rid of id, but I needed it to be unique
Wouldn't the IMEI and GpsDatetime guarantee the uniqueness of the record? Or you can have the same IMEI more than once for the exactly date and time of a day?

A lot of my queries also ask for EventId, I was wondering if I should include this in the clustered key, or does it have too many already?
We aren't seeing the whole picture so it depends. Isn't EventId a foreign key? If so then it should be already indexed.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Scott PletcherConnect With a Mentor Senior DBACommented:
Perhaps the best clustering key would:
GpsDatetime (datetime) asc
Id (primary key) asc
which guarantees uniqueness.

You shouldn't really need imei number in the key unless you search a lot by a specific datetime and imei number, and that doesn't seem likely at all.  If you need to, create a nonclus index on imei number.

Don't worry about the clustering key being narrow as long as it matches the way you (almost) always search for data in the table.
0
 
Olaf DoschkeSoftware DeveloperCommented:
The best effect this clustered index has is on read performance, by keeping data with same ImeiNumber together on near pages physically on disc, no matter if it is inserted in one go or over long times. So a clustered index mainly helps with SELECT queries, it always causes more effort in storing data, as new data is physically sorted in and may cause page splits and reorganization.

It only really pays, if data for the same ImeiNumber comes over a larger time interval, so the clustering sorts data which otherwise is just put on a heap as it comes.

You don't need to add as much columns as it needs to be unique, MSSQL will take care of that, it causes an overhead, but it can pay off anyway. http://sqlfool.com/2009/05/overhead-i-non-unique-clustered-indexes/

Just see for yourself, where it goes, if you leave off the id primary key column. With the high frequency of new data I fear you'll have performance problems with any clustered index. In the end all leaf nodes of such an index will be your data pages and a heap table is better functioning for insert performance, if you don't have query performance problems.

An index on a datetime ASC can also be used to query for latest datetimes (MAX), the order of the definition doesn't matter. But try for yourself.

Bye, Olaf.
0
 
Olaf DoschkeSoftware DeveloperCommented:
You're right, Scott. So the clustered index might better be in another order of columns:

ImeiNumber (bigint) asc
GpsDatetime (datetime) asc

The major information should be first.

If it is to keep similar/near GpsDatetime together, the definition is okay, though.

Bye, Olaf.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.