{"id":528,"date":"2011-11-01T13:15:00","date_gmt":"2011-11-01T13:15:00","guid":{"rendered":"\/blogs\/paul\/post\/Over-and-under-indexing-how-bad-is-it-out-there.aspx"},"modified":"2019-12-30T17:33:30","modified_gmt":"2019-12-31T01:33:30","slug":"over-and-under-indexing-how-bad-is-it-out-there","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/","title":{"rendered":"Over and under indexing &#8211; how bad is it out there?"},"content":{"rendered":"<p><span style=\"font-family: verdana, geneva; font-size: small;\">Back at the start of August I kicked off a survey (see <\/span><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/survey-nonclustered-index-counts-code-to-run\/\" target=\"_blank\" rel=\"noopener noreferrer\"><span style=\"font-family: verdana, geneva; font-size: small;\">here<\/span><\/a><span style=\"font-family: verdana, geneva; font-size: small;\">)\u00a0that gave you some code to run to produce an aggregate list of the number of tables on your server with different numbers of nonclustered indexes. I got back results from more than 1000 servers across the world &#8211; a big thank you to everyone who sent me data! <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">It&#8217;s taken me a while to get to this post because a) I needed a few hours to set aside to aggregate the comments, txt files, and spreadsheets that people sent and load them into a database; and b) I&#8217;ve been really busy with teaching etc. Finally I&#8217;ve had time this week while at SQL Connections in Las Vegas to put together the results and this post. <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">The winners: <\/span><\/p>\n<ul>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Highest\u00a0number of nonclustered indexes on a single clustered index: 1032 <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Highest\u00a0number of nonclustered indexes on a single heap: 148 <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Highest number of clustered indexes with\u00a0zero nonclustered indexes on a single server: 185237 <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Highest number of\u00a0heaps with\u00a0zero nonclustered indexes on a single server: 88042 <\/span><\/div>\n<\/li>\n<\/ul>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">Wow! <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">Now to some of the details&#8230; <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva;\"><span style=\"font-size: small;\"><strong>Tables\u00a0with\u00a0Zero Nonclustered Indexes<\/strong> <\/span><\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">The two graphs below show the number of servers that have a certain number of tables with zero nonclustered indexes. <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\"><img decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/11\/index1.jpg\" alt=\"\" \/>\u00a0 <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\"><img decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/11\/index2.jpg\" alt=\"\" \/> <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">For the clustered indexes, there is one case I can think of where having zero nonclustered indexes is acceptable: if all queries return\u00a0all columns of the table and the query search predicate for all queries is the cluster key (or a left-based subset of the cluster key). <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">All queries that have a search predicate that does not match the cluster key (or a left-based subset thereof) will be table scans, which can put pressure on the buffer pool (see my post on <\/span><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/page-life-expectancy-isnt-what-you-think\/\" target=\"_blank\" rel=\"noopener noreferrer\"><span style=\"font-family: verdana, geneva; font-size: small;\">Page Life Expectancy<\/span><\/a><span style=\"font-family: verdana, geneva; font-size: small;\">) and lead to contention on the <span style=\"font-family: 'courier new', courier;\">ACCESS_METHODS_DATASET_PARENT<\/span> latch (all manifesting as a high percentage of <span style=\"font-family: 'courier new', courier;\">LATCH_EX<\/span> or <span style=\"font-family: 'courier new', courier;\">PAGEIOLATCH_SH<\/span> and maybe <span style=\"font-family: 'courier new', courier;\">CXPACKET<\/span> waits). <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">For the heaps, all queries are inefficient table scans. Well, efficient if you&#8217;re returning all the rows in the table every time, I suppose :-) <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">Bottom line: tables usually need nonclustered indexes to provide efficient access paths to the data requested by the various queries that your workload performs. <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">There are two things you can do to help find queries that need nonclustered indexes: <\/span><\/p>\n<ol>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Use the missing index DMVs (cautiously!) to determine which nonclustered indexes to create. I use the script posted by Microsoftie Bart Duncan in his <\/span><a href=\"http:\/\/blogs.msdn.com\/b\/bartd\/archive\/2007\/07\/19\/are-you-using-sql-s-missing-index-dmvs.aspx\" target=\"_blank\" rel=\"noopener noreferrer\"><span style=\"font-family: verdana, geneva; font-size: small;\">blog post<\/span><\/a><span style=\"font-family: verdana, geneva; font-size: small;\">. However don&#8217;t just go create all the indexes there. I generally look for Bart&#8217;s &#8220;improvement_measure&#8221; column to be above 100k before I&#8217;ll consider recommending the index to a client (and on systems that already have nonclustered indexes on the table, I&#8217;ll look for index consolidation possibilities). Note also that the missing index DMVs will sometimes add the cluster key as an INCLUDEd column. This is unnecessary but harmless. <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Look directly in the plan cache to find query plans that perform scans. <\/span><span style=\"font-family: verdana, geneva; font-size: small;\">Using the graphical query plan I can see what columns are being searched for and returned from the scans and then create the correct nonclustered indexes for these. <\/span><\/div>\n<\/li>\n<\/ol>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">You can also use the Database Tuning Advisor, but I don&#8217;t use that, personally.<\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">You&#8217;ll be amazed at the performance difference by having a good set of nonclustered indexes. <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">But don&#8217;t go overboard otherwise you could detrimentally affect performance by having too many nonclustered indexes&#8230; <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva;\"><span style=\"font-size: small;\"><strong>Tables with Nonclustered Indexes<\/strong> <\/span><\/span><\/p>\n<p><span style=\"font-size: small;\">The two graphs below show the number of tables that have a certain number of nonclustered indexes.<\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\"><img decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/11\/index3.jpg\" alt=\"\" \/>\u00a0 <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\"><img decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/11\/index4.jpg\" alt=\"\" \/> <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">The data shows that it is most common to have 10 nonclustered indexes or less, but even that may be too many. <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">Every nonclustered index incurs overheard when a table row is inserted or deleted, or when any of the nonclustered index key columns (or INCLUDEd columns) are updated. Filtered indexes in SQL 2008+ are a special case, obviously. The overheard takes a few forms: <\/span><\/p>\n<ul>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Buffer pool (i.e. memory and I\/O) overhead of having to search the nonclustered index for the record to update. <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">I\/O overhead of having to flush the updated index page to disk during the next checkpoint <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Log space for the log records generated by the operation on the nonclustered index <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Resource overheard for those log records in terms of: <\/span><\/div>\n<ul>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Time to be read by the replication\/CDC log reader Agent job <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Time to be read by log backups (and data backups, if applicable) <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Time and bandwidth to send the log records to a database mirroring mirror <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Disk space to store the log records in a log backup <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Time to restore the log records on a log shipping secondary or during a disaster recovery <\/span><\/div>\n<\/li>\n<\/ul>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Locking overhead <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Page split overhead <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Time to consistency check <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Time to examine for fragmentation <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Time to update statistics <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Disk and backup space overhead <\/span><\/div>\n<\/li>\n<\/ul>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">As you can see, nonclustered indexes can be a big burden on a system &#8211; you have to be careful when creating them so that\u00a0you don&#8217;t have too many. <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">There are three things you can do to reduce the number of nonclustered indexes on your system: <\/span><\/p>\n<ol>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Use the <span style=\"font-family: 'courier new', courier;\">sys.dm_db_index_usage_stats<\/span> DMV to find indexes that are only being updated. I&#8217;ve blogged about this <\/span><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/indexes-from-every-angle-how-can-you-tell-if-an-index-is-being-used\/\" target=\"_blank\" rel=\"noopener noreferrer\"><span style=\"font-family: verdana, geneva; font-size: small;\">here<\/span><\/a><span style=\"font-family: verdana, geneva; font-size: small;\">. Again, be careful though. Just because an index hasn&#8217;t been used doesn&#8217;t mean it should be dropped. It may be used only infrequently, but it&#8217;s critical when it is used. Ideally you need to look at the output from the DMV after an entire business cycle has passed. Even then, be careful about dropping indexes that are enforcing uniqueness constraints as these can be used by the query optimizer without reflecting any user seeks or scans. <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Remove duplicate nonclustered indexes. Kimberly blogged code to find duplicate indexes <\/span><a href=\"http:\/\/www.sqlskills.com\/blogs\/kimberly\/removing-duplicate-indexes\/\"><span style=\"font-family: verdana, geneva; font-size: small;\">here<\/span><\/a><span style=\"font-family: verdana, geneva; font-size: small;\">. There is no downside to doing this. <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">Look for consolidation possibilities. Kimberly has code to show you all the key and INCLUDEd columns <\/span><a href=\"http:\/\/www.sqlskills.com\/blogs\/kimberly\/use-this-sp_helpindex-rewrites\/\" target=\"_blank\" rel=\"noopener noreferrer\"><span style=\"font-family: verdana, geneva; font-size: small;\">here<\/span><\/a><span style=\"font-family: verdana, geneva; font-size: small;\">. This is harder and is more of an art than a science. You&#8217;re looking for indexes where you can combine two or more indexes into one without affecting the ability of the optimizer to use them for the various queries that the non-consolidated indexes used to help. <\/span><\/div>\n<ul>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">For example, an index on <span style=\"font-family: 'courier new', courier;\">c1, c2, c3 INCLUDE c4, c5<\/span> can be combined with an index on <span style=\"font-family: 'courier new', courier;\">c1, c2, c3 INCLUDE c4, c6<\/span>. But only as long as <span style=\"font-family: 'courier new', courier;\">c6<\/span> isn&#8217;t a really wide column that would affect the performance of the queries using the first index. <\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-family: verdana, geneva; font-size: small;\">A harder example: would you consolidate an index <span style=\"font-family: 'courier new', courier;\">c1, c2 INCLUDE c3<\/span> with an index on <span style=\"font-family: 'courier new', courier;\">c1, c3 INCLUDE c2<\/span>? Possibly. It would depend on what the indexes are being used for in queries.\u00a0 <\/span><\/div>\n<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<p><span style=\"font-family: verdana, geneva;\"><span style=\"font-size: small;\"><strong>Summary<\/strong> <\/span><\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">Nonclustered indexes are essential for the performance of most workloads, but how many should you have? I often get someone in a class that Kimberly&#8217;s teaching on indexes to ask her &#8220;what&#8217;s the optimum number of indexes a table should have?&#8221; because I know it&#8217;s a nonsensical question. (And she reciprocates by getting someone to ask me &#8220;how long will CHECKDB take?&#8221; :-) <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">The answer is a big, fat &#8220;it depends&#8221; &#8211; and hopefully I&#8217;ve given you some pointers to figure it out for yourself. <\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">I&#8217;ll continue this series of posts with more surveys and code that you can use on your systems to gauge the health of your indexes.<\/span><\/p>\n<p><span style=\"font-size: small;\">Hope this helps!<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Back at the start of August I kicked off a survey (see here)\u00a0that gave you some code to run to produce an aggregate list of the number of tables on your server with different numbers of nonclustered indexes. I got back results from more than 1000 servers across the world &#8211; a big thank you [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47,66,91],"tags":[],"class_list":["post-528","post","type-post","status-publish","format-standard","hentry","category-indexes-from-every-angle","category-performance-tuning","category-surveys"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Over and under indexing - how bad is it out there? - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Over and under indexing - how bad is it out there? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Back at the start of August I kicked off a survey (see here)\u00a0that gave you some code to run to produce an aggregate list of the number of tables on your server with different numbers of nonclustered indexes. I got back results from more than 1000 servers across the world &#8211; a big thank you [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2011-11-01T13:15:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-12-31T01:33:30+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/11\/index1.jpg\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/\",\"name\":\"Over and under indexing - how bad is it out there? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/11\/index1.jpg\",\"datePublished\":\"2011-11-01T13:15:00+00:00\",\"dateModified\":\"2019-12-31T01:33:30+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/11\/index1.jpg\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/11\/index1.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Over and under indexing &#8211; how bad is it out there?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Over and under indexing - how bad is it out there? - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/","og_locale":"en_US","og_type":"article","og_title":"Over and under indexing - how bad is it out there? - Paul S. Randal","og_description":"Back at the start of August I kicked off a survey (see here)\u00a0that gave you some code to run to produce an aggregate list of the number of tables on your server with different numbers of nonclustered indexes. I got back results from more than 1000 servers across the world &#8211; a big thank you [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/","og_site_name":"Paul S. Randal","article_published_time":"2011-11-01T13:15:00+00:00","article_modified_time":"2019-12-31T01:33:30+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/11\/index1.jpg","type":"","width":"","height":""}],"author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/","name":"Over and under indexing - how bad is it out there? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/11\/index1.jpg","datePublished":"2011-11-01T13:15:00+00:00","dateModified":"2019-12-31T01:33:30+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/11\/index1.jpg","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2011\/11\/index1.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/over-and-under-indexing-how-bad-is-it-out-there\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Over and under indexing &#8211; how bad is it out there?"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/528","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=528"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/528\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=528"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=528"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=528"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}