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
websssCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase 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ãoMSSQL 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
Scott PletcherSenior 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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Scott PletcherSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.