![\"\"](\"https:\/\/powertuning.com.br\/blog\/wp-content\/uploads\/2024\/03\/img01.png\")
<\/p>\nApresentamos aqui um caso curioso que parece ser um bug do Microsoft IntelliSense que faz ele consumir recursos consider\u00e1veis do ambiente. Antes de entrarmos nos detalhes, vamos dar uma r\u00e1pida introdu\u00e7\u00e3o sobre o assunto.<\/p>\n
O IntelliSense \u00e9 uma ferramenta legal que vem embutida em editores como o SQL Server Management Studio (SSMS) e o Azure Data Studio (ADS). Ele \u00e9 tipo o ajudante invis\u00edvel que completa os comandos para voc\u00ea, aponta erros de sintaxe e, no geral, deixa a vida do desenvolvedor mais f\u00e1cil.<\/p>\n
Dependendo do que voc\u00ea faz nessas interfaces gr\u00e1ficas, o IntelliSense entra em a\u00e7\u00e3o. Por exemplo, quando voc\u00ea vai modificar uma procedure, ele pode aparecer:<\/p>\n
<\/p>\n
![\"\"](\"https:\/\/powertuning.com.br\/blog\/wp-content\/uploads\/2024\/03\/img02.png\")
<\/p>\n
Tendo essa base vamos ao nosso caso. Est\u00e1vamos usando o SSMS 19.x e trabalhando num Azure SQL Database. Importante destacar o tipo de ambiente pois cada um pode ter suas particularidades.<\/p>\n
Tent\u00e1vamos modificar uma procedure pela interface usando o bot\u00e3o “Modify” no SSMS ou ADS. At\u00e9 a\u00ed tudo bem, mas quando o script aparecia na tela, o IntelliSense gerava uma sess\u00e3o para buscar alguns dados (provavelmente para guardar em cache e validar a query). O problema era que cada uma dessas sess\u00f5es consumia cerca 10% dos DTUs do cliente. Podemos observar o consumo constante com a WhoIsActive usando o @delta_interval=1:<\/p>\n
![\"\"](\"https:\/\/powertuning.com.br\/blog\/wp-content\/uploads\/2024\/03\/img03.png\")
<\/p>\n
Para cada usu\u00e1rio era uma nova sess\u00e3o, tivemos ac\u00famulos de 50% do consumo das DTUs. N\u00e3o adiantava dar “KILL” nessas sess\u00f5es, porque ele abria outra sess\u00e3o no lugar. At\u00e9 fechar a aba que gerou a query seguido do Kill n\u00e3o adiantava, as sess\u00f5es s\u00f3 eram encerradas com o fechamento do SSMS ou ADS por completo.<\/p>\n
Tentamos simular em outros ambientes, mas n\u00e3o conseguimos. Em outros ambientes j\u00e1 tivemos casos de planos de execu\u00e7\u00e3o ruins causados pelo Job Manager, e conseguimos resolver atualizando umas estat\u00edsticas relacionadas ao problema. Por\u00e9m, devido \u00e0s limita\u00e7\u00f5es do ambiente n\u00e3o conseguimos seguir essa linha de racioc\u00ednio.<\/p>\n
Parece que alguma particularidade do ambiente do cliente contribu\u00eda para o problema. Depois de alguns testes, chegamos a algumas conclus\u00f5es:<\/p>\n
Pelos testes acreditamos que alguma particularidade do ambiente contribu\u00eda para esse Bug do IntelliSense presente nas vers\u00f5es 19.x.<\/p>\n
Com o SSMS atualizado, o cliente segue sem problemas. Se o problema voltar a acontecer, iremos coletar as informa\u00e7\u00f5es necess\u00e1rias e reportar pra Microsoft, assim podemos ter certeza se \u00e9 realmente um Bug.<\/p>\n
Para os aventureiros segue a query que o IntelliSense ficava agarrado:<\/p>\n
(\r\n @_msparam_0 nvarchar(4000),\r\n @_msparam_1 nvarchar(4000),\r\n @_msparam_2 nvarchar(4000)\r\n)\r\nSELECT\r\n clmns.name AS [Name],\r\n clmns.column_id AS [ID],\r\n clmns.is_nullable AS [Nullable],\r\n clmns.is_computed AS [Computed],\r\n CAST(ISNULL(cik.index_column_id, 0) AS bit) AS [InPrimaryKey],\r\n clmns.is_ansi_padded AS [AnsiPaddingStatus],\r\n CAST(clmns.is_rowguidcol AS bit) AS [RowGuidCol],\r\n CAST(ISNULL(cc.is_persisted, 0) AS bit) AS [IsPersisted],\r\n ISNULL(clmns.collation_name, N'') AS [Collation],\r\n CAST(\r\n ISNULL(\r\n (\r\n select\r\n TOP 1 1\r\n from\r\n sys.foreign_key_columns AS colfk\r\n where\r\n colfk.parent_column_id = clmns.column_id\r\n and colfk.parent_object_id = clmns.object_id\r\n ),\r\n 0\r\n ) AS bit\r\n ) AS [IsForeignKey],\r\n clmns.is_identity AS [Identity],\r\n CAST(ISNULL(ic.seed_value, 0) AS numeric(38)) AS [IdentitySeedAsDecimal],\r\n CAST(ISNULL(ic.increment_value, 0) AS numeric(38)) AS [IdentityIncrementAsDecimal],\r\n (\r\n case\r\n when clmns.default_object_id = 0 then N''\r\n when d.parent_object_id > 0 then N''\r\n else d.name\r\n end\r\n ) AS [Default],\r\n (\r\n case\r\n when clmns.default_object_id = 0 then N''\r\n when d.parent_object_id > 0 then N''\r\n else schema_name(d.schema_id)\r\n end\r\n ) AS [DefaultSchema],\r\n ISNULL(dc.Name, N'') AS [DefaultConstraintName],\r\n (\r\n case\r\n when clmns.rule_object_id = 0 then N''\r\n else r.name\r\n end\r\n ) AS [Rule],\r\n (\r\n case\r\n when clmns.rule_object_id = 0 then N''\r\n else schema_name(r.schema_id)\r\n end\r\n ) AS [RuleSchema],\r\n CAST(\r\n ISNULL(\r\n COLUMNPROPERTY(clmns.object_id, clmns.name, N'IsDeterministic'),\r\n 0\r\n ) AS bit\r\n ) AS [IsDeterministic],\r\n CAST(\r\n ISNULL(\r\n COLUMNPROPERTY(clmns.object_id, clmns.name, N'IsPrecise'),\r\n 0\r\n ) AS bit\r\n ) AS [IsPrecise],\r\n ISNULL(ic.is_not_for_replication, 0) AS [NotForReplication],\r\n CAST(\r\n COLUMNPROPERTY(\r\n clmns.object_id,\r\n clmns.name,\r\n N'IsFulltextIndexed'\r\n ) AS bit\r\n ) AS [IsFullTextIndexed],\r\n CAST(clmns.encryption_type AS int) AS [EncryptionType],\r\n clmns.encryption_algorithm_name AS [EncryptionAlgorithm],\r\n clmns.column_encryption_key_id AS [ColumnEncryptionKeyID],\r\n ceks.name AS [ColumnEncryptionKeyName],\r\n CAST(clmns.is_filestream AS bit) AS [IsFileStream],\r\n CAST(clmns.is_sparse AS bit) AS [IsSparse],\r\n CAST(clmns.is_column_set AS bit) AS [IsColumnSet],\r\n usrt.name AS [DataType],\r\n s1clmns.name AS [DataTypeSchema],\r\n ISNULL(baset.name, N'') AS [SystemType],\r\n CAST(\r\n CASE\r\n WHEN baset.name IN (N'nchar', N'nvarchar')\r\n AND clmns.max_length <> -1 THEN clmns.max_length \/ 2\r\n ELSE clmns.max_length\r\n END AS int\r\n ) AS [Length],\r\n CAST(clmns.precision AS int) AS [NumericPrecision],\r\n CAST(clmns.scale AS int) AS [NumericScale],\r\n ISNULL(xscclmns.name, N'') AS [XmlSchemaNamespace],\r\n ISNULL(s2clmns.name, N'') AS [XmlSchemaNamespaceSchema],\r\n ISNULL(\r\n (\r\n case\r\n clmns.is_xml_document\r\n when 1 then 2\r\n else 1\r\n end\r\n ),\r\n 0\r\n ) AS [XmlDocumentConstraint],\r\n CASE\r\n WHEN usrt.is_table_type = 1 THEN N'structured'\r\n ELSE N''\r\n END AS [UserType],\r\n clmns.generated_always_type AS [GeneratedAlwaysType],\r\n CAST(clmns.is_hidden AS bit) AS [IsHidden],\r\n CAST(clmns.is_dropped_ledger_column AS bit) AS [IsDroppedLedgerColumn],\r\n CAST(clmns.is_masked AS bit) AS [IsMasked],\r\n CAST(\r\n CASE\r\n WHEN [sc].[label] IS NOT NULL\r\n or [sc].[label_id] IS NOT NULL\r\n or [sc].[information_type] IS NOT NULL\r\n or [sc].[information_type_id] IS NOT NULL\r\n or [sc].[rank] IS NOT NULL THEN 1\r\n ELSE 0\r\n END AS bit\r\n ) AS [IsClassified],\r\n ISNULL(clmns.graph_type, 0) AS [GraphType]\r\nFROM\r\n sys.tables AS tbl\r\n INNER JOIN sys.all_columns AS clmns ON clmns.object_id = tbl.object_id\r\n LEFT OUTER JOIN sys.indexes AS ik ON ik.object_id = clmns.object_id\r\n and 1 = ik.is_primary_key\r\n LEFT OUTER JOIN sys.index_columns AS cik ON cik.index_id = ik.index_id\r\n and cik.column_id = clmns.column_id\r\n and cik.object_id = clmns.object_id\r\n and 0 = cik.is_included_column\r\n LEFT OUTER JOIN sys.computed_columns AS cc ON cc.object_id = clmns.object_id\r\n and cc.column_id = clmns.column_id\r\n LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id\r\n and ic.column_id = clmns.column_id\r\n LEFT OUTER JOIN sys.objects AS d ON d.object_id = clmns.default_object_id\r\n LEFT OUTER JOIN sys.default_constraints as dc ON clmns.default_object_id = dc.object_id\r\n LEFT OUTER JOIN sys.objects AS r ON r.object_id = clmns.rule_object_id\r\n LEFT OUTER JOIN sys.column_encryption_keys AS ceks ON (\r\n ceks.column_encryption_key_id = clmns.column_encryption_key_id\r\n )\r\n LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id\r\n LEFT OUTER JOIN sys.schemas AS s1clmns ON s1clmns.schema_id = usrt.schema_id\r\n LEFT OUTER JOIN sys.types AS baset ON (\r\n baset.user_type_id = clmns.system_type_id\r\n and baset.user_type_id = baset.system_type_id\r\n )\r\n or (\r\n (baset.system_type_id = clmns.system_type_id)\r\n and (baset.user_type_id = clmns.user_type_id)\r\n and (baset.is_user_defined = 0)\r\n and (baset.is_assembly_type = 1)\r\n )\r\n LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns ON xscclmns.xml_collection_id = clmns.xml_collection_id\r\n LEFT OUTER JOIN sys.schemas AS s2clmns ON s2clmns.schema_id = xscclmns.schema_id\r\n LEFT OUTER JOIN sys.sensitivity_classifications AS sc ON sc.major_id = clmns.object_id\r\n and sc.minor_id = clmns.column_id\r\nWHERE\r\n (clmns.name = @_msparam_0)\r\n and(\r\n (\r\n tbl.name = @_msparam_1\r\n and SCHEMA_NAME(tbl.schema_id) = @_msparam_2\r\n )\r\n )<\/pre>\nSe algu\u00e9m j\u00e1 passou por isso ou tem alguma sugest\u00e3o sobre a causa, deixa a\u00ed nos coment\u00e1rios, ser\u00e1 de grande ajuda!<\/p>\n","protected":false},"excerpt":{"rendered":"
Apresentamos aqui um caso curioso que parece ser um bug do Microsoft IntelliSense que faz ele consumir recursos consider\u00e1veis do ambiente. Antes de entrarmos nos detalhes, vamos dar uma r\u00e1pida introdu\u00e7\u00e3o sobre o assunto. O IntelliSense \u00e9 uma ferramenta legal que vem embutida em editores como o SQL Server Management Studio (SSMS) e o Azure…
Leia mais<\/a><\/p>\n","protected":false},"author":17,"featured_media":3024,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"content-type":"","_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[62,63],"tags":[104,153,150,154,151,152],"class_list":["post-3012","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-casos-reais-de-clientes","category-sql-server","tag-azure","tag-azure-data-studio","tag-bug","tag-dtu","tag-intellisense","tag-ssms"],"jetpack_featured_media_url":"https:\/\/powertuning.com.br\/blog\/wp-content\/uploads\/2024\/03\/Novidade-no-blog46.png","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/posts\/3012","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/users\/17"}],"replies":[{"embeddable":true,"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/comments?post=3012"}],"version-history":[{"count":8,"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/posts\/3012\/revisions"}],"predecessor-version":[{"id":3023,"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/posts\/3012\/revisions\/3023"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/media\/3024"}],"wp:attachment":[{"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/media?parent=3012"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/categories?post=3012"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/powertuning.com.br\/blog\/wp-json\/wp\/v2\/tags?post=3012"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}