Query return time too long on some configurations

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Query return time too long on some configurations

Author

Topic

DeepGround
Yak Posting Veteran

73 Posts

Posted - 2009-08-27 : 18:23:28

Hello,

I have multiple boxes, all configured the same, Windows 2003 SQL 2005 SP2.

On our inhouse server I have a query that takes 1 second to finsih. On many other servers including one I just setup as a new test exactly the same it takes 20+ minutes to finish. The same exact database in the same exact state. The only difference is that on the server that runs in 1 second we use development edition and we use enterprise edition / standard edition on the other servers.

What configuration could possibly be taking so much longer?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-08-27 : 18:44:15

Run UPDATE STATS WITH FULLSCAN on the problematic systems. Also check fragmentation levels and ALTER INDEX REBUILD where needed. View blocking, compare execution plans, check out PerfMon counters, ... The list goes on and on.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

DeepGround
Yak Posting Veteran

73 Posts

Posted - 2009-08-27 : 19:15:56

That was the first day of checks, perfmon shows no hardware limitations, tried updating stats and rebuilding each table, there is no other queries running that could block as no one has access to the database, comparing execution plans now...

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-28 : 00:10:12

set statistic io on

then show us the query, the stats, and the execution plan..on the long running server

DeepGround
Yak Posting Veteran

73 Posts

Posted - 2009-08-28 : 12:00:26

Is there a way I can get this statistic info without finishing the query. I have never waited for the entire query to finish, I figured it would be like 20 minutes but I am into it 40 minutes and counting so far. It takes forever!!

DeepGround
Yak Posting Veteran

73 Posts

Posted - 2009-08-28 : 12:55:47

Ok it ran for an hour and a half, I finally stopped it, no results on the statistics. This is dumb. How in the world do I figure out what the server is waiting for?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-28 : 13:25:54

you can show the estimated execution plan by hitting <ctrl> L without executing the query.

i would rerun it though, then in antoher window, execute sp_who2 to see if it is being blocked.

can we see the query?

DeepGround
Yak Posting Veteran

73 Posts

Posted - 2009-08-28 : 13:42:19

I can get the execution plan but not sure how to post it in here.

As for the query... Here goes:

