Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #52 – Arguments

    tsqltuesdayIt’s time for T-SQL Tuesday, this month hosted by Michael J Swart, our artistic DBA in the community. I love Michael’s pictures, and enjoy the time I get to chat with him at events.

    His invitation this month for #52 is to argue against a popular opinion. He asking us to find a "never ever" issue and argue for it. Or perhaps an "always do this" advice and argue against that. It’s a great topic.

    T-SQL Tuesday is the monthly blog party, invented by Adam Machanic. The second Tuesday of the month is the time to publish a post on your own blog about the topic and join the other people that write about the same topic.

    Never Turn on Auto Shrink

    I think that if you turn on the auto-shrink setting, Paul Randal rips an LED off his interactive panels. Even if he doesn’t, why would you ever enable auto shrink? It causes fragmentation, it’s somewhat insane as your files will just regrow if the space is needed for a workload such as a maintenance operation.

    The recommendation is to never turn on Auto Shrink on your databases. There are numerous references, though I’ll point to Brent Ozar’s nice summary. I’m going to pick a fight with this advice, though only in a narrow, limited sense.

    Auto shrink is, in general, a very bad idea. However I think there are a few places where I’d use the setting, understanding the implications.

    The Road Warrior

    There was a salesperson that had a laptop, traveling around and working his a piece of SQL Server (Express) based software. Let’s call him Joe, since that’s a nice generic name for this story.

    When Joe arrived at a client site, he’d get data from the clients in an Excel spreadsheet. He’d use the import wizard, load a bunch of data into a new table, point his software at it, and do some analysis of the data. He’d then make recommendations to the client and sell something based on his recommendations. When he was done, he’d click a button in the software and drop the table. The amount of data from clients varied, and it sometimes caused his database (and log files) to grow. He’d also forget to the drop the table sometimes and would wind up with 4-5 clients’ worth of data before he removed the tables.

    Over time, this also caused his laptop to run low on space. This wasn’t a big deal, and the help desk could respond to his concerns by remotely running a shrink of the files for him. However the time spent dealing with questions, and the concern that Joe experienced weren’t worth the effort. Auto-shrink worked fine for Joe and some of his colleagues.

    Performance wasn’t a big concern, as a single user accessed the database. The data didn’t live long enough to worry about fragmentation or maintenance and the reduction in irritation from Joe and the help desk was worth it.

    The Dusty Archives

    I once worked in a company that had an archive database. We had a process that copied data from previous years to a new database that was taken offline and a copy burned to a few optical discs for long term archival. We actually build this process to use a separate instance for the copy as the source SQL Servers were located in various parts of the US, and the data was remotely transferred to a central location for archival, and because we didn’t want to ship optical drives to multiple locations and explain to warehouse personnel how to use them.

    I, and my cohorts in IT, also didn’t really want to travel to small, inconvenient warehouses located in small cities without direct flights.

    One of the requirements was that we make these databases as small as possible to save space. We typically built a few archive databases before we shrank them and took them offline. In hindsight, the process of copying the data should have minimized the size of data and log files with its own shrink, but that wasn’t the case. An administrator needed to perform the shrink and sometimes they’d forget. Actually, most of the time they’d forget.

    The simplest solution was to enable auto shrink on this instance. That way once database files were sitting on the instance for any length of time, the databases would shrink themselves. No need for anyone to worry.

    Performance wasn’t an issue in these cases as recovery of the data would have meant a transfer back to a source system, where fragmentation and indexing would have been a concern.

    Narrow Cases

    In both cases, the reason auto shrink worked was performance wasn’t an issue, and administration was. Performance didn’t matter, and it wasn’t a heavily used system. I think these, and perhaps a few other narrowly defined cases are acceptable uses of Auto Shrink.

    However I also think there are many more abuses of the setting and I’d prefer it removed from SQL Server. Scheduling this process with Powershell would be trivial if you really needed it.

    And if you don’t know how to use Powershell (or SQLCMD), perhaps you shouldn’t be shrinking database files.

  • T-SQL Tuesday #51 – Place Your Bets

    tsqltuesdayIt’s T-SQL Tuesday time again and Jason Brimhall is hosting this month’s event. He used to live in Las Vegas, and with that theme in mind, he calls for you to Place Your Bets.  That’s a time when someone has gambled, or risked something on an application, venture, process that relates to SQL Server? When have you done something that caused an issue you could have avoided?

    It’s an interesting topic, and I’m looking forward to reading what people write.

    T-SQL Tuesday is a monthly event, the second Tuesday of the month, started by Adam Machanic (blog|twitter). You can participate by setting up a blog and writing on the month’s topic. Check Twitter for the #tsql2sday hash tag or look for the blog postings with a search.

    If you want to host, contact Adam.

    The Big Bet

    I didn’t place this bet, but a bunch of application designers and management did. I came in to the company late, and realized we had issues, but wasn’t able to effect change in any short order.

    I worked for a large software company. Large as in thousands of employees, and certainly hundreds of developers. Most were engaged in writing the software we sold, but there were plenty of people engaged in managing our internal systems and writing software that we were using internally. One of these was our internal Intranet site for employees.

    This was a large project, replacing a number of applications, and designed to be a central point of information where employees could turn and various departments could publish information. Being a software company, we knew that it’s worth paying for software that’s written and we chose a framework that would cover many of our needs, but allow us to customize parts of the site for use by different departments. All in all, a CMS-type system that would fit our needs.

    The project started before I did, and no one consulted me or my group, as the production DBAs, as to the architecture, hardware spec, or anything else. It’s not that we should have, or would have, led the way, but we could have offered opinions about how things might perform.

    The day of the roll out came. I was told which day it was and never participated in any testing. The global policy was changed to set everyone’s default browser to the intranet, and I came into work expecting to find a new site where I’d see information posted, resulting in (I hoped) less emails from various departments. Not long after I arrived, I was pulled out of a meeting because of performance problems.

    I had no idea what to look at on the site, but we found CPU pegged and very high I/O on the server. I decided to run a short Profiler trace from my machine to get an idea of what the workload was and what we might look to run. I found fairly simple queries, lots of SELECT * to a few tables. What was interesting was that the queries were scrolling rather quickly on the screen. I found that the default trace values in SQL Server 2000 were storing around 25MB/sec worth of data on my local hard drive.

    In 2001, this was a fairly large load on a SQL Server 2000 instance. When we checked the tables and calculated result sets, we found very little data from each query. There were missing indexes, but even adding those to tables that were a few hundred rows of data didn’t help. The problem was simply that so many employees, each hitting the server by default, with a series of sidebars on the web pages, each of which created its own connection, ran its own query, and ran lots of them on each page, was simply overloading the hardware.

    If was bad design, poorly tested, and not well thought through. Even simple caching mechanisms as Brent Ozar has talked about would have dramatically removed a lot of the load. In the end, those weren’t really needed. What we found was that the majority of the information being returned by queries changed less than once a week. A few weeks of development time removed most of those queries and had them replaced with static XML files that were loaded by the application for display, and could be updated from the database by an administrator.

    Ultimately the site worked well, and we did get less emails from various departments since announcements could be made on the Intranet, which most of us learned to check once or twice a day.

  • T-SQL Tuesday #50–Automation

    tsqltuesdayI missed last month and I was a bit sad, but I was traveling the week before and speaking, and then was on vacation. No time to write a post, and it was a tough topic from Robert Davis. However I’m back this month, and I’m thrilled to participate in topic #50.

    This month’s topic comes from @sqlchow who gives us the topic of automation. How would we do it? What would we do? What tools? It’s a great topic.

    This is part of the monthly blog party, started by Adam Machanic. To participate, grab the logo above, write a post on the 2nd Tuesday of the month, and link to the invitation (from @sqlchow this week). If you want to host, contact Adam (blog|twitter).

    Automation

    I’m going to tackle the topic of tools, since I’ve been working on the Powershell Challenge.

    I’ve used lots of tools for automation in my career. I started with DOS and batch files professionally, which were easy for me after a few years of Korn shell and C shell working on Sun OS in college. The idea of scripting in a batch file made perfect sense, so much so that I had alias batch files for various Unix commands, like ls, ps, etc. that I used often. We even had 411.bat on our network, that would grep a text file of our internal phonebook to return extensions.

    As I moved to SQL Server, I found myself frustrated with some of the limitations that SQL had, especially while working with files. Batch files weren’t great, and a full C++ or VB program was too heavyweight. I eventually moved to VBScript, and learned to love the FileSystemObject. I had tried Perl briefly, but abandoned it because getting ActivePerl installed on all machines was too much of a hassle.

    However I’ve started to move to PowerShell (PoSh) and I am starting to really like the idea of quick modules and cmdlets written in the language. It has a lot of the piping and simplicity of Perl, but the power of working with objects. It’s also quick to write scripts. While I find it cumbersome for now, that’s me, not the language.

    These days, if I needed to automate something in SQL Server, I think I’d really look hard at PoSh if I needed to work outside of an instance. Meaning access the host system, work with files, or connect to multiple instances.

    Inside once instance, I still love T-SQL scripting where I can use it.

    One further note, scheduling is an important part of automation. While there are some good enterprise level schedulers, they aren’t always available. That’s why I tend to use the SQL Agent to do lots of scheduling for me. It’s more reliable than the Windows scheduler built into all Windows hosts, and provides some notification capabilities.

    More importantly, I can query the status of my automated tasks.

  • T-SQL Tuesday #48–Cloud Atlas

    tsqltuesdayI never saw the movie, though it’s on my list to watch sometime. I love the Wachowski’s as directors.

    This month Jorge Segarra is hosting. He is better known as the SQLChicken, and his theme this month is Cloud Atlas. He’s asking us to write about the cloud and what we think of it. What’s our take on the cloud. He wants to know if we’ve used it, what we think, probably how we even define it. It’s a great question with all the media and hype about the cloud, especially for data professionals.

    This is a monthly blog party, where bloggers write on a specific topic every month, on the second Tuesday. I’ve got a complete list of topics on this blog, and to participate, do this:

    • write a blog
    • publish it on the Tuesday, November 12, 2013 (for this month)
    • be sure a trackback or comment is on Jorge’s blog invitation
    • include the T-SQL Tuesday logo, linked to the invitation.

    That’s it. Write, and enjoy.

    The Cloud Atlas

    What is the cloud? It’s been hyped in commercials and numerous articles, and to some extent I think we don’t have a good definition. We have PaaS, IaaS, and SaaS, all of which are legitimately the “cloud” for different people.

    As far as I’m concerned, the cloud really is any computing service that is provided by a vendor. This means you don’t own the hardware and rent out either a machine (physical or virtual), a service, a set of APIs, etc. This exclude co-location, but anything else from renting a VM to using IFTTT, is cloud computing.

    With that in mind, what do I think? There are certainly problems with the cloud in terms of security and recovery of data. However I think the promise of renting out computing resources (machines/platform/software/services) in a scalable fashion is amazing to me.

    Do I use the cloud? Sure. I have Evernote, Skydrive and Dropbox to keep my data in sync. My family uses OurGroceries to manage a shopping list. We daughter is hooked on Spotify, which alleviates the need to keep much music on her device. I even have backups of data on Glacier.

    On the career side, we (SQLServerCentral and Red Gate) offer AdventureWorks on Azure for users to play with. I’ve done minor work in Azure, and spun up a SQL instance on AWS. I haven’t actually done anything more than test, personally, but certainly the cloud is useful for SQLServerCentral. We host our database server and web server on virtual machines we rent from Rackspace. We use a Content Delivery Network for some videos and YouTube for others. We’ve dramatically lowered costs and increased scale with the cloud.

    The cloud works.

    Sometimes.

    For some applications.