T-SQL Tuesday #56 – Assumptions

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “Assumptions “. If you want to read the opening post, please click the image below to go to the party-starter: Dev Nambi (Blog | @DevNambi).



 
This months topic is about assumptions. A few years back, I worked in a team that consisted of mainly .NET developers. Every time we mentioned “I think so…”, “I assume it works like this…” or “I think we should…”, one of them used the quote: “Assumption is the mother of all f*ck ups!”, which is a quote from the movie Under Siege 2: Dark Territory. But he was right. The moment you assume something, it’s going to blow up in your face in the end.

 
I tested it, and it works
Working for larger organizations should mean they are more prepared to certain things then smaller organizations. But I’ve seen large organization being badly prepared, or just plain unprepared. They assume their processes work, or they will never encounter failure at all.

One of the companies I worked for, took backups every night. Full backups. Of databases there were between 100 GB and 500+ GB… And they never tested a restore… Why? They used the default maintenance functionality with “Verify Backup Integrity” enabled, and they never needed to restore a database before. So they only took backups because management wanted that. They didn’t understand why, because their processes never failed, and would never fail in the future.

But one day, it wasn’t their processes that failed. A LUN went offline during the ETL process, and SQL Server naturally detected that. SQL Server put a database into suspect mode, because of database corruption. But because there were no backups, they needed to move to plan B: process about 2+ years of data (stored in XML files) again.

Eventually I solved it and recovered the database without the need of a restore, but it scared them. They now saw why they needed backups, and why they needed to test the restore on a regular basis. But they forgot about it after a few days, and we never got the time to change the maintenance processes or test any restore. After that, I made the best choice possible in my opinion: I found myself a new challenge.

 
If you don’t know what you’re talking about…
Another example of assumption I have seen a lot over the years, is people explaining stuff to other people, without any proper knowledge about a certain subject. I can recall a conversation between me and an intern. He was a .NET developer, and had some questions about how a T-SQL feature worked. Another junior BI developer started laughing, when the intern asked his question. “What a stupid question, everybody knows the answer to that!” he said. Kind of irritated by that, I asked him to provide the answer to that question. He didn’t want to. I asked him again: “you answer the question, because you laughed about it, and I want to hear the answer from you.”

He started to stutter, and he explained the functionality all wrong. When I explained it the right way, the .NET intern thanked me, and walked away with his new knowledge, ready to bring it into practice. The BI developer wanted to continue the discussion. “You’re all wrong! That feature doesn’t work that way!”. I nicely told him, that I used this feature on a daily basis, and that he was wrong. The discussion went on a little more, but I stopped the discussion by telling him: “I’m doing this for a number of years now, and working with SQL Server is what I do. You just started, and wrote your first query a few months ago. If you find any resources that show me being wrong, I’ll be happy to quit my job. Until then, please don’t explain T-SQL to other people if you don’t know what you’re talking about.”. Until this day, he never got back to me on this discussion.

 
I don’t need to check that!
Another great assumption you’ll see in several companies, are IT people that trust their own automation a little bit too much. The rule in IT is that if you need to do something more than once or a couple of times, you need to automate it. Automation is a good thing, and it can save you time. But who checks if your process doesn’t fail? You don’t want to build a system, that checks another system for you. One of the things I’ve seen is a developer that created an automated process, that checked a log table on a database server, and mailed new errors to the developer. Looking at this, it’s a perfect solution. You don’t have to monitor the log table by yourself, but an automated process does that for you.

At one moment, an application seemed to fail. It threw exceptions, and the end-users weren’t able to do anything with the application. The developer was called, and he told the users to contact the system administrator, because it must have been a server- or hardware problem. The system administrator called the developer after a few minutes, and told him the server and hardware were in perfect condition. The developer insisted his software wasn’t failing, because he didn’t receive any errors by email. But after a quick check, the developer came to the conclusion his automated process failed. The developer lost a lot of credits because of this attitude. As you see, this is another example of an assumption that went wrong.

 
Never stop asking questions
One of the most important things I wanted to show you with this blog post, is that if you don’t know the answer to a question, don’t be afraid to ask someone. The same goes for processes, tools, functionality, or any other question you want to ask. If people mock you for asking questions, they are the ones that are wrong. You’re just trying to learn and grow, so don’t feel bad about yourself!

