Problème de performance de requête Odd SQL 2000

Lors de l'optimization des performances des requêtes SQL, j'ai remarqué que la requête suivante fonctionnait lentement, mais qu'elle ne détruisait pas le processeur et qu'il ne semblait pas y avoir d'autres goulots d'étranglement système pour ralentir l'exécution. En fait, la moyenne de l'UC était de 15% pendant l'exécution:

UPDATE: La requête en question s'exécute dans une boucle de slider qui contient 800 loggings:

cursor = SELCT DISTINCT param1,param2, param3, param4 FROM t_Table GROUP BY param1,param2, param3, param4 ORDER BY param1,param2, param3, param4 DESC cursor loop: SELECT @maxval1 = max(iField1), @maxval2 = max(iField2), @dtDateMin = Min(dtDate), @dtDateMax = Max(dtDate) FROM t_Table WHERE iSomeField1 = @param1 AND iSomeField2 = @param2 AND iSomeField3 = @param3 AND iSomeField4 = @param4 next 

(Remarque: il existe des index configurés pour iSomeField1-4)

J'ai alors éclaté les parties min / max individuelles en quatre requêtes pour voir comment le server a répondu et a obtenu le plein débit avec le CPU culminant à 100% et le bloc a couru en 2 secondes, par opposition à> 5 minutes pour ce qui précède. Alors que je suis vraiment heureux avec les augmentations de performance, je voudrais un DBA intelligent pour expliquer pourquoi c'est et quels autres conseils ils pourraient donner sur ces types de requêtes?

 SELECT TOP 1 @maxval1 = iField1 FROM t_Table WHERE iSomeField1 = @param1 AND iSomeField2 = @param2 AND iSomeField3 = @param3 AND iSomeField4 = @param4 ORDER BY field1 DESC SELECT TOP 1 @maxval2 = iField2 FROM t_Table WHERE iSomeField1 = @param1 AND iSomeField2 = @param2 AND iSomeField3 = @param3 AND iSomeField4 = @param4 ORDER BY field2 DESC SELECT TOP 1 @dtDateMin = dtDate FROM t_Table WHERE iSomeField1 = @param1 AND iSomeField2 = @param2 AND iSomeField3 = @param3 AND iSomeField4 = @param4 ORDER BY dtDate ASC SELECT TOP 1 @dtDateMax = dtDate FROM t_Table WHERE iSomeField1 = @param1 AND iSomeField2 = @param2 AND iSomeField3 = @param3 AND iSomeField4 = @param4 ORDER BY dtDate DESC 

S'il vous plaît noter que je suis un développeur et non un DBA mais j'aimerais comprendre un peu plus sur la façon dont SQL Server fonctionne derrière la scène sur des requêtes comme ci-dessus. Pour append à certaines des réponses ci-dessous, je suis conscient et ai utilisé le planificateur d'exécution de requête pour parsingr le problème de performance et ce qui m'insortinggue est que malgré les index sur la table, pourquoi la première requête fonctionne mal sur le server SQL.

MISE À JOUR: Les captures d'écran de l'utilisation du processeur de la requête 1 par rapport à la requête 2+ s'exécutent dans une boucle de slider:

Utilisation du processeur pour la requête 1 et la requête 2 http://img22.imageshack.us/img22/3262/sqlperfodd.png Query 1 EP http://img513.imageshack.us/img513/5681/query1.png Query 2 EP http: //img365.imageshack.us/img365/9715/query2.png

Le schéma est montré ci-dessous (c'est une version réduite de notre table réelle mais la structure et les index sont ce que nous utilisons).

 CREATE TABLE [dbo].[t_Table] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [dtDate] [datetime] NULL , [iField1] [int] NULL ,[iField2] [int] NULL , [iSomeField1] [int] NULL ,[iSomeField2] [int] NULL , [iSomeField3] [int] NULL ,[iSomeField4] [int] NULL) ON [PRIMARY] CREATE CLUSTERED INDEX [IX_dtDate] ON [dbo].[t_Table]([dtDate], [iField1], [iSomeField1]) WITH FILLFACTOR = 90 ON [PRIMARY] ALTER TABLE [dbo].[t_Table] ADD CONSTRAINT [PK_t_Table] PRIMARY KEY NONCLUSTERED ([ID]) ON [PRIMARY] CREATE INDEX [idx_field1234] ON [dbo].[t_Table]([iSomeField1], [iSomeField2], [iSomeField3], [iSomeField4]) ON [PRIMARY] CREATE INDEX [idx_field1] ON [dbo].[t_Table]([iSomeField1]) [PRIMARY] 

