Grand conseil de table (SQL Server)

J'éprouve une lenteur énorme quand j'accède à l'une de mes tables et j'ai besoin de conseils sur le ré-affacturage. Désolé si ce n'est pas la bonne zone pour ce genre de chose.

Je travaille sur un projet qui vise à rendre count des statistics de performance du server pour nos servers internes. Je traite les journaux de performance Windows tous les soirs (12 servers, 10 counturs de performance et la journalisation toutes les 15 secondes). Je stocke datatables dans un tableau comme suit:

CREATE TABLE [dbo].[log]( [id] [int] IDENTITY(1,1) NOT NULL, [logfile_id] [int] NOT NULL, [test_id] [int] NOT NULL, [timestamp] [datetime] NOT NULL, [value] [float] NOT NULL, CONSTRAINT [PK_log] PRIMARY KEY CLUSTERED ( [id] ASC )WITH FILLFACTOR = 90 ON [PRIMARY] ) ON [PRIMARY]

Il y a actuellement 16 529 131 rangées et il continuera de croître.

J'accède aux données pour produire des rapports et créer des charts à partir de coldfusion comme ceci:

 SET NOCOUNT ON CREATE TABLE ##RowNumber ( RowNumber int IDENTITY (1, 1), log_id char(9) ) INSERT ##RowNumber (log_id) SELECT l.id FROM log l, logfile lf WHERE lf.server_id = #arguments.server_id# and l.test_id = #arguments.test_id#" and l.timestamp >= #arguments.report_from# and l.timestamp < #arguments.report_to# and l.logfile_id = lf.id order by l.timestamp asc select rn.RowNumber, l.value, l.timestamp from log l, logfile lf, ##RowNumber rn where lf.server_id = #arguments.server_id# and l.test_id = #arguments.test_id# and l.logfile_id = lf.id and rn.log_id = l.id and ((rn.rownumber % #modu# = 0) or (rn.rownumber = 1)) order by l.timestamp asc DROP TABLE ##RowNumber SET NOCOUNT OFF

