Help with appropriate index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Help with appropriate index

Author

Topic

maladen
Starting Member

3 Posts

Posted - 2014-01-13 : 08:06:21

Hello, I have a very large table with only 3 columns:

time_stamp(datetime), variable_name(varchar(40)), value(real)

which is used for storing measured values.

I've multiple queries which look like

Select avg(value) from my_table where variable_name = 'xxxx' and time_stamp between some_date and other_date

Select max(value)-min(value) from my_table where variable_name = 'xxxx' and time_stamp between some_date and other_date

To summarise, all of my queries are doing some math functions with the VALUE for a variable_name within a time period.

What would be the appropriate index for this table?
Currently i have a non clusetered index

create index on (variable_name, time_stamp) include value

Currently some of the queries are taking up to 30min. Is there a better way of indexing the table?

I am also going to create views or result tables with data for a period of time like 1 month. But this is another topic. For now I just want to clarify if my index is suitable.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-13 : 12:47:46

What is the selectivity of the variable_name and time_stamp columns, both separately and together (plus reversed together)? Maybe switch the order of the index. What is the clustered index set to? Could you show us the execution plan?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

maladen
Starting Member

3 Posts

Posted - 2014-01-13 : 13:30:42

I can't give exact selectivity for the whole table because new records are constantly inserted.
The only constant is the number of distinct values for the variable_name column and they are 1450. These 1450 variables are logged about 4100 times every day (who needs such accuracy a?) this means in the end of the day I have approximately 6 000 000 new rows. If we calculate the selectivity for this day it should be 0.024% for the variable_name and 0.07% for the time_stamp. The selectivity of the time_stamp will remain the same but the selectivity of the variable_name will keep on dropping.

There is no clustered index yet.

I will show an execution plan tomorrow.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-13 : 13:47:08

We just need rough numbers.

select count(*) from my_table (nolock)
select count(*) from (select distinct time_stamp from my_table (nolock)) t
select count(*) from (select distinct variable_name from my_table (nolock)) t

I would probably try a clustered index on time_stamp, variable_name.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-13 : 13:48:43

Also, if your data is wildly changing, you may need to update stats often. How often are you updating stats currently?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

maladen
Starting Member

3 Posts

Posted - 2014-01-13 : 15:06:53

I am not updating the stats manually.
The data in the table is never updated. New data is constantly inserted. But once it is in the table it won't be updated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-13 : 15:13:37

Sounds like you need an update stats job. The auto update stats doesn't kick in until 20% or more of the data has changed. That's not enough for our systems. We have an update stats job that runs daily on most systems but twice a day on our critical/busy system.

It may not solve your issue, but it sounds like you need it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources