Mar 112014
 

T-SQL Tuesday LogoThis post is part of T-SQL Tuesday #52, which is being hosted this month by Michael J. Swart (@MJSwart). Michael is asking us to argue against a popular opinion, and I’m more than happy to do so, as this is a belief that I’ve kept to myself for quite a while.

SQL Server’s row compression feature can be an amazing tool. Not only is it lightweight on CPU usage, especially when compared to page compression, but it can save a significant amount of disk space as well. Your data also remains compressed while in the buffer pool, meaning more rows can be stored in memory, reducing the need to make slower requests to disk. On top of all that, some queries (especially those involving index scans) can see dramatic performance improvements.

In fact, row compression is so good that Microsoft’s whitepaper actually states “If row compression results in space savings and the system can accommodate a 10 percent increase in CPU usage, all data should be row-compressed.”

Yes, row compression is a wonderful thing, and the databases I maintain frequently benefit from its use.

But I hate it.

Why? Because all too often, features designed to help make things easier also make people lazy.

By far, the biggest issue that row compression addresses is poor data typing, the use of a data type that isn’t appropriate for the values at hand. For example, if a column is only going to store the values 1 through 5, an integer data type is not necessary. A tinyint data type would be just as effective, and would consume only one quarter of the space. However if you are unable to change the data type, perhaps because the database is part of an application written by a third party, row compression can be a big help.

Row compression allows the storage engine to treat fixed-width data types as if they were variable-width. This means the disk space that isn’t needed to store a value in a fixed-width column and would typically go unused can be put to work. The savings can be tremendous, and SQL Server’s data compression features are completely transparent to end users or applications – all the magic happens behind the scenes, which is why a third party application would be none the wiser.

But what if you are able to change those data types, and just don’t feel the need to do so anymore? Data compression gives you most of the advantages of proper data typing, but all you have to do to get them is flip a switch – no pesky forethought necessary. And that’s why it’s terrible. Because for every person out there who designs their databases and data types with care, there are many more who aren’t interested, don’t care, or don’t even realize it’s an option. Features like row compression that mask these issues aren’t going to interest anyone in solving them the right way.

So while row compression is a wonderful tool and can do amazing things when used properly, don’t forget it’s also an enabler.

Feb 212014
 

I know most of my important file locations by heart. Whenever I need to do things that require typing out a file’s full path, such as copying a backup or taking a peek at logs, I can type those paths from memory without issue.

On the other hand, when I’m working with systems I’m less familiar with or have never seen before, my memory can’t help me. At this point I’ve always opted for copying and pasting the paths of the file into SSMS or wherever else I might need it. This works just fine, but I’ve always been annoyed that the path and file name need to be selected and copied into the editor separately.

Two rounds of copy and paste seemed a little much. There has to be a better way, and in fact there is. This has probably been present in Windows for quite a while, but I found out about it just this week. I’ve long known that holding down the shift key while right-clicking on a file brings up extra options in the context menu, but what I didn’t realize is that one of those options is “copy as path”.

This will copy the full path of the file, including the name, to the clipboard, and you can now paste it wherever you like.

The Catch

The only downside to this is that, as you can see in the image above, the file path is surrounded in double quotes. This is great for pasting into a command prompt, but you’ll need to replace them with single quotes when working in SSMS.

Jan 312014
 

The ability to keep track of what’s being executed on a SQL Server instance, or to create a read-only copy of a database at a specific point in time can be very valuable if required by the solution you are developing. But a lesser-known fact about SQL Auditing and Database Snapshots is that not only do they work well on their own, but they also play nicely together.

The Setup

Let’s say you work for that awesome company AdventureWorks, which embraces open data so much that you can download their corporate database on CodePlex here. That database contains some super-sensitive payroll data, and they’d like to keep track of everyone who has accessed it. To do this, you set up SQL Auditing to capture all SELECT operations on the HumanResources.EmployeePayHistory table and log them to a file.

-- create a server audit
USE [master]
GO