T-SQL Tuesday #54 – An Interview Invitation

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “An Interview Invitation “. If you want to read the opening post, please click the image below to go to the party-starter: Boris Hristov (Blog | @BorisHristov).



 
This months topic is about job interviews. Looking back, I can say I had a few interview over the last few years. Not only to get myself at a company, but also to hire new employees for the companies I worked for. Both types of conversations can be very interesting, or get on your nerves very quickly. I’ve seen both…

 
Hiring people
When you work for a company, and you hire new people, you want to make sure you’re talking to the right type of people. Whether or not I’m hiring you, isn’t just bases on technical skills. The way I see it is that technical skills is mostly build from experience and insight, and knowledge can be build up by reading a book, watching a video, or talking to and learning from other people with a similar skill set. The thing I’m personally looking at in the interview is personality, passion for what a person does, and what he or she wants to achieve in the next few years. That will tell you something about the person. I’m not saying the technical skills aren’t that important, but it’s not the only thing I look for. One of the last companies I worked for looks for people they want to drink a beer with on a Friday night. So again, the personality is in some cases as important as technical skills.

 
Interviewing people
Talking to other database professionals can be either very interesting, or very frustrating. One of the interviews I had in the past few years, was to hire a new colleague for a project I was working on. His resume looked good, he had a few years experience on equal projects, and I thought he would make a good addition to the team. When I started the interview, he seemed to be a little nervous. I tried to make him feel a little more comfortable, by asking him about his experience, past projects, and personal life. When he calmed down, we slowly moved the conversation to his technical skills.

Talking about the project, I started to notice he wasn’t giving me the answer I would’ve expected from someone with his experience. Okay, maybe he misunderstood the project. We moved on to common issues we all ran into when working on projects. Every question was answered by “it depends…”, and “I’ve read that…”. Okay, that’s no good. Theoretical knowledge isn’t what I expected. And not long after that, I ended the interview. If you’re too scared to even make a theoretical decision, and defend that point of view, I don’t want you to work on my environment. That shows me that if you make a mistake, and that can happened to even the most experienced employee, you won’t have the guts to tell me you did something wrong. And I want you to tell me that, so we can solve it together, and learn from it.

Another interview I had with a database professional, turned out to be a dull conversation. The person came in to apply for a senior function, and he definitely had the experience for it. His personality was good, and would fit the team perfectly. But every answer we asked him, he answered with a lot of needless words, that ended up in a best-practice answer. The reason I didn’t want to hire him, was his lack of creativity. If you’re spilling answers you read on the internet, or in books, it shows me you’re probably not creative enough to solve the problems you’re encountering. If you’re in a stressful situation, and you need to read a book to think of a solution to the problem, you’re probably not the DBA I’m looking for.

 
Being interviewed
One of the most interesting interviews I had in the last few years, was one of my last job interviews. The first interview went well, where we talked about the company, the team, etc. The IT manager and team leader wanted to talk to me a second time, and that was the most interesting interview I had until now.

The interview started out nice. I talked to the team leader again, and another team member joined the interview. We had a good conversation about technical stuff, the team, projects, etc. When we were approaching the end of the interview, the most interesting part started. I knew the company worked with a consultant company from England, and that the lead consultant visited the office every few weeks. Big surprise, he was there that day. So the moment he walked in, I started to struggle. I needed to switch from my native language Dutch to English, and started an interview with an MCM and MVP. This made me even more nervous, and because of that I stared to doubt every answer I gave. But in the end, I did a reasonable job apparently, because the company hired me. Now I see him every month, and we have some good conversations, even though I’m still afraid to ask him a question sometimes, worrying I’m just asking a dumb question.

 
Maybe the most annoying person is the perfect candidate…
Thinking about hiring a new colleague, you might end up with a pretty big dilemma. The person you want to hire because of his personality, might not be the best choice. The one with the best technical skills might be a better fit, but you don’t like him at all. So when looking for a new colleague or team member, you might end up wondering what’s best for the company, and set your own feelings aside. But when you do, it’s always a gamble with new people. At first, the new colleague had perfect technical skills and was very nice, but in the end his skills aren’t that good or his personality is slightly different than you thought. Hiring people is still a combination of asking the right questions and a gut feeling.

T-SQL Tuesday #51 – Place Your Bets

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “Place Your Bets”. If you want to read the opening post, please click the image below to go to the party-starter: Jason Brimhall (Blog | @sqlrnnr).



 
When I read about this months T-SQL Tuesday topic, the first thing that came to mind was things that you know will go wrong sooner or later. When you encounter a situation like this, you immediately know this can’t last forever. You want to fix it when you see it, but there’s no money, or there’s no time at that moment. But they promise you, in a few weeks you can take all the time you need. Well, that’ll never happen. Until things go wrong, and you can clean up the mess. Sounds familiar? Yes, we’ve all seen this, or will see this sooner or later.

 
With power comes great responsibility
Just imagine this with me. One of your colleagues asks you to look at a problem he’s having with a script someone in your company wrote. You probably solved it while he was standing right next to you. He watches you solve the problem, and when it’s solved, he walks away with a thousand-yard stare in his eyes. You don’t really think about it when it happens, but it’ll come to you…

