Skip to main content

Finding missing indexes in SQL Server 2005

There are quite a lot of DMV's which have been introduced along with SQL Server 2005. One such is sys.dm_db_missing_index_details. Using this DMV we can identify what all indexes SQL optimizer is expecing in our tables.

Query: select * from sys.dm_db_missing_index_details
Fields to note: equality_columns, inequality_columns, included_columns, statement

So by querying this DMV and having a look at these 4 columns we can decide on the types of indexes which needs to be created for our tables. Each of this column has a meaning. Either find it yourself :) or drop a line in the comment section.

One important point to keep in mind is the result you see out of running the above DMV is just a 'suggestion' on the way to improve the performance of your query. But its not 100% guaranteed that on creating all these indexes would help.

We cannot access the DMV using accounts which doesn't have permission for 'View Server State'. I would write more on these in upcoming posts.

Hope this helps!

Comments

Unknown said…
Hello Vadivel, my name is Sergio Alvarez, I'm a contract recruiter currently engaged with TV Guide in on of their searches. I am trying to reach you in order to talk with you about an opening within the company. I'm sorry that I have to contact you in this way, but I've tried to reach you everywhere, but it seems that I have an old copy of your resume.

You can reply at sergio.alvarez|at| avature.net so we can discuss this opening with you and see if it matches what you consider to be a solid next step in your career.

Thank you Vadivel,
looking forward to your reply
Anonymous said…
wat happened to ur blog enteries sir????
Anonymous said…
looks like he got the position from the guy above..
Vadivel said…
lol no i haven't changed my job :) I was busy with something but since 2 weeks back I have started blogging. Sorry for the delay!

Popular posts from this blog

Registry manipulation from SQL

Registry Manupulation from SQL Server is pretty easy. There are 4 extended stored procedure in SQL Server 2000 for the purpose of manupulating the server registry. They are: 1) xp_regwrite 2) xp_regread 3) xp_regdeletekey 4) xp_regdeletevalue Let us see each one of them in detail! About xp_regwrite This extended stored procedure helps us to create data item in the (server’s) registry and we could also create a new key. Usage: We must specify the root key with the @rootkey parameter and an individual key with the @key parameter. Please note that if the key doesn’t exist (without any warnnig) it would be created in the registry. The @value_name parameter designates the data item and the @type the type of the data item. Valid data item types include REG_SZ and REG_DWORD . The last parameter is the @value parameter, which assigns a value to the data item. Let us now see an example which would add a new key called " TestKey ", and a new data item under it called TestKeyValue :

Screen scraping using XmlHttp and Vbscript ...

I wrote a small program for screen scraping any sites using XmlHttp object and VBScript. I know I haven't done any rocket science :) still I thought of sharing the code with you all. XmlHttp -- E x tensible M arkup L anguage H ypertext T ransfer P rotocol An advantage is that - the XmlHttp object queries the server and retrieve the latest information without reloading the page. Source code: < html > < head > < script language ="vbscript"> Dim objXmlHttp Set objXmlHttp = CreateObject("Msxml2.XMLHttp") Function ScreenScrapping() URL == "UR site URL comes here" objXmlHttp.Open "POST", url, False objXmlHttp.onreadystatechange = getref("HandleStateChange") objXmlHttp.Send End Function Function HandleStateChange() If (ObjXmlHttp.readyState = 4) Then msgbox "Screenscrapping completed .." divShowContent.innerHtml = objXmlHttp.responseText End If End Function </ script > < head > < body > &l

Script table as - ALTER TO is greyed out - SQL SERVER

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug? No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables. For generating ALTER Table script there is an work around. Right click on the table, choose "Modify" and enter into the design mode. Make what ever changes you want to make and WITHOUT saving it right click anywhere on the top half of the window (above Column properties) and choose "Generate Change Script". Please be advised that SQL Server would drop actually create a new table with modifications, move the data from the old table into it and then drop the old table. Sounds simple but assume you have a very large table for which you want to do this! Then it woul