SQL Terms of Art — Pronouncing them correctly

There is English.  There is also geek-speak.  Then there is DBA-Speak.  Then, for the truly advanced among us, there is SQL Server DBA-speak.

However, if we are to sound like professional DBAs and not newbies, we must pronounce our terms of art correctly.  Failure to do so can result in: (a) Losing the chance to speak at SQL Pass Summit; (b) Being uninvited from the cool vendor parties at the Summit; (c) Generally being ostracized by the SQL DBA community; and (d) Death.

So, here are some terms that are often mispronounced, along with the canonical ways to pronounce them:

SQL:  Spell the letters, lose your job.  It is pronounced SEQUEL.

SQL Versions:  Never insert the word “server!”  It’s “SEQUEL TWENTY FOURTEEN” and not “S Q L Server Two Thousand Fourteen.”

The Data Type CHAR:  Is it pronounced CHARR, like the delicious blackened coating on a delicious piece of steak served at a vendor party at SQL Pass Summit? Or is it pronounced CARE, like the first symbol of “character?”  The latter pronunciation is a capital offense.

The Data Type VARCHAR:  This is more complex, because we have multiple pronunciations:

  • V-ARE CHARR:  the Rhyme-like pronunciation that shows that you are a mighty DBA and not some poseur.
  • V-AIR CHARR: Welcome to poseur world.
  • V-ARE CARE: Capital Offense
  • V-AIR CARE: Double Capital offense.  They execute you, wake you up, and do it again.  Sort of like the end of Braveheart.

SARGABLE:  Short for “search argument able.”  This refers to the good situation where a WHERE, HAVING or ON clause doesn’t do silly things like wrap the searching column in a function to force a scan.  It lets SQL use indexes.  Is the word pronounced SARGE-able, sounding like a short name for a Marine Drill Instructor?  Or is it S-ARGH-able, with the hard G?  Finally, could it be S-AAAAARGH-able, as if the DBA is a pirate?

In this case, the first or the third are equally cool.  However, a DBA may only use the third version at SQL Pass Summit, and only when presenting, and only with a parrot perched on his or her shoulder. Use the second version only if your résumé (and you must type the word with the accented “e” in both places) is up to date.

Standardization is important.  This standard will be updated as new terms of art arise.  Feel free to contribute your own goodies.

Latch Waits. What they mean, and when are they bad?

Recently our shop migrated to the new Version 11 of Dell’s Spotlight on SQL Server Enterprise, our monitoring tool.  Immediately we began seeing alerts that read like this:

The latch wait time rate is 7,500 ms per second.

Sounds ominous!  You are waiting 7.5 seconds every second to get a needed latch?!  Why is this happening to my servers?  They seem alright, because queries are moving along nicely and the users are happy.  However, this bad statistic must mean that there is a massive latent problem that this new default alert in the monitoring tool caught.

Research time!

What is a latch wait?

Well, first, we should define a latch.  A latch is a type of lock that protects access to SQL Server’s structures in memory like the buffer pool or SQL Server’s internal data structures.  They’re vitally necessary to keep integrity on in memory data pages.

Latches are serialized.  One latch completes work before the next latch is granted.  If a process needs a latch and one cannot be obtained, it is suspended until the latch becomes available.

Now, with that description, the latch wait seems even more ominous!  Waiting 7.5 milliseconds for each latch of memory?  Yipes!  Anything more than a microsecond’s wait is an eternity – correct?

Well, let’s use a DMV – sys.dm_os_latch_stats to see what’s going on:

latch_stats

I looked at the total wait time quickly, and see that ACCESS_METHODS_DATASET_PARENT is the predominant latch wait on this server.  Paul Randal wrote a wonderful post that explains many of these latch waits.  This particular wait is created by parallelism.  As Paul warns in his post, let’s not go bananas and set MAXDOP to 1.

Instead, the first thing to check is whether hyperthreading was accidentally enabled on this server.  In this case, the answer appears to be no, but the sysadmins need to check this (you can’t do it from SQL but there is a VB Script out there that can detect it — if your sysadmins will let you run that script on a production server).

While the Sysadmins investigate, I note that the second most prevalent class is BUFFER.  This is the classic wait for an in memory page or structure.  This one seems to be one that I can live with, especially since ACCESS_METHODS_DATASET_PARENT is responsible for over 90% of the wait time and also the highest max wait times.