A few weeks later, it’s 10 AM and you’re still having your first coffee of the day, the same developer asks you to look at “his script”. Wait, what?! Yes, he watched you work your magic, and that funny language of “Es-Que-El” seemed easy to learn. So he bought himself a “SQL Server for dummies”, learned all he needs to know in only a weekend, and wonders why it took you so long to learn it. From now on, he can write his own scripts, so he doesn’t need you anymore. Except for this last time.

Opening the script scares you: it’s a cursor. But in your frustration and amazement you “fix” the broken script, by refactoring his select statement in the cursor. Because the cursor only collects data, you add a “TOP 10″ clause in the select statement, and run the script as test. Nice, it finishes is 25 seconds. “It will only consume 500 rows” is the last thing you heard him say. You send the guy off, so you can continue your own work.

Later in the day, it’s about 4 PM, you meet the same guy at the coffee machine. He starts a discussion about how he needs a new PC, because the script YOU wrote is slow (see where this is going…?). It’s running for about 4 hours now, while it should only collect about 500 records. I know what you think: that’s impossible. You walk with him to his desk, stop the script, and look at his code. That isn’t the query you looked at this morning. Asking your colleague about it explains it all: he “slightly refactored” the script, because he didn’t need al those weird statements to get him his results. Well, after a fiery discussion of a few minutes, you explain him the DOES need the “FETCH NEXT” in the query, because the query now ran the same statement for only the first record in the select statement you declared for your cursor.

So this funny “Es-Que-El” language, isn’t that easy to learn. A beautiful quote about that, and I’m not sure who said that, says: “T-SQL is easy to learn, but hard to master”. So putting your money on one horse, in this case buying yourself a book, isn’t a good idea.

 
Putting your money on one color
Another great example is a company that had a wonderful Business Intelligence environment. They used the whole nine yards: SQL Server, SSIS, SSAS, SSRS, etc. The downside of that you ask? It was all hosted on 1 physical machine, on a single SQL Server instance. Oh, and it was running low on disk space, and there was no room in the chassis to put in extra disks. That’s right: it was like juggling burning chainsaws with only one hand. Or an interesting challenge, if you will.

Eventually we hosted a few databases on NAS volumes. At that point, I was told the databases we moved were less important. Pro tip: never EVER trust them when they say that!!! They forgot to tell me the biggest database of the moved databases wasn’t in the backup plan (500 GB database takes a long time to backup), and the last backup was made over a year ago. Surprise, one night the network card failed for maybe only a microsecond, and SQL Server thought the LUN was offline or the disk crashed. So SQL Server said that the database was corrupt, and that the datafiles were unavailable. After a few hours, a reboot of the server fixed it, and SQL Server could see the disk volumes again. So the database was saved after all.

But you see where I’m going with this? You never know when things go wrong, and putting all your money on one color when playing roulette isn’t the best idea. If the hardware of your single server fails, you fail.

 
Next, Next, Finish?
But the biggest example I can give you of a bad placed bet, are companies that work with SQL Server, but don’t hire a DBA. Have you ever worked for a company that work with Oracle? Every single company that works with Oracle, has a dedicated Oracle DBA. But have you ever wondered why that isn’t the case when a company works with SQL Server?

Thinking about it, I guess this is because a successful SQL Server installation is only a few “Next, Next, Finish”-mouse clicks away. So if the installation is so easy, every developer or person with IT experience can administer it probably. They couldn’t be more wrong. You know that, I know that, every SQL Server professional knows that, but try to convince other people of that fact.

So the worst bet you can place, and this is how I write myself back to the subject of this month, is not hiring a professional to manage your data and data stores. You wouldn’t let your local baker fix your car, because the wrote some books about cars, right? So why do you let a developer with basic knowledge near your SQL Server? Just because real DBA’s cost money? Yes, we do cost some serious money. But in the end, at least when you hire a GOOD DBA, they will make you money. You don’t think so? What does a DBA cost per hour? And how much money do you lose when your servers are down for just an hour?

T-SQL Tuesday #50 – Automation: yea or nay

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “Automation”. If you want to read the opening post, please click the image below to go to the party-starter: Hemanth D. (Blog | @SqlChow).



 
Being a DBA, you want to automate as many processes as you can, in order to save time that you can spend on more important things. But have you ever considered over-automating your processes?

 
We’re safe! What can go wrong here?
At one of the companies I worked for, they thought they had everything sorted out. Indexes were rebuild every day or every week (depended on the database), databases and logfiles were shrinked, databases were checked for corruption, backups were running, etc. They felt safe, knowing that if something happened they could anticipate on any situation SQL Server would throw at them. It would blow up in their faces eventually…