Ça dépend. Quels sont les index sur la table? Quel volume de lignes est dans la table? Je viens de créer un échantillon qui a bien fonctionné, mais il peut être très différent de votre scénario. En règle générale, si l'optimiseur rencontre des problèmes, la requête doit être simplifiée. Ce que vous avez fait peut-être ce qui est nécessaire. Ça dépend. Voici le SQL que j'ai créé pour voir si je pouvais find quelque chose d'évident avec le plan d'exécution du spectacle.

mettre nocount sur

ALLER

if object_id ('tempdb .. # MaxMinExample') n'est pas null drop table #MaxMinExample

ALLER

create table #MaxMinExample ([key] int identité (1,1) key primaire en cluster, iField1 int, iField2 int, date_dt_date, iSomeField1 int, iSomeField2 int, iSomeField3 int, iSomeField4 int)

ALLER

– set initial de données que nous allons cartésien

insert dans #MaxMinExample (iField1, iField2, dtDate, iSomeField1, iSomeField2, iSomeField3, iSomeField4) valeurs (1,2, getdate (), 1,2,3,4) insert dans #MaxMinExample (iField1, iField2, dtDate, iSomeField1, valeurs iSomeField2, iSomeField3, iSomeField4) (2,3, getdate () + 1,4,5,6,7) insert dans les valeurs #MaxMinExample (iField1, iField2, dtDate, iSomeField1, iSomeField2, iSomeField3, iSomeField4) (3,4 , getdate () + 2,5,6,7,8) insert dans #MaxMinExample (iField1, iField2, dtDate, iSomeField1, iSomeField2, iSomeField3, iSomeField4) valeurs (5,6, getdate () + 3,6,7, 8,9) insert dans #MaxMinExample (iField1, iField2, dtDate, iSomeField1, iSomeField2, iSomeField3, iSomeField4) valeurs (6,7, getdate () + 4,7,8,9,10)

ALLER

–créer des charges de données

declare @count int set @ count = 1 while (select count (*) from #MaxMinExample) <865830 begin insert dans #MaxMinExample (iField1, iField2, dtDate, iSomeField1, iSomeField2, iSomeField3, iSomeField4) select a.iField1+@count, un .iField2 + @ count, a.dtDate + @ count, a.iSomeField1 + @ count, a.iSomeField2 + @ count, a.iSomeField3 + @ count, a.iSomeField4 + @ countz de #MaxMinExample une jointure croisée #MaxMinExample b set @ count = @ count + 1 fin

ALLER

–créer les index

create index MaxMinExample_iSomeField1 sur #MaxMinExample (iSomeField1) créer l'index MaxMinExample_iSomeField2 sur #MaxMinExample (iSomeField2) créer l'index MaxMinExample_iSomeField3 sur #MaxMinExample (iSomeField3) créer l'index MaxMinExample_iSomeField4 sur #MaxMinExample (iSomeField4) créer l'index MaxMinExample_dtDate sur #MaxMinExample (dtDate)

ALLER

déclare @ maxval1 int, @ maxval2 int, @ date_dt time, @ dtDateMax date et heure, @ param1 int, @ param2 int, @ param3 int, @ param4 int

select @ param1 = 4, @ param2 = 5, @ param3 = 6, @ param4 = 7

select @ maxval1 = max (iField1), @ maxval2 = max (iField2), @dtDateMin = Min (dtDate), @ dtDateMax = Max (dtDate) de #MaxMinExample où iSomeField1 = @ param1 et iSomeField2 = @ param2 et iSomeField3 = @ param3 et iSomeField4 = @ param4

select top 1 @ maxval1 = iField1 de #MaxMinExample où iSomeField1 = @ param1 et iSomeField2 = @ param2 et iSomeField3 = @ param3 et iSomeField4 = @ param4 par iField1 DESC select top 1 @ maxval2 = iField2 de #MaxMinExample où iSomeField1 = @ param1 et iSomeField2 = @ param2 et iSomeField3 = @ param3 et iSomeField4 = @ param4 par iField2 DESC select top 1 @dtDateMin = dtDate de #MaxMinExample où iSomeField1 = @ param1 et iSomeField2 = @ param2 et iSomeField3 = @ param3 et iSomeField4 = @ param4 par dtDate ASC sélectionner top 1 @dtDateMax = dtDate de #MaxMinExample où iSomeField1 = @ param1 et iSomeField2 = @ param2 et iSomeField3 = @ param3 et iSomeField4 = @ param4 par dtDate DESC

Mark Baekdal – http://www.dbghost.com – Développeur SQL

J'ai jeté un oeil à votre requête et j'ai effectué des tests rapides. Comme Diego l'a mentionné, le plan d'exécution dans Query Analyzer est un excellent outil pour résoudre ce genre de problème.

Tout d'abord, une note sur les index – quand SQL reçoit une requête, il passera par une série d'étapes – pour simplifier ce process, l'une des tâches principales ici est de décider quel est le moyen le plus efficace pour récupérer datatables. SQL examinera la structure de la table, les statistics et les index pour déterminer ce qu'il pense être le path optimal. Il choisira finalement un index à utiliser pour récupérer des données (jamais des index multiples).

L'index est essentiellement une search vers où datatables sont stockées sur la table (ou dans le cas de l'index clusterisé, il définit réellement comment datatables sont stockées sur le disque). Si votre requête utilise un certain nombre de colonnes (soit dans les clauses query, where ou order), alors la requête devra les récupérer afin qu'elle puisse renvoyer votre requête. SQL examinera l'index et examinera ensuite l'ordre dans lequel il doit extraire les index. Idéalement, SQL pourra «searchr» directement datatables que vous avez demandées (l'alternative la plus courante est un «scan», ce qui signifie essentiellement index / structure entiers a été scanné pour datatables). Ces termes sont utilisés dans le plan d'exécution ci-dessus. Il y a beaucoup de complexité supplémentaire dans ce qui précède (par exemple, dans certaines requêtes, vous pouvez voir des searchs de signets, où SQL utilise un index pour find la ligne, puis effectuer une search pour get toutes datatables associées) .