As Paul’s article tells us, the NESTING_TRANSACTION_READONLY and NESTING_TRANSACTION_FULL latch classes are related to (duh) nested transactions.  Parallel nested transactions.

What My problem was

In my case, it seems that parallelism is the cause.  Hyperthreading is not, in this case, the problem.  SSIS also runs packages on this server to do ETL work.  We have four sockets with two cores each.  What I did to resolve this issue was to configure max degree of parallelism to 4, allowing four cores to be free to handle the SSIS packages and other SQL traffic.

We are seeing an incremental improvement in query performance on this server.  What was good is now a little better.  The alert hasn’t showed up since we changed configuration.

Does the new alert help?

Short answer:  yes.  However, this alert needs to be tuned server-by-server in Spotlight, and may need to be ignored for certain test servers.

I will take this opportunity to strongly encourage every DBA who manages more than five SQL Servers to review all of the available monitoring products out there.  I like Spotlight.  You might like SQL Sentry or Idera or RedGate.  Find the one that works best with your style of DB management and implement it.  Be ready to tune the thing for 30-120 days.

Spotlight did me right with the latest alert in v11.

How SQL AD Group Security promotes Clean DBA Living.

We know that we can improve auditability and simply database security administration by doignt he following:

  1. Eliminate SQL Server authentication in favor of integrated security; and
  2. Create AD Groups, and set those groups up as logins and database users, with specific permissions granted and or denied as the business requires; and
  3. Assigning AD users to those groups.

But why the extra level of AD Groups?  Why not just add the AD users as database logins with specific permissions at the more granular user level?

We could certainly do that, but the administrative tasks that come from this approach grow geometrically.

AD Groups reduce SQL Login maintenance.

Sheer Overhead

Let’s assume that you have 100 AD users who can access 4 databases on 2 SQL servers each.  That is the size of a very large workgroup or a small enterprise.  Without a grouping approach maintained at the AD level, you have to create 200 logins, 400 database users, and assign roles or (worse) individual permissions to each of these users.  That is a boat load of maintenance work.

On the other hand, grouping means that the user’s DB access is provisioned with his or her AD profile, and DBA has no involvement aside from a one-time provisioning of the access to the DBA group.

DBA resources are freed to work on more pressing issues.

Orphan Prevention

Assume for argument that a person is terminated and his or her AD account is deleted.  That does nothing to delete the SQL Server logins or database users.  You now have an orphaned login, and if you drop the login you have orphaned database users.

While it is true that you can write or download T-SQL scripts that can check AD for missing logins, identify orphans, and sweep the databases to drop users and then logins, this is maintenance work that requires attendance and usually automation in the form of SQL jobs that run regularly.

On the other hand, if the users are organized into AD Groups and one of the logins is deleted, there is no SQL Server maintenance at all.

Auditability Redux

Assume that Jane Doe is promoted from Payroll assistant to sales manager.  Jane no longer needs access to the payroll tables, meaning that her roles must be individually maintained by DBA, separately and in addition to any maintenance of her profile in Active Directory.  Worse, if the roles don’t cover everything and there are individual GRANT commands to tables and other entities that need to be revoked, Jane’s simple change results in significant script work for the DBA.

On the other hand, if you have an AD Group DOMAINPayrollWorker with all permissions set there, then the network admin who provisions Jane’s profile need only drop her from the group and her permissions change instantly.

That kind of control over access is the kind of stuff that gives auditors cause for celebration.

Conclusion

I’ve previously discussed the security benefits of eliminating SQL Server authentication.  Combined with the approach of Active Directory group and role based authentication, the security benefit also comes with a savings in DBA time.

Security with Auditability.

IF you are a DBA in any of the following:

  • A publicly-traded company
  • A company that has to comply with HIPAA or PCI
  • A company with DBA that hate disorder

…then you need to have an audit-friendly method of securing your SQL assets.  In general, a few rules make this easier on DBA and on management.

1. Get rid of SQL Server authentication!

The sooner the better.  If you do not have a sa login, it is really hard for a third party to login as sa.  And even if you have disabled sa  in favor of another sysadmin-powered login with a name like “janitor_readonly,” if that login is hacked then your data is in the open.

