|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Query is much slower in 2008 than in 2005
Hello all
we have a query involving several tables, subselect and scalar-valued functions. It ran in about 10 minutes in SQL Server 2005 on a old server with 4 GB of RAM. Now we upgraded to SQL Server 2008, on a server with 8 processors and 16 GB of RAM (8 dedicated to the instance), and the query runs in ... 1 hour ! Of course I rebuilt the indexes and updated the statistics, but the query is still very slow. Any hint about what I could search for would be welcome pher Have you looked at the query plan? Does it show any table/index scans?
-- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "wpher56" <wphe***@gmail.com> wrote in message we have a query involving several tables, subselect and scalar-valuednews:%23pb1REO8JHA.1248@TK2MSFTNGP04.phx.gbl... Hello all functions. It ran in about 10 minutes in SQL Server 2005 on a old server with 4 GB of RAM. Now we upgraded to SQL Server 2008, on a server with 8 processors and 16 GB of RAM (8 dedicated to the instance), and the query runs in ... 1 hour ! Of course I rebuilt the indexes and updated the statistics, but the query is still very slow. Any hint about what I could search for would be welcome pher I'd check if the query plans have changed. Normally, that would be the case
for this type of issues. If not, I'd do a detailed trace on both SQL2005 and SQL2008 to find out where the time is being spent in each case, and focus on the part that execution seems to be stuck, i.e. the part it takes a significant amoutn of time to execution on SQL2008. Then maybe, it's not a version issue, but a trace issue. Does the following happen to apply? http://sqlblog.com/blogs/linchi_shea/archive/2009/06/15/performance-impact-sql-trace-and-user-defined-scalar-functions.aspx Linchi Show quoteHide quote "wpher56" wrote: > Hello all > we have a query involving several tables, subselect and scalar-valued > functions. It ran in about 10 minutes in SQL Server 2005 on a old server > with 4 GB of RAM. Now we upgraded to SQL Server 2008, on a server with 8 > processors and 16 GB of RAM (8 dedicated to the instance), and the query > runs in ... 1 hour ! Of course I rebuilt the indexes and updated the > statistics, but the query is still very slow. > Any hint about what I could search for would be welcome > pher > > Thank you both for your help.
There is no trace running, except the default one. Anyway I disabled it, but no change in the execution time. Concerning the execution plans: in SQL Server 2005 I get an error "the query has exceeded the maximum number of result sets" and it seems that only part of the execution plan is displayed. In SQL Server 2008, I don't get anything at all ! After 1:55, the SSMS stops responding and I have to close it ! pher Show quoteHide quote "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message news:F190B147-0926-46E8-8F7B-4D202B89758A@microsoft.com... > I'd check if the query plans have changed. Normally, that would be the > case > for this type of issues. > > If not, I'd do a detailed trace on both SQL2005 and SQL2008 to find out > where the time is being spent in each case, and focus on the part that > execution seems to be stuck, i.e. the part it takes a significant amoutn > of > time to execution on SQL2008. > > Then maybe, it's not a version issue, but a trace issue. Does the > following > happen to apply? > > http://sqlblog.com/blogs/linchi_shea/archive/2009/06/15/performance-impact-sql-trace-and-user-defined-scalar-functions.aspx > > Linchi > > "wpher56" wrote: > >> Hello all >> we have a query involving several tables, subselect and scalar-valued >> functions. It ran in about 10 minutes in SQL Server 2005 on a old server >> with 4 GB of RAM. Now we upgraded to SQL Server 2008, on a server with 8 >> processors and 16 GB of RAM (8 dedicated to the instance), and the query >> runs in ... 1 hour ! Of course I rebuilt the indexes and updated the >> statistics, but the query is still very slow. >> Any hint about what I could search for would be welcome >> pher >> >> Do you know the exact query that is the problem? Perhaps you can have it
generate the plan and post it here. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "wpher56" <wphe***@gmail.com> wrote in message Thank you both for your help.news:%23rzhenA9JHA.4168@TK2MSFTNGP05.phx.gbl... There is no trace running, except the default one. Anyway I disabled it, but no change in the execution time. Concerning the execution plans: in SQL Server 2005 I get an error "the query has exceeded the maximum number of result sets" and it seems that only part of the execution plan is displayed. In SQL Server 2008, I don't get anything at all ! After 1:55, the SSMS stops responding and I have to close it ! pher Show quoteHide quote "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message news:F190B147-0926-46E8-8F7B-4D202B89758A@microsoft.com... > I'd check if the query plans have changed. Normally, that would be the > case > for this type of issues. > > If not, I'd do a detailed trace on both SQL2005 and SQL2008 to find out > where the time is being spent in each case, and focus on the part that > execution seems to be stuck, i.e. the part it takes a significant amoutn > of > time to execution on SQL2008. > > Then maybe, it's not a version issue, but a trace issue. Does the > following > happen to apply? > > http://sqlblog.com/blogs/linchi_shea/archive/2009/06/15/performance-impact-sql-trace-and-user-defined-scalar-functions.aspx > > Linchi > > "wpher56" wrote: > >> Hello all >> we have a query involving several tables, subselect and scalar-valued >> functions. It ran in about 10 minutes in SQL Server 2005 on a old server >> with 4 GB of RAM. Now we upgraded to SQL Server 2008, on a server with 8 >> processors and 16 GB of RAM (8 dedicated to the instance), and the query >> runs in ... 1 hour ! Of course I rebuilt the indexes and updated the >> statistics, but the query is still very slow. >> Any hint about what I could search for would be welcome >> pher >> >> Hi Tom
I let my 2008 server show the plan last night, it took 42 minutes ! The plan tells me that 92% of the cost is relative to a part of the query that takes 12 seconds to execute for an overall run time of 1 hour ! So I spent 2-3 days analyzing this query (5 pages long). I could find out that 95% of the execution time was spent in functions containing where-clauses with many IN, AND, OR and LIKE statements, in order to include/exclude people with certain characteristics. It does not appear to be a problem of index, statistics or a bad join in the query. I suspect that the query was running quicker in 2005 just because there were less data ! To validate this assertion, I asked my developer to remove half of the data (on a copy of the database). This will take some time, I will come back with a result soon. pher Show quoteHide quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:OEvxABG9JHA.1336@TK2MSFTNGP05.phx.gbl... > Do you know the exact query that is the problem? Perhaps you can have it > generate the plan and post it here. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "wpher56" <wphe***@gmail.com> wrote in message > news:%23rzhenA9JHA.4168@TK2MSFTNGP05.phx.gbl... > Thank you both for your help. > There is no trace running, except the default one. Anyway I disabled it, > but > no change in the execution time. > Concerning the execution plans: > in SQL Server 2005 I get an error "the query has exceeded the maximum > number > of result sets" and it seems that only part of the execution plan is > displayed. > In SQL Server 2008, I don't get anything at all ! After 1:55, the SSMS > stops > responding and I have to close it ! > > pher > > > > "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message > news:F190B147-0926-46E8-8F7B-4D202B89758A@microsoft.com... >> I'd check if the query plans have changed. Normally, that would be the >> case >> for this type of issues. >> >> If not, I'd do a detailed trace on both SQL2005 and SQL2008 to find out >> where the time is being spent in each case, and focus on the part that >> execution seems to be stuck, i.e. the part it takes a significant amoutn >> of >> time to execution on SQL2008. >> >> Then maybe, it's not a version issue, but a trace issue. Does the >> following >> happen to apply? >> >> http://sqlblog.com/blogs/linchi_shea/archive/2009/06/15/performance-impact-sql-trace-and-user-defined-scalar-functions.aspx >> >> Linchi >> >> "wpher56" wrote: >> >>> Hello all >>> we have a query involving several tables, subselect and scalar-valued >>> functions. It ran in about 10 minutes in SQL Server 2005 on a old server >>> with 4 GB of RAM. Now we upgraded to SQL Server 2008, on a server with 8 >>> processors and 16 GB of RAM (8 dedicated to the instance), and the query >>> runs in ... 1 hour ! Of course I rebuilt the indexes and updated the >>> statistics, but the query is still very slow. >>> Any hint about what I could search for would be welcome >>> pher >>> >>> > Functions should be like me - short and sweet. ;-) You may have to pare
down the query to eliminate the functions. Any query that is 5 pages long is likely in need of a re-write. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "wpher56" <wphe***@gmail.com> wrote in message I let my 2008 server show the plan last night, it took 42 minutes ! The plannews:%23FFZ$CK9JHA.1376@TK2MSFTNGP02.phx.gbl... Hi Tom tells me that 92% of the cost is relative to a part of the query that takes 12 seconds to execute for an overall run time of 1 hour ! So I spent 2-3 days analyzing this query (5 pages long). I could find out that 95% of the execution time was spent in functions containing where-clauses with many IN, AND, OR and LIKE statements, in order to include/exclude people with certain characteristics. It does not appear to be a problem of index, statistics or a bad join in the query. I suspect that the query was running quicker in 2005 just because there were less data ! To validate this assertion, I asked my developer to remove half of the data (on a copy of the database). This will take some time, I will come back with a result soon. pher Show quoteHide quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:OEvxABG9JHA.1336@TK2MSFTNGP05.phx.gbl... > Do you know the exact query that is the problem? Perhaps you can have it > generate the plan and post it here. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "wpher56" <wphe***@gmail.com> wrote in message > news:%23rzhenA9JHA.4168@TK2MSFTNGP05.phx.gbl... > Thank you both for your help. > There is no trace running, except the default one. Anyway I disabled it, > but > no change in the execution time. > Concerning the execution plans: > in SQL Server 2005 I get an error "the query has exceeded the maximum > number > of result sets" and it seems that only part of the execution plan is > displayed. > In SQL Server 2008, I don't get anything at all ! After 1:55, the SSMS > stops > responding and I have to close it ! > > pher > > > > "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message > news:F190B147-0926-46E8-8F7B-4D202B89758A@microsoft.com... >> I'd check if the query plans have changed. Normally, that would be the >> case >> for this type of issues. >> >> If not, I'd do a detailed trace on both SQL2005 and SQL2008 to find out >> where the time is being spent in each case, and focus on the part that >> execution seems to be stuck, i.e. the part it takes a significant amoutn >> of >> time to execution on SQL2008. >> >> Then maybe, it's not a version issue, but a trace issue. Does the >> following >> happen to apply? >> >> http://sqlblog.com/blogs/linchi_shea/archive/2009/06/15/performance-impact-sql-trace-and-user-defined-scalar-functions.aspx >> >> Linchi >> >> "wpher56" wrote: >> >>> Hello all >>> we have a query involving several tables, subselect and scalar-valued >>> functions. It ran in about 10 minutes in SQL Server 2005 on a old server >>> with 4 GB of RAM. Now we upgraded to SQL Server 2008, on a server with 8 >>> processors and 16 GB of RAM (8 dedicated to the instance), and the query >>> runs in ... 1 hour ! Of course I rebuilt the indexes and updated the >>> statistics, but the query is still very slow. >>> Any hint about what I could search for would be welcome >>> pher >>> >>> >  |
|||||||||||||||||||||||
Other interesting topics