c# - 将 Azure 索引建议作为 Json 响应
问题描述
我计划通过一个实用程序处理 azure 建议,主要任务是列出订阅下所有数据库的所有索引建议。
我已经尝试查看所有可用的文档、所有休息调用和 azure sdk 代码,但无法找到通过 http 请求列出索引的方法。
是否有一个端点可以让我发出 http 请求并获得所有索引建议?
我认为索引建议存储在主数据库中的一个表中,有人可以指定在哪个表下,查看我可以查找建议。
感谢帮助。
解决方案
以下查询将以 JSON 形式返回缺失的索引。
create table #results (target_object_name nvarchar(100), equality_columns nvarchar(100), inequality_columns nvarchar(100), included_columns nvarchar(100))
declare @query_plan as xml
declare @totalimpact as float
declare querycursor CURSOR FAST_FORWARD FOR
SELECT top (50) cast(replace(cast(qp.query_plan as nvarchar(max)),'xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"','') as xml),
qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]' , 'decimal(18,4)') * execution_count AS TotalImpact
FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(sql_handle) st cross apply sys.dm_exec_query_plan(plan_handle) qp
WHERE qp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex[@Database!="m"]') = 1
ORDER BY TotalImpact DESC
OPEN querycursor
FETCH NEXT FROM querycursor
INTO @query_plan, @totalimpact --need to remove the namespace
WHILE @@FETCH_STATUS=0
BEGIN
insert into #results (target_object_name, equality_columns, inequality_columns, included_columns)
SELECT cast(index_node.query('concat(string((./@Database)[1]),".",string((./@Schema)[1]),".",string((./@Table)[1]))') as nvarchar(100)) as target_object_name
,replace(convert(nvarchar(max), index_node.query('for $colgroup in ./ColumnGroup,$col in $colgroup/Column where $colgroup/@Usage = "EQUALITY" return string($col/@Name)')), '] [', '],[') as equality_columns
,replace(convert(nvarchar(max), index_node.query('for $colgroup in ./ColumnGroup,$col in $colgroup/Column where $colgroup/@Usage = "INEQUALITY" return string($col/@Name)')), '] [', '],[') as inequality_columns
,replace(convert(nvarchar(max), index_node.query('for $colgroup in .//ColumnGroup,$col in $colgroup/Column where $colgroup/@Usage = "INCLUDE" return string($col/@Name)')), '] [', '],[') as included_columns
from (select convert(xml, @query_plan) as xml_showplan) as t outer apply xml_showplan.nodes ('//MissingIndexes/MissingIndexGroup/MissingIndex') as missing_indexes(index_node)
FETCH NEXT FROM querycursor
INTO @query_plan, @totalimpact
END
CLOSE querycursor
DEALLOCATE querycursor
select distinct 'Create NonClustered Index IX_' + substring(replace(replace(target_object_name,'[',''),']',''), 0, charindex('.',replace(replace(target_object_name,'[',''),']',''))) +' On ' + target_object_name +
' (' + IsNull(equality_columns,'') +
CASE WHEN equality_columns IS Null And inequality_columns IS Null THEN ',' ELSE '' END + IsNull(inequality_columns, '') + ')' +
CASE WHEN included_columns='' THEN
';'
ELSE
' Include (' + included_columns + ');'
END 'Index'
from #results
FOR JSON PATH
drop table #results
推荐阅读
- html - 如何在 CSS 中对齐左右两个部分?
- git - gitignore 有什么用?
- javascript - 在我将 angualr 5 更新为 7 之后,同时在 angular 1.x 中运行 angular 5 时,无法实例化模块 $$UpgradeModule?
- magento-1.9 - magento 1.9 版 Transactional Email 模板内容大小限制
- node.js - 如何在对话流中制作水平轮播?
- in-app-purchase - 实施自动更新订阅
- java - 如何使用可分页和对象创建缓存键?
- swift - Swift:在 Mac 上检索文件图标时出现问题
- flutter - 如何检查用户名是否已存在于数据库中并验证没有空格的用户名
- javascript - 如何让用户的 gmail 使用我的应用程序脚本?