SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Help with appropriate index
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maladen
Starting Member

Bulgaria
3 Posts

Posted - 01/13/2014 :  08:06:21  Show Profile  Reply with Quote
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

or
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

USA
36670 Posts

Posted - 01/13/2014 :  12:47:46  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

maladen
Starting Member

Bulgaria
3 Posts

Posted - 01/13/2014 :  13:30:42  Show Profile  Reply with Quote
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.

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36670 Posts

Posted - 01/13/2014 :  13:47:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36670 Posts

Posted - 01/13/2014 :  13:48:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

maladen
Starting Member

Bulgaria
3 Posts

Posted - 01/13/2014 :  15:06:53  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36670 Posts

Posted - 01/13/2014 :  15:13:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000