January 16, 2014 at 7:31 am
If I run this query
SELECT *
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, N'LIMITED')
WHERE [sys].[dm_db_index_physical_stats].[page_count] > 100 -- Smallest tables are ignored by page size
AND [sys].[dm_db_index_physical_stats].[avg_fragmentation_in_percent] > 10 -- maximum fragmentation that we allow
AND [sys].[dm_db_index_physical_stats].[index_id] > 0 -- We don't want to do anything to heaps
then look at the object and index it returns, I am getting back indexes that don't exist if I query the sys.index table, and I am also getting back non-existant indexes that claim they tie back to table functions based on the object id, schema and database...
any idea what would cause this?
January 16, 2014 at 7:36 am
You're passing NULL as the first parameter so the query is checking all indexes on all tables in all databases on the instance. Look at the database ID that the function returns and look for those objects in their databases.
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
January 16, 2014 at 7:40 am
yes, I know, I am trying to get all the fragmented indexes back in all databases.
When I looked up the object ID I did look it up by database ID also, and it still returns bad data... for instance... this query returns to me a row
database_id = 9
object_id = 933578364
index_id = 5
partition_number = 1
when I look up that object and index on that database, the object only has 4 total indexes, not 5. So the highest Index_ID for that table was 4, but it was telling me that index_id 5 was fragmented...
January 16, 2014 at 7:42 am
How are you checking the index defs in database 5? What query?
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
January 16, 2014 at 7:44 am
just going against the sys.indexes table
January 16, 2014 at 7:49 am
As in, from the database you're in, querying FROM sys.indexes where object_id = 933578364?
Is the database you're currently using the one with an ID of 5? The sys.indexes view is database-specific, sys.dm_db_index_physical_stats is instance-wide.
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
January 16, 2014 at 8:00 am
yes, I am querying the sys.indexes table from the database that the database ID matches
January 16, 2014 at 8:07 am
Then all I can suggest is double check that you are looking at the correct server, correct database and correct query. Most common reason I've seen for problems like this is accidentally looking at the wrong server (incredibly easy to do) or the wrong database on the server.
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
January 16, 2014 at 8:59 am
nope, still same result...
even closed down SSMS and started from scratch again just to make sure I wasn't mistyping something... still showing a 5th index as the result on DB_ID 9, I am connected and using DBID 9 on the same server... I'm lost...
January 16, 2014 at 9:31 am
SELECT database_id, object_id, index_id, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(9,933578364,5,null, 'limited')
SELECT DB_NAME(9)
SELECT object_name(933578364,9)
What do those return?
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
January 16, 2014 at 9:35 am
database_id object_id index_id avg_fragmentation_in_percent
----------- ----------- ----------- ----------------------------
9 933578364 5 30
(1 row(s) affected)
--------------------------------------------------------------------------------------------------------------------------------
Db_LiveSys
(1 row(s) affected)
and
USE [Db_LiveSys]
GO
SELECT * FROM [sys].[indexes] WHERE [sys].[indexes].[object_id] = 933578364
GO
returns
object_id name index_id type type_desc is_unique data_space_id ignore_dup_key is_primary_key is_unique_constraint fill_factor is_padded is_disabled is_hypothetical allow_row_locks allow_page_locks has_filter filter_definition
----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ---- ------------------------------------------------------------ --------- ------------- -------------- -------------- -------------------- ----------- --------- ----------- --------------- --------------- ---------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
933578364 PK_RPatternsForAccount 1 1 CLUSTERED 1 3 0 1 0 90 0 0 0 1 1 0 NULL
933578364 IX_RPatternsJunAccounts_1 2 2 NONCLUSTERED 0 3 0 0 0 0 0 0 0 1 1 0 NULL
933578364 IX_RPatternsJunAccounts_2 3 2 NONCLUSTERED 0 3 0 0 0 0 0 0 0 1 1 0 NULL
933578364 IX_RPatternsForAccount 4 2 NONCLUSTERED 0 3 0 0 0 0 0 0 0 1 1 0 NULL
(4 row(s) affected)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply