August 14, 2014 at 2:48 am
Hi,
What are driving criteria for creating filtered indexes on SQL server. I am trying to analyze the index stats through DMV,histogram and have to analyze if the filtered indexes should be created on tables. This exercise has to be done for all the transaction tables on the database. What are the approaches I should be looking on?
There was a deadlock on the DB because of huge writes on one of the big tables. Having filtered index on this table for the effected column would reduce the time taken for write operations. Hence we are looking for creating filtered indexes appropriately
Please drop your suggestions
August 14, 2014 at 3:14 am
In general, high level, when you have a fixed predicate that is frequently applied to a table along with other predicates. So if you frequently have <someColumn> IS NULL in your where clauses against a table, that may be an indication to create a filtered index on there.
Be aware that filtered indexes don't handle parameterisation well, need the query predicates to match the index predicates and have a few bugs in them.
http://sqlinthewild.co.za/index.php/2011/11/09/sql-university-advanced-indexing-filtered-indexes-2/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2014 at 3:34 am
Is there a way to find missing filtered index in database through DMV's apart from DTA(tuning advisor)? The sys.dm_db_missing_index_details DMV do not list the missing filtered columns
August 14, 2014 at 3:40 am
Nope, missing indexes DMV is too dumb to consider filtered indexes (and it's index recommendations need to be very carefully evaluated anyway, but then so do DTA's)
Filtered indexes are something you consider when you encounter a situation like I describe, not a general indexing strategy.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2014 at 5:27 am
Hey thanks for the reply. I found a way to capture the histogram details for every index and check the candidate for filtered index based on the below condition---
If the number of rows in histogram is less than 10 for a particular index and the percentage of equality rows is greater than 30 , then the particular index is a good candidate for applying filtered condition for the range value specified on the first key column. Please find the attached script
CREATE TABLE histogram1
( RANGE_HI_KEY sql_variant,
RANGE_ROWS bigint,
EQ_ROWS bigint,
DISTINCT_RANGE_ROWS bigint,
AVG_RANGE_ROWS bigint)
--create table for holding the result set
CREATE TABLE histogram_details
(
tname sysname,
iname sysname,
RANGE_HI_KEY sql_variant,
res float,
result varchar(30))
---variables declaration
create procedure tp_Missing_Filtered_index
as
Begin
declare @tablecount int=0
declare @i int=1
DECLARE @tname varchar(100)
DECLARE @iname varchar(max)
declare @cnt int
declare @sum float
declare @res varchar(20)
--table variable to hold the input tablenames
declare @indextable table (id int identity (1,1), Tname sysname, iname sysname)
insert into @indextable(Tname,iname)
select st.name, si.name from sys.indexes si inner join sys.tables st
on si.object_id=st.object_id where si.index_id>2 and st.name like 't[a-z]%'
order by st.name
--take the count of table variable for looping
select @tablecount=count(*) from @indextable
while(@i<@tablecount)
Begin
select @tname=Tname,@iname=iname from @indextable where id=@i;
INSERT INTO histogram1 exec ('DBCC SHOW_STATISTICS('+@tname+','+@iname+') with histogram')
select @cnt=count(*) from histogram1
select @SUM=SUM(EQ_ROWS+RANGE_ROWS) FROM histogram1
insert into histogram_details select @tname,@iname, RANGE_HI_KEY, (cast(EQ_ROWS as float))/(cast (@SUM as float))*100.00000 as res,
(case when (@cnt<10 and ((cast(EQ_ROWS as float))/(cast (@SUM as float))*100.00000) > 30) then 'candidate for FI' else 'no FI' end)
from histogram1 group by RANGE_HI_KEY,EQ_ROWS
truncate table histogram1
set @i=@i+1
END
END
select * from histogram_details where result='candidate for FI' order by tname--35685-31927
select * from histogram1
August 18, 2014 at 6:24 am
Sindhu Ravi (8/18/2014)
If the number of rows in histogram is less than 10 for a particular index and the percentage of equality rows is greater than 30 , then the particular index is a good candidate for applying filtered condition for the range value specified on the first key column.
I strongly disagree there.
Indexes, especially filtered indexes, should be created based on the queries that access the tables. With no info as to what queries query those tables with what kind of predicates, you cannot create useful indexes at all, much less useful filtered indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2014 at 11:11 am
yes I agree,filtered indexes, should be created based on the queries that access the tables. but through the above query, we categorize the candidates for filtered index. Then for those categories, based on the queries that hit on those particular tables, a decision should be made if the filtered index has to be created or not.
August 18, 2014 at 11:35 am
Do it the other way around. Look at the queries, look at how they filter, whether there's a common predicate (like WHERE Archived = 0), which is not parameterised. That will tell you whether the query may benefit from a filtered index
It's not about the data distribution in the table, it's about the predicate forms on the queries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2014 at 1:55 am
Thanks for the replies.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply