{"id":2072,"date":"2012-04-29T08:11:43","date_gmt":"2012-04-29T08:11:43","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2072"},"modified":"2012-05-09T10:02:31","modified_gmt":"2012-05-09T10:02:31","slug":"%e5%be%85%e5%bb%ba%e7%ab%8b%e7%9a%84%e7%b4%a2%e5%bc%95%e5%8f%8a%e8%a6%81%e5%88%a0%e9%99%a4%e7%9a%84%e7%b4%a2%e5%bc%95-2","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/04\/29\/%e5%be%85%e5%bb%ba%e7%ab%8b%e7%9a%84%e7%b4%a2%e5%bc%95%e5%8f%8a%e8%a6%81%e5%88%a0%e9%99%a4%e7%9a%84%e7%b4%a2%e5%bc%95-2\/","title":{"rendered":"\u5f85\u5efa\u7acb\u7684\u7d22\u5f15\u53ca\u8981\u5220\u9664\u7684\u7d22\u5f15"},"content":{"rendered":"

1\uff0c\u67e5\u8be2\u5f85\u5efa\u7acb\u7684\u7d22\u5f15\u3002sys.dm_db_missing_index_groups,sql\u5982\u4e0b\uff1a<\/p>\n

\r\n\r\n\r\nSELECT * FROM (\r\n\tSELECT TOP 50\r\n\tROUND(s.avg_total_user_cost * (s.avg_user_impact\/100) *\r\n\t(s.user_seeks + s.user_scans),0) AS [improvement_measure]\r\n\t, s.avg_user_impact\r\n\t, d.statement AS TableName\r\n\t, d.equality_columns\r\n\t, d.inequality_columns\r\n\t, d.included_columns,\r\n\t  'CREATE INDEX [missing_index_' + CONVERT (varchar, g.index_group_handle) + '_' + CONVERT (varchar, d.index_handle)\r\n\t\t  + '_' + LEFT (PARSENAME(d.statement, 1), 32) + ']'\r\n\t\t  + ' ON ' + d.statement\r\n\t\t  + ' (' + ISNULL (d.equality_columns,'')\r\n\t\t\t+ CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ',' ELSE '' END\r\n\t\t\t+ ISNULL (d.inequality_columns, '')\r\n\t\t  + ')'\r\n\t\t  + ISNULL (' INCLUDE (' + d.included_columns + ')', '') AS create_index_statement\r\n\tFROM sys.dm_db_missing_index_groups g\r\n\tINNER JOIN sys.dm_db_missing_index_group_stats s\r\n\tON s.group_handle = g.index_group_handle\r\n\tINNER JOIN sys.dm_db_missing_index_details d\r\n\tON d.index_handle = g.index_handle\r\n\tORDER BY improvement_measure DESC)M\r\nWHERE m.improvement_measure > 10000 --\u5927\u4e8e10000\u8005\u9700\u8981\u91cd\u5efa\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n<\/pre>\n
\n2\uff0c\u6ce8\uff1a1)\uff0cavg_user_impact\uff0c\u7528\u6237\u67e5\u8be2\u53ef\u80fd\u83b7\u5f97\u7684\u5e73\u5747\u767e\u5206\u6bd4\u6536\u76ca\uff0c\u56e0\u662f\u6570\u5b57\uff0c\u6545\u8981\u9664\u4ee5100\uff0c\u4ee5\u53d6\u5f97\u53ef\u83b7\u53d6\u7684\u63d0\u9ad8\u6027\u80fd\u7684\u500d\u6570\u30022)\uff0c\u6b64\u65b9\u6cd5\u6709\u5c40\u9650\u6027\uff0c\u6307\u5b9a\u7684\u5efa\u7acb\u7d22\u5f15\u5217\u7684\u5e76\u4e0d\u90fd\u662f\u6b63\u786e\u7684\uff0c\u8981\u624b\u5de5\u5224\u5b9a<\/a>\u3002<\/p>\n
3\uff0c\u67e5\u8be2\u53ef\u5220\u9664\u7684\u7d22\u5f15\uff0c\u5f88\u5c11\u4f7f\u7528\u3002<\/p>\n
\r\n   \r\n\r\nSelect Top 30 database_id, t.object_id, ix.index_id,user_updates, (user_seeks+user_scans) ,t.name,ix.name,sc.name\r\n    from sys.dm_db_index_usage_stats ius\r\n    JOIN sys.tables t ON ius.object_id = t.object_id\r\n    JOIN sys.indexes ix ON t.object_id = ix.object_id AND ix.index_id = ius.index_id\r\n    JOIN sys.index_columns ixc ON t.object_id = ixc.object_id AND ix.index_id = ixc.index_id\r\n    JOIN sys.columns sc ON t.object_id = sc.object_id AND ixc.column_id = sc.column_id\r\n    where user_updates > 10 * (user_seeks+user_scans)\r\n    and ius.index_id > 1\r\n    AND CHARINDEX('merge',t.name) <1\r\n    order by user_updates \/ (user_seeks+user_scans+1) DESC\r\n    \r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
1\uff0c\u67e5\u8be2\u5f85\u5efa\u7acb\u7684\u7d22\u5f15\u3002sys.dm_db_missing_index_groups,sql\u5982\u4e0b\uff1a SELECT […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2072","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2072","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2072"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2072\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2072"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2072"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2072"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}