Voice of the DBA

Author: way0utwest

  • Powershell in a Month Day 16 – Working with many objects

    This is part of my Powershell Challenge, to learn more about PowerShell (PoSh) using the Learn Windows Powershell 3 in a Month of Lunches book by Don Jones.

    One of the great powers of T-SQL is that we can owrk with many rows at once. We use a query to tell the server what we want done to a group of objects, and then the query executes against every row that matches the filters. It’s this power that makes SQL much more efficient than the RBAR method of moving through a cursor structure. This chapter introduces the ways in which we do that with Powershell.

    The first part of the chapter is about batch commandlets, using things like Stop-Process receiving a pipeline of objects from something like Get-Process. I like their favorite example, which I’ve yet to run. I need to do this in a VM and see what happens.
    Get-Process | Stop-Process
    That’s an easy concept for me, and I see how valuable this is. However not all of the cmdlets we call will handle batches. The next part of the chapter examines WMI and the Invoke-WmiMethod call, which can take a series of objects as well. I somewhat dread the idea of needing to use this method, or worse, having to call a series of methods in which I have to pass in all the parameters. It’s good to know that I need the $null parameter for those parameters I don’t want to deal with, but it’s an ugly syntax. It’s handy to know this is an option, but I think I’ll be doing MSDN research for those times that I need to work with these methods.
    The next part of the chapter looks at the ForEach-Object syntax. I’ve seen this in scripts, and I could guess what i does, but this starts to make sense now and I can more easily decode the scripts when I see them. Along with the $_ marker to represent an object, the whole structure of PoSh is becoming easier to read.
    The chapter isn’t long and and starts to spend time comparing the various methods of enumeration. That’s helpful. Seeing how the same end result can be achieved in different ways is very handy. The lab was easy, though I had to skip the WMi stuff as I was unwired at the time that I went through this chapter. However I had an idea of what to do, just no way to look up the WMI classes.

  • How Often Do I Backup my Log?

    Do you know how often to back up your transaction log in SQL Server? Most of us have stock answers, hopefully answers that have time intervals like every hour. I worry that most people have a time interval of “never” because their answer is “I make full backups and don’t need to backup logs” or “What’s a transaction log?” That’s a different discussion and if those are your answers, I have an article for you to read.

    Managing a transaction log is a bit tricky and not straightforward. I think far too many people manage their logs based on the space they are trying to maintain on disk. However that’s not the way you decide when to back up logs. The way you decide how to back up a log has nothing to do with space. It’s best stated in this quote from Gail Shaw: “the log backup interval should be less than the allowable data loss for the system.”

    Simply put, decide how much data you can afford to lose. Or how much loss will exceed your tolerance for being yelled at. That’s your log backup interval. Make a log backup after that much time passes. Schedule that interval into your maintenance operations and observe how big the log backups are. That will determine your log size, not the other way around.

    As with most guidance and advice, this is based on a time and place in the platform’s lifecycle. This is the advice I’ve used from SQL Server 7 to 2012, but it’s subject to change, so make sure it’s still valid if you’re reading this in 2020.

    And, of course, make sure that you also understand how to restore these backups in case you do have an issue. That’s probably the most important thing to know.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and Mevio . feed

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • 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.

  • The Data Platform Vision

    Recently the Microsoft Data Platform blog published a piece on what drives Microsoft’s vision. It’s an interesting look at the ways in which data is being used in some businesses as well as the variety of types of situations that must be dealt with. There is this idea that the value derived from having lots of different data was once only used by specialized companies or applications. However today, it’s being more widely analyzed and accepted by more businesses. Of course, the vision is that Microsoft can help those businesses.

    In the cloud.

    I know this piece is more marketing than technical, but I have concerns that Microsoft is not driven by what customers want and need, but is instead seeking to drive customers to their own tooling and offerings in the Azure cloud. While I do like many things about the cloud and think it has a place in the future of many companies, I also think that there are still lots of opportunities, as well as demand from customers, for on-premises solutions.

    I worry that the focus of Microsoft, which can be tightly bound in one idea as they eye more sales revenue, is driven with the idea that everyone, and all applications, will some day be in the cloud. I can’t see that in the future for many businesses, and certainly hope that Microsoft recognizes this. Otherwise many of our hybrid solutions that push some data into the cloud might be backed by PostgreSQL or some other platforms as the on-premises database.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and Mevio . feed

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.