Voice of the DBA

Author: way0utwest

  • SQL Server Thumbnail Metrics – OS Memory

    I’m gathering a few metrics around the Internet for SQL Server from people that I think really know how to run a SQL Server. This is a series of posts that I’m making to gather these metrics up for easy access later.

    Host Memory

    The short metric is:

    Leave 4GB for the OS, or 10% of physical (or guest) memory, whichever is greater.

    Examples: If I have the following amounts of RAM, I leave this for the OS.

    • 16GB of RAM – leave 4GB since 4 > (16 * 10%)
    • 32GB – leave 4GB since 4 > (32 * 10%)
    • 64GB – leave 6.4GB since (64 * 10%) > 4

    Essentially leave 4GB until you get to 48GB of RAM, then go to 10%.

    References: Mr. Brent Ozar of Brent Ozar, PLF who’s presented and written this a few times, but the reference I’m using is: How to Set SQL Server Max Memory for VMware

    Explanation: In the older x86 days there were all sorts of rules for setting memory for the OS and SQL Server. I won’t go into those, and for the time being we’re working in the x64 world, not x128. For that, you need to be sure the OS isn’t starved by SQL Server and unable to manage the host processes. These days 32GB isn’t expensive, and 4GB gives you a nice cushion at lower levels. At higher ones, you need

  • Don’t Explain Too Much

    sketch
    Leave out the details when communicating with management.

    I was reading a note recently from a DBA working at a software company. Their management wanted to ensure clients had a simple backup solution and were leaning towards Windows OS backup instead of SQL Server backups. They were planning on running databases in simple mode instead of taking transaction log backups, which were seen as too complex. While this can work, I’m not sure this is the type of discussion that should even come up.

    Management should be concerned with the higher level goals. Clients need a simple scripted backup. Period. The implementation of that isn’t something that management should be discussing with developers. This is the perfect example of where the software development goes off the track with micro management. Managers becoming deeply involved in technical decisions and implementations is a sure way to ensure that less than optimal decisions are being made.

    What should happen? Technical developers should get the goals of management (a simple backup process for clients, every day). They should then recommend a solution, but with a minimal of technical details. Managers should have no idea that transaction log backups are being made or a part of the process. Developers should write scripts, tools, or processes that allow an administrator to accomplish a goal in an easy to execute fashion, but shouldn’t need to explain how every detail works to the end user.

    Keep it simple and effective. That’s a mantra that’s worked well for me throughout my career.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Regulators, Mount Up

    Warren G - Regulate
    More auditing of regulation compliance is coming for data professionals working in health care.

    I have an encryption talk that I give and usually find a few people in the audience that have implemented encryption. In almost every case this has been because of PCI or HIPAA regulations that dramatically reduce penalties if data is encrypted. Whether you agree with the regulations or not isn’t important. There are rules that some of us have to follow because of our data and my guess is that the number and scope of those rules will increase in the future, not just in these industries, but others as well.

    If you are covered by HIPAA law, you may have gotten some increased scrutiny this year. There are audits underway from the Office of Civil Rights (OCR) for 115 organizations that will help them to ensure they comply with regulations. Penalties aren’t supposed to be assessed unless there are serious violations, but starting in 2013, the  Health Information Technology for Economic and Clinical Health (HITECH) Act requires that the auditing program will be enforced with surprise audits.

    For those managing health care data, you should be sure that you are complying with HIPAA regulations. If you’re not, you ought to make sure your boss is aware that next year you could have a surprise audit and should be ensuring that you meet the laws regulations. The OCR has released their audit protocol, and you should be sure that you understand what is being evaluated.

    If you aren’t regulated by PCI or HIPAA, you might still check over the protocol as much of it is good practice for securing any data. It can be general, but if you abide by the spirit of the criteria, I’d bet that will pass an audit by your security group.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • The Last DBCC CHECKDB Date and Restores

    I ran across a question on Twitter recently where someone asked about the DBCC CHECKDB date after a restore. For those of you that don’t know this, you can run this command on your instance (in a database)

    DBCC DBINFO WITH tableresults;

    This returns a lot of information, but I’ve circled one value below:

    dbcc1

    This is the last known good DBCC date that exists for this database. It’s also the value in the error log from the execution of DBCC CHECKDB on my instance early this morning (Arrow marks the entry).

    dbcc2

    If I were to restore this database, what happens to this value? I’d expect that it would be restored to the last value that was contained in the backup file. That would make sense to me, but let’s test it.

    First I run a backup:

    BACKUP DATABASE [db1] TO  DISK = N'C:\SQLBackup\db1_20121115.bak' WITH NOFORMAT, NOINIT,  NAME = N'db1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

    This should have the last DBCC date inside the file. I know run a CHECKDB and note the time in the error log.

    dbcc3

    This returns successfully (whew, didn’t want to test corruption restores). If I then run DBINFO again, I get the current date from the error log returned. Now let’s restore from my backup.

    This completes and when I run DBCC DBINFO again I find the dbccLastKnownGood date is reset back to the 12:04am value instead of the 9:11am value.

    That’s what I expect, and that was what a few other people confirmed on Twitter. It’s logical that this should be the behavior, but you never know until you’ve tested it.