SELECT FilteredStudentsWeb.StudentID, vwStudentsJoin.SISStudentID, vwStudentsJoin.StudentName, SchoolNum = vwStudentsJoin.SchoolCodeDisplay, vwStudentsJoin.SchoolName, vwStudentsJoin.Birthdate, vwStudentsJoin.Gender, vwStudentsJoin.OrigEnterDate, vwStudentsJoin.Grade, vwStudentsJoin.EngProf, vwStudentsJoin.ELLDesig, vwStudentsJoin.InstrSet, case when vwStudentsJoin.GiftedYN = 1 then 'X' else '' end as Gifted, vwStudentsJoin.Advisor, vwStudentsJoin.AdvisorName, vwStudentsJoin.Parent, vwStudentsJoin.Address, vwStudentsJoin.CityStateZip, vwStudentsJoin.Phone, vwScoresJoin1.TestID, vwScoresJoin1.TestName, vwScoresJoin1.TestDate, vwScoresJoin1.ScoreStudentGrade, vwScoresJoin1.TestGrade, vwScoresJoin1.TestSubject, vwScoresJoin1.TestMM, vwScoresJoin1.ScoreSchoolName, vwScoresJoin1.ScoreAdvisorName, vwScoresJoin1.ScoreCorrect, vwScoresJoin1.TestPossible, vwScoresJoin1.ScoreProficiency, vwScoresJoin1.PerformanceBand, vwScoresJoin1.ProfCode, vwScoresJoin1.ProfDescription, vwScoresJoin1.TestStudentProfile, vwScoresJoin1.TestELProfile FROM (select StudentID from students students where ((SchoolNum = 460) And ((Advisor = 913) or (TeacherPeriod0 = 913) or (TeacherPeriod1 = 913) or (TeacherPeriod2 = 913) or (TeacherPeriod3 = 913) or (TeacherPeriod4 = 913) or (TeacherPeriod5 = 913) or (TeacherPeriod6 = 913) or (TeacherPeriod7 = 913) or (TeacherPeriod8 = 913)) And ((( LastSchoolYear=9 AND (StatusOfEnrollment is NULL or StatusOfEnrollment='')))))) as filteredstudentsweb LEFT JOIN (SELECT * FROM (SELECT testprofperformanceband.performanceband as proficientperformanceband, Scores.ScoreTestID, Scores.ScoreStudentID, Scores.ScoreAdvisor, Scores.ScoreCorrect, Scores.ScoreProficiency, Scores.ScoreWeight, Scores.ScoreTeacherInput, Scores.ScoreNPRNotAvail, Scores.ScoreRawStanford, Scores.ScoreAdvisorName, Scores.ScoreDrop, Scores.ScoreSchoolNum, dbo.GetSchoolName(Scores.ScoreSchoolNum) AS ScoreSchoolName, Scores.APIExclude AS ScoreAPIExclude, Scores.[User], Scores.DateStamp AS ScoreDateStamp, Scores.ScoreNCE, Scores.ScoreScaled, Scores.ScoreStanine, Scores.ScoreGE, Scores.DMExclude AS ScoreDMExclude, Scores.PostID AS ScorePostID, Scores.ScoreStudentGrade, Scores.PerformanceBand, Tests.TestGrade, Tests.ID AS TestID, Tests.TestDate, Tests.TestName, Tests.TestType, Tests.TestSubject, Tests.TestPossible, Tests.TestAboveProf, Tests.TestProf, Tests.TestWeight, Tests.TestSelect, Tests.TestRunRecord, Tests.TestMM, Tests.TestProgram, Tests.TestLow, Tests.TestHigh, Tests.TestGroupID, Tests.NPR AS TestNPR, Tests.StudentProfile AS TestStudentProfile, Tests.ELProfile AS TestELProfile, Tests.AltTestID AS TestAltTestID, Tests.PostID AS TestPostID, Tests.CAStandardID AS TestCAStandardID, TestProfs.ID AS ProfID, TestProfs.TestID AS ProfTestID, TestProfs.MinScore AS ProfMinScore, TestProfs1.MinScore AS ProfMinScore1, TestProfs2.MinScore AS ProfMinScore2, TestProfs3.MinScore AS ProfMinScore3, TestProfs4.MinScore AS ProfMinScore4, TestProfs5.MinScore AS ProfMinScore5, TestProfs.ProfNumeric, TestProfs.ProfCode, TestProfs.ProfDescription, TestProfs.PostID AS ProfPostID, vwTestProfsOnlyP.ID AS ProficientProfID, vwTestProfsOnlyP.TestID AS ProficientProfTestID, vwTestProfsOnlyP.MinScore AS ProficientProfMinScore, vwTestProfsOnlyP.ProfNumeric AS ProficientProfNumeric, vwTestProfsOnlyP.ProfCode AS ProficientProfCode, vwTestProfsOnlyP.ProfDescription AS ProficientProfDescription, vwTestProfsOnlyP.PostID AS ProficientProfPostID, vwStudentsJoin.StudentID, vwStudentsJoin.SISStudentID, vwStudentsJoin.LastName, vwStudentsJoin.FirstName, vwStudentsJoin.MiddleName, vwStudentsJoin.StudentName, vwStudentsJoin.Birthdate, vwStudentsJoin.Gender, vwStudentsJoin.Grade, vwStudentsJoin.EthnicCode, vwStudentsJoin.EngProf, vwStudentsJoin.HomeLang, vwStudentsJoin.InstrSet, vwStudentsJoin.RSP, vwStudentsJoin.Lunch, vwStudentsJoin.Gifted, vwStudentsJoin.Advisor, vwStudentsJoin.DIS, vwStudentsJoin.Parent, vwStudentsJoin.Address, vwStudentsJoin.City, vwStudentsJoin.State, vwStudentsJoin.Zip, vwStudentsJoin.CityStateZip, vwStudentsJoin.Phone, vwStudentsJoin.OrigEnterDate, vwStudentsJoin.LeaveDate, vwStudentsJoin.LastUpdate, vwStudentsJoin.StudentLink, vwStudentsJoin.LastSchoolYear, vwStudentsJoin.StudentLink98, vwStudentsJoin.Title1, vwStudentsJoin.ELLClass, vwStudentsJoin.ELLDesig, vwStudentsJoin.PrimaryLang, vwStudentsJoin.DIS2, vwStudentsJoin.DIS3, vwStudentsJoin.DIS4, vwStudentsJoin.DistrictMobility, vwStudentsJoin.SchoolMobility, vwStudentsJoin.DistrictMobilityTemp, vwStudentsJoin.UserDef1, vwStudentsJoin.UserDef2, vwStudentsJoin.UserDef3, vwStudentsJoin.UserDef4, vwStudentsJoin.UserDef5, vwStudentsJoin.UserDef6, vwStudentsJoin.UserDef7, vwStudentsJoin.UserDef8, vwStudentsJoin.UserDef9, vwStudentsJoin.UserDef10, vwStudentsJoin.UserDef11, vwStudentsJoin.UserDef12, vwStudentsJoin.UserDef13, vwStudentsJoin.UserDef14, vwStudentsJoin.UserDef15, vwStudentsJoin.HealthyStart, vwStudentsJoin.GiftedYN, vwStudentsJoin.MigrantYN, vwStudentsJoin.MentoredYN, vwStudentsJoin.ParentEd, vwStudentsJoin.ReadingMM, vwStudentsJoin.MathMM, vwStudentsJoin.Track, vwStudentsJoin.CaELSince, vwStudentsJoin.ReturnDateLeft, vwStudentsJoin.ReturnDate, vwStudentsJoin.AsianPI, vwStudentsJoin.NormalMatriculationYN, vwStudentsJoin.Disability, vwStudentsJoin.ReadingMMTotal, vwStudentsJoin.MathMMTotal, vwStudentsJoin.Retained, vwStudentsJoin.AtRisk, vwStudentsJoin.Title7YN, vwStudentsJoin.IndianEdYN, vwStudentsJoin.Section504YN, vwStudentsJoin.ClassReduction, vwStudentsJoin.USEntryDate, vwStudentsJoin.StateCompEdYN, vwStudentsJoin.ELDYN, vwStudentsJoin.BilingualYN, vwStudentsJoin.SDAIEYN, vwStudentsJoin.Teacher1, vwStudentsJoin.Teacher2, vwStudentsJoin.Teacher3, vwStudentsJoin.Teacher4, vwStudentsJoin.Teacher5, vwStudentsJoin.Teacher6, vwStudentsJoin.Teacher7, vwStudentsJoin.Teacher8, vwStudentsJoin.Teacher9, vwStudentsJoin.Teacher10, vwStudentsJoin.CahseeELA, vwStudentsJoin.CahseeMath, vwStudentsJoin.StudentPostID, vwStudentsJoin.TeacherPeriod0,vwStudentsJoin.TeacherPeriod1,vwStudentsJoin.TeacherPeriod2,vwStudentsJoin.TeacherPeriod3, vwStudentsJoin.TeacherPeriod4,vwStudentsJoin.TeacherPeriod5,vwStudentsJoin.TeacherPeriod6,vwStudentsJoin.TeacherPeriod7,vwStudentsJoin.TeacherPeriod8, vwStudentsJoin.AdvisorSchoolNum, vwStudentsJoin.AdvisorNumber, vwStudentsJoin.AdvisorLastName, vwStudentsJoin.AdvisorFirstName, vwStudentsJoin.AdvisorName, vwStudentsJoin.AdvisorAltNumber, vwStudentsJoin.AdvisorPostID, vwStudentsJoin.SchoolCode1, vwStudentsJoin.SchoolCode, vwStudentsJoin.SchoolName, vwStudentsJoin.PeriodAttendance, vwStudentsJoin.SchoolWideTitle1YN, vwStudentsJoin.ClassSizeReductionHalfYN, vwStudentsJoin.ClassSizeReductionFullYN, vwStudentsJoin.HighSchoolYN, vwStudentsJoin.ASAM, vwStudentsJoin.SchoolTestDate, vwStudentsJoin.SchoolPostID, vwStudentsJoin.SchoolNum, vwStudentsJoin.DisAltAssess,vwStudentsJoin.DistrictEnrolledYears, vwStudentsJoin.ELDPriLang, vwStudentsJoin.StatusOfEnrollment, vwStudentsJoin.ELParentalWaiverGranted, vwStudentsJoin.ELParentalWaiverSubmitted,vwStudentsJoin.RFEPYearRedisig, vwStudentsJoin.SchoolEnrolledYears, vwStudentsJoin.InstrSet2, vwStudentsJoin.InstrSet3, vwStudentsJoin.InstrSet4,vwStudentsJoin.IEP, vwStudentsJoin.OriginNationCode, ShortTestDate= replace(convert(varchar,Tests.TestDate,10),'-','/'), case when ScoreCorrect < vwTestProfsOnlyP.MinScore then 0 else 1 end as IsProficient, case ScoreProficiency when 1 then 1 else 0 end as IsProfLevel1, case ScoreProficiency when 2 then 1 else 0 end as IsProfLevel2, case ScoreProficiency when 3 then 1 else 0 end as IsProfLevel3, case ScoreProficiency when 4 then 1 else 0 end as IsProfLevel4, case ScoreProficiency when 5 then 1 else 0 end as IsProfLevel5, case scores.PerformanceBand when 1 then 1 else 0 end as IsPerformanceBand1, case scores.PerformanceBand when 2 then 1 else 0 end as IsPerformanceBand2, case scores.PerformanceBand when 3 then 1 else 0 end as IsPerformanceBand3, case scores.PerformanceBand when 4 then 1 else 0 end as IsPerformanceBand4, case scores.PerformanceBand when 5 then 1 else 0 end as IsPerformanceBand5 FROM Scores Scores WITH (NOLOCK) LEFT OUTER JOIN TestProfs TestProfs WITH (NOLOCK) ON Scores.ScoreTestID = TestProfs.TestID AND Scores.ScoreProficiency = TestProfs.ProfNumeric LEFT OUTER JOIN testProfs TestProfs1 WITH (NOLOCK) on testprofs.testid = TestProfs1.testid and TestProfs1.profnumeric = 1 left outer join testProfs testProfs2 WITH (NOLOCK) on testprofs.testid = testprofs2.testid and testprofs2.profnumeric = 2 left outer join testprofs testprofs3 WITH (NOLOCK) on testprofs.testid = testprofs3.testid and testprofs3.profnumeric = 3 left outer join testprofs testprofs4 WITH (NOLOCK) on testprofs.testid = testprofs4.testid and testprofs4.profnumeric = 4 left outer join testprofs testprofs5 WITH (NOLOCK) on testprofs.testid = testprofs5.testid and testprofs5.profnumeric = 5 left join testprofs tp WITH (NOLOCK) on scores.scoretestid = tp.testid and tp.proficient = 1 left join testprofperformanceband on tp.id = testprofperformanceband.testprofid left outer join (SELECT Students.SchoolNum, Students.StudentID, Students.SISStudentID, Students.LastName, Students.FirstName, Students.MiddleName, isnull(Students.LastName, '') + N', ' + isnull(Students.FirstName, '') + N' ' + isnull(Students.MiddleName, '') AS StudentName, Students.Birthdate, Students.Gender, Students.Grade, Students.EthnicCode, Students.EngProf, Students.HomeLang, Students.InstrSet, Students.RSP, Students.Lunch, Students.Gifted, Students.Advisor, Students.DIS, Students.Parent, Students.Address, Students.City, Students.State, Students.Zip, isnull(Students.City, '') + N', ' + isnull(Students.State, '') + N' ' + isnull(Students.Zip, '') AS CityStateZip, Students.Phone, Students.OrigEnterDate, Students.LeaveDate, Students.LastUpdate, Students.StudentLink, Students.LastSchoolYear, Students.StudentLink98, Students.Title1, Students.ELLClass, Students.ELLDesig, Students.PrimaryLang, Students.DIS2, Students.DIS3, Students.DIS4, Students.DistrictMobility, Students.SchoolMobility, Students.DistrictMobilityTemp, Students.UserDef1, Students.UserDef2, Students.UserDef3, Students.UserDef4, Students.UserDef5, Students.UserDef6, Students.UserDef7, Students.UserDef8, Students.UserDef9, Students.UserDef10, Students.UserDef11, Students.UserDef12, Students.UserDef13, Students.UserDef14, Students.UserDef15, Students.HealthyStart, Students.GiftedYN, Students.MigrantYN, Students.MentoredYN, Students.ParentEd, Students.ReadingMM, Students.MathMM, Students.Track, Students.CaELSince, Students.ReturnDateLeft, Students.ReturnDate, Students.AsianPI, Students.NormalMatriculationYN, Students.Disability, Students.ReadingMMTotal, Students.MathMMTotal, Students.Retained, Students.AtRisk, Students.Title7YN, Students.IndianEdYN, Students.Section504YN, Students.ClassReduction, Students.USEntryDate, Students.StateCompEdYN, Students.ELDYN, Students.BilingualYN, Students.SDAIEYN, Students.Teacher1, Students.Teacher2, Students.Teacher3, Students.Teacher4, Students.Teacher5, Students.Teacher6, Students.Teacher7, Students.Teacher8, Students.Teacher9, Students.Teacher10, Students.CahseeELA, Students.CahseeMath, Students.PostID AS StudentPostID, Students.TeacherPeriod0,Students.TeacherPeriod1,Students.TeacherPeriod2,Students.TeacherPeriod3, Students.TeacherPeriod4,Students.TeacherPeriod5,Students.TeacherPeriod6,Students.TeacherPeriod7,Students.TeacherPeriod8, Advisors.AdvisorSchoolNum, Advisors.AdvisorNumber, Advisors.AdvisorLastName, Students.DisAltAssess,Students.DistrictEnrolledYears, Students.ELDPriLang, Students.StatusOfEnrollment, Students.ELParentalWaiverGranted, Students.ELParentalWaiverSubmitted,Students.RFEPYearRedisig, Students.SchoolEnrolledYears, Students.InstrSet2, Students.InstrSet3, Students.InstrSet4, Students.IEP, Students.OriginNationCode, Advisors.AdvisorFirstName, isnull(Advisors.AdvisorLastName, '') + N', ' + isnull(Advisors.AdvisorFirstName, '') AS AdvisorName, Advisors.AdvisorAltNumber, Advisors.PostID AS AdvisorPostID, SchoolCodes.SchoolCode1, SchoolCodes.SchoolCode, SchoolCodes.SchoolCodeDisplay, SchoolCodes.SchoolName, SchoolCodes.PeriodAttendance, SchoolCodes.SchoolWideTitle1YN, SchoolCodes.ClassSizeReductionHalfYN, SchoolCodes.ClassSizeReductionFullYN, SchoolCodes.HighSchoolYN, SchoolCodes.ASAM, SchoolCodes.TestDate AS SchoolTestDate, SchoolCodes.PostID AS SchoolPostID FROM Students Students WITH (NOLOCK) LEFT OUTER JOIN SchoolCodes SchoolCodes WITH (NOLOCK) ON Students.SchoolNum = SchoolCodes.SchoolCode1 LEFT OUTER JOIN Advisors Advisors WITH (NOLOCK) ON Students.SchoolNum = Advisors.AdvisorSchoolNum AND Students.Advisor = Advisors.AdvisorNumber) vwStudentsJoin ON Scores.ScoreStudentID = vwStudentsJoin.StudentID LEFT OUTER JOIN Tests Tests WITH (NOLOCK) ON Scores.ScoreTestID = Tests.ID LEFT OUTER JOIN vwTestProfsOnlyP vwTestProfsOnlyP WITH (NOLOCK) ON Tests.ID = vwTestProfsOnlyP.TestID) vwScoresJoin WHERE TestStudentProfile = 1) vwScoresJoin1 ON FilteredStudentsWeb.StudentID = vwScoresJoin1.StudentID LEFT JOIN (SELECT Students.SchoolNum, Students.StudentID, Students.SISStudentID, Students.LastName, Students.FirstName, Students.MiddleName, isnull(Students.LastName, '') + N', ' + isnull(Students.FirstName, '') + N' ' + isnull(Students.MiddleName, '') AS StudentName, Students.Birthdate, Students.Gender, Students.Grade, Students.EthnicCode, Students.EngProf, Students.HomeLang, Students.InstrSet, Students.RSP, Students.Lunch, Students.Gifted, Students.Advisor, Students.DIS, Students.Parent, Students.Address, Students.City, Students.State, Students.Zip, isnull(Students.City, '') + N', ' + isnull(Students.State, '') + N' ' + isnull(Students.Zip, '') AS CityStateZip, Students.Phone, Students.OrigEnterDate, Students.LeaveDate, Students.LastUpdate, Students.StudentLink, Students.LastSchoolYear, Students.StudentLink98, Students.Title1, Students.ELLClass, Students.ELLDesig, Students.PrimaryLang, Students.DIS2, Students.DIS3, Students.DIS4, Students.DistrictMobility, Students.SchoolMobility, Students.DistrictMobilityTemp, Students.UserDef1, Students.UserDef2, Students.UserDef3, Students.UserDef4, Students.UserDef5, Students.UserDef6, Students.UserDef7, Students.UserDef8, Students.UserDef9, Students.UserDef10, Students.UserDef11, Students.UserDef12, Students.UserDef13, Students.UserDef14, Students.UserDef15, Students.HealthyStart, Students.GiftedYN, Students.MigrantYN, Students.MentoredYN, Students.ParentEd, Students.ReadingMM, Students.MathMM, Students.Track, Students.CaELSince, Students.ReturnDateLeft, Students.ReturnDate, Students.AsianPI, Students.NormalMatriculationYN, Students.Disability, Students.ReadingMMTotal, Students.MathMMTotal, Students.Retained, Students.AtRisk, Students.Title7YN, Students.IndianEdYN, Students.Section504YN, Students.ClassReduction, Students.USEntryDate, Students.StateCompEdYN, Students.ELDYN, Students.BilingualYN, Students.SDAIEYN, Students.Teacher1, Students.Teacher2, Students.Teacher3, Students.Teacher4, Students.Teacher5, Students.Teacher6, Students.Teacher7, Students.Teacher8, Students.Teacher9, Students.Teacher10, Students.CahseeELA, Students.CahseeMath, Students.PostID AS StudentPostID, Students.TeacherPeriod0,Students.TeacherPeriod1,Students.TeacherPeriod2,Students.TeacherPeriod3, Students.TeacherPeriod4,Students.TeacherPeriod5,Students.TeacherPeriod6,Students.TeacherPeriod7,Students.TeacherPeriod8, Advisors.AdvisorSchoolNum, Advisors.AdvisorNumber, Advisors.AdvisorLastName, Students.DisAltAssess,Students.DistrictEnrolledYears, Students.ELDPriLang, Students.StatusOfEnrollment, Students.ELParentalWaiverGranted, Students.ELParentalWaiverSubmitted,Students.RFEPYearRedisig, Students.SchoolEnrolledYears, Students.InstrSet2, Students.InstrSet3, Students.InstrSet4, Students.IEP, Students.OriginNationCode, Advisors.AdvisorFirstName, isnull(Advisors.AdvisorLastName, '') + N', ' + isnull(Advisors.AdvisorFirstName, '') AS AdvisorName, Advisors.AdvisorAltNumber, Advisors.PostID AS AdvisorPostID, SchoolCodes.SchoolCode1, SchoolCodes.SchoolCode, SchoolCodes.SchoolCodeDisplay, SchoolCodes.SchoolName, SchoolCodes.PeriodAttendance, SchoolCodes.SchoolWideTitle1YN, SchoolCodes.ClassSizeReductionHalfYN, SchoolCodes.ClassSizeReductionFullYN, SchoolCodes.HighSchoolYN, SchoolCodes.ASAM, SchoolCodes.TestDate AS SchoolTestDate, SchoolCodes.PostID AS SchoolPostID FROM Students Students WITH (NOLOCK) LEFT OUTER JOIN SchoolCodes SchoolCodes WITH (NOLOCK) ON Students.SchoolNum = SchoolCodes.SchoolCode1 LEFT OUTER JOIN Advisors Advisors WITH (NOLOCK) ON Students.SchoolNum = Advisors.AdvisorSchoolNum AND Students.Advisor = Advisors.AdvisorNumber) vwStudentsJoin ON FilteredStudentsWeb.StudentID = vwStudentsJoin.StudentID ORDER BY vwStudentsJoin.SchoolNum, vwStudentsJoin.Grade, vwStudentsJoin.StudentName, FilteredStudentsWeb.StudentID, ScoreStudentGrade, TestSubject, TestDate, TestName

