Missing Indexes in SQL Server 2005

Did you know that your SQL Server is keeping track of the indexes that it thinks you should create? The "missing index" DMVs in SQL are a really great new feature in SQL Server 2005 that (in my opinion) seem to have been underutilized so far. If you want to see if this feature can spare you the tedium of an afternoon identifying poor performing queries and tuning them, all you have to do is ask:

SELECT

migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,

'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)

+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'

+ ' ON ' + mid.statement

+ ' (' + ISNULL (mid.equality_columns,'')

+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END

+ ISNULL (mid.inequality_columns, '')

+ ')'

+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,

migs.*, mid.database_id, mid.[object_id]

FROM sys.dm_db_missing_index_groups mig

INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

You'll want to run this after your server has been up and running a normal workload for a while. If this returns no results, that's good news and indicates that you're not missing any indexes that are obvious enough for the DMV to detect. If it does return some suggestions, even better: you just improved your server's perf with almost no work.

While to me this feature is so cool it almost seems magical, it does have a few limitations you should be aware of:

• It's not as smart as the Database Engine Tuning Advisor. If you have identified a query that you know is expensive and needs some help, don't pass up DTA just because the missing index DMVs didn't have any suggestions. DTA might still be able to help.
• The missing index DMVs don't take into account the overhead that new indexes can create (extra disk space, slight impact on insert/delete perf, etc). DTA does take this into account, however.
• The "improvement_measure" column in this query's output is a rough indicator of the (estimated) improvement that might be seen if the index was created. This is a unitless number, and has meaning only relative the same number for other indexes. (It's a combination of the avg_total_user_cost, avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.)
• The missing index DMVs don't make recommendation about whether a proposed index should be clustered or nonclustered. This has workload-wide ramifications, while these DMVs focus only on the indexes that would benefit individual queries. (DTA can do this, however.)
• Won't recommend partitioning.
• It's possible that the DMVs may not recommend the ideal column order for multi-column indexes.
• The DMV tracks information on no more than 500 missing indexes.

Missing Indexes in SQL Server 2005

source : http://www.sqlservercentral.com/articles/Indexing/64134/ By Ranga Narasimhan, 2010/07/23 (first published: 2008/09/16) Total article views: 15101 | Views in the last 30 days: 1011   Rate this |   Join the discussion |   Briefcase |   Print There are several new features in SQL Server 2005. There are a few features to help find missing indexes, which are some of the very good ones. How great it will be if you know what indexes you need to create based on your workload? In SQL Server 2000, we had to use SQL Profiler trace files and Index tuning wizard. But with SQL Server 2005 DMVs, we can easily figure out what indexes we need to create which would benefit our application. The following are the missing index DMVs ( From SQL Server 2005 BOL) sys.dm_db_missing_index_group_stats Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes. sys.dm_db_missing_index_groups Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes that are contained in that group. sys.dm_db_missing_index_details Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index. sys.dm_db_missing_index_columns Returns information about the database table columns that are missing an index. Let’s see what indexes are there for table [Person.Address] table in AdventureWorks database by running this code: use AdventureWorks; exec sp_helpindex [Person.Address] Fig:1 Zoom in  |  Open in new window I don’t see an index for ModifiedDate column for [Person.Address] table. So, to get a entry in the “sys.dm_db_missing_index_details” DMV, lets run a query like this: Query: 1 select * from Person.Address where ModifiedDate = '01/01/2008' You may not see any results for the query above, but SQL Server internally recorded that a query was run and a index on “ModifiedDate” column would have been very useful. Query: 2 select * from sys.dm_db_missing_index_details: Fig: 2 Zoom in  |  Open in new window In Fig: 2, see the “equality_columns” field, which implies that a index on the [Modified Date] column is missing ( or might be helpful) Query: 3: select db_name(d.database_id) dbname, object_name(d.object_id) tablename, d.index_handle, d.equality_columns, d.inequality_columns, d.included_columns, d.statement as fully_qualified_object, gs.* from sys.dm_db_missing_index_groups g join sys.dm_db_missing_index_group_stats gs on gs.group_handle = g.index_group_handle join sys.dm_db_missing_index_details d on g.index_handle = d.index_handle where d.database_id = d.database_id and d.object_id = d.object_id and object_name(d.object_id) = 'Address' Run Query 1 several times. Now, run Query: 3, Fig: 3 In Fig 3, notice the “user_seeks” column. So every time a query is run, for which an index might be useful, SQL Server keeps updating the missing index DMVs. This is very valuable information, based on this you can create indexes to support those queries. Isn’t this cool! Yes, SQL Server 2005 rocks! The DMVs for missing indexes are great new features. I work with a Siebel CRM database where queries are built dynamically. So it is hard to design indexes in advance. The missing index feature helps to me create indexes for those queries that have high “user_seeks” for a particular column in a table. For more information see “About the Missing Indexes Feature” in SQL Server 2005 Books Online. By Ranga Narasimhan, 2010/07/23 (first published: 2008/09/16)

