Пример #1
0
        public static bool TryFixByHandle(SpyMonitor.SpyOptions options, ShowplanEvent showplanEvent)
        {
            var planByHandle = GetPlanByHandle(options, showplanEvent.PlanHandle);

            if (planByHandle == null)
            {
                return(false);
            }


            showplanEvent.Showplan     = ApplyBetterStatement(showplanEvent.ShowplanXml.RawString, planByHandle, out string statementText);
            showplanEvent.SqlStatement = statementText;
            return(true);
        }
Пример #2
0
        private static string GetPlanByHandle(SpyMonitor.SpyOptions options, byte[] planHandle)
        {
            var handleHex = BitConverter.ToString(planHandle).Replace("-", "");

            Log.Logger.Verbose("Looking up better plan for query handle {handle}", handleHex);

            if (Cache[handleHex] is string cached)
            {
                Log.Logger.Verbose("Found query handle {handle} in cache", handleHex);
                return(cached);
            }

            const string findByPlanHandle = "select query_plan from sys.dm_exec_query_plan(@handle)";

            var connString = ConnectionStringBuilder.BuildWithAppName(
                options.Database,
                options.Server,
                "ShowplanSpy",
                options.UserName,
                options.Password);

            using (var conn = new SqlConnection(connString))
            {
                conn.Open();
                using (var command = new SqlCommand(findByPlanHandle, conn))
                {
                    command.Parameters.AddWithValue("handle", planHandle);
                    var newPlan = command.ExecuteScalar();
                    if (!(newPlan is string showPlanXml))
                    {
                        return(null);
                    }

                    Log.Logger.Verbose("Found better plan for {handle} in sys tables", handleHex);
                    Cache.Add(handleHex, showPlanXml, DateTimeOffset.Now.AddMinutes(5));
                    return(showPlanXml);
                }
            }
        }