June 8, 2017 at 8:56 am
Does anyone know why this "feature" would not be present in a SQL 2014 SP2 environment?
I'm trying to determine fragmentation levels in a DB I was just introduced to and I've been using the following script to do this - it works on all my other instances (dozens) but not here.
SELECT CAST(DB_NAME(DATABASE_ID) AS VARCHAR(20)) AS 'DatabaseName',
CAST(OBJECT_NAME([OBJECT_ID]) AS VARCHAR(20)) AS 'TableName',
INDEX_ID,Object_ID,Page_Count,
CAST(INDEX_TYPE_DESC AS VARCHAR(20)) AS INDEX_TYPE_DESC,
AVG_FRAGMENTATION_IN_PERCENT
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID('ProfilerTest')
,OBJECT_ID('dbo.ProfilerResults'),NULL,NULL,NULL )
where database_id > 4
and page_count > 1000
and AVG_FRAGMENTATION_IN_PERCENT > 30
GO
I get a message: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.DM_DB_INDEX_PHYSICAL_STATS'.
Why would this not be there?
June 8, 2017 at 9:07 am
My guess is that you have a case-sensitive database or server. You need to type the name of the DMV in lower case.
John
June 8, 2017 at 9:09 am
My best guess is that you're running the query from a database with a case-sensitive collation.
Switch to all lower-case for the object name and/or check the collation for your current database.
Cheers!
EDIT: Darn, John beat me to it 🙂
June 8, 2017 at 9:50 am
Thanks I'll give it a try. Its SAP, so it is case sensitive. Thanks, that could be easy. (not to mention, dumb of me)
June 8, 2017 at 1:56 pm
Yup, case-sensitive is the cause (and that's why I'm allergic to case-sensitive databases).
The name of the dmv is sys.dm_db_index_physical_stats, all lowercase. You'll have to fix the casing of all the column names as well, I think they're all lowercase.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply