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 #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 #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 #35 – Soylent Green

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 “Soylent Green”. If you want to read the opening post, please click the image below to go to the party-starter: Nick Haslam (Blog | @nhaslam).



The question of this month was to write down our most horrifying discovery from our work with SQL Server. If you work with SQL long enough, you will encounter some strange situations. One way or the other…

My first experience with SQL Server was at my first official IT job. Back then I worked for an insurance company, and there I was offered the opportunity to become a “Conversion Specialist”. This meant that I visited customers, advised them about their data and our software, and converted and import their data into our main system. When I started the job, I’d never wrote a single T-SQL statement. So the learning curve was pretty steep, but after some sleepless nights of studying, I got the hang of it. And during this job, I encountered my first (of many) horrifying experiences…

In this company, the main application took it’s data from one (!!!) SQL 2000 database. The system contained all the data the company had, and it was a “rebuild” DOS application in Delphi (at that time the company worked with Delphi and .NET). In order to store all of their data into one data model (yeah, I know!), they created a “flexible and dynamic” model… In one table…

The data was stored in one table (it wants to remain anonymous, so from now on we’ll call him “Foo”), and Foo contained more then 200 columns (as I recall correctly). Every time we inserted a record into Foo, SQL Server calmly mentioned that the limit of 8000 bytes (max. per record) was exceeded. How they fixed that? I still haven’t got a clue…

Every “object” stored in Foo contained properties, or a collection of properties (which obviously ended up in 1 record for each item in the collection). But as I mentioned, they were stored “dynamically”. So if you wanted to retrieve an object “Tree”, then you needed columns 15, 18, 20 and 52. When retrieving an object “Bird”, you needed columns 15, 18, 25 and 2550 for the same properties.

But I must honestly admit: I left the company after six years with tears in my eyes. They offered me a lot of opportunities, and the colleagues were awesome!

Another example I encountered on a production environment (at a different company), was an issue with currency calculations. The product data and the currency rates were loaded from 2 different data sources. To combine these in one record (and calculate the turnover), they used a CASE statement in the script, that ran in the Data Warehouse. But when I took over the Data Warehouse, they forgot to mention one thing…

If a product was sold for 100 Mexican pesos (with current exchange rates this is about € 6.00 or $ 7.80), and no exchange rate from pesos to dollar was present, the script ended up in the ELSE clause. This clause multiplied the amount with 1, “not to mess up the data”. And without anyone noticing, 100 Mexican pesos turned into $ 100! It actually took a while for people to notice this (including me!).

And I’m probably not the only one with these experiences, so I’m glad Nick asked us to share them with the rest of the #SQLFamily! And if you want to read more of the horrifying experiences, let me know. I might write another blog post about this, because this feels like therapy! And it’s another change for me to make Rob Volk (a great guy, and great example to me!) proud, by using the “Evil” tag again! ;)

SET TEXTSIZE – Good or Evil?

One of the first things I try do to every morning when I’m at the office (besides getting a lot of coffee to get the engine started), is reading up on blogposts that were posted the night before or when I’m at the office. My goal is to try to learn at least 1 thing every day, by reading a blog post or article.

Today, one of those articles was written by Pinal Dave (Blog | @pinaldave). He wrote a blogpost about SET TEXTSIZE. I wasn’t familiar with that functionality, so I decided to take it out for a spin.

What SET TEXTSIZE does, is limit the size of the data returned by a SELECT statement. As Pinal describes in his blog post, it could be used as a replacement for the LEFT function on each column you retrieve from the database. But I agree: use it only for test purposes. If used in production, in a query that returns (for example) 5 columns, the SET TEXTSIZE is overlooked much easier then 5 LEFT functions. This reduces the chance that you or your colleagues wonder why the returned column value isn’t shown correctly.

The other remark I need to make, is that it’s interpreted differently by the SQL engine. A few examples of this can be found in the comments of the article Pinal wrote.

But when I used SET TEXTSIZE, I started wondering what this will do to your execution plan. According to MSDN TEXTSIZE is set at execute or run time, and not at parse time. But what does this mean for your execution plan?

To try this out, I created a table, and inserted 10.000 records in that table:

CREATE TABLE RandomData
	(ID INT IDENTITY(1,1),
	 Col1 VARCHAR(MAX),
	 Col2 VARCHAR(MAX),
	 Col3 VARCHAR(MAX),
	 Col4 VARCHAR(MAX),
	 Col5 VARCHAR(MAX))


INSERT INTO RandomData
	(Col1, Col2, Col3, Col4, Col5)
SELECT
	REPLICATE('1234567890', 100),
	REPLICATE('1234567890', 100),
	REPLICATE('1234567890', 100),
	REPLICATE('1234567890', 100),
	REPLICATE('1234567890', 100)

GO 10000

Once you’ve created the table, you can run the “old fashioned” script with the LEFT functions:

SELECT
	LEFT(Col1, 10),
	LEFT(Col2, 10),
	LEFT(Col3, 10),
	LEFT(Col4, 10),
	LEFT(Col5, 10)
FROM RandomData

If you look at the exection plan, it contains a table scan, Compute Scalar (that computes the new values of each row), and the select of the data. Nothing out of the ordinary I would say.

But if you run the same query with the SET TEXTSIZE, it results in an error:

An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:
There is an error in XML document (1, 6).
Unexpected end of file while parsing Name has occurred. Line 1, position 6.

The query actually returns the whole set 10.000 records, and the result is correct. Of every column, only the first 10 characters are returned. So what’s happening with the execution plan?

If you use either one of the statements below in your session, you can see that the execution plan is generated without any issues:

SET SHOWPLAN_TEXT ON
SET SHOWPLAN_ALL ON
SET SHOWPLAN_XML ON

There is a Connect item for this issue, but the SQL Server team decided not to fix it in SQL Server 2008. And looking at my screen, they didn’t fix it in SQL Server 2012 either…

So my best guess (without knowing what the actual code does), is that the execution plan XML isn’t completely transfered to the client. This is part of the resultset, and thus also delimited because of the SET TEXTSIZE.

So my conclusion would be: don’t use SET TEXTSIZE, unless you’re absolutely aware that the results you receive are delimited and that visualising your execution plan may cause an error (but only in the SSMS!). The query results are retrieved and shown correctly, but the execution plan XML is causing problems when using a small TEXTSIZE.

But if my conclusions are incorrect, or if I’ve overlooked something, I’d love to hear your comments on it! So don’t hesitate to correct me if necessary! ;)

Time-outs and locks on objects

How often does it happen: you write an application that uses a SQL Database as source, and when you want to fetch data you get a time-out. Damn! But when you need to get a time-out (for example, if you want to test an exception), you don’t get one. In SQL Server you can create your own time-outs if you want.

One of the simplest ways to generate a time-out, is to lock an object, and running a query against the object in another session. You can lock an object with a simple query:

SELECT *
FROM Company WITH(TABLOCKX, HOLDLOCK)

 
The query hint TABLOCKX puts an Exclusive lock on the table, and the HOLDLOCK hint creates a more exclusive Shared Lock, but this will be hold until the end of your transaction.

This will be obsolete if your query will retrieve 10 records for example, because this will not hold a lock long enough to cause a time-out. You can hold a lock longer by using the WAITFOR hint:

BEGIN TRANSACTION

SELECT *
FROM Company WITH(TABLOCKX, HOLDLOCK)

WHERE 0 = 1

WAITFOR DELAY '00:00:05'

ROLLBACK TRANSACTION

 
This query uses the same hints as the first query, with the addition with the WAITFOR DELAY. This will instruct SQL Server to wait for a specified time or time interval, before executing the ROLLBACK TRANSATION.
 
 
The WAITFOR can be used in two ways:

WAITFOR DELAY ‘00:00:05’

This will instruct SQL to wait for five seconds.

or:

WAITFOR TIME ‘12:00:05’

This will instruct SQL to wait untill 5 seconds past 12:00, to execute the ROLLBACK.
 
 
With these statements it’s possible to test use Locks and test time-outs without killing a SQL Server. There is only one more thing I need to tell you:
 
 
USE THE FORCE WISELY! ONLY FOR GOOD, AND NOT FOR EVIL!!! :)

Follow

Get every new post delivered to your Inbox.

Join 41 other followers