November 2, 2017 at 5:41 am
Lynn Pettis - Friday, October 27, 2017 1:22 PMBrandie Tarvin - Friday, October 27, 2017 1:14 PMYeah, but both pale in comparison to baby spit up and other issues. When he wants his diaper changed or decides he's hungry, it doesn't matter how urgent the project I'm working on is or what meeting / conference call I'm online with.And I've only been back to work two days as of now... Sigh. Parenthood. What a joy. Still, he's very warm and cuddly at this age. I "wore him" to my home office yesterday.
Enjoy it while you can. Next thing you will know they are walking across the stage graduating from High School.
Shut up Lynn, he's five now and I don't know how that happened, and you're scaring me. Somehow I have to absorb the next ten years very slowly (helps that I'm working from home now)
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
November 2, 2017 at 6:20 am
Luis Cazares - Wednesday, November 1, 2017 1:49 PMjasona.work - Wednesday, November 1, 2017 1:18 PMjasona.work - Wednesday, November 1, 2017 1:01 PMI'm hoping someone here can help me out finding a blog posting...I *know* a couple weeks or so back, I saw a posting, I thought by Brent Ozar (but I couldn't find it on his blog,) that the gist of it was "whoever is taking the backups of the SQL database, they're the DBA."
I'm to the point of talking to my boss to try to wrest control of my SQL backups from our datacenter guys and I think that article would be worth showing him as well, kind of to indicate *why* the database backups should be pulled in-house, as it were.
Of course, then the fun of getting sufficient storage (also from the datacenter guys) to put the backups on starts, followed by making sure they have a schedule to backup that location and take said backups off-site...
Aanndd found it!
https://spaghettidba.com/2017/09/13/expensive-enterprise-backup-tools-a-survival-guide/Now to write up an outline of the whys and wherefores to explain why I want to take control of my DB backups.
Or explain why they should guarantee RPO and RTO. Even better, you should get some guarantee on PTO.
I don't think it'll be too hard to sell to my boss, it's just a matter of getting it through the rest of the bureaucracy around this place...
For instance, checking the backups this morning, which I have to do by querying MSDB seeing as I can't have access to the front-end of the backup software, I see that the Differentials that should've run this morning, didn't. And, because of the boneheaded way the backup software works, that seems to have jammed up the Transaction Log backups that should've been running...
So, I had to send an e-mail to the backup guy, flagged as important, and hope he gets to it soon. Can't go back and hover over his cube (aside from the fact that I'm teleworking today,) because he's in a controlled access area, can't look in the backup software to see what the h**l is going on and maybe fix it myself...
So, yeah, going to bring up getting the database backups in my control next week, going to discuss with the boss what our current RPO / RTOs are with the customers, going to suggest getting SQL Backup Pro for the backups if we do bring them under my control. The biggest headache will be getting a sufficient chunk of storage to direct the backups too (yes, I'll be backing up over the network, I don't have the room on my servers to keep backups for long,) and getting the existing backup software to back that location up daily (those backups would get dropped to tape once a week and taken offsite.) Ideally, I want to keep 2 weeks of backups on-hand, if there's a lot of push-back, I'll go to 1 week.
As for PTO, well, that accrues at a nice rate, and working a compressed schedule (8x days @ 9hrs ea, 1x day @ 8hrs, every other Friday off) I don't use a lot of it.
But hey, I've got a 2 week vacation scheduled in August that's going to be a blast!
November 2, 2017 at 8:54 am
jonathan.crawford - Thursday, November 2, 2017 5:41 AMLynn Pettis - Friday, October 27, 2017 1:22 PMBrandie Tarvin - Friday, October 27, 2017 1:14 PMYeah, but both pale in comparison to baby spit up and other issues. When he wants his diaper changed or decides he's hungry, it doesn't matter how urgent the project I'm working on is or what meeting / conference call I'm online with.And I've only been back to work two days as of now... Sigh. Parenthood. What a joy. Still, he's very warm and cuddly at this age. I "wore him" to my home office yesterday.
Enjoy it while you can. Next thing you will know they are walking across the stage graduating from High School.
Shut up Lynn, he's five now and I don't know how that happened, and you're scaring me. Somehow I have to absorb the next ten years very slowly (helps that I'm working from home now)
Mine are 27 (with three of her own), 23, and 19. They are still my little babies.
November 2, 2017 at 10:37 am
Not really sure why but the wikipedia page for Sargable has been deleted. This was my goto quick reference to direct other to. Anybody know any good resources that are a decent explanation without going too deep into indexes that is useful for semi-technical people?
I did find the content using my wayback machine.
In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument ABLE.
A query failing to be sargable is known as non-sargable query and typically has a negative effect on query time, so one of the steps in query optimization is to convert them to be sargable. The effect is similar to searching for a specific term in a book that has no index, beginning at page one each time, instead of jumping to a list of specific pages identified in an index.
The typical situation that will make a SQL query non-sargable is to include in the WHERE clause a function operating on a column value. The WHERE clause is not the only clause where sargability can matter; it can also have an effect on ORDER BY, GROUP BY and HAVING clauses. The SELECT clause, on the other hand, can contain non-sargable expressions without adversely affecting the performance.
- Sargable operators:
=, >, <, >=, <=, BETWEEN, LIKE, IS [NOT] NULL, EXISTS- Sargable operators that rarely improve performance:
<>, IN, OR, NOT IN, NOT EXISTS, NOT LIKE
_______________________________________________________________
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/
November 2, 2017 at 10:47 am
Sean Lange - Thursday, November 2, 2017 10:37 AMNot really sure why but the wikipedia page for Sargable has been deleted. This was my goto quick reference to direct other to. Anybody know any good resources that are a decent explanation without going too deep into indexes that is useful for semi-technical people?I did find the content using my wayback machine.
Apparently "Notability of term called into question." https://en.wikipedia.org/w/index.php?title=Special%3ALog&type=delete&user=&page=sargable&year=&month=-1&tagfilter=&subtype=
You can ask for it to be reviewed for undeletion here: https://en.wikipedia.org/wiki/Wikipedia:Requests_for_undeletion
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 2, 2017 at 10:52 am
Thom A - Thursday, November 2, 2017 10:47 AMSean Lange - Thursday, November 2, 2017 10:37 AMNot really sure why but the wikipedia page for Sargable has been deleted. This was my goto quick reference to direct other to. Anybody know any good resources that are a decent explanation without going too deep into indexes that is useful for semi-technical people?I did find the content using my wayback machine.
Apparently "Notability of term called into question." https://en.wikipedia.org/w/index.php?title=Special%3ALog&type=delete&user=&page=sargable&year=&month=-1&tagfilter=&subtype=
You can ask for it to be reviewed for undeletion here: https://en.wikipedia.org/wiki/Wikipedia:Requests_for_undeletion
Notability of term. That is a strange way of wording it. Wonder who was questioning it. Most people in the industry know that term quite well. Sometimes the things on wiki are truly strange.
_______________________________________________________________
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/
November 2, 2017 at 10:56 am
Sean Lange - Thursday, November 2, 2017 10:52 AMNotability of term. That is a strange way of wording it. Wonder who was questioning it. Most people in the industry know that term quite well. Sometimes the things on wiki are truly strange.
Maybe we should ask for 3NF to be removed from Wikipedia too. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 2, 2017 at 12:33 pm
Sean Lange - Thursday, November 2, 2017 10:37 AMNot really sure why but the wikipedia page for Sargable has been deleted. This was my goto quick reference to direct other to. Anybody know any good resources that are a decent explanation without going too deep into indexes that is useful for semi-technical people?I did find the content using my wayback machine.
In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument ABLE.
A query failing to be sargable is known as non-sargable query and typically has a negative effect on query time, so one of the steps in query optimization is to convert them to be sargable. The effect is similar to searching for a specific term in a book that has no index, beginning at page one each time, instead of jumping to a list of specific pages identified in an index.
The typical situation that will make a SQL query non-sargable is to include in the WHERE clause a function operating on a column value. The WHERE clause is not the only clause where sargability can matter; it can also have an effect on ORDER BY, GROUP BY and HAVING clauses. The SELECT clause, on the other hand, can contain non-sargable expressions without adversely affecting the performance.
- Sargable operators:
=, >, <, >=, <=, BETWEEN, LIKE, IS [NOT] NULL, EXISTS- Sargable operators that rarely improve performance:
<>, IN, OR, NOT IN, NOT EXISTS, NOT LIKE
Be careful with how you explain LIKE and NOT LIKE, Sean. If they have a leading % in the search term, it's not SARGable and will result in an NCI scan at best; CI scan if no NCI available to save on the reads.
November 2, 2017 at 12:42 pm
Ed Wagner - Thursday, November 2, 2017 12:33 PMSean Lange - Thursday, November 2, 2017 10:37 AMNot really sure why but the wikipedia page for Sargable has been deleted. This was my goto quick reference to direct other to. Anybody know any good resources that are a decent explanation without going too deep into indexes that is useful for semi-technical people?I did find the content using my wayback machine.
In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument ABLE.
A query failing to be sargable is known as non-sargable query and typically has a negative effect on query time, so one of the steps in query optimization is to convert them to be sargable. The effect is similar to searching for a specific term in a book that has no index, beginning at page one each time, instead of jumping to a list of specific pages identified in an index.
The typical situation that will make a SQL query non-sargable is to include in the WHERE clause a function operating on a column value. The WHERE clause is not the only clause where sargability can matter; it can also have an effect on ORDER BY, GROUP BY and HAVING clauses. The SELECT clause, on the other hand, can contain non-sargable expressions without adversely affecting the performance.
- Sargable operators:
=, >, <, >=, <=, BETWEEN, LIKE, IS [NOT] NULL, EXISTS- Sargable operators that rarely improve performance:
<>, IN, OR, NOT IN, NOT EXISTS, NOT LIKEBe careful with how you explain LIKE and NOT LIKE, Sean. If they have a leading % in the search term, it's not SARGable and will result in an NCI scan at best; CI scan if no NCI available to save on the reads.
Oh yeah this is not a great example for sure. I would not include LIKE or NOT LIKE on either list because truly without the rest of the predicate it is indeterminate if it is SARGable or not.
_______________________________________________________________
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/
November 2, 2017 at 12:47 pm
UGH!!! I was forced to use READ UNCOMMITTED today on about 30-40 procedures. This is for custom work that extends a third party ERP system. We have been an increasing number of deadlocks when these procedures have been executing. Every one of these is nothing more select statements. But of course the deadlock graphs are showing that inserts and deletes from the ERP are all using read uncommitted. And even the developer documentation suggests that you should use read uncommitted for everything. Who decided this was the right system again??? Certainly not me. Ed Wagner knows fully well how horrific this system is since he has had to work this menace in the past.
_______________________________________________________________
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/
November 2, 2017 at 12:51 pm
Sean Lange - Thursday, November 2, 2017 12:47 PMUGH!!! I was forced to use READ UNCOMMITTED today on about 30-40 procedures. This is for custom work that extends a third party ERP system. We have been an increasing number of deadlocks when these procedures have been executing. Every one of these is nothing more select statements. But of course the deadlock graphs are showing that inserts and deletes from the ERP are all using read uncommitted. And even the developer documentation suggests that you should use read uncommitted for everything. Who decided this was the right system again??? Certainly not me. Ed Wagner knows fully well how horrific this system is since he has had to work this menace in the past.
How often do you get the deadlocks? And if I remember correctly (Gail can correct me) on inserts and deletes SQLServer will still take locks.
November 2, 2017 at 1:08 pm
Lynn Pettis - Thursday, November 2, 2017 12:51 PMSean Lange - Thursday, November 2, 2017 12:47 PMUGH!!! I was forced to use READ UNCOMMITTED today on about 30-40 procedures. This is for custom work that extends a third party ERP system. We have been an increasing number of deadlocks when these procedures have been executing. Every one of these is nothing more select statements. But of course the deadlock graphs are showing that inserts and deletes from the ERP are all using read uncommitted. And even the developer documentation suggests that you should use read uncommitted for everything. Who decided this was the right system again??? Certainly not me. Ed Wagner knows fully well how horrific this system is since he has had to work this menace in the past.How often do you get the deadlocks? And if I remember correctly (Gail can correct me) on inserts and deletes SQLServer will still take locks.
Yes the insert and delete will take locks of course. It is just awful that the vendor code is written like this. The deadlocks happen several times a day across a couple of tables because there is a lot of data movement.
_______________________________________________________________
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/
November 2, 2017 at 1:09 pm
Sean Lange - Thursday, November 2, 2017 12:47 PMUGH!!! I was forced to use READ UNCOMMITTED today on about 30-40 procedures. This is for custom work that extends a third party ERP system. We have been an increasing number of deadlocks when these procedures have been executing. Every one of these is nothing more select statements. But of course the deadlock graphs are showing that inserts and deletes from the ERP are all using read uncommitted. And even the developer documentation suggests that you should use read uncommitted for everything. Who decided this was the right system again??? Certainly not me. Ed Wagner knows fully well how horrific this system is since he has had to work this menace in the past.
Just curious, but would something like this help? It is a snippet from some code I wrote here. I also forced the deadlock priority to -10 in the procedure I wrote this.
while 1 = 1 -- Loop to permit deadlock handling
begin
begin transaction;
begin try;
-- Your code
break;
end try
begin catch
rollback transaction;
select
@ErrorNumber = ERROR_NUMBER(),
@ErrorLine = ERROR_LINE(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorProcedure = ERROR_PROCEDURE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
if @ErrorNumber = 1205 -- Trap deadlock
begin
WAITFOR DELAY ''00:00:00.05'' -- Wait for 50 ms
end
else begin
-- All other errors
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
end
end catch
end
November 2, 2017 at 1:16 pm
Lynn Pettis - Thursday, November 2, 2017 1:09 PMSean Lange - Thursday, November 2, 2017 12:47 PMUGH!!! I was forced to use READ UNCOMMITTED today on about 30-40 procedures. This is for custom work that extends a third party ERP system. We have been an increasing number of deadlocks when these procedures have been executing. Every one of these is nothing more select statements. But of course the deadlock graphs are showing that inserts and deletes from the ERP are all using read uncommitted. And even the developer documentation suggests that you should use read uncommitted for everything. Who decided this was the right system again??? Certainly not me. Ed Wagner knows fully well how horrific this system is since he has had to work this menace in the past.Just curious, but would something like this help? It is a snippet from some code I wrote here. I also forced the deadlock priority to -10 in the procedure I wrote this.
while 1 = 1 -- Loop to permit deadlock handling
begin
begin transaction;
begin try;
-- Your code
break;end try
begin catch
rollback transaction;
select
@ErrorNumber = ERROR_NUMBER(),
@ErrorLine = ERROR_LINE(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorProcedure = ERROR_PROCEDURE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
if @ErrorNumber = 1205 -- Trap deadlock
begin
WAITFOR DELAY ''00:00:00.05'' -- Wait for 50 ms
end
else begin
-- All other errors
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
end
end catch
end
That is interesting. Not sure how I might implement this since all I am doing is select statements. But it might be worth a shot. I can certainly roll this up in QA and see how it plays. Thanks!!
_______________________________________________________________
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/
November 2, 2017 at 2:07 pm
Sean Lange - Thursday, November 2, 2017 1:16 PMLynn Pettis - Thursday, November 2, 2017 1:09 PMSean Lange - Thursday, November 2, 2017 12:47 PMUGH!!! I was forced to use READ UNCOMMITTED today on about 30-40 procedures. This is for custom work that extends a third party ERP system. We have been an increasing number of deadlocks when these procedures have been executing. Every one of these is nothing more select statements. But of course the deadlock graphs are showing that inserts and deletes from the ERP are all using read uncommitted. And even the developer documentation suggests that you should use read uncommitted for everything. Who decided this was the right system again??? Certainly not me. Ed Wagner knows fully well how horrific this system is since he has had to work this menace in the past.Just curious, but would something like this help? It is a snippet from some code I wrote here. I also forced the deadlock priority to -10 in the procedure I wrote this.
while 1 = 1 -- Loop to permit deadlock handling
begin
begin transaction;
begin try;
-- Your code
break;end try
begin catch
rollback transaction;
select
@ErrorNumber = ERROR_NUMBER(),
@ErrorLine = ERROR_LINE(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorProcedure = ERROR_PROCEDURE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
if @ErrorNumber = 1205 -- Trap deadlock
begin
WAITFOR DELAY ''00:00:00.05'' -- Wait for 50 ms
end
else begin
-- All other errors
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
end
end catch
endThat is interesting. Not sure how I might implement this since all I am doing is select statements. But it might be worth a shot. I can certainly roll this up in QA and see how it plays. Thanks!!
Missing my commit in the TRY block. That's what I get for cutting out a ton of code. The commit would go before the break statement.
Viewing 15 posts - 60,256 through 60,270 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply