Performance Tuning is quite interesting and Index plays a vital role in it. A proper index can improve the performance and a bad index can hamper the performance. In this blog post we will discuss about Missing Index. Here is the script from my script bank, which I use to identify missing indexes on any database.
Please note, if you should not create all the missing indexes this script suggest. This is just for guidance. You should not create more than 5-10 indexes per table. Additionally, this script sometime does not give accurate information so use your common sense.
Anyway, the scripts is a good starting point. You should pay attention to Avg_Estimated_Impact when you are going to create an index. The index creation script is also provided in the last column.
-- Missing Index Script
-- Original Author: Pinal Dave
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
If you need further help with this script, do leave a comment or send me email at my email address.
Note: It goes without saying, please test all the script on your development server and after they pass your test, deploy them on production server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
92 Comments. Leave new
I need to prepare a report with the following feilds.
Agent ID
Agent name
Login time
Log out time
Break In Time
Break out Time
Meeting In Time
Meeting Out Time
I need the output like Agent ID Agent name Login time Log out time Break In Time Break out Time Meeting In Time Meeting Out Time
pls send me the source code
I am storing login and logout time, break in time, break out time, meeting intime, meeting outtime in table. If a user logged in — logged out, again logged in and logged out and gone for break and for meeting too( multiple times on the same day), how can I calculate the individual time as well as total time
I shall highly appreciate if you please help me.
Thank you in advance for your help
You need to post some sample data with expected result
AgentID [47620] Agentname [xyz] Logintime [2010-12-28 16:58:56.617] Logouttime [2010-12-28 17:18:44.218] BreakInTime [2010-11-29 12:42:11.120] BreakoutTime [2010-11-29 12:42:15.320] MeetingInTime [2010-11-29 12:40:00.000] MeetingOutTime [2010-11-29 13:10:00.000]
this is the sample data. can u do me one more favour like i need the data in both ways with (date with time) format and only time format thanx in for ur help in advance
Please send me the query asap pls i need it very badly pls pls
I need the report in the following format (include the feilds breakintime, breakouttime, meetin intime,meeting outtime)
AgentId AgentName LoginTime LogOutTime
1 xyz 5/20/2008 12:40AM 5./20/2008 1:10 AM
2 xyz 5/20/2008 1:15 AM 5/20/2008 2:10 AM
3 abc 5/20/2008 2:15 AM 5/20/2008 3:20 AM
4 def 5/20/2008 3:40 AM 5/20/2008 4:00 AM
Check if the below query helps….
SELECT AgentID, AgentName,
CONVERT(VARCHAR,Logintime,100) AS LOGIN_WITH_DATETIME,
CONVERT(VARCHAR,Logintime,108) AS LOGIN_WITH_ONLY_TIME,LogOutTime,
CONVERT(VARCHAR,Logouttime – Logintime,108) as Login_Duration,
BreakInTime, BreakoutTime,
CONVERT(VARCHAR,BreakoutTime – BreakInTime,108) as Break_Duration,
MeetingInTime, MeetingOutTime,
CONVERT(VARCHAR,MeetingOutTime – MeetingInTime,108) as Meeting_Duration,
CONVERT(VARCHAR,(Logouttime – Logintime) + (BreakoutTime – BreakInTime) + (MeetingOutTime – MeetingInTime),108)
AS TOTAL_DURATION
FROM Agent_Log
GO
Thank you so much for your response Ahmed.
But The feilds i have given are Client Requirement in the report. I dont have exact feild names in the tables. As i am very new to this SQL server. I need to create a stored procedure.
The Requirement is…
If the Agent is logged in at 9am (I need Login Time)
If the Agent goes for break by 11am (I need Break on time)
If the Agent has come back from break by 11:15am (I need Break Off Time)
If the Agent goes for meeting by 1pm (I need meeting On Time)
If the Agent Has come back from Meeting by 1:30pm (I need Meeting Out Time)
If the Agent is Logged off by 6pm(I need Logout time)
I need this for daily wise report
Hi Guys, Suppose if the agent gets logged in, Login time should be noted, if the agent had gone for break several breaks, then break times should be noted and if the agent goes for meeting, the meeting time should be displayed and logout time.
I need Login Time, number of Break times with time if possible(how many times the agent had gone for break)and meeting In time and meeting out time
Seriously Abdul,
Why exactly are you taking on “client requirements” in a space that you obviously know nothing about? Do you intend to tell your customers that you’re an expert in SQL Server, and then beg people who have actually invested time into their craft to do all of their work for you?
Sheesh…
Thanks for the great script. Someone pointed out the limitations of this to beware of so I thought I would share:
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms345485(v=sql.105)
Cheers.
Hi Pinal,
Thanks for the script, this has turned out to be very useful. Also, we have found that this script also works on SQL Server 2005.
Regards,
Jacob
Great job with writing this script! It is really useful. It saved me couple of hours worth of work. Keep up the good work!
I have to say, this is probably in the top 10 most useful scripts you have provided for the greater good.
Together with a duplicated Index finding script I found, these 2 will really open up transactional performance wide open.
You are doing some serious master work.
Appreciated by DBA’s and Testers alike.
its works good ….
i have some below doubts .
Problem statement.
create index abc on table xyz
select * from xyz with (index abc)
when we run this statement in one transaction the index is not gets created
but separately it do.
i.e. when i run this in one go i get error like index specified in the from
clause does not exist.
when you create one SP which embeded this statement it will throw this above mentioned error.
Your response to my question is much appreciate.
Thanks,
Nishad
Thanks so much for this, it’s been tremendous.
One question, I keep getting the same suggested index for a foreign key on a table. Is there a reason that an item that is a foreign key would show up as needing a index when it doesn’t suggest any other columns to be included?
CREATE INDEX [IX_foreign_table_key_id] ON [DATABASE_NAME].[dbo].[table] ([foreign_table_id])
where foreign_table_id is a foreign key in SQL Server.
Thanks a lot Pinal.
Excellent post….too informative!
Regards,
Girijesh
hi ,
i create the index by using this query…. but when i run the query for unused index it gives the name of newly created index in that query….
do we need to do anything else after creating indexs…..
You’d probably need to rebuild the indexes on the table (if not the entire DB), so the index fields can be included in the tree structure of the database files. This is usually a scheduled job you set up to run every week or two during some low-demand timespan; exactly how often you should run something like this, and how long it will take, depends on what you’re indexing on and how often those field values change.
Thanks @Keith
Thank you for sharing your info. I really appreciate your efforts
and I am waiting for your further write ups thanks once again.
It’s very trouble-free to find out any topic on net as compared to books, as I found this paragraph at this web site.