DeepGround
Yak Posting Veteran

73 Posts

Posted - 2009-08-28 : 13:51:59

New findings, it always works on DEVELEOPMENT Edition, never works on STANDARD or ENTERPRISE. I can install Dev edition on the same exact server its not working on and it works just like that. We can't give development edition to clients though, there is not susposed to be any difference in speed....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-08-28 : 15:47:05

Are the execution plans different? Have you tried upgrading to SQL 2005 sp3 + cu4?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

DeepGround
Yak Posting Veteran

73 Posts

Posted - 2009-08-28 : 17:02:27

The execution plans are different, no idea why. Tried upgrading to SP3, it went through and installed everything, said the upgrade was successful and it still shows SP2, tried restarting everything, another thread was already posted about this, I think SP3 is not stable considering it doesnt even know what its doing.

DeepGround
Yak Posting Veteran

73 Posts

Posted - 2009-08-28 : 17:07:09

Also I have all these configurations setup:

Server #1: SQL 2005 Enterprise Edition FAIL
Server #2: SQL 2005 Enterprise Edition FAIL
Server #3: SQL 2005 Standard Edition FAIL / SQL 2005 Dev Edition PASS
Server #4: SQL 2005 Standard Edition FAIL / SQL 2005 Dev Edition PASS
Server #5: SQL 2005 Dev Edition PASS

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-08-29 : 12:10:16

Did you specify FULLSCAN when you updated the stats on each of the "failed" servers?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

DeepGround
Yak Posting Veteran

73 Posts

Posted - 2009-08-31 : 10:59:10

No, we do not expect the data to be the problem. I found someone else who posted the same exact issue. They fixed it by changing max parallisim to 1, we tried that though and it did not work.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-08-31 : 13:16:22

You should open a case with Microsoft PSS. If it is found to be a bug, then the call is free.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

DeepGround
Yak Posting Veteran

73 Posts

Posted - 2009-08-31 : 16:21:05

Good to know, we opened a case a couple hours ago.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources