Last day at work we were confronted with an error in our monthly production run. It turned out the Trustworthy property of the database had been set to “off”, so I suspected one of the dba’s to have performed a restore without knowing about this setting. It turned out our dba was helping us with disk space issues by moving database files around to new disks where there was more free space.
My first thought was: “They should have used the detach/attach method when they were moving the database files!”
I soon learned from another coworker that that was what they had probably done in the first place.
Somehow I thought that when you use the detach/attach method, the database settings are kept in the sys.databases table, and are applied automatically when you re-attach the database. I set out to test this, and booted one of my vms at my home workstation to do this.
I then detached and attached the database using the standard SSMS menu:
I queried sys.databases, and it turned out no information was left in this table about my database:
So, next time you want to move your database files. Do not use detach/attach.
Instead, alter the database to use the new file location. Then, take the database offline, move the files and set the database to online again. Presto!
This post is about something I come across more times than I would want to, and not even the least of people are repeatedly triggering my nerves with a thing like this. Anyway, I expected you not to, when you are a database developer in a multinational software development company.
Let’s cut to the chase: this post is about triggers. Database triggers. Very handy database objects that can perform a lot of useful tasks for you whenever you modify data in a table. I’m kidding. Of course, triggers are handy, but they can cause issues for performance, deadlocking, concurrency, and can even bring a not-too-busy database to a grinding halt when they are used wrong.
This post is not about that.
A few weeks ago, I received a call from one of our application managers. They told me they received some code to fix a bug in their application, and asked me if I would execute this code inside the application’s database:
CREATE TRIGGER _UpdatePlaceId
ON dbo.Appointments
FOR UPDATE
AS
IF UPDATE(PlaceId)
BEGIN
SET NOCOUNT ON;
UPDATE dbo.AppointmentOccurence set X= @placeId WHERE AppointmentOccurenceId = @appointmentId;
END
GO
I wonder.. What would you think about this code? Is it ok or not? And why? Think about the impact this code would have if you are thinking like a business person.
I have changed the code a little bit by the way. Only the one who wrote it can tell it refers to something he wrote.
It took me one glance at this code to tell my application managers: “No, I am not going to run this code, because I think it is dangerous.” I am fortunate to work in a company where people trust eachother’s judgements, so they came to ask me why, and I told them: “Well, this code could work great, if you are sure you will always update one item at a time. But any time you to update multiple items at once, you (or in fact your users) will be screwed.
When updating multiple rows, the displayed code will insert only one of the occuring values into the @appointmentId and @placeId variables. Next, it will update the AppointmentOccurence table and set all the values in the two columns to those values. I know a little thing about business, and I doubt a business would want all their appointments to be at the same place at the same time. Take a look at this code again, and if you still can’t see what I mean, then please resign from your development job ;). Or start studying. One or the other.
This post showed you, triggers are dangerous stuff when you don’t know how to use them. In fact, I think any software developing company should have a policy like: “If you are contemplating using database triggers, first consult with your senior dba or database consultant.”
“Ok, we hear you, but if this developer said this code fixed a bug, how can we make it right?”
Well, I am glad you asked. It’s very easy as well. This developer made use of the “inserted” table. That’s one thing he did right. The solution is to join this table to the one that should be updated, and update the appropriate columns accordingly like this:
CREATE TRIGGER _UpdatePlaceId
ON dbo.Appointments
FOR UPDATE
AS
IF UPDATE(PlaceId)
BEGIN
SET NOCOUNT ON;
UPDATE AO
SET AO.X=inserted.PlaceId
FROM dbo.AppointmentOccurence AO
INNER JOIN inserted i
ON i.xId = AO.AppointmentId
END
GO
Is it me, or does that code look even smaller than the previous one?
Anyway. As I stated before, there are some uses for triggers that are useful. When you are working on a very busy application they could prevent an expensive round trip to the application server and back. But please, do not use triggers for elaborate processing work, and you would even prefer not to use the solution I posted as joining tables can be an expensive process. This post was about the correctness, the integrity of your data. Not about performance.
Keep in mind, when you start working with triggers, you will always have an overhead, performance or otherwize.
Anyway, never use variables in a trigger! Of course you could, if you know what you are doing, but, in general: “variables + trigger = wrong”.
.. Which makes me think about indexes. Indexes are great. They allow you to search for one item fast in a multi-million row table. Why not put 35 or more indexes on one table? Just to make everything go faster… What do you think?
I got in at 9 AM, and found an empty spot in the back of the room between two fellow DBA’s. I said good morning. Only the guy on my left responded with a similar gesture. I felt a bit awkward as this was my first day amongst this many computer scientists nerds ever. I did not know what to expect, but at least I expected people to say “Hello” or “Good morning”. I felt astranged, asking myself: “Am I also like this?”. I know I am not, because my friends repeatedly tell me I am overly social. I have got quite reserved feelings about what they mean when they say this, but at least I say “Hello”.
This was my first morning at SQLbits. Not knowing what to expect, and feeling a little shy as wel because I didn’t know anything about the other guys in the room. “Do they know more? Are they better than me? Am I at the right place here to learn something new?”
“Good morning! How y’all doin’!?”. A Texas voice sounded through the room. I looked up from my laptop and there were our gurus: Sean and Jennifer McCown. A husband and wife couple, and I don’t remember if this is what they said exactly but they are from Texas, so let’s just stay with that, y’all.
Let’s get a view of the audience: “How many sql servers are you guys managing, right now?”
He wanted to hear from all of us, going left to right, front to back of the room: “4 I heard. 20, 15, 50, 150, (me:)200, 400”. 400 was about the largest number I heard that day, thinking: “Damn, I’m a pretty big fish in this pond of sql server dba’s.”
“WRONG!” Sean exlaimed. “One!”
“Huh?” We just told this guy we have to manage hundreds of servers, and he is like: “One!”? Of course, he wanted to make a statement. Theatrical, like all things from the US, but effective nonetheless.
Watching and learning from Sean and Jennifer was a treat.
Sean told us how we should standardize our sql servers across our environment, like: standardizing mountpoint names so you can script operations across your entire farm.
“How would you go about updating server settings across your server farm?” Sean asked us. Again, lots of different answers from the room: use a cursor to loop over instances from your management db, or get your junior dba to do manual updates for you on all the servers. (This is a nice one, I think. Let’s see how long the junior guy (m/f) takes before he/she figures out how to script things instead of doing them manually :p). “Umf pwfswl!”, was what I heard someone say from the right side of the room… “Pwfswl!” turned out to be the answer Sean had been waiting for. Of course the guy meant “Powershell” but hey, in a time of ethnic emoji, this guy was entirely right.
Standardization is key. Like, for instance, you have your set of maintenance scripts: instead of installing this script manually on all of your servers, use Powershell to push it to all of your servers.
It’s quite simple: say you have your own little custom made management db where you keep a list of all your known instances. (I have mine; have you?). If you do, it is very easy. Use Powershell to get a list of all your managed instances. When you have that list, use something like Invoke-Sqlcmd to run your sql script on all your managed instances. It’s that easy. It will take less than an A4 paper size (or Letter size) of coding to get your stuff running.
This is where the core of their presentation was about: using tools like Powershell, deploying scripts, updates, etcetera across your server farm just like you were managing only one server. Great stuff.
Of course, there were a lot more things they talked about. I have about 5 A4 pages (I’m from Europe. Letter size would be 5.5 pages :)) full of notes, so I couldn’t possibly go into them all without boring you.
One thing more though, and…
It’s about backups. Of course it is. In my company, we are using Ola Hallengren’s backup scripts. Take a look at his free maintenance solution which has won best-of prices in consequtive years in SQL Server Magazine. It does a lot more than making backups, but there is one drawback to his solution: it doesn’t allow you to manage your backup intervals in a central location.
Why would I want that? Well, I know, right now, all my backup schedules are allright. Meaning: I know some of the backup schedules for specific applications by heart and I know my standard schedule. That should be enough. I mean, I know I am performing a log backup on my BizTalk Messagebox database every 3 minutes. Generic non-sla dependent application databases have hourly log backups, VMWare’s VCenter database is in simple recovery model according to VMWare’s recommendation (although I am not so sure if this recommendation is right to our needs; I’m going to talk to my VMWare admin about this) and my SCCM databases are being backed up by SCCM itself, using snapshots of the Windows disk.
Right now, when and if a db should be backed up is configured locally on each server, without centralized oversight. This is really bad, because this means I can’t push a new version of my maintenance script to my servers without manually checking for discrepancies, and also having to manually set backup schedules accordingly.
So, in the next few weeks I will be investigating what my options are in centralizing my backup settings. I am waiting on Minion Backup for inspiration, but if I find something in the meantime, I will keep you posted!
*Beep beep*: my alarm clock. Whoat!? 8 AM already? I thought I had it set to 7:45. Bollocks. Now I have to hurry for the one class I did not want to miss this weekend. My alarm clock was set too late and although I streamlined my routine for this morning – had my bag packed, ready to go – I still had to shower. Anyway, I thought showering would be the civilized way to go.
When I entered the convention center I found “her”, the only pretty girl about my age, already there, sitting in one of the best spots. I was left with a spot next to an indian guy who had a cold so couldn’s stop coughing through the presentation. If you stop hearing from me after this, it is because I have got his thing as well…
Anyway, Brent is already on stage. The first thing I thought was: “Damn! He’s much taller in real life!” I am 178 (cm) so not very short. Brent is like.. Ten centimeters taller than me. I console myself with the fact that he has a belly (he didn’t have that when he did the mankini presentation) and I have not. We were given a “little” handout. I put quotes in there because it’s an understatement here. This thing looks like it’s the collected works of Brent Ozar, soon to be on a shelf next to Shakespeare’s combined works. This initial thought proved to be true, because there is so much in this book that Brent had to skip over complete presentations as he was doing his talk.
Let’s talk about the first session: I came here, because I am an enterprise DBA and I want to know what I can do to help my business succeed, without my developers being able to change the code. Let’s say, you are in a company, and your managers have bought this shiny new product, without consulting you (of course, why would they, you are only the one who keeps things running 😉 ). And you will lose support if you ever modify the product. Hmmm. Tough question. His short answer: change indexes in the database, but be sure you mark these indexes. For instance by preceding the index names with your company’s name, so when the guy from MS* support comes in to fix something, you can change things quickly to how they were out of the box. Same goes for server settings like MAXDOP. If you see MAXDOP causing a problem (either SOS_SCHEDULER_YIELD or CXPACKET), sure… Change your SQL server’s settings, but be sure to change it back when the support guys come rolling in. Damn, I love this guy.
Coffee break. Brent asked us to leave him alone so he could get his coffee too, just like the rest of us. After that, we were allowed to ask questions.
I went to get a quick coffee, and decided I would go straight towards my ultimate goal for this week: I wanted a selfie with Brent Ozar. Back in Holland, I had been bragging about this, so now, even though I am a naturally shy person, I couldn’t back out anymore. So there I was, standing in a group of guys, who were asking all kinds of sql related questions. I was next. Brent pointed at me and said: “Ok, I have to continue this presentation, so you are the last one for now…. I looked at the guy behind me and felt a bit ashamed and told Brent: “Well… I don’t have a SQL question, I just want to make my coworkers jealous… Will you be in a selfie with me?” “Sure”, he said, smiling his broad Brent Ozar smile. So I got my Samsung S3 out, hands shaking, and tried to make a selfie, pushed the wrong button and turned my phone off. “Don’t worry”, Brent said, “this happens to me all the time”. Anyway, I got the camera app running again, and here is the result:
Mission accomplished!
Anyway, back to the contents of Brent’s presentation.
Brent started off by asking how much of us have gotten questions like: “My SQL Server is slow, can you take a look at it?”. 200 arms went up, duh :). “Users are the enemy” is a well known adage between IT people, but we know they can’t help it. They just want to be able to do their jobs, and we as DBA’s are here to help them do it.
Brent introduced a couple of basic stats, while comparing SQL Server to different types of cars. Have you got a Volkswagen Beetle, or do you own a Ferrari. So, these were the questions: (I won’t break into elaborate explanations of these as you can find these on MS Books Online, and I consider these to be very basic. If you think otherwise, please let me know in the comments)
1. How fast are we going? –> Batch requests per second
2. How hard are we working to achieve that speed? –> sys.dm_os_wait_stats: what is your server waiting on. You should store this metric periodically so you can see when things change for your server. Check wait time per core per second, as percentages don’t mean anything. Your server could be virtually idle and display a lot of IO waits that amount to fairly nothing when it comes to query durations.
3. How much weight are we carrying? –> Total data size and/or number of DB’s.
For all the above, Brent put his sp_AskBrent stored procedure online for free. Try it out.
Baselining.
Baseline, baseline, baseline! Store your baselines based on month, day of month, weekday vs weekend and hour of day because this will tell you about your normal workload. Your server can be very busy, but that doesn’t mean anything if it’s the last day of the month and it is busy doing calculations on the previous month, or year or whatever. Your baseline can help you determine what workload is to be expected today.
Your business wants to go faster. Brent introduced a matrix, which we can use to ask questions to management and application owners to determine what things we are allowed to change in our database (for instance, MS Sharepoint doesn’t allow you to change anything in the DB. If you do, you will lose support). Brent’s advice: prefix all your custom indexes with say, your company name, so when you have a support call and your supplier comes in, you can quickly drop your custom indexes and be supported again.
Right now it’s 6:33 PM, so I have to get cooking. Of course, there were many more topics that Brent discussed, maybe I will blog about those later..
