SQL Server 2000 Query slowing down in SQL Server 2005
Hi everybody,
I have a query slower in SQL Server 2005 than in SQL Server 2000. I have a database in SQL 2000, I put it on the same server, but with SQL 2005 and the query take 5 seconds instead of 0 seconds. The DB compatibility is SQL Server 2000 (I tried with 2005 and result is the same). Execution plan seems right and I tried to change some DB options without results. It is weird, when I remove left join on MandatsEx, it take 2 seconds. The view currentEmployeeLevelHistoric returns 45 000 rows and mandatsex has 0 rows.
Here is the sample:
Select ls.EmployeNiveau.pk_EmployeNiveauID as NoNiveau,
IsNull(nullif(ls.Traduction.Description, ''), ls.TraductionDefaut.Description) + ' (' +
ls.currentLevelHIstoric.NoNiveau + ')' As Nom,
ls.currentEmployeeLevelHistoric.DebAssign As DateAssignationDebut,
ls.EmployeNiveau.assignmentReason As AssignmentReason,
ls.currentEmployeeLevelHistoric.FinAssign As DateAssignationFin,
ls.MandatsEx.noDossier, ls.MandatsEx.pk_MandatID,
'' As Period, ls.currentEmployeeLevelHistoric.NiveauPrincipal,
ls.currentEmployeeLevelHistoric.pk_emplNiveauHisto_Id,
ls.EmployeNiveau.fk_NiveauID_Niveaux, ls.EmployeNiveau.No_Ent_leg,
IsNull(nullif(TradPere.Description, ''), TradDefautPere.Description) + ' (' + NiveauPere.NoNiveau
+ ')' As NomSup,
ls.EmployeNiveau.No_Ent_leg + ls.EmployeNiveau.no_divisio + ls.EmployeNiveau.no_sec_eco +
ls.EmployeNiveau.no_etabli + ls.EmployeNiveau.no_mat as employeeScope,
case when ls.mandatExternalisation.fk_mandatID_MandatsEx is null then 2 else 1 end as
ContractCategory
From ls.currentEmployeeLevelHistoric
Inner Join ls.EmployeNiveau on
ls.currentEmployeeLevelHistoric.pk_EmployeNiveauID = ls.EmployeNiveau.pk_EmployeNiveauID
Inner join ls.currentLevelHistoric On
ls.EmployeNiveau.fk_NiveauID_Niveaux = ls.currentLevelHistoric.fk_niveauID_niveaux
Left Outer Join ls.TraductionDefaut On
ls.currentLevelHIstoric.fk_TraductionID_TraductionDefaut = ls.TraductionDefaut.pk_NoTraduction
Left Outer Join ls.Traduction On
ls.TraductionDefaut.pk_NoTraduction = ls.Traduction.No_Traduction and
ls.Traduction.Langue = 1
Left Join ls.MandatsEx on
ls.EmployeNiveau.fk_MandatID_MandatsEx = ls.MandatsEx.pk_MandatID
Left Join ls.mandatExternalisation on
ls.MandatsEx.pk_MandatID = ls.mandatExternalisation.fk_mandatID_MandatsEx
left Join ls.Niveaux as NiveauPere on
NiveauPere.niveauID = ls.currentLevelHIstoric.supId
Left Outer Join ls.TraductionDefaut As TradDefautPere On
NiveauPere.fk_TraductionID_TraductionDefaut = TradDefautPere.pk_NoTraduction
Left Outer Join ls.Traduction As TradPere On
TradDefautPere.pk_NoTraduction = TradPere.No_Traduction and
TradPere.Langue = 1
Where ls.EmployeNiveau.fk_EmploID_Emplos = 345158 and (convert(varchar,
ls.currentEmployeeLevelHistoric.DebAssign, 112) <= '20060802' and
(ls.currentEmployeeLevelHistoric.FinAssign is null or
convert(varchar, ls.currentEmployeeLevelHistoric.FinAssign, 112) >= '20060802'))
Thank you
Steve Gadoury
Not sure if it's gonna make any difference or not (you did say that the plan looked ok), but it's unnecessary to use convert on the dates columns.
Where ls.EmployeNiveau.fk_EmploID_Emplos = 345158
and (convert(varchar, ls.currentEmployeeLevelHistoric.DebAssign, 112) <= '20060802'
and (ls.currentEmployeeLevelHistoric.FinAssign is null
or convert(varchar, ls.currentEmployeeLevelHistoric.FinAssign, 112) >= '20060802'))
..assuming these are datetimes (which they probably are?), you can loose the converts to increase the likleyhood of index usage
........
Where ls.EmployeNiveau.fk_EmploID_Emplos = 345158
and ls.currentEmployeeLevelHistoric.DebAssign <= '20060802'
and (ls.currentEmployeeLevelHistoric.FinAssign is null
or ls.currentEmployeeLevelHistoric.FinAssign >= '20060802'))
Just a final note on explicitness..
Please don't rely on defaults when writing code. Defining varchar instead of varchar(n) may produce unexpected results, since the default value of n can be different in different situations.
-- from BOL --
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.
-- end BOL --
=;o)
/Kenneth
Hi,
Thank you for your reply. I already tried to convert varchar(8) without result and I also tried to run query without the "where" clause. For your information, I use convert in this context because I have a full date saved in DB (yyyyMMdd HHmm) and I have a condition on a short date (yyyyMMdd).
Finally, I know this is not a great query, but I don't understand why it is so slow in SQL 2005 and not in SQL 2000, even if I use DB compatibility level SQL 2000.
Thank you
Well, the primary source of finding out what's happening with a query is to look at the queryplan.
You said that they both 'looked OK'.
Is that the same as they were identical, or were there any differences?
/Kenneth
Hi,
In the query plan, I have many compute scalar (0%) and hash match (inner join) in sql 2005 and the query plan seems to repeat the same index seek many times.
Thank you
Also, I have only index seeks in SQL 2000 and many index scan in sql 2005
Very simplified we can say that seeks = fast and scans = slow, so it seems that this may be the source to your problem.
Do you have the same data in the two db's, or are there more in the 2005 version?
Do you have the same indexes in place on both?
Try UPDATE STATISTICS on the 2005 to make sure statistics are up to date.
/Kenneth
I have exactly the same datas and the same indexes. I tried update statistics (on all tables and views) without result. I also tried to update statistics with a compatibility level of SQL 2000 and SQL 2005.
Thank you
Please post SET STATISTICS PROFILE output from SQL Server 2000 and 2005 for the same query, schema and data. It is important that the tables between the servers are identical. The output will give clue as to what might be wrong. It might be a plan change in SQL Server 2005 that is causing the slowness.
Thanks for the statistics profile output. Yes, there is plan change between the queries from SQL Server 2000 to SQL Server 2005. The problem is that there are few clustered index and index scans in SQL Server 2005 which is producing large number of rows during execution. See partial output below:
56124 1 | | | | | | | |--Filter
56124 1 | | | | | | | |--Compute 56124 1 | | | | | | | |--Hash 56124 1 | | | | | | | |--56128 1 | | | | | | | |--56128.0 7.4976854E-2 6.1897799E-2 11 0.13687465 88667 1 | | | | | | |--Table 88667 1 | | | | | | |--Filter(WHERE:
And table spools as seen in the output is generally a bad sign that some required indexes are missing. Also, I see lot of bookmark lookups in SQL Server 2000. These have been replaced by clustered index seek or rid lookups in SQL Server 2005 and will appear as joins in the showplan.
Can you try the following before I suggest some course of action?
1. I am assuming that the data and schema is identical for the involved tables
2. Run UDPATE STATISTICS WITH FULLSCAN on all these tables
3. Run the queries again in SQL Server 2000 / 2005 with SET STATISTICS PROFILE ON
It is possible to rewrite some of the conditions like below to make better use of indexes based on what I am seeing in the output.
(convert(varchar(8),
ls.currentEmployeeLevelHistoric.DebAssign, 112) <= '20060802' and
(ls.currentEmployeeLevelHistoric.FinAssign is null or
convert(varchar(8), ls.currentEmployeeLevelHistoric.FinAssign, 112) >= '20060802'))
But it is hard to say. It might even help if you drop redundant indexes (if any) from the table(s). Lastly, we could try using the new DMVs in SQL Server 2005 (like sys.dm_db_missing_index_details) to see which indexes will benefit your query.
I tried fullscan on all tables in SQL 2000 and 2005 ans there is the result: ftp.sigma-rh.com/SampleSQL2005SteveWithFullscan.txt
I know "where clause" is not great, but If I remove it, I have the same result. I tried to run "Database Engine Tuning Advisor", run all the indexes proposed without results.
When I run sys.dm_db_missing_index_details, I have this output:
index_handle database_id object_id equality_columns inequality_columns included_columns statement
-- -- - - - -
4323 12 1310627712 [fk_StructureID_Structure] NULL [NONIVEAU], [fk_TraductionID_TraductionDefaut], [NiveauID] [Bell_steve].[ls].[NIVEAUX]
Thank you
Looks like the problem is a different plan strategy in SQL Server 2005. It will be hard to troubleshoot the issue completely given that there are so many tables involved in the query and the number of possible plans. One thing I noticed is that there are couple of hash joins in the query in SQL Server 2005. So you could try including a loop join hint in your query at the end "OPTION(loop join)" and see if you get the same plan as in SQL Server 2000. Note that this might produce an even worse plan also. It is hard to say. It will be hard to predict what will happen if the data in the tables grow and loop join is still used. Currently, what is the difference in execution times between the queries? Can you try with different data sets and see if the SQL Server 2005 query performs better? To summarize try the following:
0. Are you using SQL Server 2005 with latest service pack SP1 and the post SP1 updates? This is very important. If you are still on RTM then you may want to try after the latest updates. This will be the first thing that PSS might also want to do if you call them and you are still on RTM. SP1 has lot of QP fixes and it is possible that some of these cases might have got fixed.
1. Create the missing index reported by the DMV and see if the plan changes or execution time improves
2. If #1 doesn't work then try to use the loop join hint (option (loop join)) in the query and see if it helps
3. If both #1 and #2 doesn't work then you will have to try to just analzye the plan change in SQL Server 2005. Try to see how you can eliminate the scans on some indexes. This might require rewriting some of your predicates or modifying the query.
Lastly, if this is a common occurrence on your database and you are seeing lot of plan changes between SQL Server 2000 and 2005 you can contact PSS. They will be able to troubleshoot the problem since it requires at least db with stats to try and run the query & make changes.
Links to SP1 and post-SP1 updates.
Thank you for your help. We use SP1, but not SP1 Hot fix. We will install it later to see result. When I create all index recommanded, I win 1 second, but this is not enough. Option loop join does not change the situation.
I will advise you when SP1 hotfix will be install.
Thank you
