Voice of the DBA

Author: way0utwest

  • Brad McGehee talking about tempdb issues

    Brad presented a session that tries to help you identify and fix tempdb problems. Performance monitor is your friend. the local disks, avg read and write counters should help you determine if I/O is an issue. Watch for systems where you have >20ms for the averages, this is a gross number, and not an exact measure, but something to be aware of.

    Wait states can help you to identify contention for tempdb on allocation structures. Using sys.dm_OS_waiting_tasks, you can look for pagelatches on the pfs, gam, and sgam pages. If you query and find that you have lots of waits for these allocations, you might want to add more tempdb files to help alleviate contention.

    There is no reason a DBA should allow a database to run out of space. A quote from Brad and for the most part i agree. You ought to have alerting setup, monitor space, and grow files as needed. There are cases where a runaway event might cause issues, but for the most part, you should be managing space actively.

    Optimizing: a variety of suggestions, with the idea that you ought to assume tempdb will be an issue over time. So pre-plan for performance.
    – minimize usage. Dont return more rows than you need. Don’t sort data you don’t need to, don’t use order by or distinct if not needed, keep transactions short, index well, avoid temp tables.
    – Avoid cursors, especially static or keyset driven cursors.
    – avoid LOB columns if you can, consider vertical parittioning.
    – avoid table variables
    – avoid triggers where you can
    – avoid aggregating large sets of data
    – avoid snapshot isolation or read committed isolation levels
    – if you use sort in tempdb for index rebuilds, schedule it during off hours

    You can use these features if you need them, but understand they impact tempdb. Be smart and minimize the usage where you can.

    Isolating tempd on separate physical disks can help speed it up, adding RAM might help if sql server can avoid spilling to tempdb, but that depends if you have memory pressure. If you do not have any memory pressure, then you may not get any benefit from adding ram.

    SSDs are used for tempdb, but the problem is that tempdb has lots of read.write and can “wear out” an SSd drive.

    “The DBA life is about compromise”. I would agree with that. We can’t always do what we want and dont have the money so we need to make tradeoffs.

    Preallocate tempdb space, monitor, and then resize as needed. Use IFI for data growth if needed. Note this does not apply to log growth.

    Multiple files can help prevent tempdb contention, recommendation is 1/4 to 1/2 the number of cores, up to 8. Note that you want to make all your files the same size.

    If you enable TDE, which is a good feature, be aware that tempdb is encrypted. Also be aware that if you remove TDE from all databases, tempdb remains encrypted. Be careful of “testing” TDE on servers.

  • Brian Kelley on Security at SQL Server Connections

    Random notes from Brian Kelley’s security talk. Brian spent a number of years working a a security guy for a bank in the Windows and infrastructure teams.

    Networking, the UDP port is 1434. This is the listener and browser for SQL.

    Default Tcp1433 is the default port for SQL. What about TCP 2433? In SQL Server 2000, the “hide my server” button moved the listener to 2433.

    If you are a worthwhile target, a hacker might take weeks to dig through the access points for your servers.

    Those hallways conversations matter. You never know when someone is actively listening to get information about your internal systems. Tom Clancy is a perfect example. “The Hunt for Red October” contained a lot of classified information about submarines, Mr. Clancy was questioned by the CIA to determine how he learned things. He did a lot of research, listened a lot around personnel and put various pieces of information together to understand how submarines work,

    These are hacking tools and you should use them with caution and with permission in your organization. use them carefully.
    Nmap is a port scanner.
    Quest’s Discovery Wizard – will find sql servers
    SQL Ping – similar to the Discovery Wizard
    Perl, Python, PowerShell scripting
    Nessus and other scanners

    What do you check? Blank sa or simple sa password. Using “sa”, “dba”, “password” or anything like this is a problem, once someone can get into one server, they often jump to another one,

    What was the last SQL server vulnerability? I didn’t remember, but Brian told us it was MS09-004, which affected SQL Server 2000 SP4 and SQL Server 2005 SP2. It allowed access through a replication extended stored procedure.

    People hop from server to server, so it’s a good reason to have separate service accounts for each instance.

    The standard hacker methodology is to find a weak point, attach, and then expand your attack. Move from machine to machine, or grow to more privileged accounts. This is why you need to provide a series of barriers, enforce auditing, and be diligent on vulnerabilities.

    Third party apps are a problem, often with weak passwords, sometimes stored on the file system, if someone can get that file, they can attack your sql server.

    Lots of scary stuff. It paints a picture that makes it seem like you cannot protect your serveres. After 45 minutes of scary ways to attack, Brian goes into a series of ways to protect your servers. Firewall your servers at the network layer, and remember to cover domain controllers, backup servers, dns servers, and other servers that your sql server might talk to.

    A intrusion detection system behind your firewall is a good idea.

    Most issues come from user workstations. Limit the accesss their systems have to the server, remove netbios, file shares, etc.

    Pen test your systems. Run real world tests. Watch out for maintenance crews, cleaning crews, weekend open doors, etc. Watch out for backup tapes. They are a source of vulnerability. Data center tours are an issue, if you have a large data center.

    Watch out for open sessions, remaining logged in, your password is in memory and if someone can get to the server, they can get logged in users’ passwords.

    SQL Server and Windows 2008 are much more secure. Many of the known vulnerabilities from the past will not work. A good reason to upgrade past 2003/2005.

    Overall i think that a good firewall protects most of your systems, but there is s lot of education needed among both administrators and non IT people to keep secrets secret, be aware of conversations away from your office, and dont take shortcuts, most security isn’t hard. It might be a pain, but often putting up with a little inconvenience can greatly increase your security.

  • Data Improves Lives

    I’m not sure if this NYT article will get through, but I really liked “This Data Isn’t Dull. It Improves Lives“. It talks about how government data that’s around and using it to provide other services and enrich lives.

    Even though I mostly believe the Libertarian philosophy, I disagree in places and one of those places is that I think government ought to do a certain about of work in research and providing services in a way that private companies won’t do it. One of those is producing a lot of data on the world. However since that data is being funded by our tax dollars, I’d like to see the data available for anyone to use as a part of their company. With everyone on an equal footing, we could see some amazing applications.

    The app that shows the bus locations, schedules, and real time updates is cool. Very handy for people that need to use public transportation to move around the city. I know that there are times I would have loved to have had that in my life when I didn’t have a car. Heck, maybe apps like that will encourage more people to use public transport and have less cars on the road.

    Data is truly becoming the World 3.0, at least in my mind, and I’d like to see more government data available, and then used by private companies.

     

     

  • Extended Events at SQL Server Connections with Jonathan Kehayias

    I was very interested to hear Jonathan Kehayias’ session on extended events. I know this is one of the areas i need work and it is becoming a more important part of SQL Server with each version. Beginning with a good discussion of what extended events are and the terminology is, Jonathan presented a lot of information. Much like PBM, this subsystem requires you to learn a lot of new names and terms in order to even understand the documentation.

    With a dozen or so terms I am sure i wasn’t the only one that was slightly confused, not because of the explanations but just because it’s a lot of information. I know i need to read up on this a bit more before i see another session.

    One important note: You Need to learn XML. all of the results come back as xml and some knowledge of xquery is going to be necessary to work with the event results, not my favorite thing, but i have another skill to work on now.

    After 20 minutes, it’s demo time. The first questions is do log operations cause waits? We see a demo that shows what is recorded in DMVs, what the events see, and what is reported in the query statistics.

    An interesting fact. Once the event data is gathered, you cannot drop the session without losing the data. You can however drop the events and leave the session so you can query the data.

    Another item, causality tracking is needed to associate events to a specific process. It adds overhead, but you need this at times to determine which events correspond to which particular sessions doing work.

    How often does ghost cleanup run and what does it do? Demo 2 looks at the events that are associated with ghost cleanup. In a busy system, ghost cleanup can become a bottleneck, so it can be handy to know if this is a problem. There is a trace flag to turn this off, but it turns it off for the instance.

    The demo looks at what happens when you delete a number of records and the ghost cleanup task begins to fire. By turning off and on ghost cleanup, we can see records being deleted. Lots of events, and we need ghost cleanup hitting lots of records in a short period of time. In fact we saw 12 rows being cleaned up in one ms.

    The last demo is a look at checkpoint. What happens with a checkpoint? When does it run during a backup? During the demo we can see the checkpoint being started right after the backup starts and we can see lots of pages being flushed to disk.

    Very cool demos. Lots more information than you normally want, but useful stuff for digging into problematic systems.