June 30, 2010 at 4:18 am
Hi
In one of my sql instance i am not getting records for table/index updation in sys.dm_db_index_usage_stats DMV. i did some googling and found that i nned to enable trace flag , So i did that DBCC TRACEON(8206,-1) then DBCC TRACESTATUS but it couldnt help me.
Can anybody help me why i cant get records in DMV and if it is because of TRACE FLAG , what are the steps for enable it.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 30, 2010 at 4:25 am
have you been granted VIEW SERVER STATE permission ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 30, 2010 at 4:29 am
i am sql administrator, is that enough for it ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 30, 2010 at 5:03 am
Bhuvnesh (6/30/2010)
i am sql administrator, is that enough for it ?
That should be ok.
What level of SQL2005 are you on (sp/CU )
Select @@version
or
Select Serverproperty( 'BuildClrVersion' ) as BuildClrVersion
, Serverproperty( 'ComputerNamePhysicalNetBIOS' ) as ComputerNamePhysicalNetBIOS
, Serverproperty( 'Edition' ) as Edition
, Serverproperty( 'EditionID' ) as EditionID
, Serverproperty( 'EngineEdition' ) as EngineEdition
, Serverproperty( 'MachineName' ) as MachineName
, Serverproperty( 'ProductLevel' ) as ProductLevel
, Serverproperty( 'ResourceLastUpdateDateTime' ) as ResourceLastUpdateDateTime
, Serverproperty( 'ResourceVersion' ) as ResourceVersion
, Serverproperty( 'ServerName' ) as ServerName
, Serverproperty( 'InstanceName' ) as InstanceName
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 30, 2010 at 5:42 am
ALZDBA (6/30/2010)
Select @@version[/code]Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
or
r
Select Serverproperty( 'BuildClrVersion' ) as BuildClrVersion
, Serverproperty( 'ComputerNamePhysicalNetBIOS' ) as ComputerNamePhysicalNetBIOS
, Serverproperty( 'Edition' ) as Edition
, Serverproperty( 'EditionID' ) as EditionID
, Serverproperty( 'EngineEdition' ) as EngineEdition
, Serverproperty( 'MachineName' ) as MachineName
, Serverproperty( 'ProductLevel' ) as ProductLevel
, Serverproperty( 'ResourceLastUpdateDateTime' ) as ResourceLastUpdateDateTime
, Serverproperty( 'ResourceVersion' ) as ResourceVersion
, Serverproperty( 'ServerName' ) as ServerName
, Serverproperty( 'InstanceName' ) as InstanceName
[/quote]
v2.0.50727D2DBA03
Developer Edition (64-bit
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 30, 2010 at 5:43 am
Bhuvnesh (6/30/2010)
ALZDBA (6/30/2010)
Select @@version[/code]Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Select Serverproperty( 'BuildClrVersion' ) as BuildClrVersion, Serverproperty( 'ComputerNamePhysicalNetBIOS' ) as ComputerNamePhysicalNetBIOS
, Serverproperty( 'Edition' ) as Edition
, Serverproperty( 'EditionID' ) as EditionID
, Serverproperty( 'EngineEdition' ) as EngineEdition
, Serverproperty( 'MachineName' ) as MachineName
, Serverproperty( 'ProductLevel' ) as ProductLevel
, Serverproperty( 'ResourceLastUpdateDateTime' ) as ResourceLastUpdateDateTime
, Serverproperty( 'ResourceVersion' ) as ResourceVersion
, Serverproperty( 'ServerName' ) as ServerName
, Serverproperty( 'InstanceName' ) as InstanceName
v2.0.50727D2DBA03
Developer Edition (64-bit[/quote]
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 30, 2010 at 5:47 am
Do you get any error when you run sys.dm_db_index_usage_stats?
Is the database compatibility level = 90?
Do you have any index (which is used) on this database?
June 30, 2010 at 5:51 am
Suresh B. (6/30/2010)
Do you get any error when you run sys.dm_db_index_usage_stats?Is the database compatibility level = 90?
Do you have any index (which is used) on this database?
Good points Suresh.
If this is an upgraded instance, keep in mind your system databases also need you to alter the compatibility level !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 30, 2010 at 5:55 am
Bhuvnesh (6/30/2010)
HiIn one of my sql instance i am not getting records for table/index updation in sys.dm_db_index_usage_stats DMV.
Please post the SQL query you are running.
June 30, 2010 at 6:11 am
Suresh B. (6/30/2010)
Bhuvnesh (6/30/2010)
HiIn one of my sql instance i am not getting records for table/index updation in sys.dm_db_index_usage_stats DMV.
Please post the SQL query you are running.
SELECT OBJECT_NAME(OBJECT_ID),* FROM sys.dm_db_index_usage_stats
This is a user DB has alots of indexes.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 30, 2010 at 6:15 am
Suresh B. (6/30/2010)
Do you get any error when you run sys.dm_db_index_usage_stats?
NO
Is the database compatibility level = 90?
Yes
Do you have any index (which is used) on this database?
yes a lot of
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 30, 2010 at 6:17 am
Is this instance restarted just before running this query?
Because when the instance is restarted, all the dmv values are reset.
Is database property AUTO_CLOSE = On?
June 30, 2010 at 6:22 am
No
But in case it got restarted and we have some index seeks or scan then we must have some entries in this view. i am right ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 30, 2010 at 6:23 am
Suresh B. (6/30/2010)
Is database property AUTO_CLOSE = On?
NO. it is set FALSE
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 2, 2010 at 3:15 am
i got the resolution
From DBCC tracestatus
i was getting 2330 flag on
so i eanbled it by DBCC traceoff(2330,-1)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply