|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
differnt execution plans for same querywe have two sql server 2000sp4 on to branded win 2k3 servers.
both systems have equal data in the same database on the first machine a query needs about 3-5 secondes on the other machine the same query needs about 30 to 40 sec with an different execution plan to the first one what is to do, to make the second as fast as expected from the first machine? what can be the reason? thanks -- mfg andreas The execution plan depends a lot on what the parameters are that are first
passed in to the sp or used with the query when it is first executed. From then on subsequent executions may reuse the previous query plan. If the first run required a scan because it affected so many rows all others will scan as well. Make sure the stats are updated on both by running sp_updatestats. If you get a bad plan recompile that procedure to get a new one. -- Show quoteAndrew J. Kelly SQL MVP "AnRa" <A***@discussions.microsoft.com> wrote in message news:E8F86E2F-34D4-453C-BA09-729CB2CE0E23@microsoft.com... > we have two sql server 2000sp4 on to branded win 2k3 servers. > both systems have equal data in the same database > > on the first machine a query needs about 3-5 secondes > > on the other machine the same query needs about 30 to 40 sec with an > different execution plan to the first one > > what is to do, to make the second as fast as expected from the first > machine? > what can be the reason? > > thanks > -- > mfg andreas Hi,
1. As Andrew suggested execute a update statistics (SP_Updatestats can be used) on all tables Incase if you still have issues then:- 1. See the confuguration values in both servers 2. compare the indexes and Primary keys (contains index) in both databases and ensure nothing is missed out. Thanks Hari SQL Server MVP Show quote "AnRa" <A***@discussions.microsoft.com> wrote in message news:E8F86E2F-34D4-453C-BA09-729CB2CE0E23@microsoft.com... > we have two sql server 2000sp4 on to branded win 2k3 servers. > both systems have equal data in the same database > > on the first machine a query needs about 3-5 secondes > > on the other machine the same query needs about 30 to 40 sec with an > different execution plan to the first one > > what is to do, to make the second as fast as expected from the first > machine? > what can be the reason? > > thanks > -- > mfg andreas thank you for your answer
i used sp_updatestats, database and data are the same, i backed up and restored on the other machine the spedific query is still slow. one specific table is used twice (clustered index scan) in execution plan on the slow machine. the number of rows of these table in this step is displayed as 37 mio, which is nearly 300 x real number of rows (126.657), one of these symbol has very thick lines in execution plan. i think here could be a starting point for the loss of performance on these machine. -- Show quotemfg andreas "Hari Prasad" wrote: > Hi, > > 1. As Andrew suggested execute a update statistics (SP_Updatestats can be > used) on all tables > > Incase if you still have issues then:- > > 1. See the confuguration values in both servers > 2. compare the indexes and Primary keys (contains index) in both databases > and ensure nothing is missed out. > > Thanks > Hari > SQL Server MVP > > > "AnRa" <A***@discussions.microsoft.com> wrote in message > news:E8F86E2F-34D4-453C-BA09-729CB2CE0E23@microsoft.com... > > we have two sql server 2000sp4 on to branded win 2k3 servers. > > both systems have equal data in the same database > > > > on the first machine a query needs about 3-5 secondes > > > > on the other machine the same query needs about 30 to 40 sec with an > > different execution plan to the first one > > > > what is to do, to make the second as fast as expected from the first > > machine? > > what can be the reason? > > > > thanks > > -- > > mfg andreas > > > AnRa (A***@discussions.microsoft.com) writes:
> i used sp_updatestats, database and data are the same, i backed up and I guess that's number the estimated total number of rows from that> restored on the other machine > > the spedific query is still slow. > > one specific table is used twice (clustered index scan) in execution > plan on the slow machine. the number of rows of these table in this step > is displayed as 37 mio, which is nearly 300 x real number of rows > (126.657), one of these symbol has very thick lines in execution plan. table and preceding tables in the plan. Is the hardware the same on the two machines? The same number of CPUs? The same amount of memory? Does any of the plans include parallelism? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Did you run the updatestats after you restored it? If not then you need to.
-- Show quoteAndrew J. Kelly SQL MVP "AnRa" <A***@discussions.microsoft.com> wrote in message news:14052C18-C5EB-451A-9698-BAC256431B8F@microsoft.com... > thank you for your answer > > i used sp_updatestats, database and data are the same, i backed up and > restored on the other machine > > the spedific query is still slow. > > one specific table is used twice (clustered index scan) in execution plan > on > the slow machine. the number of rows of these table in this step is > displayed > as 37 mio, which is nearly 300 x real number of rows (126.657), one of > these > symbol has very thick lines in execution plan. > > i think here could be a starting point for the loss of performance on > these > machine. > > > -- > mfg andreas > > > "Hari Prasad" wrote: > >> Hi, >> >> 1. As Andrew suggested execute a update statistics (SP_Updatestats can be >> used) on all tables >> >> Incase if you still have issues then:- >> >> 1. See the confuguration values in both servers >> 2. compare the indexes and Primary keys (contains index) in both >> databases >> and ensure nothing is missed out. >> >> Thanks >> Hari >> SQL Server MVP >> >> >> "AnRa" <A***@discussions.microsoft.com> wrote in message >> news:E8F86E2F-34D4-453C-BA09-729CB2CE0E23@microsoft.com... >> > we have two sql server 2000sp4 on to branded win 2k3 servers. >> > both systems have equal data in the same database >> > >> > on the first machine a query needs about 3-5 secondes >> > >> > on the other machine the same query needs about 30 to 40 sec with an >> > different execution plan to the first one >> > >> > what is to do, to make the second as fast as expected from the first >> > machine? >> > what can be the reason? >> > >> > thanks >> > -- >> > mfg andreas >> >> >> hy,
thank for your reply. yes we used sp_updatestats after backup and restore. we also tried update statstics table with FULLSCAN. --> no improvement on performance parallelism is used, we tried also with hardware disable Hyperthreading --> no improvement on performance number of cpu = 1, memory is 2 gb, slow machine = iXeon 3,2 MHz, fast machine = i Xean 3,4 MHz so, i don´t know why displayed (and probable used) 37.620.396 as number of lines. The estimated number of lines is the real number of count(*) from this table (126.668) what is to do next? thank you -- Show quotemfg andreas "Andrew J. Kelly" wrote: > Did you run the updatestats after you restored it? If not then you need to. > > -- > Andrew J. Kelly SQL MVP > > "AnRa" <A***@discussions.microsoft.com> wrote in message > news:14052C18-C5EB-451A-9698-BAC256431B8F@microsoft.com... > > thank you for your answer > > > > i used sp_updatestats, database and data are the same, i backed up and > > restored on the other machine > > > > the spedific query is still slow. > > > > one specific table is used twice (clustered index scan) in execution plan > > on > > the slow machine. the number of rows of these table in this step is > > displayed > > as 37 mio, which is nearly 300 x real number of rows (126.657), one of > > these > > symbol has very thick lines in execution plan. > > > > i think here could be a starting point for the loss of performance on > > these > > machine. > > > > > > -- > > mfg andreas > > > > > > "Hari Prasad" wrote: > > > >> Hi, > >> > >> 1. As Andrew suggested execute a update statistics (SP_Updatestats can be > >> used) on all tables > >> > >> Incase if you still have issues then:- > >> > >> 1. See the confuguration values in both servers > >> 2. compare the indexes and Primary keys (contains index) in both > >> databases > >> and ensure nothing is missed out. > >> > >> Thanks > >> Hari > >> SQL Server MVP > >> > >> > >> "AnRa" <A***@discussions.microsoft.com> wrote in message > >> news:E8F86E2F-34D4-453C-BA09-729CB2CE0E23@microsoft.com... > >> > we have two sql server 2000sp4 on to branded win 2k3 servers. > >> > both systems have equal data in the same database > >> > > >> > on the first machine a query needs about 3-5 secondes > >> > > >> > on the other machine the same query needs about 30 to 40 sec with an > >> > different execution plan to the first one > >> > > >> > what is to do, to make the second as fast as expected from the first > >> > machine? > >> > what can be the reason? > >> > > >> > thanks > >> > -- > >> > mfg andreas > >> > >> > >> > > > AnRa (A***@discussions.microsoft.com) writes:
> yes we used sp_updatestats after backup and restore. we also tried update Did you try adding OPTION (MAXDOP 1). Parallelism is not always good> statstics table with FULLSCAN. > --> no improvement on performance > > parallelism is used, we tried also with hardware disable Hyperthreading > --> no improvement on performance for performance. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx thank you for your message.
paralism was not the problem, we tried also without hw hyperhreading and with option maxdopt 1 we solved the problem know. there where some joins using different datatypes varchar and int. till sp2 there was no problem, since sp4 sql srv generates the execution plan on an other way and so a bad execution plan was the result. changeing the datatype to eqaul solves the problem thank for your support -- Show quotemfg andreas "Erland Sommarskog" wrote: > AnRa (A***@discussions.microsoft.com) writes: > > yes we used sp_updatestats after backup and restore. we also tried update > > statstics table with FULLSCAN. > > --> no improvement on performance > > > > parallelism is used, we tried also with hardware disable Hyperthreading > > --> no improvement on performance > > Did you try adding OPTION (MAXDOP 1). Parallelism is not always good > for performance. > > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >  |
|||||||||||||||||||||||
Other interesting topics