All geek questions in one place

Need advice on choosing a clustered index for the logging table

I am using SQL Server 2005. I have a simple logging table that my web application uses to track user activity and visited URLs. Table design is very simple

Identifier (identifier)

LogDate (datetime),

Activity (nvarchar (200)),

Url (nvarchar (1000))

We mainly put in this table. From time to time, we perform some queries against this table if we want to investigate specific user actions over a period. The table currently contains an identity column as a primary key. This is also a clustered index.

I am wondering if it is better for me to change my clustered index into a LogDate column. The LogDate column stores the date / time of the activity and may have duplicates, but since we always insert into the table, new records should always be at the end of the table, so there is no reason for SQL Server to reorganize or break pages that will affect Insert performance. Having a LogDate column as a clustered index should also help in finding performance.

Please let me know if my reasoning is correct. Thanks!

+3
source share
3 answers

Yes, your reasoning is correct if the insertion speed is much less than the datetime granularity ( 3.33ms )

SQL Server 2008 has the new DATETIME2 data type with higher precision (100 nanoseconds).

If you leave a reasonable amount of free space (FILLFACTOR between 80-90) and regularly rebuild the index (once a week), everything should be fine.

+3
source

Before choosing clustering indices, we need to set priorities. More importantly: accelerating rare samples or minimizing slower frequent inserts? If your insertions are more important, keep the existing clustering index.

+3
source

I think the best option is to use the Database Configuration Wizard. This will take care of choosing the best option for your business. You just need to run it long enough to cover your different cases, so that its analysis best suits your real case.

You can find more at http://msdn.microsoft.com/en-us/library/ms189303%28SQL.90%29.aspx (for SQL Server 2005)

0
source

Source: https://habr.com/ru/post/1299149/

More articles:


All Articles