One of the first things I checked were the backups. The backup job was running, but only a few databases were actually selected for backup. And the biggest database (500+ GB), which was pretty important, was skipped because it took too long to backup. And guess what, they didn’t EVER test recovering from a backup, because of a lack of disk space and time. And there you have it: a false sense of safety!

I don’t have to tell you not to shrink your database and logfiles. Everybody knows that every time you shrink your database, a kitten dies… Or an index dies… Or the soul of your database… I’m not sure which one, but take your pick. It causes (and I quote Paul Randal (Blog | @PaulRandal) on this!): “*massive* index fragmentation”. Read more about that over at Paul’s blog. Besides that, if your next query needs more space in a data- or logfile you'll see more wait time because of file growth.

The indexes were rebuild every night on the important databases, and every weekend on less used databases. But they never checked if the problem they had before was fixed when switching to this solution.

Also the corruption check was run only on user databases. They never heard of running a corruption check on system databases. The system database were in the backup process, but they never took the time checked if they could restore them or were running a backup of a corrupted database.

 
Focus on the important stuff
So instead of automating all your processes, maybe you should focus on what’s really important. You just automated your backup process. But does it run every time? Are the backups actually written to disk? Can you restore one of the backups you created?

What I’m trying to say is, you can automate tasks whenever and wherever you like, but don’t forget to test them. Once you’ve automated something, plan regular tests to verify if the automated process runs the way you expect it to. And is the end result really the result you want and expect?

 
Don’t reinvent the wheel
Another tip is: don’t reinvent the wheel. There are more people that encountered the same issue, and wrote about it or logged about a solution. So before you build your own maintenance solution, or automate health status reports, check with your community members. There’s can be found help for every problem, but the checkup on that solution is all on you.

T-SQL Tuesday #49 – Wait for it…

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “Wait for it…”. If you want to read the opening post, please click the image below to go to the party-starter: Robert Davis (Blog | @SQLSoldier).



 
Explaining developers how SQL Server works is something we all do. Maybe not on a daily basis, but you’re asked questions like “why is my index not working”, or “what’s the best way to add multiple columns to a table”. And most of the time, these questions lead to whole other bunch of questions you need to answer. And the one question we all are asked more than once: “why is my query running slow?”. So where do you start explaining?

 
Wait Types
There are lots and lots of wait types that can be found in SQL Server. In SQL Server 2005 there are 230 different wait types, 475 in SQL Server 2008 and 491 in SQL Server 2008 R2. In SQL Server 2012 they added another 197 new ones to the list. The wait types can be found by running this query:

SELECT wait_type
FROM sys.dm_os_wait_stats
ORDER BY wait_type ASC

 
These wait types can tell you what SQL Server is doing to execute your statement, and what the possible delays are. I’m not going to sum up all the wait types, but here’s a short list of common wait types you’ll see on your SQL server:

 
SOS_SCHEDULER_YIELD
Yielding processor time

LCK_M_*
Waiting for a lock

OLEDB
Wait on the OLEDB provider (Linked servers, Full-Text Search)

WRITELOG
Writing transaction log to disk

RESOURCE_SEMAPHORE
Waiting for a query memory grant

CXPACKET
Query parallelism

PAGEIOLATCH_*
Latch on a memory address while data is retrieved from disk

LAZYWRITER_SLEEP
System process waiting to start

 
All these different wait types could indicate a problem with your statement or the server. Some are more informative, while others show you a real issue. But what I really would like to show you, is how you can find these wait types.

 
DIY or just ask for help…
One of the ways to find the wait types on your SQL server, is to dive into the seemingly endless list of DMV’s. You could use the “sys.dm_exec_requests” and “sys.dm_os_waiting_tasks” DMV’s to find what you want, or you could take the easy way out: sp_WhoIsActive by Adam Machanic (Blog | @AdamMachanic ).

Adam (also the party starter of T-SQL Tuesday) wrote a no less than brilliant script to find problems on your server. But how does it work?

 
Installing sp_WhoIsActive
The “installation” of sp_WhoIsActive couldn’t be easier. You just need to download the script, and run it. This creates a stored procedure in the database of your choice. Usually I just create it in the master database. But if you have a DBA database with useful scripts, it’s okay to create it there.

 
Running it for the first time
The stored procedure can be executed without any parameters. That way, you use the default options. Just run the statement shown below:

EXEC master.dbo.sp_WhoIsActive

 
If you need it, or just like to see more information, you can also configure the procedure with a lot of parameters. If you want to see all the options you can configure, just set the documentation parameter to 1 (true):

EXEC master.dbo.sp_WhoIsActive
  @help = 1

 
The options
If you start using sp_WhoIsActive more and more, you’ll get your own set of favorite options. It all depends on the purpose you’re using the procedure for. Most of the time, I use it to determine why queries run slow, or why the performance of the SQL server is so low.

The information sp_WhoIsActive retrieves gives you a good indication of what SQL Server is doing, or what queries are bugging each other. I’ll list my favourite options below:

First, I set @show_own_spid on, so I can see my own query in the resultset.

The second option I love is @get_plans. This shows you the execution plans of the running queries:

 
Another great parameter to set is @get_outer_command. That way, you won’t just see the query running at the moment, but also the outer-command of the query (in the example below, the INSERT INTO the temp table is executed from within the stored procedure you see in the right column):

 
To see which transaction logs are used when running your query, set @get_transaction_info to 1:

 
Information regarding locks can be found, by setting @get_locks to 1:

 
If you click the XML, you’ll see which locks are granted, pending or denied:

 
The last option I’d like to set, is @get_additional_info. This will show you more information regarding the connection settings, session variables, etc:

 
Clicking the XML shows you the properties I mentioned above:

 
So this is what the query looks like, the way I personally like to use it:

EXEC master.dbo.sp_WhoIsActive
  @show_own_spid = 1,
  @get_plans = 1,
  @get_outer_command = 1,
  @get_transaction_info = 1,
  @get_locks = 1,
  @get_additional_info = 1

 
Conclusion
Wait types are your keys to open the door of the next level of SQL Server. Not all wait types are that easy to read and understand, but there are plenty of resources to be found online. For example, just take a look at the rest of the posts today. Most of the posts for T-SQL Tuesday can be found on Twitter, when you search for #TSQL2sDay.


I want to say thanks to the employees at Coeo for the easy explanation of some of the wait types!

T-SQL Tuesday #48 – Cloud Atlas

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “Cloud Atlas”. If you want to read the opening post, please click the image below to go to the party-starter: Jorge Segarra (Blog | @SQLChicken).



 
In the last few years, “the cloud” has become more and more important in our lives. Not only in IT, or as a database- or data-professionals, but also in our personal lives. Take a look around you. How many people do you still see, carrying around a USB drive to store their data? Now do the same, and count the people that use a cloud solution for their data, like Dropbox, SkyDrive (if we are still allowed to call it that…), or Copy.com?

So everyone is storing their data in the cloud now. From personal information like a copy of a passport, to text files with peoples password lists. So without jumping to conclusions just yet, I guess we trust the companies that hold our data right…?

 
Trust
But now comes the hard (and controversial) part: we trust them with our personal data, but not our corporate data. It’s okay to store your passwords and private documents in the cloud, but it’s unthinkable that you store business data in the cloud!

So where is that distrust coming from? It probably has something to do with the whole NSA-thing. There, I said it! Without completely going off-topic, I would like to explain something about this statement.

My personal opinion is that people in the Netherlands are different from the rest of the world, when it comes to their privacy. They don’t care if the ISP is monitoring web traffic. They know it’s being monitored, but they accept that as a fact. When it comes to downloading games, music or movies, they think their entitled to that. But when it comes to government agencies monitoring the corporate data they put in the cloud, they draw the line.

 
Are you… the one…?
In the past few years, the discussion about on premise and off premise data intensified. People try to convince each other with arguments, and think the other is completely wrong.

A while ago, I encountered my first “cloud-company”. I’ve done some consulting for them, and they’ve set themselves the goal to move to the cloud within the next few years. The biggest advantages they see are costs, scalability and administration. And I fully agree with them.

 
Why use a cloud solution
Choosing a WASD (Windows Azure SQL Database) solution makes it easier to maintain your environment. You don’t have to monitor the hardware, and move to another server if your hardware fails or dies. This is all being taken care of by Microsoft.

Looking at the cost of a cloud solution is easy: it saves you money. When you run on premise servers, where you need a data center, electricity, maintenance team, etc. When you use a cloud solution, you only pay for the hardware you need. And if you’re done with it, you can just shut down the machine you were working on.

The same goes for scalability. For example, if you need to run a billing process, you could “spawn” twice as many cloud machines. This makes scalability a piece of cake. And again, when your done, just get rid of the machines you don’t use anymore. This makes it easier for companies to run big processes in a smaller amount of time.

 
Trying it out
The only time I’ve used WASD is on the machine that Jamie Thomson (Blog | @jamiet) made available to the SQL Family (read about it here). This was later taken over by Red-Gate, but I’m not sure this is still available.

But if you want to try it out, just create your own AdventureWorks on Azure. You can download the scripts here, and start your Azure trial here.

