December 21, 2011 at 12:40 pm
Hi Everyone,
Hope all is well.
I was wondering if there is a way to determine when a new index was added to a table or how long an existing index was being used. Please let me know if any one has a query to pull this information from the underlying DMVs.
Thanks.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 21, 2011 at 1:05 pm
AFAIK, regular indexes cannot be determined by create_date or modified date; if the data exists, it is not exposed.
you can infer the index creeation for Primary Key and unique constraints, because their info is in sys.objects, but not regular indexes.
you can find the first and last times an index was last used (since the last server restart) but that is not the same thing.
Lowell
December 21, 2011 at 1:13 pm
I do that kind of thing through source control and DDL logs. I'm assuming you don't have those in place, so really can't help you on this one.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 21, 2011 at 2:39 pm
Answering for your 2nd question: Create a daily job which saves daily snapshot of sys.dm_db_index_usage_stats to another table, plus date/time of running this job.
This DMV keeps counting values since the last server startup. What you need to do is calculate a delta between your last snapshot and some older one which you want to compare with. Fields user_seeks, scans, etc, will give you an answer.
December 21, 2011 at 3:31 pm
Sapen (12/21/2011)
Hi Everyone,Hope all is well.
I was wondering if there is a way to determine when a new index was added to a table or how long an existing index was being used. Please let me know if any one has a query to pull this information from the underlying DMVs.
Thanks.
If an index has been added recently then you can try to get creation date from the default trace file otherwise there is no way you can do it unless you have DDL triggers.
DECLARE @filename VARCHAR(500)
SELECT @filename = CAST(value AS VARCHAR(500))
FROM fn_trace_getinfo(DEFAULT)
WHERE property = 2
AND value IS NOT NULL
-- Go back 4 files since default trace only keeps the last 5 and start from there.
SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'
SELECT
gt.EventClass,
gt.EventSubClass,
te.Name AS EventName,
gt.HostName,
gt.StartTime,
gt.DatabaseName,
gt.ObjectName
FROM fn_trace_gettable(@fileName, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE EventClass = 46
and ObjectType = 22601
and gt.DatabaseName <> 'tempdb'
ORDER BY StartTime desc;
December 21, 2011 at 7:00 pm
If an index has been added recently then you can try to get creation date from the default trace file otherwise there is no way you can do it unless you have DDL triggers.
DECLARE @filename VARCHAR(500)
SELECT @filename = CAST(value AS VARCHAR(500))
FROM fn_trace_getinfo(DEFAULT)
WHERE property = 2
AND value IS NOT NULL
SELECT
gt.EventClass,
gt.EventSubClass,
te.Name AS EventName,
gt.HostName,
gt.StartTime,
gt.DatabaseName,
gt.ObjectName
FROM fn_trace_gettable(@fileName, 4) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE EventClass = 46
and ObjectType = 22601
and gt.DatabaseName <> 'tempdb'
ORDER BY StartTime desc;
The above script returned nothing.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 22, 2011 at 6:21 am
Sapen (12/21/2011)
If an index has been added recently then you can try to get creation date from the default trace file otherwise there is no way you can do it unless you have DDL triggers.
DECLARE @filename VARCHAR(500)
SELECT @filename = CAST(value AS VARCHAR(500))
FROM fn_trace_getinfo(DEFAULT)
WHERE property = 2
AND value IS NOT NULL
SELECT
gt.EventClass,
gt.EventSubClass,
te.Name AS EventName,
gt.HostName,
gt.StartTime,
gt.DatabaseName,
gt.ObjectName
FROM fn_trace_gettable(@fileName, 4) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE EventClass = 46
and ObjectType = 22601
and gt.DatabaseName <> 'tempdb'
ORDER BY StartTime desc;
The above script returned nothing.
Try this:
USE ProofOfConcept;
GO
CREATE TABLE dbo.TraceTest (
ID INT);
GO
CREATE INDEX IDX_TraceTest ON dbo.TraceTest(ID);
GO
DECLARE @filename VARCHAR(500)
SELECT @filename = CAST(value AS VARCHAR(500))
FROM fn_trace_getinfo(DEFAULT)
WHERE property = 2
AND value IS NOT NULL
SELECT
gt.EventClass,
gt.EventSubClass,
te.Name AS EventName,
gt.HostName,
gt.StartTime,
gt.DatabaseName,
gt.ObjectName
FROM fn_trace_gettable(@fileName, 4) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE EventClass = 46
and ObjectType = 22601
and gt.DatabaseName <> 'tempdb'
ORDER BY StartTime desc;
GO
DROP TABLE dbo.TraceTest;
You'll get the index create event from the trace.
It will only work if the index was created within the scope of the trace files. They roll over, given time, and you'll lose older events out of them. Look up "sql server default trace" in your prefered search engine for details.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 22, 2011 at 9:14 am
Thanks for the script. I am thinking that the index was created around november 8th,2011. And they were atleast 2 sqlserver service restarts from then and also the server got rebooted once. So do you think would I still have the information in the default trace. Also when the index was created I used the option SORT_IN_TEMPDB = OFF.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 27, 2011 at 6:12 am
Sapen (12/22/2011)
Thanks for the script. I am thinking that the index was created around november 8th,2011. And they were atleast 2 sqlserver service restarts from then and also the server got rebooted once. So do you think would I still have the information in the default trace. Also when the index was created I used the option SORT_IN_TEMPDB = OFF.
A restart doesn't reset the trace, but the amount of time probably means it's rolled over out of it. Depends on the level of activity in the server, but I'm used to about a week or two, tops, in the default trace.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 27, 2011 at 9:43 am
ok..thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 13, 2012 at 11:22 am
Sapen,
sorry I provided you a wrong script. It will read only the last active trace file but there are usually 4 more historical ones.
You need to modify file name like this:
-- Go back 4 files since default trace only keeps the last 5 and start from there.
SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'
I've updated my original post.
January 13, 2012 at 11:39 am
No Problem. I have DDL Triggers in place now.
Thanks.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 2, 2015 at 5:20 pm
Check if the query posted at this link works. Seems to get me what I want with respect to an index creation date
http://www.sqlpanda.com/2013/10/how-to-check-index-creation-date.html
SELECT object_name(i.object_id) as TableName, i.object_id, i.name, i.type_desc,o.create_date,o.type,i.is_disabled
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.type NOT IN ('S', 'IT')
and o.is_ms_shipped = 0
and i.name is not null
ORDER BY create_date DESC
October 2, 2015 at 5:54 pm
toniothomas (10/2/2015)
Check if the query posted at this link works. Seems to get me what I want with respect to an index creation datehttp://www.sqlpanda.com/2013/10/how-to-check-index-creation-date.html
SELECT object_name(i.object_id) as TableName, i.object_id, i.name, i.type_desc,o.create_date,o.type,i.is_disabled
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.type NOT IN ('S', 'IT')
and o.is_ms_shipped = 0
and i.name is not null
ORDER BY create_date DESC
Have you even tried to understand what the query is doing? Or you blindly trust anyone who post something on their blog as long as it says SQL in the name? 🙂
October 3, 2015 at 8:49 am
toniothomas (10/2/2015)
Check if the query posted at this link works. Seems to get me what I want with respect to an index creation datehttp://www.sqlpanda.com/2013/10/how-to-check-index-creation-date.html
SELECT object_name(i.object_id) as TableName, i.object_id, i.name, i.type_desc,o.create_date,o.type,i.is_disabled
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.type NOT IN ('S', 'IT')
and o.is_ms_shipped = 0
and i.name is not null
ORDER BY create_date DESC
The date you grabbed is from sys.objects(o.create_date), which is the date the Table was created.
it is NOT when the index was created. there is not any exposed created/modified dates related to indexes.
Lowell
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply