January 13, 2014 at 9:16 am
I'm hoping to get back to SQLBits this year, but with surgery coming, timing will matter. I hope they announce something soon.
January 14, 2014 at 7:26 am
Here is a true story from the field that a buddy of mine shared with me yesterday.
Yesterday my supervisor sent me an email regarding a view that wasn't returning accurate info to the users and he wanted me to look at it. Unfortunately I was unable to last night as I was busy with another priority that took all night.
The view uses a lot of subqueries in the select list, many of them from the same tables, and used group by with select top 1. Needless to say these subqueries could (and was) pulling data from different records with the same parent key. I wonder why the information was inconsistent.
Well, my supervisor decided to try and tackle the problem and rewrite the view. He moved the subqueries to left outer joins in the FROM clause but was not working very well. He didn't have time to use the DTA to help tune the indexes to help improve his query, so when I got in he turned it back over to me.
After about an hour, I had consolidated the multiple individual subqueries into three subqueries using outer apply in the FROM clause. The original view with inaccurate data ran in about 33 seconds. My rewrite runs in 6 seconds, and I am sure I could come up with a few covering indexes on the subtables that would increase the performance more. Both versions return about 56,000 rows of data. My boss’s, well I stopped it after 2 hours and 16 minutes and it had only returned a little over 20,000 rows.
Goes to show that you should leave rewriting SQL to the professionals.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 14, 2014 at 8:40 am
Sean Lange (1/14/2014)
Goes to show that you should leave rewriting SQL to the professionals.
[/code]
Or teach others to be more professional.
How about an article from your buddy on the process for the code? That's how people learn.
January 14, 2014 at 9:05 am
Steve Jones - SSC Editor (1/14/2014)
Sean Lange (1/14/2014)
Here is a true story from the field that a buddy of mine shared with me yesterday.Goes to show that you should leave rewriting SQL to the professionals.
[/code]
Or teach others to be more professional.
How about an article from your buddy on the process for the code? That's how people learn.
I can't say I disagree with your sentiment there Steve. That was all copy-paste from me. My guess there are some other levels of politics and BS involved. I know this guy pretty well and I know that if there is the opportunity to teach he will take that road. Of course all know that isn't always the case because sometime the student doesn't want to learn.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 14, 2014 at 10:54 am
Sean Lange (1/14/2014)
Here is a true story from the field that a buddy of mine shared with me yesterday.Yesterday my supervisor sent me an email regarding a view that wasn't returning accurate info to the users and he wanted me to look at it. Unfortunately I was unable to last night as I was busy with another priority that took all night.
The view uses a lot of subqueries in the select list, many of them from the same tables, and used group by with select top 1. Needless to say these subqueries could (and was) pulling data from different records with the same parent key. I wonder why the information was inconsistent.
Well, my supervisor decided to try and tackle the problem and rewrite the view. He moved the subqueries to left outer joins in the FROM clause but was not working very well. He didn't have time to use the DTA to help tune the indexes to help improve his query, so when I got in he turned it back over to me.
After about an hour, I had consolidated the multiple individual subqueries into three subqueries using outer apply in the FROM clause. The original view with inaccurate data ran in about 33 seconds. My rewrite runs in 6 seconds, and I am sure I could come up with a few covering indexes on the subtables that would increase the performance more. Both versions return about 56,000 rows of data. My boss’s, well I stopped it after 2 hours and 16 minutes and it had only returned a little over 20,000 rows.
Goes to show that you should leave rewriting SQL to the professionals.
DTA!!!!!!
Burn it with fire!!!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2014 at 5:04 pm
Grant Fritchey (1/14/2014)
Sean Lange (1/14/2014)
Here is a true story from the field that a buddy of mine shared with me yesterday.Yesterday my supervisor sent me an email regarding a view that wasn't returning accurate info to the users and he wanted me to look at it. Unfortunately I was unable to last night as I was busy with another priority that took all night.
The view uses a lot of subqueries in the select list, many of them from the same tables, and used group by with select top 1. Needless to say these subqueries could (and was) pulling data from different records with the same parent key. I wonder why the information was inconsistent.
Well, my supervisor decided to try and tackle the problem and rewrite the view. He moved the subqueries to left outer joins in the FROM clause but was not working very well. He didn't have time to use the DTA to help tune the indexes to help improve his query, so when I got in he turned it back over to me.
After about an hour, I had consolidated the multiple individual subqueries into three subqueries using outer apply in the FROM clause. The original view with inaccurate data ran in about 33 seconds. My rewrite runs in 6 seconds, and I am sure I could come up with a few covering indexes on the subtables that would increase the performance more. Both versions return about 56,000 rows of data. My boss’s, well I stopped it after 2 hours and 16 minutes and it had only returned a little over 20,000 rows.
Goes to show that you should leave rewriting SQL to the professionals.
DTA!!!!!!
Burn it with fire!!!
And here I thought I was the only one that felt this way. 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 14, 2014 at 6:02 pm
dwain.c (1/14/2014)
Grant Fritchey (1/14/2014)
DTA!!!!!!Burn it with fire!!!
And here I thought I was the only one that felt this way. 😛
Nope. And I'm starting the rewrite on the chapter on the DTA for the performance tuning book. I've been told, again, I can't just write this for the entire chapter:
No.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2014 at 6:20 pm
Grant Fritchey (1/14/2014)
dwain.c (1/14/2014)
Grant Fritchey (1/14/2014)
DTA!!!!!!Burn it with fire!!!
And here I thought I was the only one that felt this way. 😛
Nope. And I'm starting the rewrite on the chapter on the DTA for the performance tuning book. I've been told, again, I can't just write this for the entire chapter:
No.
Better yet - can you just erase the chapter altogether????
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 14, 2014 at 6:35 pm
Grant Fritchey (1/14/2014)
dwain.c (1/14/2014)
Grant Fritchey (1/14/2014)
DTA!!!!!!Burn it with fire!!!
And here I thought I was the only one that felt this way. 😛
Nope. And I'm starting the rewrite on the chapter on the DTA for the performance tuning book. I've been told, again, I can't just write this for the entire chapter:
No.
So then Knuth was wrong: DTA is the root of all evil.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 14, 2014 at 9:27 pm
Just verifying that 824 errors usually point to a possible I/O Subsystem issue.
January 14, 2014 at 10:33 pm
Lynn Pettis (1/14/2014)
Just verifying that 824 errors usually point to a possible I/O Subsystem issue.
Almost definite. Something changed values on that page on disk after SQL wrote it out, before SQL read it back.
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 14, 2014 at 10:37 pm
GilaMonster (1/14/2014)
Lynn Pettis (1/14/2014)
Just verifying that 824 errors usually point to a possible I/O Subsystem issue.Almost definite. Something changed values on that page on disk after SQL wrote it out, before SQL read it back.
Good, I didn't miss speak. I have our SA's checking the system to see if there are errors and such at this time. The consistency error is related to a single table and from the error from CHECKDB affects 2 pages. I think we caught this early enough to minimize any data loss.
January 15, 2014 at 4:24 am
Found out today that my indemnity insurance was accidentally not renewed this year. Problem their end not mine. So still covered - in theory. Get transferred to the payments team to sort out the payment and the person says "so you want to start a new policy", I know its semantics but I felt the need to correct them and say no it's a renewal / continuation of a policy that you accidentally forgot to renew.
They then say payment will be taken out on Feb 1st so I had to check about this month "The missing month"... Oh we'll do a double payment then, is that all right? Er yes that's fine thanks.
This just gave me an uneasy feeling, as depending on the wording I may not be covered for this month, I'm sure the insurance company is used to this sort of thing, but would have liked the second person I spoke to say the right words so I went away reassured. Why do I think only a single payment will be taken next month???
Thankfully I only have access to a couple of production servers at the moment :O
Oh and I see that @sqlbits has replied saying there will be an announcement soon, just hope it doesn't clash with any of the Saturdays in May / June that I'm already doing something. Just hope the location is kind if they do clash 🙂
Rodders...
January 15, 2014 at 4:35 am
rodjkidd (1/15/2014)
Oh and I see that @sqlbits has replied saying there will be an announcement soon, just hope it doesn't clash with any of the Saturdays in May / June that I'm already doing something. Just hope the location is kind if they do clash 🙂
I hadn't noticed, thanks.
What would be perfect (for me anyway) is if there's a 'nearby' SQLSaturday the Saturday before. then I'll have an excuse to play tourist for a couple of days.
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 15, 2014 at 4:42 am
GilaMonster (1/15/2014)
rodjkidd (1/15/2014)
Oh and I see that @sqlbits has replied saying there will be an announcement soon, just hope it doesn't clash with any of the Saturdays in May / June that I'm already doing something. Just hope the location is kind if they do clash 🙂I hadn't noticed, thanks.
What would be perfect (for me anyway) is if there's a 'nearby' SQLSaturday the Saturday before. then I'll have an excuse to play tourist for a couple of days.
Gail,
Only one European SQL Sat at the moment in May, Kiev on 24th. If Bits is the 31st then I'll have to bail early on the Saturday or miss the whole day depending on how close it is to North London, oh and who are the first couple of bands on the Saturday of Celebr8.3 🙂
But I am totally guessing, sadly I have no insider info - would be great if I did 😀
Rodders...
Viewing 15 posts - 42,586 through 42,600 (of 66,738 total)
You must be logged in to reply to this topic. Login to reply