On the other hand, in the modern era, there is little to no need for SQL Server authentication at all.  With SQL 2008 and newer, the DBA actually has to opt in to using SQL Server authentication.  Your database servers should exclusively use integrated security.

Why?  First of all, every login is mapped to the AD user that was running the program that logged in.  Server applications have their own logins, and the clients are logged in with the end-user’s login.

2. Groups and Roles

The first thing you might think is something like, “I have 10,000 people logging into application X!”  I’ll be spending every waking minute maintaining SQL logins and permissions to the databases they need.

Nope.  Create Active Directory groups.  Assign them logins and database users with the minimum permissions that are needed. Examples:

  • DOMAINAccounting_RO – Read only to Accounting
  • DOMAINAccounting_RW – Read/Write to Accounting
  • DOMAINAccounting_Payroll – Read/Write Payroll Data
  • DOMAINSQL Admins – DBA with sysadmin privileges

Usually each database has a limited set of privilege levels.  in my shop, we have a limited set of groups and we use T-SQL to generate DSADD commands to add the group accounts to Active Directory, and pass these on to the sysadmins to be added (no DBA should be a Domain Admin – would you trust any DBA with that much power?  😀 ).

Each domain user is added to one or the other domain groups (or multiple groups; the permissions accumulate unless an explicit DENY is issued).

Use database roles to dereference objects further.  You then GRANT permissions to the database role, and then assign the database user for the AD Group.

Then when the desktop/sysadmins provision a user, they are added to the groups using ADUC (Active Directory Users and Computers), and the database users and permissions are defined once at the outset, and only need minor review as DDL changes are made.

User DOMAINCFO would be added to DOMAINAccounting_RW and DOMAINAccountingPayroll.  Then he or she would have the needed permissions at the outset.

3. Auditing Made Easy

If you enable successful logins to be saved to the database log, you will see that you will see the actual AD user that was logged in, along with the machine from which the login was made.  This information is also shown in sp_who2 and elsewhere in good monitoring tools.

Auditing tools now have access as well.  Now, if someone went in and ran a query like this:

update EmployeeSalary set SalaryRate = SalaryRate * 2 where EmployeeType = 'Janitor';

…any good auditing tool would capture the user ID and workstation name / IP of the culprit.  If someone did the same query with the sa login, you would have a lot less information on the culprit than would be advisable.

In short, it’s high time that SQL Server Authentication join the relics of Information Technology in the museum, right next to the IBM System/370-158.

Missing Index Suggestions — Discretion Required!

If you are in Management Studio and have seen a suggested index, or if you have used a resource such as Pinal Dave’s Missing Indexes Script, you will be tempted to apply the suggested index without exercising discretion in doing so.

Please, be careful!  I have seen a table with 10GB of data pages and 50GB+ of index pages.  This is a terrifying thing for write performance, and often, a little bit of analysis is in order.

A common anomaly is to see multiple indexes with identical sort columns  and different groups of include columns.  Fixing these into a single covering index is a low-hanging fruit to pick.  Simply delete the duplicate indexes and replace with one that has all necessary included columns — most of the time.

What if all of these grouping columns add up to most or all of the table columns?  You are then keeping a second copy of the table in the nonclustered index and are causing numerous extra write I/O operations.

In that case, the best bet is to eliminate all included columns and just keep the sort columns as one index — most of the time.  You may have an outlier query that requires included columns to evade a performance bottleneck.  How do you determine this? The best way is with the DMV included in SQL Server. Analyze the missing indexes based on estimated impact of index change, multiplied by the number of executions that asked for this missing index.  Look at the highest values of the product.

Pinal Dave’s script calculates this number.  Again, however, you have to analyze each recommendation. Just throwing the indexes at the database may solve a read issue, and create a write or disk space issue.

We are DBA. We thrive on using the simplest solution and moving on.  However, in many cases, we must do a little digging to determine what solution is truly the simplest.

Happy New Year!

I wish you all the happiest 2015.

May your queries never be accidentally Cartesian.

May your DBCC CHECKDB always come back without inconsistencies or errors.

May none of your databases ever be marked suspect.

May both your budget and your salary increase.

May the next packaged product that your company installs come with a database that is properly normalized, with the correct indexes, and following all best practices.

May your monitoring tools never generate false positives.

May your new SAN contain nothing but solid state drives.

Have a wonderful new year!