pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

Friday, August 28, 2015

SQL Saturday Baton Rouge 2015 Recap

SQL Saturday Baton Rouge 2015 was our 7th annual event, but we're still learning lessons. Here's my official Planning Committee Chair's recap of the event.


Here's one of the big new things we did this year:
  • We have a large, four-building facility for our event with multiple entry points. We have one registration desk, sure, but we're also onsite at a major university's campus, so we get a lot of walk-ups. So it's hard to use anything in the SpeedPASS to count our folks. So this year we got these inexpensive, thin tyvek wristbands, similar to ones they would put on your wrist at a bar, concert or event. We ordered from wristbandexpress.com.
    • To give folks incentive to get themselves a wristband (and therefore get counted), we told everyone we're going to use the five-digit number on the wristbands as a raffle ticket as well for the big end-of-day giveaway.
    • Unfortunately, the wristbands come in packs of 500, and the labeled numbers for each pack are NOT contiguous OR guaranteed to be unique. So we ordered four packs of wristbands in two colors as we expected around 600 people. If we get overlapping wristband numbers, we'd use different colors. If we somehow got contiguous numbers, we'd use those two packs.
    • We gave away the wristbands at the registration table, and had folks (including myself) grabbing a sheet of 10 wristbands and walking around to hunt for folks who might not be wearing one. We scoured the lunch line to make sure people got one, I think we did a good job. At the raffle at the end of the day, we asked if anyone in attendance didn't have a wristband, and maybe a handful didn't.
    • Turns out, we got unique numbers but not contiguous. Our plans was to give out the first 500, then start pulling from the second pack. Our count at the end of day was 586. We had given out all 500 from the first pack, 86 from the second range of numbers.
  • After years of meeting for lunch, we had most of our planning committee meetings this year online via Skype for Business. We had better attendance, more frequent meetings, and better note-taking.
  • We splurged on a vegetarian lunch option from Zoe's Kitchen, a vegetarian salad and roasted veggies, and it was a big hit even with omnivores.
  • We tried our best to use the new PASS volunteer portal, but it had limitations, so our gallant volunteer coordinator Adrian Aucoin had to revert to manual, excel-based scheduling of volunteers (for multiple volunteer timeslots/jobs) and email communication. Already working with PASS to get that promising website improved for 2016.
  • Mesh construction vests instead of volunteer tee shirts was a big hit. 
    • Easier for volunteers to throw on - without having to layer or change shirts.
    • Lightweight and very highly visible
    • Some might enjoy wearing them as night-time running gear!

Some of the new ideas that we took in from our Post-Mortem Meeting and an online document shared with all planning committee members and volunteers:
  • Leave badge holders out of the bags, hand them at registration. We noticed a lot of people digging in their bags for their badge holders, clogging up the registration area. The registration area must be a high-velocity traffic area for us since we have so many attendees.
  • We didn't realize this until the night of the bag packing event, but for the first time, not a single sponsor provided labeled pens or notepads for the conference bags. Wow! Next year, we need to make sure someone provides pens, and instead of notepads, we'll add a couple extra blank pages to the letter-paper-sized conference booklet. (We need to make sure the conference booklet is therefore NOT printed on glossy paper.)
  • Instead of plastic bags, perhaps we should encourage a sponsor to provide more durable, more re-usable, more eco-conscious fabric grocery store bags? We will work together to get the bags printed with both the SQLSat and sponsor logo.
  • During the raffle, invite any leaders of any user groups to come up and grab some free swag. Tell folks if they want these prizes, they gotta go to the meetings!
  • Assign volunteers to time slots of garbage duty. We got overwhelmed with garbage during lunch.
  • Assign volunteers to specifically refill the ice chests with drinks. We ran out during lunch.
  • After years of doing lunch sessions, last year we were told by our host LSU that no food/drink were allowed in the classrooms. Many of our attendees don't know this yet. We need more NO FOOD/DRINK SIGNS, probably in each classroom.
  • Put that message and more on an informational post-it poster in each classroom.
  • Assign specific volunteers to stay after the event to clean up, move tables, haul trash and pack our stuff. We need at least 20 people for this, 10 was not enough this year. 
  • Buy dry-erase markers and erasers and put them in each classroom. It’ll be a small donation to LSU since apparently they can’t afford dry erase markers (or the professors hoard them selfishly).
  • The volunteer coordinator should stay put and in an obvious place to greet volunteers and give out assignments. He/she should stay put in Volunteer HQ so that our 80+ volunteers are never in doubt as to how they can help.
  • Speakers should be given a slide to add to their slidedecks that includes instructions for how attendees can fill out the online speaker evaluations. The word didn't get out enough to give speakers feedback online.
  • Do a better job of reaching out to local university departments, community colleges, social media, and newspapers and event calendars.
  • A Monty Python-themed SQL Saturday would be AMAZING. But where would we find enough shrubberies?

Sunday, August 23, 2015

Actual Emails: Allow NUNS to Lead You to Good Clustered Index Design

Wrote this email exchange with a colleague who wanted to confirm that the client-proposed design for a table was... less than optimal.


Subject: clustered index
From: A. Developer

Hey William,
I believe the client created this Clustered Index... <horrifying screenshot of a clustered index with many large nvarchar columns as the key>

From: William 

The clustered index is ideally 
1)      Non-changing
2)      Unique
3)      Narrow
4)      Sequential 
“NUNS” 
Having those multiple nvarchar columns in the clustered index is probably not a good idea, as it violates #3 and probably #1 and #4 too.  
The most ideal clustered index is on an integer identity column. You can modify the design of a table to add one of those. 
Look at the data and suggest a new clustered index. That five-column clustered index might be a perfectly fine nonclustered index, but is an inefficient clustered key.

From: A. Developer
That makes sense. Thanks William!

Thursday, August 20, 2015

Your Devs' Questions Answered With sys.dm_server_services

It starts with "I'm a developer...

"... with no RDP access, how can I tell if the SQL Server Agent service is running?"
"... and I don't think CDC is working. Is the SQL Agent on?"
"... how can I tell how long has the SQL Server been running? Did somebody reboot it last night?"
"... so hey I need to know if the SQL Server service set to automatic startup."

Here's an easy script that anyone can use, it can be especially helpful for developers without RDP access to the Windows Server that is hosting the SQL Server service. It'll answer these developer questions and more, in addition to a lot of other utility for you as a DBA.
SELECT  servicename -- Ex: SQL Server (SQL2K8R2)
, startup_type_desc -- Manual, Automatic
,  status_desc -- Running, Stopped, etc.
,  process_id
,  last_startup_time -- datetime
,  service_account
,  filename
,  is_clustered -- Y/N
,  cluster_nodename
FROM   sys.dm_server_services

You don't need to be a member of the sysadmin server role to run this, but you do need VIEW SERVER STATE. Developers can be given this permission, even in production, as it gives them access to many key dynamic management views (DMVs), including DMV's for diagnostics and performance tuning.

MSDN reference: https://technet.microsoft.com/en-us/library/Hh204542(v=SQL.110).aspx