January 27, 2008 at 10:05 am
Hello,
I need some help to understand what is failling in the following query.
-------
Query:
-------
SELECT DISTINCT N'Resolvidos' AS Class,
tb1.Tipo AS Tipo,
tb1.ID AS ID,
tb1.Data AS Data,
tb1.EquipaID AS EqpID,
ISNULL(tb1.PessoaID, 0) AS PesID,
tb1.EstadoID AS EstadoID,
MIN(tb3.Data) AS DataI,
MAX(tb2.Data) AS DataF
FROM Tbl_Tmp_Trace tb1 INNER JOIN
Tbl_Tmp_Trace tb2 ON tb1.ID = tb2.ID AND tb1.Tipo = tb2.Tipo INNER JOIN
Tbl_Tmp_Trace tb3 ON tb1.ID = tb3.ID AND tb1.Tipo = tb3.Tipo
WHERE tb1.Tipo = N'Chamadas'
AND tb1.Data < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
AND tb1.EstadoID NOT IN (281479457144954, 3094610095, 3094610096)
AND tb2.EstadoID = 3094610094
GROUP BY tb1.Tipo,
tb1.ID,
tb1.Data,
tb1.EquipaID,
ISNULL(tb1.PessoaID, 0),
tb1.EstadoID
UNION
SELECT DISTINCT N'Fechados' AS Class,
tb4.Tipo AS Tipo,
tb4.ID AS ID,
tb4.Data AS Data,
tb4.EquipaID AS EqpID,
ISNULL(tb4.PessoaID, 0) AS PesID,
tb4.EstadoID AS EstadoID,
MIN(tb6.Data) AS DataI,
MAX(tb5.Data) AS DataF
FROM Tbl_Tmp_Trace tb4 INNER JOIN
Tbl_Tmp_Trace tb5 ON tb4.ID = tb5.ID AND tb4.Tipo = tb5.Tipo INNER JOIN
Tbl_Tmp_Trace tb6 ON tb4.ID = tb6.ID AND tb4.Tipo = tb6.Tipo
WHERE tb4.Tipo = N'Chamadas'
AND tb4.Data < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
AND tb5.EstadoID = 3094610096
AND tb4.ID NOT IN (SELECT DISTINCT tb7.ID
FROM Tbl_Tmp_Trace tb7
WHERE tb7.Estadoid = 3094610094
AND tb7.Tipo = N'Chamadas')
GROUP BY tb4.Tipo,
tb4.ID,
tb4.Data,
tb4.EquipaID,
ISNULL(tb4.PessoaID, 0),
tb4.EstadoID
go
In the past this query spent only 42 seconds running but now this query was running during 6 hours and the process was killed.
I can’t tell you much more since i arrive this new job last Monday.
People who works here tell me that they dettach the database where this query is running from one SQL Server 2000 to SQL Server 2005.
My questions are the following and hope if i’m forgetting something you tell me:
Q1: Shoul I use some hints in this query, if yes can you tell me what kind of hints should I study?
Q2: When a dettach and attach is done what changes are really done in Master database and what could be wrong in this process?
Q3: Can i rebuild indexes online without affecting the performance of the database?
If yes, can you send me the code that I should use?
Q4: Can you tell me some sites/tools what else to i tune queries that they send me?
The true is that i’ve this point of failure because i’m bad when we talk about tunning queries, because in my last job we’ve got people that were used in specific problems.
Another question that I’ve got to ask you is can you tell me where I can get the following braindump free.
(70-443 and 70-447)
Thanks and hope you can help me.
Regards.
Jorge Mendes
😉
SQL 2000 – 42 sg
Sql 2005 – 6 h…
January 27, 2008 at 10:56 am
JMSM (1/27/2008)
I can’t tell you much more since i arrive this new job last Monday.People who works here tell me that they dettach the database where this query is running from one SQL Server 2000 to SQL Server 2005.
Update the stats on all tables in the DB. SQL 2005 creates more detailed stats than SQL 2000 did. The 2005 optimiser works poorly with SQL 2000 hints
Look up UPDATE STATISTICS in Books online.
My questions are the following and hope if i’m forgetting something you tell me:
Q1: Shoul I use some hints in this query, if yes can you tell me what kind of hints should I study?
None. Use hints only as a last resort and if you fully understand what the hint will do
Q3: Can i rebuild indexes online without affecting the performance of the database?
If yes, can you send me the code that I should use?
You can rebuild indexes online in SQL 2005. It will still have some effect on system performance. Look up ALTER INDEX in books online
Another question that I’ve got to ask you is can you tell me where I can get the following braindump free.
(70-443 and 70-447)
Brain dumps are cheating, plain and simple. Using or providing brain dumps can get your MS certifications revoked, permanently. Requesting them is not condoned on this site.
If you want to study for the exam, there are lots of good books out there. Check the amazon review before buying, as there are also bad books out there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2008 at 1:09 am
When the db's are detached & attached to SQL2K5 the compatability level for the db must be changed to 90. If not some of the new features will not be supported for use.
Murali.a
January 28, 2008 at 3:19 am
Hi
When you say the query ran quickly before, did it run on the same database with the same numbers of users? I would suspect that there have been some data changes - possibly large loads. Re setting the statistics may help, as may re-indexing. But an increase in the query time by this amount is worrying.
Look for any blocking transactions (use Activity monitor and check the Blocked By column)
January 28, 2008 at 3:41 am
Hello again,
Thanks for the help but i've one other question, how can i make one 'update statistics.......... for all indexed columns' look that i don't know if this syntax can be used in sql server. Can i recreate all indexes online without problems.
Thanks Gail Shaw for the tip that i post on friday, your response was very useful to to me, so i only can tell you all, sorry :blush:
"Brain dumps are cheating, plain and simple. Using or providing brain dumps can get your MS certifications revoked, permanently. Requesting them is not condoned on this site."
Thanks everyone and regards
Jorge Mendes 😉
January 28, 2008 at 5:00 am
Not read all the responses but one thing that i have seen in SQL 2005 that was causing one of our queries to take 1hr 30 mins (it used to take 15 mins on a live sql 2000 box that was a lot less spec and used a lot more) was the NOT IN statement. I changed the query to use a derived table instead of the NOT IN and it took 2min 28secs 🙂
Not saying this will work in every situation but it worked for me.
see this post below and try some of the suggested alternatives.
http://sql-server-performance.com/Community/forums/p/2422/13885.aspx#13885
January 28, 2008 at 5:24 am
JMSM (1/28/2008)
how can i make one 'update statistics.......... for all indexed columns'
Update Statistics <Table name> That will update all the statistics on that table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2008 at 9:04 am
Hello,
I change the NOT IN with the NOT EXISTS at the end of this query, response time changes but the result its different. So, can u help me on this point please?:blush:
Thanks and regards
Jorge Mendes
January 28, 2008 at 9:15 am
What does the query look like now? From a quick glance
AND tb4.ID NOT IN (SELECT DISTINCT tb7.ID
FROM Tbl_Tmp_Trace tb7
WHERE tb7.Estadoid = 3094610094
AND tb7.Tipo = N'Chamadas')
should go to
AND NOT EXISTS (SELECT 1
FROM Tbl_Tmp_Trace tb7
WHERE tb7.Estadoid = 3094610094
AND tb7.Tipo = N'Chamadas' AND tb7.ID = tb4.ID)
Did the stats update help at all?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2008 at 9:16 am
Haven't gone through the whole post.
Couple of things
-Use UNION ALL instead of UNION
- Try to remove NOT IN using temp table and left outer join.
January 28, 2008 at 9:19 am
Instead of Not In, I recommend a left outer join.
"AND tb4.ID NOT IN (SELECT DISTINCT tb7.ID
FROM Tbl_Tmp_Trace tb7
WHERE tb7.Estadoid = 3094610094
AND tb7.Tipo = N'Chamadas')"
Replaced with (in the From clause):
"left outer join Tbl_Tmp_Trace tb7
on tb4.id = tb7.id
and tb7.estadoid = 3094610094
and tb7.Tipo = N'Chamadas'"
and in the Where clause add:
"and tb7.id is null"
A left outer join is usually much faster and more efficient than "Where not in", and doesn't require the correlated sub-query for "Not Exists".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2008 at 9:33 am
I already not test but i'm quite sure that will function perfectly, thanks a lot everybody.
Once more thank you very much GilaMonster, you give me an advice and i'll better if i dont look for any braindump. :blush:
You all are very good professional and i grateful for your tips and 'warnings'.
Can you tell me what kind of book can i buy for 'lazy DBA' query tunnig :sick:
Thanks and regards
Jorge Mendes 😉
January 28, 2008 at 11:25 am
I would normally do something like this:
LEFT JOIN
(SELECT tb7.Estadoid FROM Tbl_Tmp_Trace
WHERE tb7.Estadoid = 3094610094
AND tb7.Tipo = N'Chamadas' AND tb7.ID = tb4.ID
) AS X ON
tb4.ID = X.Estadoid
Then add this to the WHERE clause of the query:
AND X.Estadoid IS NULL
I usually get good performance out of this type of NULL LEFT JOIN.
Does the NOT EXITS subquery perform better?
Todd Fifield
January 28, 2008 at 12:07 pm
Sorry, but now i'm feeling completely donkey :crying:
I cant understand what changes do you want to make, the code that i changed give me errors when executing query. Was the code that i'm sending you in bold that i should change?
Once more i need help.
I'm so greatfull to all of you.
Regards,
Jorge Mendes:sick:
FROM Tbl_Tmp_Trace tb4 INNER JOIN
Tbl_Tmp_Trace tb5 ON tb4.ID = tb5.ID AND tb4.Tipo = tb5.Tipo INNER JOIN
Tbl_Tmp_Trace tb6 ON tb4.ID = tb6.ID AND tb4.Tipo = tb6.Tipo
LEFT JOIN (SELECT tb7.Estadoid FROM Tbl_Tmp_Trace WHERE tb7.Estadoid = 3094610094 AND tb7.Tipo = N'Chamadas' AND tb7.ID = tb4.ID) AS X ON tb4.ID = X.Estadoid
WHERE tb4.Tipo = N'Chamadas'AND tb4.Data < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND tb5.EstadoID = 309461009 AND X.Estadoid IS NULL
GROUP BY tb4.Tipo,
tb4.ID,
tb4.Data,
tb4.EquipaID,
ISNULL(tb4.PessoaID, 0),
tb4.EstadoID
go
January 28, 2008 at 12:38 pm
JMSM (1/28/2008)
Can you tell me what kind of book can i buy for 'lazy DBA' query tunnig :sick:
I haven't seen any book that does 'query tuning in 24 hours' or the like. The Inside SQL Server 2005 series are excellent. There are 4 books in the series, i recommend them all. Just note they are not beginner level books
SQL Server Query Performance Tuning Distilled by Sajal Dam is excellent. It's written for SQL 2000, but a lot of the principles apply to SQL 2005 as well.
A lot of query tuning is trial and error more than anything.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply