March 30, 2017 at 9:55 am
SQLRNNR - Thursday, March 30, 2017 9:44 AMSean Lange - Thursday, March 30, 2017 8:09 AMEd Wagner - Thursday, March 30, 2017 7:29 AMBeatrix Kiddo - Thursday, March 30, 2017 1:50 AMSean Lange - Wednesday, March 29, 2017 9:10 AMThom A - Wednesday, March 29, 2017 8:56 AMjasona.work - Wednesday, March 29, 2017 8:45 AMUm, is it bad if I ran this in production a week ago:UPDATE ANNUALSALARY SET SALARY = 'POTATO';OH don't get me started on that. Someone was asked to update the name of someone last week, as it had been entered incorrectly. The SQL?
UPDATE misys_users
SET surname = 'Lapine';
Yeah... That took that software off line for good 30 minutes. We were scrambling to work out why no one could login, and they finally piped up 20 minutes later to tell us they may have "made a mistake"... To add insult to injury, Lapine was also not the correct spelling. :pinch:HAHA!!! This reminds me of very first job as a dev. I had to update the employee table to change somebody's name. I ran a very similar update statement without a where clause that was supposed to disable one users account but instead disabled the whole table. To pour salt on the wound they had a trigger on that table that checked to see if the account was disabled and if so, it used some sp_OACreate stuff (sql 7) and disable their AD account. I managed to disable the entire company's AD accounts. Close to 5,000 employees nation wide. It was a bit of a nightmare to say the least that took almost two days for the whole IT staff to unravel.
This sounds like actual hell on toast for you, but I enjoyed reading about it.
That does sound pretty brutal. I suppose the bright side is that there was at least one user left in AD that could update accounts. 😉
Yeah there were a couple of admin AD service accounts that were not in the database....phew!!! Would probably have been the end of my still fledgling career if we would have had to build a brand new domain because all the users got disabled. :w00t:
I sure hope they removed the sp_OACreate right after that. Talk about bass-ackward processes.
No kidding!!! The intention was that this ran on the intranet and when a user was disabled on the Employee page it would disable their AD account. The intention was good but the implementation was awful. This was classic asp with a sql 7 database.
_______________________________________________________________
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/
March 30, 2017 at 10:00 am
Many thanks Gail and her blog about parameter sniffing. I can't count the number of times I have sent others there but today I ran into it with a procedure I have on our production system. It is stupidly complicated with data coming from multiple remote servers. The performance of this was really fast considering it is querying data from several different servers. However today I ran into a situation where the procedure that usually runs in about 8-10 seconds with the amount of data in this particular case was timing out the application. Running this procedure directly it took over 2 minutes. The simple solution of using local variable dropped the execution down to consistently around 4-5 seconds for the same data. I effectively cut the execution time in half.
_______________________________________________________________
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/
March 30, 2017 at 11:40 am
Sean Lange - Thursday, March 30, 2017 10:00 AMMany thanks Gail and her blog about parameter sniffing. I can't count the number of times I have sent others there but today I ran into it with a procedure I have on our production system. It is stupidly complicated with data coming from multiple remote servers. The performance of this was really fast considering it is querying data from several different servers. However today I ran into a situation where the procedure that usually runs in about 8-10 seconds with the amount of data in this particular case was timing out the application. Running this procedure directly it took over 2 minutes. The simple solution of using local variable dropped the execution down to consistently around 4-5 seconds for the same data. I effectively cut the execution time in half.
Glad to hear it. The blog posts are badly out of date, I probably wouldn't use local variables any longer, in favour of OPTION(OPTIMIZE FOR UNKNOWN)
Shameless self-promotion: https://app.pluralsight.com/library/courses/identifying-fixing-performance-issues-caused-parameter-sniffing/table-of-contents
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
March 30, 2017 at 3:21 pm
GilaMonster - Thursday, March 30, 2017 11:40 AMSean Lange - Thursday, March 30, 2017 10:00 AMMany thanks Gail and her blog about parameter sniffing. I can't count the number of times I have sent others there but today I ran into it with a procedure I have on our production system. It is stupidly complicated with data coming from multiple remote servers. The performance of this was really fast considering it is querying data from several different servers. However today I ran into a situation where the procedure that usually runs in about 8-10 seconds with the amount of data in this particular case was timing out the application. Running this procedure directly it took over 2 minutes. The simple solution of using local variable dropped the execution down to consistently around 4-5 seconds for the same data. I effectively cut the execution time in half.Glad to hear it. The blog posts are badly out of date, I probably wouldn't use local variables any longer, in favour of OPTION(OPTIMIZE FOR UNKNOWN)
Shameless self-promotion: https://app.pluralsight.com/library/courses/identifying-fixing-performance-issues-caused-parameter-sniffing/table-of-contents
I also recommend http://sqlblog.com/blogs/hugo_kornelis/archive/tags/Parameter+sniffing/default.aspx. (Yes, that is also self-promotion)
March 30, 2017 at 3:31 pm
Hugo Kornelis - Thursday, March 30, 2017 3:21 PMGilaMonster - Thursday, March 30, 2017 11:40 AMSean Lange - Thursday, March 30, 2017 10:00 AMMany thanks Gail and her blog about parameter sniffing. I can't count the number of times I have sent others there but today I ran into it with a procedure I have on our production system. It is stupidly complicated with data coming from multiple remote servers. The performance of this was really fast considering it is querying data from several different servers. However today I ran into a situation where the procedure that usually runs in about 8-10 seconds with the amount of data in this particular case was timing out the application. Running this procedure directly it took over 2 minutes. The simple solution of using local variable dropped the execution down to consistently around 4-5 seconds for the same data. I effectively cut the execution time in half.Glad to hear it. The blog posts are badly out of date, I probably wouldn't use local variables any longer, in favour of OPTION(OPTIMIZE FOR UNKNOWN)
Shameless self-promotion: https://app.pluralsight.com/library/courses/identifying-fixing-performance-issues-caused-parameter-sniffing/table-of-contents
I also recommend http://sqlblog.com/blogs/hugo_kornelis/archive/tags/Parameter+sniffing/default.aspx. (Yes, that is also self-promotion)
Shame on both of you 😀:hehe::hehe::hehe::hehe::hehe:😀
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
March 31, 2017 at 2:45 am
Hugo Kornelis - Thursday, March 30, 2017 3:21 PMGilaMonster - Thursday, March 30, 2017 11:40 AMSean Lange - Thursday, March 30, 2017 10:00 AMMany thanks Gail and her blog about parameter sniffing. I can't count the number of times I have sent others there but today I ran into it with a procedure I have on our production system. It is stupidly complicated with data coming from multiple remote servers. The performance of this was really fast considering it is querying data from several different servers. However today I ran into a situation where the procedure that usually runs in about 8-10 seconds with the amount of data in this particular case was timing out the application. Running this procedure directly it took over 2 minutes. The simple solution of using local variable dropped the execution down to consistently around 4-5 seconds for the same data. I effectively cut the execution time in half.Glad to hear it. The blog posts are badly out of date, I probably wouldn't use local variables any longer, in favour of OPTION(OPTIMIZE FOR UNKNOWN)
Shameless self-promotion: https://app.pluralsight.com/library/courses/identifying-fixing-performance-issues-caused-parameter-sniffing/table-of-contents
I also recommend http://sqlblog.com/blogs/hugo_kornelis/archive/tags/Parameter+sniffing/default.aspx. (Yes, that is also self-promotion)
Well, my personal recommendation is that you buy this book, SQL Server Deep Dives II, and read the chapter I wrote on parameter sniffing and it's solutions. Not only will you learn, but you'll be supporting a charity.
HA!
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 31, 2017 at 8:53 am
Hey guys. Just sharing a nightmare with you. I already ran a third backup to a second offsite storage location for my important stuff. Yeah, it's Reddit. Still worth a glance.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 31, 2017 at 8:58 am
Grant Fritchey - Friday, March 31, 2017 8:53 AMHey guys. Just sharing a nightmare with you. I already ran a third backup to a second offsite storage location for my important stuff. Yeah, it's Reddit. Still worth a glance.
That's one heck of a way to start the weekend:pinch:
😎
March 31, 2017 at 9:12 am
Grant Fritchey - Friday, March 31, 2017 8:53 AMHey guys. Just sharing a nightmare with you. I already ran a third backup to a second offsite storage location for my important stuff. Yeah, it's Reddit. Still worth a glance.
That wasn't actually you getting hit with that, was it?
March 31, 2017 at 9:28 am
Brandie Tarvin - Friday, March 31, 2017 9:12 AMGrant Fritchey - Friday, March 31, 2017 8:53 AMHey guys. Just sharing a nightmare with you. I already ran a third backup to a second offsite storage location for my important stuff. Yeah, it's Reddit. Still worth a glance.That wasn't actually you getting hit with that, was it?
No. You'd hear the screams.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 31, 2017 at 11:22 am
Grant Fritchey - Friday, March 31, 2017 9:28 AMBrandie Tarvin - Friday, March 31, 2017 9:12 AMGrant Fritchey - Friday, March 31, 2017 8:53 AMHey guys. Just sharing a nightmare with you. I already ran a third backup to a second offsite storage location for my important stuff. Yeah, it's Reddit. Still worth a glance.That wasn't actually you getting hit with that, was it?
No. You'd hear the screams.
And I thought it was the neighbour's alarm, silly me
😎
March 31, 2017 at 7:44 pm
Crud. I guess I blew it. Today was the last day to submit contributions for MVP renewal. I went to update my profile at 8:30PM and it won't take any submissions unless they're after 31 March 2017 even though I got a reminder that today was the last day to submit contributions. I thought we had until midnight. Guess MS doesn't check for local times. Really disappointing especially since I've been doing nearly 80 hours a week on my regular job for a couple of months now and still find the time to post answers on this and other forums.
Sent the MVP goddess an email describing my plight... we'll see what happens.
--Jeff Moden
April 1, 2017 at 6:48 am
Jeff Moden - Friday, March 31, 2017 7:44 PMCrud. I guess I blew it. Today was the last day to submit contributions for MVP renewal. I went to update my profile at 8:30PM and it won't take any submissions unless they're after 31 March 2017 even though I got a reminder that today was the last day to submit contributions. I thought we had until midnight. Guess MS doesn't check for local times. Really disappointing especially since I've been doing nearly 80 hours a week on my regular job for a couple of months now and still find the time to post answers on this and other forums.Sent the MVP goddess an email describing my plight... we'll see what happens.
Good luck. Though if you were renewed now, aren't you good until Jun 2018? I was as a Jan 2017 renewal.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 1, 2017 at 6:50 am
I have typically tried to keep 3 backups where possible. One on the local machine, one remote, one archived offsite. These days the local one probably isn't a big deal with SAN networking.
Reading that piece, that's a tough spot. I think virtualization can cause issues for some people when you have different groups that view a file or process as something on that machine, and not something that is another machine. I expect we'll see similar issues with containers for people that don't treat them as stateless.
I do try to keep a couple copies of my stuff around, though with internconnections, versioning is a concern. What happens if I get corruption in dropbox/evernote/etc and it goes to all my copies? I know there's some versioning, but how can they tell bad bits v good ones? It's a concern when you have automated, automatic, connected backups of data.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 1, 2017 at 4:46 pm
Steve Jones - SSC Editor - Saturday, April 1, 2017 6:48 AMJeff Moden - Friday, March 31, 2017 7:44 PMCrud. I guess I blew it. Today was the last day to submit contributions for MVP renewal. I went to update my profile at 8:30PM and it won't take any submissions unless they're after 31 March 2017 even though I got a reminder that today was the last day to submit contributions. I thought we had until midnight. Guess MS doesn't check for local times. Really disappointing especially since I've been doing nearly 80 hours a week on my regular job for a couple of months now and still find the time to post answers on this and other forums.Sent the MVP goddess an email describing my plight... we'll see what happens.
Good luck. Though if you were renewed now, aren't you good until Jun 2018? I was as a Jan 2017 renewal.
I believe the april renewals only got push back to the new single annual renewal date in June thus the deadline for his contributions. The change in the program was announced after the January renewals so they got bumped out to the June 2018 date because that was the next renewal after the new annual renewal date (January 2018) .
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
Viewing 15 posts - 57,961 through 57,975 (of 66,000 total)
You must be logged in to reply to this topic. Login to reply