SQL 2005 Datenbank erstellen How-to
20. April 2007 10:04
Hallo,
hat irgendjemand eine Standart anleitung worauf man beim Erstellen der Datenbank achten muß (Always Rowlock: yes ....) oder wie ihr es aus erfahrung immer macht.
Problem: Nav 4.03 SQL 2005 SP1 .
Eine Table 99999 mit 4 mio Records eingelesen. Nur No. und Bezeichnung. Prim. Key auf No. Wenn man jetzt F7 1234* eigibt gibt es viele abenteuerliche Wartezeiten 8 bis 120 sec [schild=19 fontcolor=000000 shadowcolor=C0C0C0 shieldshadow=1]gäähn[/schild] bis das ergebnis sichtbar ist. Der Server idlet nur herum bevor jetzt alle es auf CPU-Ram schieben. Wenn man der Query direkt in SQL feuert ist das Resultat im nu da.
Bei der Installation von 2005 kann man ja nichts grundlegendes falsch machen. Next -> Next -> Next -> Finish.
Gruß
hat irgendjemand eine Standart anleitung worauf man beim Erstellen der Datenbank achten muß (Always Rowlock: yes ....) oder wie ihr es aus erfahrung immer macht.
Problem: Nav 4.03 SQL 2005 SP1 .
Eine Table 99999 mit 4 mio Records eingelesen. Nur No. und Bezeichnung. Prim. Key auf No. Wenn man jetzt F7 1234* eigibt gibt es viele abenteuerliche Wartezeiten 8 bis 120 sec [schild=19 fontcolor=000000 shadowcolor=C0C0C0 shieldshadow=1]gäähn[/schild] bis das ergebnis sichtbar ist. Der Server idlet nur herum bevor jetzt alle es auf CPU-Ram schieben. Wenn man der Query direkt in SQL feuert ist das Resultat im nu da.
Bei der Installation von 2005 kann man ja nichts grundlegendes falsch machen. Next -> Next -> Next -> Finish.
Gruß
20. April 2007 11:07
Aus meiner Sicht muß bei der Abfrage 1234* der SQL-Server einen Table-Scan durchführen. Welchen Datentype hat das Feld "No." eventuell Code? Integer wäre besser. Falls du Code verwendest stell auf SQL-Servertype Integer um - falls dir Zifferen reichen.
20. April 2007 11:19
Hmmm,
ich denke das der SQL Server sich über diese Anfrage Totlacht .
Habe die Query mal direkt ins Managment studio geschrieben
select * from [testtable] where [No_] like '1234%'
Die Antwort kam, so schnell konnte man nicht mal gucken.
Integer können wir leider nicht nutzen für dieses Feld.
Irgendwo hat sich ein Fehler versteckt.
Für uns Deutsche ist doch Sortierung :
(x)SQl-Sortierung
Nr. 42
( ) Zeichsatz validieren
richtig.
Gruß
ich denke das der SQL Server sich über diese Anfrage Totlacht .
Habe die Query mal direkt ins Managment studio geschrieben
select * from [testtable] where [No_] like '1234%'
Die Antwort kam, so schnell konnte man nicht mal gucken.
Integer können wir leider nicht nutzen für dieses Feld.
Irgendwo hat sich ein Fehler versteckt.
Für uns Deutsche ist doch Sortierung :
(x)SQl-Sortierung
Nr. 42
( ) Zeichsatz validieren
richtig.
Gruß
20. April 2007 11:28
Tja, bin mit meinem Latein am Ende. Hat sonst noch wer eine Idee?
20. April 2007 13:17
Im Zusammenhang mit Navision gibt es im SQL 2005 SP2 einige Änderungen. Bevor du weiter suchst solltest du das SP2 installieren. Ich hatte auch einige Probleme die nach der Installation gelöst waren.
20. April 2007 17:32
Hi!
Aaaaalso, wo anfangen ... In aller Kürze:
DB Konfiguation: Alle Automatiken abschalten und durch Agent Jobs ersetzten. "Always Rowlock" veringert zwar Blockaden, geht aber erhebluch zu Lasten der Gesamtperformance. Empfehlung: Ausschalten; Blockaden anders lösen.
Was die Abfrage angeht:
Wie sieht denn C/AL Code aus? In NAV wird das ganze nämlich noch in einen Cursor geladen, und das ist immer langsamer als eine Ad hoc Abfrage. Wenn Wildcards verwendet werden liegt die Wahrscheinlichkeit eines Clustered Index Scans bei nahezu 100% - was "spricht" denn der Profiler?
Aaaaalso, wo anfangen ... In aller Kürze:
DB Konfiguation: Alle Automatiken abschalten und durch Agent Jobs ersetzten. "Always Rowlock" veringert zwar Blockaden, geht aber erhebluch zu Lasten der Gesamtperformance. Empfehlung: Ausschalten; Blockaden anders lösen.
Was die Abfrage angeht:
Wie sieht denn C/AL Code aus? In NAV wird das ganze nämlich noch in einen Cursor geladen, und das ist immer langsamer als eine Ad hoc Abfrage. Wenn Wildcards verwendet werden liegt die Wahrscheinlichkeit eines Clustered Index Scans bei nahezu 100% - was "spricht" denn der Profiler?
23. April 2007 09:16
Hi Jörn,
Ok Always Rowlock aus hat nix gebracht.
SP2 für SQL 2005 hat nix gebracht.
C/AL Code ist nicht vorhanden ich drücke ja nur F7 in einer Table.
Was der Profiler sagt ist in der Angehängen txt-datei
Währe es eine Möglichkeit das es die 32-Bit SQL ist ?!? Irgendwo muß man ja anfangen zu suchen
Gruß
Ok Always Rowlock aus hat nix gebracht.
SP2 für SQL 2005 hat nix gebracht.
C/AL Code ist nicht vorhanden ich drücke ja nur F7 in einer Table.
Was der Profiler sagt ist in der Angehängen txt-datei
Währe es eine Möglichkeit das es die 32-Bit SQL ist ?!? Irgendwo muß man ja anfangen zu suchen
Gruß
23. April 2007 09:16
Oh verpeilt, dann so :
RPC:Completed declare @p1 int
set @p1=180150685
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=0
exec sp_cursoropen @p1 output,N'SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable" WHERE (("Description" LIKE @P1)) AND "No_">=@P2 ORDER BY "No_" OPTION (FAST 10)',@p3 output,@p4 output,@p5 output,N'@P1 varchar(30),@P2 varchar(20)','001691%','0000000001'
select @p1, @p3, @p4, @p5 Microsoft Business Solutions-Navision client super 3109 22938 14 8055 3996 52 2007-04-23 09:03:11.430 2007-04-23 09:03:19.477
RPC:Completed declare @p1 int
set @p1=180150687
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=0
exec sp_cursoropen @p1 output,N'SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable" WHERE (("Description" LIKE @P1)) AND "No_"<@P2 ORDER BY "No_" DESC OPTION (FAST 10)',@p3 output,@p4 output,@p5 output,N'@P1 varchar(30),@P2 varchar(20)','001691%','0000000001'
select @p1, @p3, @p4, @p5 Microsoft Business Solutions-Navision client super 0 45 0 4 3996 52 2007-04-23 09:03:19.477 2007-04-23 09:03:19.477
SQL:BatchStarting SET SHOWPLAN_ALL ON Microsoft Business Solutions-Navision client super 3996 52 2007-04-23 09:03:19.523
SQL:BatchCompleted SET SHOWPLAN_ALL ON Microsoft Business Solutions-Navision client super 0 0 0 0 3996 52 2007-04-23 09:03:19.523 2007-04-23 09:03:19.523
SQL:BatchStarting SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable" WHERE (("No_"<'')) AND (("Description" LIKE '001691%')) Microsoft Business Solutions-Navision client super 3996 52 2007-04-23 09:03:19.523
SQL:BatchCompleted SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable" WHERE (("No_"<'')) AND (("Description" LIKE '001691%')) Microsoft Business Solutions-Navision client super 0 42 0 3 3996 52 2007-04-23 09:03:19.523 2007-04-23 09:03:19.523
SQL:BatchStarting SET SHOWPLAN_ALL OFF Microsoft Business Solutions-Navision client super 3996 52 2007-04-23 09:03:19.540
SQL:BatchCompleted SET SHOWPLAN_ALL OFF Microsoft Business Solutions-Navision client super 0 0 0 0 3996 52 2007-04-23 09:03:19.540 2007-04-23 09:03:19.540
SQL:BatchStarting SET SHOWPLAN_ALL ON Microsoft Business Solutions-Navision client super 3996 52 2007-04-23 09:03:19.540
SQL:BatchCompleted SET SHOWPLAN_ALL ON Microsoft Business Solutions-Navision client super 0 0 0 0 3996 52 2007-04-23 09:03:19.540 2007-04-23 09:03:19.540
SQL:BatchStarting SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable" WHERE (("No_">'')) AND (("Description" LIKE '001691%')) Microsoft Business Solutions-Navision client super 3996 52 2007-04-23 09:03:19.540
SQL:BatchCompleted SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable" WHERE (("No_">'')) AND (("Description" LIKE '001691%')) Microsoft Business Solutions-Navision client super 0 42 0 5 3996 52 2007-04-23 09:03:19.540 2007-04-23 09:03:19.540
SQL:BatchStarting SET SHOWPLAN_ALL OFF Microsoft Business Solutions-Navision client super 3996 52 2007-04-23 09:03:19.570
SQL:BatchCompleted SET SHOWPLAN_ALL OFF Microsoft Business Solutions-Navision client super 0 0 0 0 3996 52 2007-04-23 09:03:19.570 2007-04-23 09:03:19.570
RPC:Completed declare @p1 int
set @p1=1073742013
declare @p2 int
set @p2=180150689
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30)',N'SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable" WHERE (("Description" LIKE @P1)) ORDER BY "No_" DESC OPTION (FAST 10)',@p5 output,@p6 output,@p7 output,'001691%'
select @p1, @p2, @p5, @p6, @p7 Microsoft Business Solutions-Navision client super 1047 21486 0 1067 3996 52 2007-04-23 09:03:19.570 2007-04-23 09:03:20.633
RPC:Completed declare @p1 int
set @p1=180150685
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=0
exec sp_cursoropen @p1 output,N'SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable" WHERE (("Description" LIKE @P1)) AND "No_">=@P2 ORDER BY "No_" OPTION (FAST 10)',@p3 output,@p4 output,@p5 output,N'@P1 varchar(30),@P2 varchar(20)','001691%','0000000001'
select @p1, @p3, @p4, @p5 Microsoft Business Solutions-Navision client super 3109 22938 14 8055 3996 52 2007-04-23 09:03:11.430 2007-04-23 09:03:19.477
RPC:Completed declare @p1 int
set @p1=180150687
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=0
exec sp_cursoropen @p1 output,N'SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable" WHERE (("Description" LIKE @P1)) AND "No_"<@P2 ORDER BY "No_" DESC OPTION (FAST 10)',@p3 output,@p4 output,@p5 output,N'@P1 varchar(30),@P2 varchar(20)','001691%','0000000001'
select @p1, @p3, @p4, @p5 Microsoft Business Solutions-Navision client super 0 45 0 4 3996 52 2007-04-23 09:03:19.477 2007-04-23 09:03:19.477
SQL:BatchStarting SET SHOWPLAN_ALL ON Microsoft Business Solutions-Navision client super 3996 52 2007-04-23 09:03:19.523
SQL:BatchCompleted SET SHOWPLAN_ALL ON Microsoft Business Solutions-Navision client super 0 0 0 0 3996 52 2007-04-23 09:03:19.523 2007-04-23 09:03:19.523
SQL:BatchStarting SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable" WHERE (("No_"<'')) AND (("Description" LIKE '001691%')) Microsoft Business Solutions-Navision client super 3996 52 2007-04-23 09:03:19.523
SQL:BatchCompleted SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable" WHERE (("No_"<'')) AND (("Description" LIKE '001691%')) Microsoft Business Solutions-Navision client super 0 42 0 3 3996 52 2007-04-23 09:03:19.523 2007-04-23 09:03:19.523
SQL:BatchStarting SET SHOWPLAN_ALL OFF Microsoft Business Solutions-Navision client super 3996 52 2007-04-23 09:03:19.540
SQL:BatchCompleted SET SHOWPLAN_ALL OFF Microsoft Business Solutions-Navision client super 0 0 0 0 3996 52 2007-04-23 09:03:19.540 2007-04-23 09:03:19.540
SQL:BatchStarting SET SHOWPLAN_ALL ON Microsoft Business Solutions-Navision client super 3996 52 2007-04-23 09:03:19.540
SQL:BatchCompleted SET SHOWPLAN_ALL ON Microsoft Business Solutions-Navision client super 0 0 0 0 3996 52 2007-04-23 09:03:19.540 2007-04-23 09:03:19.540
SQL:BatchStarting SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable" WHERE (("No_">'')) AND (("Description" LIKE '001691%')) Microsoft Business Solutions-Navision client super 3996 52 2007-04-23 09:03:19.540
SQL:BatchCompleted SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable" WHERE (("No_">'')) AND (("Description" LIKE '001691%')) Microsoft Business Solutions-Navision client super 0 42 0 5 3996 52 2007-04-23 09:03:19.540 2007-04-23 09:03:19.540
SQL:BatchStarting SET SHOWPLAN_ALL OFF Microsoft Business Solutions-Navision client super 3996 52 2007-04-23 09:03:19.570
SQL:BatchCompleted SET SHOWPLAN_ALL OFF Microsoft Business Solutions-Navision client super 0 0 0 0 3996 52 2007-04-23 09:03:19.570 2007-04-23 09:03:19.570
RPC:Completed declare @p1 int
set @p1=1073742013
declare @p2 int
set @p2=180150689
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30)',N'SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable" WHERE (("Description" LIKE @P1)) ORDER BY "No_" DESC OPTION (FAST 10)',@p5 output,@p6 output,@p7 output,'001691%'
select @p1, @p2, @p5, @p6, @p7 Microsoft Business Solutions-Navision client super 1047 21486 0 1067 3996 52 2007-04-23 09:03:19.570 2007-04-23 09:03:20.633
23. April 2007 20:44
Tja, wie schon vermutet, so wird das Ganze - wie immer - in 'nen Cursor "gepumpt" ...
Wenn Du das Statement
über den Query Analyzer ausführst, wie viele Datensätze werden zurückgegeben, und wie sieht der Ausführungsplan aus?
Das Problem ist der LIKE Filter auf "Description" (wolltest Du nicht nur auf "No." filtern?). Gibt es einen Index für "Description"? Falls nicht, dann anlegen
und nochmal probieren ...
Wenn Du das Statement
- Code:
SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable"
WHERE (("Description" LIKE '001691%')) AND "No_">='0000000001'
ORDER BY "No_" OPTION (FAST 10)
über den Query Analyzer ausführst, wie viele Datensätze werden zurückgegeben, und wie sieht der Ausführungsplan aus?
Das Problem ist der LIKE Filter auf "Description" (wolltest Du nicht nur auf "No." filtern?). Gibt es einen Index für "Description"? Falls nicht, dann anlegen
- Code:
CREATE INDEX test01 ON "comp GmbH$TestTable" (Description)
und nochmal probieren ...
26. April 2007 11:08
- Handelt es sich um eine neue DB (4.0 SP3) oder um ein Update von 3xx (per fbk)?
- Läßt du regelmäßig Maintenance Plans durchlaufen (rebuilt indexies, update statistics)?
- Verwendest du find as you type?
- Läßt du regelmäßig Maintenance Plans durchlaufen (rebuilt indexies, update statistics)?
- Verwendest du find as you type?
30. April 2007 14:25
Die neue Datenbank ist 4.0 SP3 kommt aber von einem 3.70 fbk.
Hat aber denke ich damit nix zutun weil ich zum testen von der 4.0 sp3 cd den cronus fbk eingelesen habe und damit auch die tests gemacht habe.
Nochmals kurz zu meiner Vorgehensweise. 3 mio records in einer table, Auf primary Key stehen und (F7) 12345* enter.
ich denke das bei der installation von SQL man einen gravierenden Fehler machen kann. Vielleicht liegt es auch an dem SQL Sortierung.
GRuß
Hat aber denke ich damit nix zutun weil ich zum testen von der 4.0 sp3 cd den cronus fbk eingelesen habe und damit auch die tests gemacht habe.
Nochmals kurz zu meiner Vorgehensweise. 3 mio records in einer table, Auf primary Key stehen und (F7) 12345* enter.
ich denke das bei der installation von SQL man einen gravierenden Fehler machen kann. Vielleicht liegt es auch an dem SQL Sortierung.
GRuß
30. April 2007 14:59
Also nochmal:
Gemäß deinem Profiler Auszug ...
... filterst Du nicht auf "No." sondern auf "Description" und zwar mit 'ner Wildcard:
... WHERE (("Description" LIKE @P1)) ...
wobei der Parameter @P1 mit
'001691%' (entspricht in NAV '001691*')
übergeben wird. Da es mit ziemlicher Sicherheit keinen Index auf "Description" gibt und mittels LIKE gefiltert wird, werden die 3 Mio. Datensätze via Full Clustered Index Scan in einen Cursor gepumpt ... und das dauert ...
Hat weder mit Installation noch Konfiguration zu tun, ist ein "simples" Index-Problem ...
Gemäß deinem Profiler Auszug ...
exec sp_cursoropen @p1 output,N'SELECT * FROM "comp-test"."dbo"."comp GmbH$TestTable" WHERE (("Description" LIKE @P1)) AND "No_"<@P2 ORDER BY "No_" DESC OPTION (FAST 10)',@p3 output,@p4 output,@p5 output,N'@P1 varchar(30),@P2 varchar(20)','001691%','0000000001'
... filterst Du nicht auf "No." sondern auf "Description" und zwar mit 'ner Wildcard:
... WHERE (("Description" LIKE @P1)) ...
wobei der Parameter @P1 mit
'001691%' (entspricht in NAV '001691*')
übergeben wird. Da es mit ziemlicher Sicherheit keinen Index auf "Description" gibt und mittels LIKE gefiltert wird, werden die 3 Mio. Datensätze via Full Clustered Index Scan in einen Cursor gepumpt ... und das dauert ...
Hat weder mit Installation noch Konfiguration zu tun, ist ein "simples" Index-Problem ...
2. Mai 2007 08:29
NAja ein so einfaches Indexproblem ist es nicht.
exec sp_cursoropen @p1 output,N'SELECT * FROM "latec-test"."dbo"."comp GmbH$TestTable" WHERE (("No_" LIKE @P1)) AND "No_">=@P2 ORDER BY "No_" OPTION (FAST 10)',@p3 output,@p4 output,@p5 output,N'@P1 varchar(20),@P2 varchar(20)','001691%','0085700001'
select @p1, @p3, @p4, @p5
Keine ahnung was er damals gemacht hat. In dieser Query ist description raus was ja sowieso leer ist. Aber es ist genauso langsam.
Soll sich mal lieber das Solution Center und Microsoft damit rumschlagen.
exec sp_cursoropen @p1 output,N'SELECT * FROM "latec-test"."dbo"."comp GmbH$TestTable" WHERE (("No_" LIKE @P1)) AND "No_">=@P2 ORDER BY "No_" OPTION (FAST 10)',@p3 output,@p4 output,@p5 output,N'@P1 varchar(20),@P2 varchar(20)','001691%','0085700001'
select @p1, @p3, @p4, @p5
Keine ahnung was er damals gemacht hat. In dieser Query ist description raus was ja sowieso leer ist. Aber es ist genauso langsam.
Soll sich mal lieber das Solution Center und Microsoft damit rumschlagen.
2. Mai 2007 08:39
Da in der Abfrage die "No." gleichzeitig über ein like und auch als Begrenzung angeben wird, werden die Datensätze wohl auch hier sequetiell (und zwar alle Datensätze) abgearbeitet:
... (("No_" LIKE @P1)) AND "No_">=@P2 ...
... (("No_" LIKE @P1)) AND "No_">=@P2 ...
2. Mai 2007 09:46
exec sp_cursoropen @p1 output,N'SELECT * FROM "latec-test"."dbo"."comp GmbH$TestTable" WHERE (("No_" LIKE @P1)) AND "No_">=@P2 ORDER BY "No_" OPTION (FAST 10)',@p3 output,@p4 output,@p5 output,N'@P1 varchar(20),@P2 varchar(20)','001691%','0085700001'
select @p1, @p3, @p4, @p5
Auch bei dieser Abfrage ist soz. ein vollständiger Table-Scan zu erwarten ... bei LIKE Abfragen hat SQL Server kaum eine Chance einen "Index Seek" durchzuführen ... und wie gesagt: das ganze geht in einen "Cursor", daher auch der Unterschied zur ad hoc Abfrage ...
3. Mai 2007 14:25
Vielleicht hilft dir das Attachment...
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.