T-SQL Tuesday #46 – Rube Goldberg Machine

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “Rube Goldberg Machine”. If you want to read the opening post, please click the image below to go to the party-starter: Rick Krueger (Blog | @DataOgre).



 
This months topic is about being creative with SQL Server, and how you sometimes bend the rules a little bit, to fix a problem. This might not be the best solution, but it’s the only solution or quickest solution at the time. Everyone has a story like that, and so do I…

 
Just like a normal project: no budget
A few years back, I worked for a company as a web developer and team-DBA. One of our projects was to build a new warning system, so administrators and developers knew if something went wrong in the production environment. But the checks (like heartbeats, disk space checks, etc.) needed to be stored in SQL Server. But instead of creating budget for a new SQL Server, they told us to solve it with SQL Express.

The version we used was SQL Server 2005 Express, and all went well in the development phase. But the moment we tested the new system in the test environment, the data grew exponentially within a few hours. And after a few days, we stopped the test, because we couldn’t use a single database anymore. The checks generated close to 4 GB of data each month, and that’s the limit for SQL Server 2005 Express edition.

 
Solving it, MacGyver-style
So we needed to come up with a solution within a few days. And there was no possibility to change to a full SQL Server license. So we needed to find a solution, that worked with SQL Express. We finally solved it with a 3rd party tool, that was able to run SQL Server Agent jobs on the machine, which contained a single step, that started a stored procedure. This stored procedure contained a lot of dynamic SQL (yes, I’m sorry, but we had no other option at the time), that moved data into an archive database.

The job ran every night at a few minutes past midnight. The stored procedure first checked if there was data in the database, that was older than today. If so, it than checked if there was an archive database for that month. If there wasn’t a database, it created a database with a dynamic name: “Archive” + “_” + %ApplicationName% + “_” + %Month% + “-” + %Year%.

So now that we made sure there was an archive database, we moved the data from the day(s) before today to the archive database. The flow would look something like this:

 
Don’t try this at home kids!
So you can image that, looking back at this solution, I’m not proud of the path we were forced to choose. The technical solution however, is something that I look back on with pride. Back then I just started working with SQL Server, and didn’t have a lot of experience with building these types of solutions. But the solution we build was pretty stable. The only downside was, that if the job didn’t run at night for some reason, we needed to move the data by hand during the day. And because the database ran in “production” (there was a possibility of running transactions) we needed to move the data bit by bit, without locking the SQL server. This meant that if the job didn’t ran, I would spend most of the day moving data, waiting for that operation to finish, moving the next chunk of data, and so on.

So in the end, the man hours we put into it probably didn’t weigh up to a SQL Server license, but that would have made a cleaner solution. But in the end, the manager was right (unfortunately). We never found the time after that to perfect the checks, and the system administrators went with another 3rd party application, because it was easier to maintain. So a full SQL Server license would have been wasted money after all, if we couldn’t use that for another project. But looking back, it was a great experience to build it, and to design such a solution.

T-SQL Tuesday #45 – Follow the Yellow Brick Road

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “Follow the Yellow Brick Road”. If you want to read the opening post, please click the image below to go to the party-starter: Mickey Stuewe (Blog | @SQLMickey).



 
When I read this months subject, I didn’t know what to write about, until I read one of the other blogs. So I’m sorry Mickey, but I’m using the subject this month as a guideline, and extend it a little bit. I’m glad I know Mickey, and I’m guessing she doesn’t mind me getting a little creative.

The writer of the post I read, talked about index fragmentation and data growth, and then I remembered a situation I encountered a while back.

When you come in to a company, you’d really like to see (and maybe even expect) to walk into a well organized team. A team that has procedures that work for them, and team members that agreed to solve problems in a specific way. But sometimes, it’s not…

 
Here you go!
On my first day as DBA at the company, I walked in and after a few conversations booted up my computer. Once it was booted, they gave me the name of a server, and wished me good luck. No documentation, no explanation of the databases, and no mention of who worked on the server. The only thing they said when they “handed over the key”, is “Here you go! From now on it’s your responsibility to keep the environment running and healthy!”.

So, there I was… No documentation, no health check, no one to tell me what the status of the server was…

 
Taking that first step is always the hardest
So the first thing I did was check out which databases were on the server. There were a lot: 70 in total. Then I checked the user permissions: most users were a member of the db_owner role. Great! Asking which users really needed all that permissions was useless. Everyone needed full access to everything.

So then I took the step to tell them that I was in charge of the server from that moment on, and that if they did something without my permission, I’d remove their access to the environment. Looking back, that was easier said than done.

