On May 15, 2015, Redgate Software and one of our partners, Northwest Cadence, will be presenting one of the DLM Training Workshops I helped build in Redmond, WA. I’m excited to be traveling up to WA for the event and helping support Dan Wood as he delivers the training on Continuous Integration (CI).
This is our Database Continuous Integration workshop, and it’s a great way to learn how you can take your database code and get it automatically tested and built in a CI process. CI has taken hold in many parts of the software industry, but not so much databases.
This workshop uses the Redgate tools, which make the process easier. However you can just use concepts with your own scripting or other tools. Our goal here is to help you get comfortable with the idea of CI and
The workshop assumes you have your database in a version control system (VCS). If you don’t, then you should. We have a workshop on using source control with databases as well, but really you should just script our your database and put is in SVN, Git, or something. Or just use SQL Source Control, which makes it really easy.
The other requirement if you have a laptop with you. We’ve built a number of labs, and we really, really want you to try and implement this yourself. We’ll provide a virtual machine up in the cloud, but we want you to have the chance to actually do the work.
Register and Learn
You can register today and purchase a ticket if your company is interested and you want to get some hands on experience with the technology. If you want to learn how to better engineer your software development on SQL Server, and haven’t given this a try (or are worried about it), think about coming.
SQL Saturday #393 – Redmond
Our workshop is the Friday before SQL Saturday #393 in Redmond, WA. That’s a free event, and you can come there as well. I’ll be delivering a talk there, hopefully on testing, which is certainly important to CI.
Either way, you can get a full day of in depth training on Friday at the workshop, and then a variety of topics on Saturday at the SQL Saturday event.
Hope to see you there.
When I first started to be paid money to write software, I worked on my own. I had to test my own code, decide when it could be deployed, and make the decision to deploy it. I had to notify users, and over time, I had to delay deployments because my mistakes would stop a business from getting work done. Fortunately I didn’t cause any serious loss of revenue for a small construction company due to my coding errors.
Later I worked at a large company and was surprised when I finished a piece of work and had to submit to a code review. My boss told me to get two other programmers, at least one a senior level developer, and have them review my code. In those days we reviewed code on paper, with a red pen reminiscent of my school days. The amount of ink expended on my work was a bit overwhelming, but fortunately I recovered and was able to fix most of the issues quickly.
I’ve talked to a number of developers over the years and it seems that most of them make their own decisions about how ready their work is for deployment. Code reviews seem to be the exception rather than the rule, but I wasn’t sure if that was the case for SQL Server professionals. Are we more conservative and formal in our approaches? Here’s the question this week.
Do you perform code reviews?
Do you review just application code (C#, Java, etc)? Are stored procedures and functions included? Do you include DDL and DML changes in reviews? Are DBAs able to apply changes to production without a second employee checking them?
In many ways, despite the stereotype that DBAs want to be careful with their systems, I’ve seen many DBAs rashly apply a change to a production server without any oversight or second opinons. It has seemed to me that having the control to decide what changes are made to a SQL Server instance has led to the execution of that power without any oversight for many individuals.
Let us know this week if you review code, and what things matter. I certainly thing naming and structure matter, though I’m willing to let formatting go as many tools these days will reformat code in whatever structure an individual developer prefers.
The Voice of the DBA Podcast
Today’s editorial was originally published on Sept 13, 2010. It is being re-run as Steve is away at training.
I was re-reading Conor Cunningham’s “Does Join Order Matter?” recently and found this quote in the post that stood out. As an aside, read this post. It’s fantastic.
“…SQL Server’s QP is actually not set up to give you the best plan. Instead, it is set up to give you a good enough plan quickly (where good enough is very close to “best”).”
I get that, and it makes sense. Why spend 4 hours optimizing a query that runs in 2 minutes to get it down to 1:50 (Conor’s example) when the optimizer can spend 1 sec and have it execute in those two minutes. If the optimizer looked for the best plan, I’d be very angry as a customer. Especially if this happened with any frequency.
Or would I?
I’m not suggesting that we ought to optimize every query, or that I’d prefer the optimizer didn’t try to normalize queries, or that I want to see the internal tree and try to figure out if I can rewrite the query. I might, however, want to do that for some queries. There are queries that I might want to find the “best” plan for, either because they take a long time to run, or they are run often. I could then use that in something like a USE PLAN.
Computers are cheap these days, as is disk. If I were to somehow able to copy my database onto an instance of SQL Server on a cheap PC, wouldn’t it be cool if I could set that optimizer loose on my query and let it try ALL possible plans? I’d be happy if it spent that 4 hours and gave me back an optimal plan that I could carry back over to my expensive, busy server, and shave that 10sec off a query that I run 10,000 times a day.
I could see some value in a “test” instance of SQL Server that just looked for optimal plans. Now if I could just get Microsoft to build it.
This editorial was originally published on June 16, 2005. It is being re-run as Steve is away at training.
I can’t really disclose who told me this, or at which company, but I found it very interesting. Recently there was a worm that rolled through a large number of Windows systems on the Internet. A few friends told me about it since it had rippled fairly quickly through their companies. These were all large organizations, with over 2,000 people employed in each of them. However, one company had almost no infections. The spread between their internal systems was almost non-existant.
Now I’m sure that you are all wondering what great technique they used so you can deploy it in your environment. I was as well, until I heard the details. I dismissed it at first, but then thought it did make some sense. I’m not sure I’d recommend the technique, but it was interesting.
Their defense was chaos. They don’t really have a central IT organization, standards are almost non-existent, no central AD setup, not even a standard platform. They do make anti-virus, firewalls, etc. available, but it is up to individual departments, people, and labs/data center areas to deploy them as they see fit. Need a resource from another group? Better start making friends. Want to breach a firewall? I’d recommend buying a Starbucks card or a 6 pack of Red Bull for the admin of that firewall.
Now this is a technology company and most of the employees are fairly smart technologists. They are each responsible for the most part for their systems. If they break it, they need to fix it or find someone to help because a broken computer is not an excuse for work not being complete. But an individual can have a Mac, PC, Sparc, run Windows, Linux, whatever, as long they get their job done.
Samba is in use as a file system in many places, but permissions control is distributed. Sarbanes-Oxley, secuirty, auditing, etc. all still apply, but there is no central group that ensures it’s performed in a consistent manner.
As I mentioned, at first I was shocked. I thought this was ridiculous. But the more I thought about it, the more I realized that it made some sense. You couldn’t easily break into their network because what worked in one place wouldn’t necessarily work in another. Compromise one password and you might not get very far at all, even if you had an administrator password.
I’m still not completely sure what to think of this, but apparently it works. Probably just like my life with three kids and a wife that works hard. You juggle all the balls and hope none of them drops.
We’ve got a demo of SQL Monitor available that takes data from the actual SQLServerCentral systems and presents it publicly. The data is slightly delayed, but it’s the real data and it shows the performance of our system.
I actually use this at times when the site isn’t responding well. It’s quicker for me to hit monitor.red-gate.com than log into VPN and check the real instance.
I was reading a report for a customer recently and they mentioned they used the baseline feature to track performance of the system when they make changes, using this to decide if they think the changes were positive or negative.
Note: they rarely roll back, instead using this data to determine if they need to dig into more specifics and then create a new deployment (roll forward) that can address issues.
There has been a lot written about baselines, but essentially these are a way of capturing what is “normal” for your system. If current performance deviates from normal, you might have cause for concern.
Of course, this is a gross way of looking at a system and you have to apply some of your own knowledge to determine if you have issues, or some transient workload is altering performance during your sample period.
You can see a demo of baselines online and play with the ranges. It looks similar to this image below, in which case I can see the last 24 hours of my CPU time, and then compare that with the 24 hours before that.
Using this type of diagram, I can quickly visualize issues that might be occurring without resorting to complex analysis.
This doesn’t give me the exact problem. I’d still need to dig into the code being run, look at execution plans and other detailed data (much of which I can get from SQL Monitor as well), but this does let me make an initial judgment on where to focus my time.
I would highly encourage you to run, keep, and update baselines on your system. At the very least, you should have an idea of what types of performance should exist on critical systems.
I’ve read a lot of pieces on stored procedures. Most of the articles were for using stored procedures, quite a few were against their use, and a handful with extreme opinions. This piece from Rob Conery, while perhaps NSFW, is one of the latest, and more entertaining, pieces I’ve read that discusses the value stored procedures bring to an application.
For the most part I agree with the presentation, though not completely. It’s an interesting discussion about business logic v data logic, and while I do think that some logic isn’t really central to your business, there are ways you manipulate data that might be necessary in multiple parts of your business. Those manipulations might make more sense in the database as a stored procedure rather than trying to ensure every application implements (and updates) the logic in its own code.
I do like Mr. Conery pointing out the fact that ORMs or custom applications might be extremely wasteful in implementing a process with multiple calls, and stored procedures handle these functions more efficiently. We have fast networks and powerful hardware, but those calls do add up, especially if your system gets popular and has lots of users.
Ultimately I think the last sentence sums things up nicely: let’s wake up to the power of our relational systems. That’s something that developers should embrace. Why only use half the power of a tool you have? I do think the judgment of how much business logic to include in the database is worth debating, but not at an application by application basis. Think about whether each call, page, form, whatever makes sense as having the programming in the front end or the back end. Whatever you decide, you should be able to explain and justify your choice to the others you work with.
The Voice of the DBA Podcast
I’ve been intrigued by Windows Phone as a platform. I’ve never owned one, and while I considered getting one, the dearth of apps led me to an Android instead. I really tried to get down to a list of core apps that I needed, which was 12 or 15 at the time. However 3 or 4 weren’t in the Windows App Store and I decided against Windows phone.
My daughter really wanted a Windows Phone and got one a few years ago. She loved it at first and almost made it a year before she got annoyed by missing apps and moved to Android (which she loves).
I have been hoping that WP would continue to improve, not only in it’s internal quality, but also with more apps and users. I read a piece about the issues with Windows Phone (The End of Windows Phone?) and while I’m not sure I think it is dying, the author brings up some good points that I worry about.
Apps are critical. I know plenty of people think that a couple calculator apps, and a few password apps, etc. are good enough. People can choose from a few. However that’s missing the point.
People like diversity. They like choice. They like looking for apps. There are a few places I get news from that publish new and cool apps, or apps that are free for a limited time every week.
Every week I get a list of 5 or 10 apps, some new, some old. Periodically I’ll download one. Sometimes I’ll buy a paid version. The point is, I’m interacting with the app store and my phone, changing it to my liking on a regular basis.
My phone is something I’m always tweaking a bit, like my early PCs. However Microsoft seems to almost want to treat the phone like a network router. I set it up and forget about it, just using it. That’s not what’s going to grow the store.
I checked this week and there isn’t a native Starbucks app. That particular app has become somewhat a strong desire for me, so no Windows Phone for me. There are others, and I think that’s an issue.
As I’ve noted to many people before, I think the big failure from MS here is not just building more apps. Certainly it takes third parties to provide some diversity, but spending $10mm on developers as salary to build apps, and not as incentives, would be better. Paying interns to work with Starbucks and MLB and many other places to build native apps, would be a wise move.
I had a Windows 6 phone. I was excited about the idea of Windows everywhere. Over time I realize that model doesn’t work. The whole way I work on a desktop or laptop, with it’s semi-designed interfaced (it’s more evolved) isn’t how I want to work on a tablet or phone. It just isn’t. The idea of Windows everywhere, with apps running on all platforms without being rebuilt doesn’t make sense. The buttons, flows, controls, actions, etc. on a desktop aren’t practical elsewhere.
The reverse is also true. The way I use a tablet doesn’t work on a desktop. I’d have hoped Windows 8 taught you that.
I really want my data everywhere, not my apps. Stop fighting this. Make native Office apps for iOS, Android, WP8, separate from those on Windows. Don’t try to force things to work well and be highly engineered. We saw that fail with the CORBA architectures, with OS/2, etc. You’re better off worrying about data, not apps.
Windows 10 doesn’t really excite me on the desktop, except for one thing: Copy/paste in the command prompt. That’s it. What else do I need Windows to do? A better Start menu? Please, I spend seconds a day in the start menu. Even on a laptop, it’s very few interactions. Perhaps more on a tablet, but to get me to move to a Windows Phone or tablet, I need something compelling over the iPad. What will Windows Phone 10 do?
Cortana is interesting, but I still hesitate to speak to my devices. I’m in too many public places and it’s not an interaction I’m looking for. I’ve seen some people that use speech to text heavily, and successfully, even in louder environments. However it still feels silly, and an interruption on life for me. Perhaps if Cortana does more reminders, prediction, and analysis of my interactions on the device, I’ll try it.
The movement towards limiting switching between apps is good, especially for messaging. The quick respond on iOS hasn’t caught on for me, for some reason, and switching apps is annoying. The link to my desktop makes some sense, but if that’s going to happen, then one thing better be ready:
I make mistakes all the time. I will continue to do so, and any actions I take better have an easy, quick, and intuitive way to "undo" things.
I don’t have great hopes for Windows Phone unless the app situation is resolved. If it is, then perhaps there’s a low enough bar to just switch to try a new OS on the phones. However I’m not sure any other features you could build into a phone would overcome the app deficiency.
Deployments of database and application changes have tended to be stressful in my career. I’ve seen the actual deployment of changes fail, installation programs crash, the startup of an application error out, or the famous blue screen of death from a .DLL change. I’ve been most wary, and worried, when the changes were out of my control, and I had no chance to review things.
For most database upgrades, however, I’ve been able to examine scripts. Even when an installation program is making changes, I could run a test on a separate instance and use Trace to capture the SQL that would be executed. However even when the actual installation succeeds, what I’m often most worried about is a logic or performance problem that won’t reveal itself for hours, often not until the next business day when the system is under full load.
This week I wanted to know what formal (or informal) checks people might have to detect issues. I’d like to know if you are you actually monitoring systems in a scientific way that allows you to determine if some database deployment has gone awry.
What Monitoring process do you use to determine if a deployment is working?
One of the customers for Red Gate software noted recently that they have a series of metrics being captured on their systems with SQL Monitor. They can compare the performance of two time periods, say the 4 hours before a deployment with the 4 hours after (or 4 hours the next day) to determine if any issues have surfaced. That’s similar to what I’ve done in the past for performance using other tools, or my own metrics. I’ve often kept baselines of common queries run during the day, and I can compare that to post-deployment metrics to uncover potential issues.
Logical errors are more problematic, and I’ve often wanted to be sure I had a couple of backups stored, and at least one backup on a warm standby in case we find information is being incorrectly changed or calculated. Those are the worst errors, and since they is no way to determine how they might surface, I have usually ensured myself and other DBA staff were on standby to perform some manual ETL to move data around between databases in a hurry.
Monitoring is an important part of being a DBA. It’s not as critical as backups or security, but it’s not far behind in letting you know when something is not right. Let us know this week what tricks you might have to ease the worries you have after database changes.
The Voice of the DBA Podcast
The April Blogger Challenge is from Ed Leighton-Dick and aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.
Why Filtered Indexes
I haven’t used filtered indexes much in my career as they didn’t exist while I was a full time SQL developer and DBA. They appeared in SQL Server 2008, and are a very useful feature in certain circumstances.
Imagine I have a simple table:
I want to add some indexes that look for specific fields since those are times I am limiting searching in the table. For example, suppose that gender is an optional entry by users. I find that I often have NULL in this column, so I create a filtered index.
I’ll do that as follows:
CREATE INDEX Users_Gender_Filtered ON Users (gender) WHERE gender IS NOT NULL;
This is the same type of index statement we normally use, but we’ve added a WHERE clause to the statement. This means that only those indexes with a non-NULL value in the gender column will be included in this index.
Why Use Filtered Indexes?
There are some reasons given in the BOL article below, but the main reason to do this is performance. I could easily add an index using gender that isn’t filtered, but if I had 1.5mm rows in my table and 1.4mm of them have NULL here, then do I want to scan those 1.4mm rows if a query wants a specific gender? I don’t.
Indexes take space, which means more space on disk, more backup space, and more time for backups. However they also impact performance when we read the index, or we update a column used in an index. Each of those operations requires either a read or write of the index. If I can reduce the reads and writes to indexes, then I can improve performance dramatically.
I would urge you to look at filtered indexes whenever you have data where a large number of rows are not going to match a query. In these cases, you can use a filtered index that is highly selective.
A few places I used to research this post.
Quick and Easy Blogging
This post occurred to me while I was writing some code and was the first post I thought of for the challenge. However I realized I could start with the other posts, and so I wrote those posts first. This one took about 10 minutes to read through limits on filtered indexes, try 4-5 experiments to get the code to work, and write the post.
Look for the other posts in the April challenge.