Пример #1
0
        public PackageDependents GetPackageDependents(string id)
        {
            if (string.IsNullOrWhiteSpace(id))
            {
                throw new ArgumentNullException(nameof(id));
            }

            PackageDependents result = new PackageDependents();

            result.TopPackages       = GetListOfDependents(id);
            result.TotalPackageCount = GetDependentCount(id);
            return(result);
        }
Пример #2
0
        public PackageDependents GetPackageDependents(string id)
        {
            if (string.IsNullOrWhiteSpace(id))
            {
                throw new ArgumentNullException(nameof(id));
            }

            PackageDependents result = new PackageDependents();

            using (_entitiesContext.WithQueryHint("RECOMPILE"))
            {
                result.TopPackages       = GetListOfDependents(id);
                result.TotalPackageCount = GetDependentCount(id);
            }

            return(result);
        }
        public PackageDependents GetPackageDependents(string id)
        {
            if (string.IsNullOrWhiteSpace(id))
            {
                throw new ArgumentNullException(nameof(id));
            }

            PackageDependents result = new PackageDependents();

            // We use OPTIMIZE FOR UNKNOWN by default here because there are distinct 2-3 query plans that may be
            // selected via SQL Server parameter sniffing. Because SQL Server caches query plans, this means that the
            // first parameter plus query combination that SQL sees defines which query plan is selected and cached for
            // all subsequent parameter values of the same query. This could result in a non-optimal query plan getting
            // cached depending on what package ID is viewed first. Using OPTIMIZE FOR UNKNOWN causes a predictable
            // query plan to be cached.
            //
            // For example, the query plan for Newtonsoft.Json is very good for that specific parameter value since
            // there are so many package dependents but the same query plan takes a very long time for packages with few
            // or no dependents. The query plan for "UNKNOWN" (that is a package ID with unknown SQL Server statistic)
            // behaves somewhat poorly for Newtonsoft.Json (2-5 seconds) but very well for the vast majority of
            // packages. Because we have in-memory caching above this layer, OPTIMIZE FOR UNKNOWN is acceptable other
            // unconfigured cases similar to Newtonsoft.Json because the extra cost of the non-optimal query plan is
            // amortized over many, many page views. For the long tail packages, in-memory caching is less effective
            // (low page views) so an optimal query should be selected for this category.
            //
            // For the cases where RECOMPILE is known to perform the best, the package ID can be added to the query hint
            // configuration JSON file from the content object service. This should only be done when the following
            // things are true:
            //
            //   1. The overhead of SQL Server recompile is worth it. We have seen the overhead to be 5-50ms.
            //   2. SQL Server has up to date statistics which will lead to the proper query plan being selected.
            //   3. SQL Server actually picks the proper query plan. We have observed cases where this does not happen
            //      even with up-to-date statistics.
            //
            var useRecompile = _contentObjectService.QueryHintConfiguration.ShouldUseRecompileForPackageDependents(id);

            using (_entitiesContext.WithQueryHint(useRecompile ? "RECOMPILE" : "OPTIMIZE FOR UNKNOWN"))
            {
                result.TopPackages       = GetListOfDependents(id);
                result.TotalPackageCount = GetDependentCount(id);
            }

            return(result);
        }