Words: 1075
Time to read: ~ 5 minutes
DBA Fundamentals May 2018
I was so disappointed we don’t have a webinar on May the 4th 🙁
Tag: Index
ARIGHTABORT-ing & Anti-ANSI_WARNINGS
I recently ran into a problem with the QUOTED_IDENTIFIERS option in SQL Server, and it got me to thinking about these SET options.
I mean the fact that, on tables where there are filtered indexes or computed columns with indexes, QUOTED_IDENTIFIER is required to be on to create any other indexes is just not intuitive. But if you can’t create indexes because of it then I’d argue that it’s pretty damn important! I also found out that this problem is not just limited to QUOTED_IDENTIFIER but to ARITHABORT and ANSI_WARNINGS as well.
Just check out the Microsoft Docs and what it has to say about it:
SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.
And for ANSI_WARNINGS it says:
SET ANSI_WARNINGS must be ON when you are creating or manipulating indexes on computed columns or indexed views. If SET ANSI_WARNINGS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.
It’s not just Indexes
So, like a dog when it sees a squirrel, when I found out about the problems with ARITHABORT and ANSI_WARNINGS I got distracted and started checking out what else I could break with it. Reading through the docs, because I found that it does help even if I have to force myself to do it sometimes, I found a little gem that I wanted to try and replicate. So here’s a reason why you should care about setting ARITHABORT and ANSI_WARNINGS on.
Default to on
At one stage or another if you’re working with SQL Server, you’ve probably encountered the dreaded “Divide By 0” error:
Msg 8134, Level 16, State 1, Line 4 Divide by zero error encountered.
If you want to check this out, then here’s the code below for our table:
USE Pantheon;
-- Create our test table...
CREATE TABLE dbo.ArithAborting (
id tinyint NULL
);
GO
And our attempt at inserting that value into the table:
SET ARITHABORT ON; GO SET ANSI_WARNINGS ON; GO -- Check can we insert a "divide by 0"... BEGIN TRY INSERT INTO dbo.ArithAborting (id) SELECT 1/0; END TRY BEGIN CATCH PRINT 'NOPE!'; THROW; END CATCH;
And we get our good, old, dreaded friend:
We check our ArithAborting table and nothing is there, like we expected!
SELECT * FROM dbo.ArithAborting;
What about if we were to turn our ARITHABORT and ANSI_WARNINGS off though, what happens then? Well that’s a simple thing to test, we just turn them off and run the script again:
--Turn ARITHABORT off; SET ARITHABORT OFF; GO SET ANSI_WARNINGS OFF; GO -- ...insert into our table... BEGIN TRY INSERT INTO dbo.ArithAborting (id) SELECT 1/0; END TRY BEGIN CATCH PRINT 'NOPE!'; THROW; END CATCH;
Now before I freak out and start thinking that I’ve finally divided by zero, let’s check the table:
What’s going on here? Checking the docs…
During expression evaluation when SET ARITHABORT is OFF, if an INSERT, DELETE or UPDATE statement encounters an arithmetic error, overflow, divide-by-zero, or a domain error, SQL Server inserts or updates a NULL value. If the target column is not nullable, the insert or update action fails and the user receives an error.
Do I like this?
Nope!
If I have a terminating error in my script, I quite like the fact that SQL Server is looking out for me and won’t let me put in bad data, but if you have these options turned off, even if you wrap your code in an TRY...CATCH block, it’s going to bypass it.
Plus if you are trying to divide by 0, please stop trying to break the universe. Thank you.
T-SQL Tuesday #88 – The daily (database related) WTF! The Biggest Danger to your Database: Me.
That is more of a blurb than a title…and this is more an apology than a blog post…
Kennie Nybo Pontoppidan ( blog | twitter ) has the honour of hosting this month’s T-SQL Tuesday and has decided to base this month’s topic on ‘The Daily (database related) WTF‘.
Now I have great time for Kennie and T-SQL Tuesday since my very first blog post was in reply to a T-SQL Tuesday and it happened to be a topic where Kennie blogged about the exact same thing!
Now, truth be told, I wasn’t planning on participating in this one and this wasn’t because of not having a WTF moment, but rather having too many of them. However, reading through most of the entries, I see a vast majority of them are about moments well in the past and caused by other parties.
This is not the case for me. My WTF moment happened recently and the culprit was … myself.
Sorry Kennie 🙁
Friday:
A request came in from our Developers about a slow performing query and my Senior DBA identifies an index that can be safely modified to improve this ones performance.
So a Maintenance Window was set and it fell to me, in my role of Junior DBA, to create a SQL Agent Job to create this index.
No worries so far right?
I create a once-off SQL Agent Job to create this index, scheduled it appropriately, and I’m off on my merry way for the weekend.
Monday:
I come in on Monday morning, check my email, and I see an alert in my inbox about my job as well as an email from my Senior DBA; He’s not angry…WTF?
My whole job had failed!
Unable to connect to SQL Server ‘(local)’. The step failed.
He is not angry as he has seen this error message before, has dealt with it before, and sees it as a case of “well you’ve seen it now, investigate it and you won’t fall for it again”.
A quick investigation later pointed to this in the Error Log the moment before the SQL Agent Job Step was supposed to run:
[165] ODBC Error: 0, Connecting to a mirrored SQL Server instance using the MultiSubnetFailover connection option is not supported. [SQLSTATE IMH01]
Long sub-story short (i.e. Google-fu was involved), the main reason that this failed is that the SQL Agent Job Step has been configured to use a Database that is currently a mirrored one.
And SQL Agent does not like when you try to start off a step in a database that is mirrored.
So the solution for me was to set the Job Step property ‘Database’ to a non-mirrored database (preferred: [master]), then include a “USE [<mirrored database>]” in the ‘Command’ property.
Knowing what to do now, and having identified another maintenance window for the next morning, I make the required changes to the job step and continue on with my day.
Tuesday:
I come in on Tuesday morning, check my email, and I see an alert in my inbox about my job as well as an email from my Senior DBA; He’s angry…WTF?
My final job step had failed!
CREATE INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934). The step failed
Now I’m angry too since I count these failures as personal and I don’t like failing, so I get cracking on the investigation.
Straight away, that error message doesn’t help my mood.
I’m not indexing a view!
I’m not including computed columns!
It’s not a filtered index!
The columns are not xml data types, or spatial operations!
And nowhere, nowhere am I using double quotes to justify needing to set QUOTED_IDENTIFIER on!
SO WTF SQL SERVER, WHY ARE YOU GIVING ME THESE ERRORS???
SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.
I’ve talked about stupid error message before… but in my current mood I wail, beat my breast, and stamp my feet!
The error message above was not complaining about the index I was creating, it was complaining about indexes already on the table!
In my case, we had filtered indexes already created on the table and, as such, every single index on this table from then on requires SET QUOTED_IDENTIFIER ON.
USE [TEST]; SET QUOTED_IDENTIFIER ON; CREATE ...
Third Time’s the Charm?
No, not this time.
Luckily the Senior DBA had come in while the maintenance window was still running and manually ran the create index script.
He wasn’t angry that my job step failed. He was angry that my first job step succeeded!
Are you going “WTF? Why is he angry about that?” Let me enlighten you…
Remember at the start of this blog post I said that he had identified an index that could be safely modified?
Well, on Monday, in my haste to fix my broken job I had focused too much and thought too granular.
My second job step that created the index had failed, but my first job step, the one that dropped the original index had succeeded.
There’s not really much more to say on this. In my rush to fix a broken job, I created a stupid scenario that was luckily caught by the Senior DBA.
Wrap Up:
Yeah…so thought it would be a nice, little counter-example to the other posts out there about third parties coming along and wrecking havoc, and the DBAs swooping in to save the day.
I could make up excuses and say that, as a Junior DBA, I’m expected to make mistakes but I’m not going to.
It should be the aspiration of every Junior DBA to strive to improve and move upwards, and one of the key aspects of this is responsibility.
You should be responsible for looking after the data, looking after the jobs, and looking after the business.
And if all else fails, you should be responsible for your actions.
I have been properly chastised by my Senior and am still chastising myself for this. It’s been a long week so far…
… and it’s only Tuesday…wtf?