(pour ne pas les devs #value# insère la value et ## maps pour # )

Je crée fondamentalement une table temporaire de sorte que je puisse employer le nombre de rown pour sélectionner chaque x rangées. De cette façon, je ne sélectionne que la quantité de lignes que je peux afficher. Cela aide, mais c'est encore très lent.

SQL Server Management Studio me dit que mes index sont les suivants (je n'ai pratiquement aucune connaissance sur l'utilisation correcte de l'index):

 IX_logfile_id (Non-Unique, Non-Clustered) IX_test_id (Non-Unique, Non-Clustered) IX_timestamp (Non-Unique, Non-Clustered) PK_log (Clustered)

Je serais très reconnaissant à tous ceux qui pourraient donner un conseil qui pourrait m'aider à accélérer les choses un peu. Cela ne me dérange pas de réorganiser les choses et j'ai le contrôle complet du projet (peut-être pas sur le matériel du server si).

Bravo (désolé pour le long post)

Votre problème est que vous avez choisi une mauvaise key en cluster. Personne n'est jamais intéressé par la récupération d'une valeur de journal particulière par ID. Votre système est comme tout ce que j'ai vu, alors toutes les requêtes vont requestr:

tous les counturs pour tous les servers sur une plage de dates
des valeurs de countur spécifiques sur tous les servers pour une gamme de dates
tous les counturs pour un server sur une plage de dates
countur spécifique pour un server spécifique sur une plage de dates

Compte tenu de la taille de la table, tous vos index non clusterisés sont inutiles. Ils vont tous atteindre le sharepoint basculement de l' indice , c'est garanti, alors ils pourraient tout aussi bien ne pas exister. Je suppose que tous vos index non clusterisés sont définis comme un simple index sur le champ dans le nom, sans champs include.

Je vais prétendre que je connais vos besoins. Vous devez oublier le bon sens sur le stockage et dupliquer toutes vos données dans tous les index non clusterisés. Voici mon conseil:

Déposez l'index clusterisé sur [id], c'est aussi inutile que c'est le cas.
Organisez la table avec un index cluster (logfile_it, test_id, timestamp).
L'index non-clusterd sur (test_id, logfile_id, timestamp) inclut (valeur)
L'index CN sur (id_journal, horodatage) inclut (valeur)
L'index NC sur (test_id, timestamp) inclut (valeur)
L'index CN sur (horodatage) inclut (valeur)
Ajoutez des tâches de maintenance pour réorganiser périodiquement tous les index car ils sont sujets à la fragmentation

L'index clusterisé couvre l'interrogation «historique de la valeur de countur spécifique sur une machine spécifique». Les index non groupés couvrent diverses autres requêtes possibles (tous les counturs d'une machine dans le time, countur spécifique sur toutes les machines dans le time, etc.).

Vous remarquez que je n'ai rien commenté sur votre script de requête. C'est parce qu'il n'y a rien dans le monde que vous puissiez faire pour que les requêtes s'exécutent plus rapidement sur la structure de table que vous avez.

Maintenant, une chose que vous ne devriez pas faire est de mettre en pratique mes conseils. J'ai dit que je vais prétendre que je connais tes exigences. Mais je ne le fais pas vraiment. Je viens de donner un exemple d'une structure possible. Ce que vous devriez vraiment faire est d'étudier le sujet et de find la structure d'index correcte pour vos besoins:

Directives générales de design d'index .
Notions de base de design d'index
Index avec colonnes incluses
Types de requête et index

Aussi un google sur «l'index de couverture» fera apparaître beaucoup de bons articles.

Et bien sûr, à la fin de la journée, le stockage n'est pas gratuit, donc vous devrez équilibrer l'exigence d'avoir un index non clusterisé sur chaque combinaison possible avec le besoin de garder la taille de la database en échec. Heureusement, vous avez une table très petite et étroite, donc la duplication sur de nombreux index non clusterisés n'est pas une grosse affaire. Aussi, je ne serais pas préoccupé par les performances d'insertion, 120 counturs à 15 secondes chacun signifie 8-9 insertions par seconde, ce qui n'est rien.

Quelques choses me viennent à l'esprit.

Avez-vous besoin de garder autant de données? Si ce n'est pas le cas, envisagez de créer une table d'archive si vous voulez la conserver (mais ne la créez pas simplement pour la joindre à la table primaire chaque fois que vous lancez une requête).
J'éviterais d'utiliser une table temporaire avec autant de données. Voir cet article sur les performances de la table temporaire et comment éviter de les utiliser.

http://www.sql-server-performance.com/articles/per/derived_temp_tables_p1.aspx

Il semble qu'il vous manque un index dans le champ server_id. Je envisagerais de créer un index couvert en utilisant ce champ et d'autres. Voici un article à ce sujet également.

http://www.sql-server-performance.com/tips/covering_indexes_p1.aspx

modifier

Avec autant de lignes dans le tableau sur une période aussi courte, je vérifierais aussi les index de la fragmentation qui peut être une cause de lenteur. Dans SQL Server 2000, vous pouvez utiliser la DBCC SHOWCONTIG .

Voir ce lien pour info http://technet.microsoft.com/en-us/library/cc966523.aspx

Aussi, veuillez noter que j'ai numéroté ces éléments comme 1,2,3,4 mais l'éditeur les réinitialise automatiquement

Une fois quand je travaillais encore avec sql server 2000, j'avais besoin de faire de la pagination, et je suis tombé sur une méthode de pagination qui m'a vraiment déconcerté. Jetez un oeil à cette méthode.

 DECLARE @Table TABLE( TimeVal DATETIME ) DECLARE @StartVal INT DECLARE @EndVal INT SELECT @StartVal = 51, @EndVal = 100 SELECT * FROM ( SELECT TOP (@EndVal - @StartVal + 1) * FROM ( --select up to end number SELECT TOP (@EndVal) * FROM @Table ORDER BY TimeVal ASC ) PageReversed ORDER BY TimeVal DESC ) PageVals ORDER BY TimeVal ASC

Par exemple

 SELECT * FROM ( SELECT TOP (@EndVal - @StartVal + 1) * FROM ( SELECT TOP (@EndVal) l.id, l.timestamp FROM log l, logfile lf WHERE lf.server_id = #arguments.server_id# and l.test_id = #arguments.test_id#" and l.timestamp >= #arguments.report_from# and l.timestamp < #arguments.report_to# and l.logfile_id = lf.id order by l.timestamp asc ) PageReversed ORDER BY timestamp DESC ) PageVals ORDER BY timestamp ASC