CREATE SERVER AUDIT [AWAudit]
TO FILE (
FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA')
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
GO

-- * in a production system you'd probably want a more stringent ON_FAILURE
--   value than "continue", but for demonstration purposes it's fine

ALTER SERVER AUDIT [AWAudit] WITH (state = ON);

-- create a database audit specification
USE [AdventureWorks2012]
GO

CREATE DATABASE AUDIT SPECIFICATION [EmployeePayAuditSpec]
FOR SERVER AUDIT [AWAudit]
ADD (SELECT ON OBJECT::[HumanResources].[EmployeePayHistory] BY [public])
GO

ALTER DATABASE AUDIT SPECIFICATION [EmployeePayAuditSpec] WITH (STATE = ON);

So now anytime anyone selects from that table, there will be a record of it:

-- select from the table being audited
SELECT TOP (100) * FROM HumanResources.EmployeePayHistory;

-- query the audit file
SELECT
	DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS EventTime,
	session_server_principal_name,
	database_name,
	statement
FROM sys.fn_get_audit_file('C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\*',NULL,NULL);

So far, so good. Now let’s continue with our story and say that management wants to be able to run reports throughout the workday that show the state of the database at 6:00 am. You find the appropriate solution to be a database snapshot, so you setup a job to create a snapshot daily at 6:00 am, and another one to drop it late in the evening. All the reports can then query the snapshot and get the exact state of the database as it was at 6:00 that morning.

-- create snapshot
CREATE DATABASE AdventureWorks2012_Snap ON
(NAME = AdventureWorks2012_Data,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AW2012.ss')
AS SNAPSHOT OF AdventureWorks2012;

But what about that audit? Since a snapshot contains absolutely everything in the database at the moment it was created, it will have all that sensitive pay history data as well. How can you audit that?

The Free Lunch

The answer is that you don’t have to worry. A snapshot contains everything in the database at the moment it was created, and that doesn’t just apply to data – that goes for settings too, including audits. If someone was to query an audited table in the snapshot, this will be recorded by the audit just like on the base database. You’ll be able to tell the query was run on the snapshot by looking at the database name column:

-- query snapshot
USE [AdventureWorks2012_Snap]
GO

SELECT TOP (100) * FROM HumanResources.EmployeePayHistory;

-- query audit file
SELECT
	DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS EventTime,
	session_server_principal_name,
	database_name,
	statement
FROM sys.fn_get_audit_file('C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\*',NULL,NULL);

But what if you don’t want the snapshot to be audited?

The Free Lunch is at Grandma’s House

I can only speak from personal experience, but whenever I go visit my grandmother, I go knowing that I will be eating whether I’m hungry or not. She loves cooking and watching her grandchildren eat, and – let’s face it – there’s just no way to say no to grandma.

Similarly, there’s no way to say no to a snapshot. However the database was configured the moment the snapshot was created will be reflected in the snapshot as well. It’s important to remember that database snapshots are read-only, so even if you try to disable the audit on a snapshot, it won’t work:

USE [AdventureWorks2012_Snap]
GO

ALTER DATABASE AUDIT SPECIFICATION [EmployeePayAuditSpec] WITH (STATE = OFF);

The only way to create a snapshot without the audit in this case would be to stop the audit, create the snapshot, and then restart the audit. This raises other issues, namely that you probably shouldn’t be stopping an audit of sensitive data like that.

Jan 232014
 

Pro Tip: If you’re a recruiter and sending out mass emails about positions you’re trying to fill, do yourself a favor and make sure you’re not accidentally including other things, like a letter of reprimand from your boss. The names have been changed to protect the not-so-innocent, but here’s the email I received yesterday:

My name is Frank Schlawmeyer and I am a Sr. Executive Recruiter for B.O. Associates, a premier search firm representing major clients in the Chicago area. I discovered your information while sourcing for an opportunity we have as a SQL Server DBA for a major e-commerce company in the Chicagoland area. This is a full-time permanent DBA role and requires experience as both an operational / production DBA and must have experience with T-SQL.

Your background is impressive and I would appreciate an opportunity to speak with you regarding your work history in relation to this role. If you are interested, please send me an updated copy of your resume. I can be reached via telephone at <redacted> or via email at <redacted>. If you are not interested, please feel free to forward this information on to anyone that you feel may be a fit.

For your reference here is a link to the job description: <redacted>

Thank you in advance for your time and I am looking forward to hearing from you soon.

Frank Schlawmeyer
Sr. Executive Recruiter
B.O. Associates

Looks like a pretty standard recruiter email. But wait, there’s more! I’m so glad I scrolled down because I found this gem:

From: Otto Oberkuchen
To: Frank Schlawmeyer
Subject: calls?

Frank,

You have 2 submittals that were done yesterday yet only 5 calls so far today. It is extremely hard for me to manage the others and push them on calls when they see a senior person like yourself getting away with that. It would be different if you were making tons of placements but without that it makes it tough. Any thought on how I should reply when they ask that? Don’t make it a topic of discussion out there either. This is going to be a year of “No Excuses And All Successes”!! Thanks

Otto Oberkuchen
Partner
B.O. Associates

Ouch. “No Excuses and All Successes” sounds like an incredibly understanding and flexible management methodology to me. I’m sure it’s working out great for them, especially since this isn’t the first time I’ve had an interesting encounter with this particular agency. I’ll be sure to add this company to my list of places I hope to work someday, right after an apiary. (And if you know me, you know I will run away if I even think there’s a stinging insect nearby.)

Sadly, this also isn’t the first time I’ve received emails I shouldn’t have seen. A little proofreading could have gone a long way here – it takes only a few seconds to give an email a once-over before sending it. I’ve worked that step into my routine to help avoid situations just like this one.

Help Frank Out

I really do feel for Frank, and he did say “If you are not interested, please feel free to forward this information on to anyone that you feel may be a fit.” So, if you’re interested in this position, drop me a line and I’ll be happy to get you in touch with Frank.

Maybe he can fill this position and have one more success (and one less excuse) to take back to Otto!

Jan 222014
 

With over 40 weeks to go, PASS Summit 2014 may seem like the distant future, however the planning phase is already well underway. An event like the Summit is only possible because of the efforts of hundreds of volunteers, and opportunities to help this year are already starting to pop up.

One such opportunity is the Program Committee. It’s a wonderful way to help out, even if you can’t attend PASS Summit. That’s right, you can be part of the Program Committee without ever leaving the comfort of your home office, balcony, back porch, or wherever else you can get an internet connection.

What does the Program Committee do? In short, they determine the program! The committee is made up of a few different teams:

The Speaker/Abstract Review Team reviews and rates every speaker and abstract submission. Based on these rankings, the session lineup is determined.

Once the lineup is set, the PowerPoint Review Team goes over the selected abstracts for things like grammar before they are published on the website and printed in the guide. They also review the final drafts of PowerPoint slide decks before being presented at the Summit.

Finally, there’s a Special Projects team that helps out year-round with things like testing and reviewing the software used by the review teams.

Is it fun? I’ve been fortunate enough to be chosen for this committee the past 3 years, and it’s been an excellent experience. It’s a wonderful way to volunteer and help be a part of such a large event that so many people enjoy. It’s also a great way to meet new people: I’ve met someone new each year through program committee.

How much time does it take? I’m not going to lie, it takes time. We’re all busy people, so it’s not about having time, it’s about making time. In the case of speaker/abstract and PowerPoint review, you’ll want to pace yourself so that it takes a few hours per day over a few weeks because there’s no way you can do it all in one sitting. The number of abstracts submitted is in the hundreds, and the speaker/abstract review team reads and ranks all of them. The past 2 years I’ve put in probably 10-12 hours per week for 2-3 weeks doing abstract reviews.

What is reviewing like? Reviewing is tough – there are many abstracts of very high quality, and there’s no way the schedule can accommodate them all. Being on this committee gave me a real appreciation for how many abstracts are submitted and how good they all are.

But I’ve never done anything like this before! I hadn’t either, but there’s a first time for everything, right? It’s important to remember that you won’t be doing this on your own – you are part of a team with plenty of others who are ready and willing to offer support and advice.

So I can join the committee give my abstracts the highest rating possible, right? Now that wouldn’t be very fair. You most definitely can submit abstracts and be on the Program Committee, however you cannot review abstracts for tracks that you have submitted for.

Sound good? If the PASS Summit Program Committee sounds like something you’d be interested in, apply today! Applications are being accepted until 9:00pm PST, Wednesday 5 February 2014.