But the first step that was really hard to accomplish, was reporting of the server health. No one thought that we needed that, and if there was something went wrong we would notice in an instant. And because I knew we really needed that, I just cleared my schedule, and created a few reports.

 
Being right is always a fun thing
A few weeks later, they finally saw I was right. One of the databases filled up a disk, and now we had issues. The only way we could see what went wrong, was looking at the data growth over the previous weeks in my reports. So now I could show them how wrong they were. It’s nice to be right once in a while!

 
Audit
This is were this months topic comes into play. A clear indication that you lack an audit trail, is when there’s an issue with one of your processes, databases, or even as small as a specific dataset, and you don’t know where the issue came from. Following your data flows from beginning to end is always a good idea. And if you work at a larger company, you’re (at least in the Netherlands) required to document your data flows for a financial audit every year.

But not only your datasets need audit trails. Your databases are in dying need of an audit trail as well. Because what happens if your database increases 50% in size over the course of a week, and you don’t track those kind of numbers? Your disk is filling up, your database stops working (if you haven’t got the space to extend your datafiles), and eventually your world is becoming a dark and evil place if you can’t fix it.

 
Conclusion
Even if you don’t have a full front-to-back audit trail of your data and processes, at least try to monitor and audit the key point in your data flow. That helps you debug excessive data growth, and helps you when (for example) a user creates a new database without asking your permissions. Even small audits help you out when you’re in a world of pain and trouble.

T-SQL Tuesday #44 – The second chance

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “The second chance”. If you want to read the opening post, please click the image below to go to the party-starter: Bradley Ball (Blog | @SQLBalls).



 
This months topic isn’t easy for me. Even while I’m writing this, I’m still thinking about what that means to me personally. Second chances sound to good to be true. Doing something for a second time, and trying a different approach then the first time, in order to succeed…

Normally I try not to be a person that looks back at previous mistakes, but sometimes that’s easier said than done. But I must say, there are not a lot of things I regret in my life. Only one that’s really close to my heart, and nobody probably knows about. But I won’t bother you with that story…

 
Deserving a second chance
People always say: everybody deserves a second chance. But I don’t completely agree with that. Everyone makes mistakes, and that’s not something to be ashamed of. But if you make the same mistake twice, you need to start wondering if there’s something you could have done to prevent it. But even then, you deserve a second chance.

The people that actually know me, know I’m a nice guy, I’m honest (sometimes even a little bit too honest), and normally you can’t get me angry. But if you screw the same things up over and over again, I’m not that friendly anymore. Let me explain that with an example.

 
No, that wasn’t me!
A while ago I worked with someone who thought he was really good at his job. Personally, I had some issues with that opinion, but I gave him the benefit of the doubt. On a number of occasions he screwed things up, and I thought he should have known that what he was doing was never ever going to work. But still, I was willing to give him that second chance. But then he got me angry. And you won’t like me when I’m angry.

There were a number of SQL Server Agent Jobs running, and they locked up some of our tables and databases. When I asked him to look at that, he said he didn’t run those jobs, and focused on his screen again. So I asked him again, nicely, to look at it. He replied with the same answer.

A little bit angry, I told him the jobs were started on the server, and that he was the only one that was logged on to the server. Then he “suddenly” remembered he started the jobs, and said the locking wasn’t that bad. As a DBA, I took a deep breath, and counted to 10, and waited for him to fix the issue. But if you’re that stubborn, you’re clearly lying to me, and don’t even have the courage to tell me you screwed up, you don’t deserve a second chance in my opinion. At least be honest with yourself and to your colleagues!

 
Honesty get’s you a second chance
At this and previous companies I worked for, I always tried to teach the student and interns they need to be honest and listen to people with experience. Even if things go wrong, and you’re the one to blame, at least tell the truth. For me, that’s the difference between fixing the issue together and moving on, or letting him take the fall all on his own. But this is also an experience I got handed down to me by my colleagues a few years back. This is what happened to me, as I remember it:

When I started my first job in IT, I was offered a job as SQL Server Consultant. That meant that I was responsible for data conversions from different systems to our core system. When I took the job, I had never written a query before. But by listening to colleagues and my mentor (a good friend of mine who worked for the same company), I made it into a development team about 1.5 years after I started my first job.

That meant I was able to access the production system (yes, that’s where the problems began!). These permission were given to me, so I could solve data related issues in production. Until the day they asked me to update 5 rows in production. I checked and double checked the T-SQL statement I wrote, asked a colleague to take a look at it, and then took a break away from my computer so I could totally focus on this task when I got back.

