November 6, 2018 at 7:26 am
I'm working with the dba of a server I often pull data from to tune performance of queries against a huge table. I had him create a filtered non-clustered index on the table. However, my execution plan didn't use it. That in itself is not an issue but to see why, I scripted the index as CREATE and saw that it didn't have the WHERE clause. I asked him to recreate it with the WHERE clause and he showed me that when he scripts it out, it DOES have the WHERE clause. What would cause an index to be defined one way for one person and another way for the other person? His server is on SQL 2008R2. He shared his screen with me and I did with him and we are on the exact same server, database, table, index, etc. It is the weirdest behavior I have ever seen. Any advice is appreciated.
November 6, 2018 at 1:46 pm
If you are both connecting to the same database, perhaps the problem is the version of Management Studio you are using?
what happens if you try a query like this instead of scripting:SELECT OBJECT_SCHEMA_NAME(i.object_id) + N'.' + OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, i.type_desc, i.is_unique, i.is_primary_key,
SubString(
(SELECT N', ' + c.name + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N'' END
FROM sys.index_columns ic INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH('')), 3, 1000) AS columns,
SubString(
(SELECT N', ' + c.name
FROM sys.index_columns ic
INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
ORDER BY ic.index_column_id
FOR XML PATH('')), 3, 1000) AS included,
i.filter_definition
FROM sys.indexes i
WHERE i.name IS NOT NULL
AND i.object_id = OBJECT_ID('dbo.YourTableName')
ORDER BY table_name, i.name;
November 6, 2018 at 2:43 pm
I've seen this happen before - on 2008 R2.
It was possible to generate the table and the index, but the filter definition would not be generated depending on the permissions the individual had on the instance/database
November 7, 2018 at 6:16 am
Thank you! It did turn out to be an authority issue. Once I was given db_owner rights I could see the filters. It is strange and misleading information because someone who is not db_owner but is a developer should be able to see the actual index that is on the table, not just part of it. And the SQL instance that the database is on is 2008 R2.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply