Written on July 14th, 2010
The world of performance tuning, specifically query tuning, is quite vast, with entire books devoted to the subject. And those books come with pages and pages of dense and complex technical material. But in your quest to figure out how to get more performance from that slower-than-molasses stored procedure, don’t forget some basic steps:
- Define Success. Before you do anything, decide what the goal is? A 50% reduction in time needed. 75%? What?? And once you have met the goal stated for success, stop! I’m sure you have other tasks on your to-do list. Nobody in your company is going to care if you make it faster then needed. When the customer/end user is happy, move on.
- Do no harm. Whatever you do, don’t make the problem worst then it is. For example don’t throw a bunch of indexes into your database in an attempt to fix one procedure, without understanding the rest of the queries in your system. It is better to start off with a review of the code first. Changing the code will only affect that procedure where as creating or dropping indexes will surely affect other queries on your system. And please fully test all changes you do make! The last thing you want to do is introduce logical errors into your code.
- Document your changes. Oh yes, the dreaded need for documentation. I know all of the excuses for not documenting a code change…you’re too busy…nobody will read it…nobody will care. Trust me, the new developer who comes in behind you will be so grateful if you do this. And if you need to work with the same code again, you’ll be happy if you document your changes, because you will probably forget what you did 6 months later! So, write down what you changed before you forget!
Written on May 27th, 2010
For the past 6 weeks I’ve been attending a civilian “Boot Camp”, an fitness program designed to help you lose weight and get in shape. With this program comes the requirement to write down everything you eat in a food journal. Oh, how I have hated keeping this food journal, it is harder then the daily exercise sessions. Why do I want to remember what I ate last week if a lot of it was junk?…but that’s just the point. If you write your food choices down you are more likely to pick healthy food, not junk. Keeping track of my eating habits in a journal has given me insight into my eating habits and how they affect my health.
So what does this have to do with documenting our databases? Well, in my experience the worst databases I’ve come across have the least amount of documentation, in some cases, none. If you are throwing hacks into your database, you just want to purge the whole event from your mind, not write it down. We don’t want to remember the hack we had to put into the database or the fact we really don’t like the all the other “junk” that the last developer/DBA put into the poor database either.
But like your diet, the worse the database is, the more it needs documentation. Like the improvements in our bodily health, improvements in our SQL Servers will be much easier to come by if we take the time to document what we’ve got, as junky as it might be. But don’t try to document the whole thing at once. My food journal only asks for what you ate at your last meal or snack, not your food choices for the past month. A little bit at time makes it more likely that you will actually do it. So document your databases as you go, a small portion at a time. That way it won’t feel like such a chore.
Written on May 17th, 2010
About 2 weeks ago I heard of a SQL Server training event held on a cruise ship. Huh? What? A cruise ship….my curiosity led me to SQLCruise.com to read all about it. The more I read the more excited I got about going, so I consulted the checkbook and the Hubby, got the green light to go, and signed up!
Just 78 days until we depart. I can’t wait!
Written on March 21st, 2010
Too many Server and Login Names cluttering up you “Connect to Database Engine” dialog box in 2008? Do you want it to look like this again:
Do a search on “SqlStudio.bin”. It will be under …\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin. Close down SSMS, rename the file and start up SSMS again. Now those boxes are nice and clean for you!
For SQL Server 2005 directions, go here
Written on March 15th, 2010
I’m continuing to make my way through this book, although far more slowly then I would like. If you missed the first part of my review you can find it here.
Chapter 6 – This chapter focuses on using graphics such as charts and images in reports. This chapter provided a nice overview of graphics, and like the previous chapter, the directions were easy to follow. I’m not sure if I will use graphics to build reports in my current job, none of the reports we have in production include charts and we have very little use of images.
Chapter 7 – This chapter is called “Kicking it Up a Notch: Intermediate Reporting” and that is a perfect title for it. The chapter starts with showing how to create a template to use in future reports. Much time is spent on improving the presentation of reports and the chapter introduces concepts like totals in reports, grouping, sorting, parameters, and using stored procedures instead of SQL queries. Lots of meat here and it took me a bit of time to build all the reports in this chapter.
And I can’t remember any typos, not a one! The only criticism I have is this: stored procedures should be introduced earlier, maybe even in chapter 5. Embedded SQL in reports (or applications) is not a good thing.
So far, a very good book, well worth my money.
Written on February 8th, 2010
The other day I was looking up some information about SQL Server Profiler and I came across this book “Mastering SQL Server Profiler” by Brad McGehee. It is in PDF format, so I downloaded it and opened it up to take a look. 306 pages. Oh my! On just Profiler alone! Then I grabbed another book, “SQL Server Execution Plans” by Grant Fritchey. This one was 181 pages! And my Reporting Services book tops those two at 866 pages.
Ten years ago, when I started my first job in the database world, working with SQL Server 6.5, I could not have imagined the volume of information dedicated to SQL Server. Back then, a whole book on the product was only 400-500 pages. The product has grown so much that no one person could know it all. Even the people considered the most knowledgeable about SQL Server typically specialize in just a part of it. That’s probably why I have over 25 different SQL Server blogs in my RSS reader. No one blog covers it all.
I have to remind myself to focus my self study efforts. I have to partition out all the things I would like to know about SQL Server into two buckets: stuff I need in my head and stuff I should just know to how to look up. It’s less stressful that way.
Written on January 2nd, 2010
I have spent the last few weeks thinking about what the new year and the new decade will bring. I am happy to leave 2009, which was a strange year, at least I felt strange about it. Not much was accomplished in my civilian career because I spent much of the year deployed to Kosovo with the National Guard. It was an interesting experience, but it put me behind in building new skills for my civilian career. So….2010 is the year to jump start my career. Fortunately, I do have a good job, one that will provide excellent experience for me in the coming year.
So other then doing my day to day work at my job, what I am going to do to jump start and beef up my SQL Server skill set? I will focus on the following 3 items:
- Increase my blogging. My goal this year is to post at least once per week, 52 times this next year. Writing about a subject is a good learning experience, at least for me.
- Take and pass the two exams for SQL Server 2008 development, 70-433 & 70-451.
- Attend at least 4 in-person events relating to SQL Server. This could be a code camp, PASS, local user group, or some classroom training. It just has to be in person and related to SQL Server in some way.
So, here’s to a great new year!
Written on December 11th, 2009
Now that I am back home from my deployment to Kosovo, I’ve gone back to work. My first day back was Monday, December 7th. It was nice to come back, but I was a little nervous that first day thinking about what changed while I was gone. Now that I’ve been back for 4 days now, I feel a bit silly that I was nervous last Monday…there are many good changes. Most of our servers are now running SQL Server 2005 and we have a major upgrade project that is being developed on SQL Server 2008. It’s nice to be moving off SQL 2000.
It feels good to be back. 2010 will be a great year!
Written on March 15th, 2009
Just the other day I received a call from a developer with a database problem. He was receiving timeouts when he called a particular stored procedure from his application. So I took a look at the proc, reviewed the code, and tried to run it. It just ran and ran and ran, no error messages, nothing. Then I tried to recompile the proc, same thing, just hung. So I stopped it, looked at the amount of data it needed to run, not much data involved, so I cut the proc into pieces and ran it a chuck at a time. Finally, I got to the problem part of the code, the part that just hung. In reading the code, I noticed that it was referencing a linked server, hmmm, where does this point to? So, I go into the Client Network Utility and here we are, an old server that no longer exists! Change the server name and port number and yes! The proc complies and runs! Weird, wonder why it just didn’t just return an error message. Oh well, all in a day’s fun!
Written on November 20th, 2008
Notice the lack of table aliases for the select list:
Select FirstName,
LastName,
c.ID,
Address1,
Address2,
State,
Zip,
Phone1,
Phone2,
Email,
RegionName,
EmployeeName
FROM Customers c
JOIN Location l
ON c.ID = o.ID
JOIN Contact c
ONc.ID = o.id
WHERE c.ID = @ID
Don’t do this!
If this was a database you just inherited from the last guy (or gal), it might not be obvious which column goes to which table. So you have to take the time to figure that out before you can make the necessary changes. And that is just wasted time.
If you are writing a multi-table query, please use table aliases on every column in the select. The next person to work on the code will thank you.