Break row on gridview cell

I had the same problem, but the thing to do is quite easy. 
You do not need to convert the column to a TemplateField. 
Still as a BoundField, just set the "HtmlEncode" field property to "false". Then, the "
" tag (and I believe, any other tags) will work.

Shortcuts for Visual Studio

source:http://www.dev102.com/2008/04/17/10-visual-studio-shortcuts-you-must-know/

Here they are:

1. CTRL + ".": This is actually a shortcut for a shortcut. it is the same as CTRL + SHIFT + F10 which opens the smart tag window and allows you to add Using statements
   Or implement interfaces 
   and much more.
2. ALT + CTRL + "e":  This one will open the Exceptions window, which allows you to tell Visual Studio to halt at specific exceptions, halt on all exceptions or selecet some exceptions to ignore.
    
3. CTRL + "k" + "f" and CRTL + "k" + "d": these two will format the code in the window to be nicely indented. using "d" will format all the document while using "f" will format only selected text. The formatting is for all types of documents, HTML, Xaml, XML, C#… This one is my favorite.
4. SHIFT + Del: This one will cut the entire row from the document and past it to the clipboard. No need to select the row, just put the marker there and click SHIFT + Del and it is gone.
5. CTRL + "k" + "c" and CTRL + "k" + "u": These two are for commenting selected text (the "c" options) and uncommenting selected text (the "u" option).
6. ALT + ENTER: this little shortcut will open up the Properties window
7. CTRL + "k" + "s": This one opens up the code snippets dialogue within the code
   
   If you want to create code snippets you should read my post about snippy the code snippetseditor.
8. F12: I think you all know this but still F12 is the shortcut for the "Go to definition" command which will take you to the definition of the object your marker is currently on.
9. F9: Another one i think you all know, clicking on F9 will add a breakpoint to the code line your marker is currently at. Clicking F9 again will remove this breakpoint from that line.
10. CTRL + ALT + "q": This one will open the Quick watch window while you debug
    
    
    
    
    1. Vijay Santhanam Said on Apr 17, 2008 :
       CTRL -
       and
       CTRL SHIFT -
       moves the editing cursor to the last/next position
       this is great if you decide to edit something somewhere else and you wanna go back to that last method you were editing. CTRL - will save you so much time!
    2. Bryan Migliorisi Said on Apr 17, 2008 :
       Nice list.
       Alt + Enter is also a general Windows shortcut for accessing properties of files, drives, shortcuts, etc. It is a nice keystroke to keep in mind.
    3. Pablo Marambio Said on Apr 17, 2008 :
       Shift + Alt + Enter (FullScreen mode) is a must know! It helps you to get rid of all the properties, toolbar and other [obstrusive] windows that don’t let you see the code. Hit it a second time and the windows are back.
    4. knave Said on Apr 17, 2008 :
       CTRL+M+M : Expanding / Collapsing code blocks, e.g. regions/methods.
    5. Greg Beech Said on Apr 17, 2008 :
       CTRL + ALT + P (attach to process) is very useful when you’re debugging web sites, windows services, etc.
       CTRL + R + R (rename) is great for refactoring, particularly if you have ReSharper installed
    6. Diego Said on Apr 17, 2008 :
       SHIFT + F9 for quick watch  even better than CTRL + ALT + Q
    7. Matt Brunell Said on Apr 17, 2008 :
       Ctrl+Shift+B — Build solution
       Ctrl+J — Intellisense, list members
       Ctrl+Shift+Space — Intellisense, list parameter info
       The list members shortcut is handy. Before I started using this a lot, I would sometimes type ‘this.’ to get a list of members. The parameter info is helpful for seeing information about parameters you already typed in.
    8. Terry Aney Said on Apr 17, 2008 :
       Lot of items can be accessed from the ‘Context Menu Key’ (assuming your keyboards have that key…the one that looks like a little menu)…
       CTX+Q - Quick Watch
       CTX+D - Definition
       CTX - same as CTRL+. above
       I generally can get almost anything done with CTX+key or CTX then a quick combo of down and right arrows.
       Anyway, just my preference, by no means the end all.
    9. Dmitry Moskalyk Said on Apr 18, 2008 :
       You can also open properties window just by pressing F4. I like it more than Alt+Enter.
       A year ago I was using Ctrl+K,Ctrl+X to insert a snippet, but I have understood that the easiest way to do it is to type snippet name and press “Tab” afterwards.
    10. Steven Said on Apr 18, 2008 :
        I suggested Ctrl+A then DEL to my … less than able colleagues … didn’t go down well!
        Anyway, here’s my favourite - even if it’s not really a shortcut …
        Ctrl + space
        while coding lets VS finish for you or open intellisense if what you’re type’d is to ambiguos
        “string rediculouslyLongName;
        ret” then Ctrl + Space gives you
        “string rediculouslyLongName;
        rediculouslyLongName”
    11. David Hu Said on Apr 20, 2008 :
        I use F8 and shift+F8 to cycle through search results. It’s a gem for me.
        I think, if anything, the VS team should add an option in Visual Studio to annotate all Menu items with their corresponding shortcuts.
    12. Sam Said on Apr 22, 2008 :
        I am bookmarking this right now. I will have more to say in a few days. Thanks!
    13. Dylan Vester Said on Apr 22, 2008 :
        You should probably note that some of these are ReSharper only shortcuts. Maybe I missed that somewhere. Just a thought.
    14. wekempf Said on May 6, 2008 :
        Dylan Vester,
        Which shortcuts do you think are ReSharper only shortcuts? I don’t own ReSharper. I’ve been using the majority of these shortcuts for years. The few that were new to me worked.
    15. Amit Said on May 6, 2008 :
        Non of them are resharper shortcuts. I know that because I dont use resharper as it takes more resources than Windows Vista :). I am more of a Visual Assist kind of guy.
        Amit
    16. ichbinvinh Said on Sep 24, 2008 :
        F4 = ALT + ENTER —> show properties
        Ctrl + r + e : to generate get and set methods for member
        Anyone know shortcut view all of Methods in class (same as Ctrl + o in eclipse) ?
    17. Jeroen Said on Dec 31, 2008 :
        ctrl-c without selecting anything copies a row (ehh, ctrl-v to paste it)
        ctrl-a to select all, ctrl-k ctrl-f to format the code
        f-12 to go to definition of item under cursor
    18. Steve Said on Feb 6, 2009 :
        Ctrl + r + e : to generate get and set methods for member
        Anyone know how to do an entire block? For example highlight all 3 rows below and generate the methods in one shot instead of select and generating each one?
        public string test1;
        public string test2;
        public string test3;
    19. dell Said on Feb 16, 2009 :
        ctrl + ]
        moves you to the beginning or end of a snippet, [], {}, etc.
    20. Charles Rex Said on Mar 26, 2009 :
        Hello,
        >Ctrl + r + e : to generate get and set methods for member
        >Anyone know how to do an entire block?
        >For example highlight all 3 rows below and generate the methods >in one shot instead of select and generating each one?
        >public string test1;
        >public string test2;
        >public string test3;
        Try this tool:
        http://www.adersoftware.com/index.cfm?page=vsPropertyGenerator2
        I find it useful, it would have been perfect if the tool would be released as open source.
    21. Charles Rex Said on Mar 26, 2009 :
        Hello,
        Ctrl+Tab is a very handy shortcut as well.
        Ctrl+Tab flips forward and backwards, between the last two accessed files.
    22. Manish Jaiswal Said on May 28, 2009 :
        I think the third point which is:
        ” 3. CTRL + “k” + “f” and CRTL + “k” + “d”: these two will format the code in the window to be nicely indented. using “d” will format all the document while using “f” will format only selected text. The formatting is for all types of documents, HTML, Xaml, XML, C#……”
        is for older version(s). For VS 2008, it is CTRL + “E” + “F” and CRTL + “E” + “D”.
    23. Sangam Uprety Said on Jun 9, 2009 :
        Ctrl+Shift+H is equivalent to Ctrl+H and does very smart job for find and replace. Further, first do Ctrl+F for find and then just press F3 for next match. Easy!
        A number of such short keys have made life easier. These ones are also worth looking at:http://dotnetspidor.blogspot.com/2009/06/22-visual-studio-short-keys-and-6-short.html
        Happy Programming!
    24. gaurav Said on Sep 4, 2009 :
        Ctrl + Shift + F7 ….highlights the usages of any variable,methods …Find it very useful
    25. Max Quagliotto Said on Oct 24, 2009 :
        I personally like the following combo to clean up all my code and then go looking for a certain code-block:
        CTRL+K,D (to format the page)
        CTRL+M,O (to collapse to definitions)
        or if you want to expand all definitions: CTRL+M,P