I sat down again, looked at the query one last time, and pressed F5… One minute passed… Two minutes passed… And then the query finished… 50.000 rows affected… I slightly panicked, and noticed I only selected the update, a half “WHERE” clause, and no “BEGIN TRAN”… My heart started racing, and I picked up the phone and asked the system administrator (a good friend of mine, who worked at a different location) if he could restore the last backup for me, because I screwed up. After some questions, and some explanations about my mistake, the last thing he said, before he hung up the phone in anger, was “Which backup? The one that didn’t ran for the last few weeks?”.

I didn’t know what to do. How could I ever fix this? Almost every record in the table was updated, and there was no way of knowing what he old values of the updated records were. So it took all my courage to pick up the phone, and ring the system administrator again. All I heard on the other side of the phone was his evil laughter. Before I could ask him what was going on, he told me: “I’m glad you were honest to me. But don’t worry, I’m restoring the backup that was taken an hour ago. There’s no data lost”.

At that moment, I didn’t know what to think or feel. At first I wanted to slap him silly, but a few minutes later I wanted to thank him for his wonderful help. He probably saved my ass, and he never told anyone except my mentor (who also was my direct manager back then, and also a good friend of us both). A few days later, the three of us talked about it face to face, and eventually all laughed about the situation.

 
A wise lesson
But if I learned anything from that situation, besides never running an update without transaction or “WHERE” clause, is to be honest. Even though you might think the company will fire you for the mistake you made, it’s always better to tell them then letting them find out themselves. And that’s what I try to tell the students, interns, and junior colleagues I work with. Be honest, and then you earn a second chance…

T-SQL Tuesday #43 – Hello, Operator?

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “Hello, Operator?”. If you want to read the opening post, please click the image below to go to the party-starter: Rob Farley (Blog | @rob_farley).



 
Execution Plan
Everybody that ever got serious with SQL Server, knows about execution plans. And like Rob said in his opening post, if you don’t, start looking into that! But if you start working with execution plans, it gets confusing really fast. All those weird “building blocks” that tell you what SQL Server did with your query?! It almost looks like magic…!

But when you dive into it, they become easier to read and they suddenly are somewhat understandable. But hey, even after all those years looking at execution plans, some things still amaze me.

Last week I was asked to look at a query, that was used in an SSIS package. This package was one of the slowest from the nightly ETL processes. So I started of by looking at the execution plan:

 
Start pinpointing the first issue
Then I started digging, and I noticed this:

LEFT JOIN DB.dbo.T1 m
	ON m.Col1 = ISNULL(LOWER(t.Col1) COLLATE Latin1_General_CS_AS, '(N/A)')
LEFT JOIN DB.dbo.T2 s
	ON s.Col2 = ISNULL(LOWER(t.Col2) COLLATE Latin1_General_CS_AS, '(N/A)')
LEFT JOIN DB.dbo.T3 k
	ON k.Col3 = ISNULL(LOWER(t.Col3) COLLATE Latin1_General_CS_AS, '(N/A)')

 
But wait a minute… The collations we use aren’t Case Sensitive, so why use the LOWER() function anyway? And the collations are practically the same for the 2 databases used in the query (“SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS”). But the column collations are both the same (“SQL_Latin1_General_CP1_CI_AS”). SQL Server doesn’t throw an error if I join both sets, and the results aren’t different if I use COLLATE or not. So we learned another thing: the column collation is used in the ON clause of the JOIN. So both functions aren’t necessary because the collations are equal, and thus their wasting our precious CPU cycles…

Now here comes the moment you’ve all been waiting for. How does the execution plan differ? This is what the new execution plan looks like:

So what are we missing? You guessed right! We’re missing this bad boy:

So what does the “Compute Scalar” actually do? According to MSDN:

 

The Compute Scalar operator evaluates an expression to produce a computed scalar value

So that means it calculates a new value for every input column. This calculation can be as simple as a conversion of the value, or a concatenation of multiple values. Most of the time these “Compute Scalar” aren’t a major cost in your execution plan, but it can become an issue that uses a lot of CPU.

But what does this “Compute Scalar” do in this query? If I remove the ISNULL() from the query, it’s still there. It disappears if I remove the COLLATE. So in this case, the “Compute Scalar” is “calculating” a new value for every record. This “calculation” actually is a conversion from 1 collation to the other.

 
But what’s the difference?
If we compare both versions of the query, the modified version has a lot less impact on the server:

And this is only without the LOWER() and COLLATE functions. So with a little bit more effort, you can rewrite a part of the query, maybe add an index, etc. So as you can see, small changes can have a huge impact!

 
Conclusion
A “Compute Scalar” isn’t that hard to understand, but you’ll find it very often in your execution plans. So even though it isn’t the worst performance blocker in the world, it’s an interesting one to get familiar with.

If you don’t use SQL Sentry Plan Explorer yet, download your copy now!

Follow

Get every new post delivered to your Inbox.

Join 41 other followers