En passant, je vais mentionner un site qui, je pense, est génial pour les informations de performance SQL – http://www.sql-server-performance.com

En prenant ce qui précède et en appliquant à vos informations, nous pouvons voir que votre requête principale effectue un "scan" de l'index clusterisé, c'est-à-dire qu'il parcourt toute la table pour récupérer les informations que vous avez demandées. Cela signifie qu'aucun index n'a été trouvé qui lui permettrait de searchr directement les informations dont vous avez besoin. Pour exécuter cette requête SQL doit effectuer un filter sur vos champs 1 – 4, puis agréger des informations (la requête MAX) sur les colonnes champ et date.

À titre d'exemple extrême, cet index permettrait une search:

 CREATE INDEX [idx_field1234567] ON [dbo]. [T_Table] (
    [iSomeField1], 
    [iSomeField2], 
    [iSomeField3], 
    [iSomeField4], 
    [dtDate], 
    [iField1],
    [iField2]
 ) 
 WITH FILLFACTOR = 90, PAD_INDEX ON [PRIMAIRE]

Cependant, vous devriez noter qu'il y a beaucoup à faire dans la création d'un index, et généralement vous devriez éviter d'append trop de colonnes à un index et vous devriez éviter d'avoir trop d'index sur une table. Dans SQL 2000, l'assistant d'optimization d'index est une bonne affaire pour fournir une base de reference décente des index.

Comme Diego l'a dit, ce qui précède peut sembler décourageant mais le site ci-dessus était une excellente reference pour moi.

Bonne chance!

Malheureusement, les informations que vous fournissez ne sont pas suffisantes pour vous donner une réponse précise, mais je pense que je peux vous donner un indice utile. SQL Server vous permet d'afficher le plan de requête utilisé pour accéder aux données; Un tel plan vous dira en détail à quoi vous avez access, quand, de quelle manière, et combien de lignes sont traitées dans chaque étape. Il vous indique également combien de time / ressources consumnt chaque étape, ce qui vous permet de find un goulot d'étranglement facilement.

Pour afficher le plan d'exécution dans Query Analyzer, ouvrez le menu Requête et click "Show Execution Plan". Ensuite, lancez votre première requête et vérifiez le plan; Dans une autre window, exécutez la deuxième requête et vérifiez à nouveau le plan. De cette façon, vous pouvez voir quelle est la différence entre eux, quels index (le cas échéant) sont utilisés et get une meilleure compréhension de SQL Server.

Un indice: ne vous découragez pas si au début tout vous semble compliqué, il suffit de le prendre lentement.

Enfin, une ressource utile pour SQL Server (autre que le MSDN, bien sûr) est http://www.sqlservercentral.com, où vous pouvez find des réponses d'users et d'experts. J'espère que ça aide.

Au final, le projet est passé à une nouvelle stack DWH et BI basée sur MicroStrategy, ce qui est devenu un problème et le nouveau DWH a un schéma différent et a été fait en SQL 2008 (mais je ne l'ai jamais vu en bas: /)