Performances SQL Server et vue indexée

À l'aide de SQL Server 2008

(Désolé si cela s'avère être un article mais j'essaye de donner autant d'information que possible.)

J'ai plusieurs locations qui contiennent chacun plusieurs départements qui contiennent chacun plusieurs éléments qui peuvent avoir zéro à plusieurs balayages. Chaque balayage se rapporte à une opération spécifique qui peut ou non avoir un time de coupure. Chaque élément appartient également à un package spécifique qui appartient à un travail spécifique qui appartient à un projet spécifique appartenant à un client spécifique. Chaque travail contient un ou plusieurs packages contenant un ou plusieurs éléments.

+=============+ +=============+ | Projects | --> | Clients | +=============+ +=============+ ^ | +=============+ +=============+ | Locations | | Jobs | +=============+ +=============+ ^ ^ | | +=============+ +=============+ +=============+ | Departments | <-- | Items | --> | Packages | +=============+ +=============+ +=============+ ^ | +=============+ +=============+ | Scans | --> | Operations | +=============+ +=============+

Il y a environ 24 000 000 loggings dans la table des éléments et environ 48 000 000 loggings dans la table des parsings. De nouveaux articles sont insérés sporadiquement en masse dans la database tout au long de la journée, généralement dans des dizaines de milliers à la pop. Les nouveaux scans sont insérés en masse toutes les heures, de quelques centaines à quelques centaines de milliers.

Ces tables sont fortement demandées, tranchées et coupées dans tous les sens. J'écrivais des procs stockés très spécifiques mais cela s'est transformé en un cauchemar de maintenance car j'étais à la limite de centaines de processeurs stockés sans fin de site (par exemple quelque chose comme ScansGetDistinctCountByProjectIDByDepartmentIDGroupedByLocationID, ScansGetDistinctCountByPackageIDByDepartmentIDGroupedByLocationID, etc.). changer (ce qui ressemble) presque tous les jours et chaque fois que je dois changer / append / supprimer une colonne, eh bien … je me retrouve au bar.

J'ai donc créé une vue indexée et une poignée de procs generics stockés avec des parameters pour déterminer le filtrage et le groupement. Malheureusement, la performance a coulé dans les canvasttes. Je suppose que la première question est, puisque la performance de sélection est primordiale, dois-je restr avec l'approche spécifique et se battre à travers les changements aux tables sous-jacentes? Ou, peut-on faire quelque chose pour accélérer l'approche vue indexée / requête générique? En plus d'atténuer le cauchemar de l'entretien, j'espérais en fait que la vue indexée améliorerait aussi les performances.

Voici le code pour générer la vue:

 CREATE VIEW [ItemScans] WITH SCHEMABINDING AS SELECT p.ClientID , p.ID AS [ProjectID] , j.ID AS [JobID] , pkg.ID AS [PackageID] , i.ID AS [ItemID] , s.ID AS [ScanID] , s.DateTime , o.Code , o.Cutoff , d.ID AS [DepartmentID] , d.LocationID -- other columns FROM [Projects] AS p INNER JOIN [Jobs] AS j ON p.ID = j.ProjectID INNER JOIN [Packages] AS pkg ON j.ID = pkg.JobID INNER JOIN [Items] AS i ON pkg.ID = i.PackageID INNER JOIN [Scans] AS s ON i.ID = s.ItemID INNER JOIN [Operations] AS o ON s.OperationID = o.ID INNER JOIN [Departments] AS d ON i.DepartmentID = d.ID;

et l'index clusterisé:

 CREATE UNIQUE CLUSTERED INDEX [IDX_ItemScans] ON [ItemScans] ( [PackageID] ASC, [ItemID] ASC, [ScanID] ASC )

Voici l'un des procs generics stockés. Il obtient le nombre d'éléments qui ont été analysés et qui ont un seuil:

 PROCEDURE [ItemsGetFinalizedCount] @FilterBy int = NULL , @ID int = NULL , @FilterBy2 int = NULL , @ID2 sql_variant = NULL , @GroupBy int = NULL WITH RECOMPILE AS BEGIN SELECT CASE @GroupBy WHEN 1 THEN CONVERT(sql_variant, LocationID) WHEN 2 THEN CONVERT(sql_variant, DepartmentID) -- other cases END AS [ID] , COUNT(DISTINCT ItemID) AS [COUNT] FROM [ItemScans] WITH (NOEXPAND) WHERE (@ID IS NULL OR @ID = CASE @FilterBy WHEN 1 THEN ClientID WHEN 2 THEN ProjectID -- other cases END) AND (@ID2 IS NULL OR @ID2 = CASE @FilterBy2 WHEN 1 THEN CONVERT(sql_variant, ClientID) WHEN 2 THEN CONVERT(sql_variant, ProjectID) -- other cases END) AND Cutoff IS NOT NULL GROUP BY CASE @GroupBy WHEN 1 THEN CONVERT(sql_variant, LocationID) WHEN 2 THEN CONVERT(sql_variant, DepartmentID) -- other cases END END

La première fois que j'ai exécuté la requête et regardé le plan d'exécution réel, j'ai créé l'index manquant qu'il a suggéré:

 CREATE NONCLUSTERED INDEX [IX_ItemScans_Counts] ON [ItemScans] ( [Cutoff] ASC ) INCLUDE ([ClientID],[ProjectID],[JobID],[ItemID],[SegmentID],[DepartmentID],[LocationID])

La création de l'index a réduit le time d'exécution à environ cinq secondes, mais cela rest inacceptable (la version «spécifique» de la requête s'exécute en seconde.) J'ai essayé d'append différentes colonnes à l'index plutôt que de les inclure sans gain de performance (Cela n'aide pas vraiment que je n'ai aucune idée de ce que je fais en ce moment.)

Voici le plan de requête:

queryplan

Et voici les détails de cette première search d'index (il semble returnner toutes les lignes de la vue où Cutoff n'est pas NULL):

opération

Un proc générique peut ne pas être une mauvaise idée dans ce cas, mais vous n'avez pas besoin de mettre tous ces cas dans la requête finale comme vous le faites actuellement. J'essaierais de build vos "requêtes spécifiques" en utilisant le SQL dynamic dans votre proc générique, de la même manière que Gail Shaw construit une requête "catch-all" ici:

SQL in the Wild – Requêtes de tous les coins

De cette façon, vous pouvez mettre en cache des plans de requête et utiliser des index comme indiqué dans l'article du blog, et vous devriez être en mesure d'get les mêmes performances de sous-seconde que vous searchz.