The Daily Dose

Using Common Table Expressions to Improve Query Performance

By Jeffry Schwartz | Advice , Intermediate , Performance Tuning , SQL Development , SQL Server

-by Jeffry Schwartz Queries with multiple join clauses create particularly difficult issues when they perform poorly. Join ordering is extremely important because if a join creates a huge interim result set at the beginning of the query execution, SQL Server must continue to handle this result set until it can be trimmed down with filtering […]

Continue reading

AlwaysOn Availability Groups – Enable or Disable Jobs on Failover

By Lori Brown | Helpful Scripts , Intermediate , SQL Administration , SQL Server

— By Lori Brown  @SQLSupahStah After reading up on AlwaysOn Monitoring and Alerting (http://blog.sqlrx.com/2015/08/27/alwayson-monitoring-and-alerting/), I was asked to come up with a way to automatically enable jobs that may only need to run on the primary replica of an availability group. Requirements were that on failover of an AlwaysOn Availability Group some jobs should be […]

Continue reading

Tip of the Month: Buffer Pool Extension

By Lori Brown | SQL Administration , SQL Server , Tip of the Month

— By Lori Brown  @SQLSupahStah Available in SQL Server 2014 Enterprise Edition and Standard Edition x64 only Buffer Pool Extension is a way to use solid state disks (SSD) as nonvolatile RAM (NvRAM) instead of memory. It reduces latency. It stores clean or committed pages only so there is no risk of data loss and […]

Continue reading

Troubleshooting SQL Server CPU

By Ginger Daniel | Advice , Miscellaneous , Perfmon , SQL Administration , SQL Server

–by Ginger Keys Recently, a client of mine contacted me in a panic because CPU was running at 99-100% over an extended period of time, which was affecting everything on the machine. We’ve all seen it at one time or another. And there are several reasons this could be happening. It could be SQL consuming […]

Continue reading

New or Changed Table Alert with DDL Triggers

By Lori Brown | Helpful Scripts , SQL Administration , SQL Server

— By Lori Brown  @SQLSupahStah If you are the usual DBA, you are often the last person to know when something new has been added to the databases you are managing. After finding that some people in an organization were abusing their access to a certain database, I decided to create a TSQL DDL trigger […]

Continue reading

SQL Cluster Failover or Restart Notification

By Lori Brown | Helpful Scripts , SQL Administration , SQL Server

— by Lori Brown @SQLSupahStah Since I manage several SQL clusters, I came up with a way to get a notification that the cluster may have failed over. This failover notification was created in self-defense because often times no one from the system administrators team will tell the DBA when work is being done or […]

Continue reading

Tip of the Month: WAIT_AT_LOW_PRIORITY option for Index Rebuilds

By Lori Brown | SQL Administration , SQL Maintenance , SQL Server , Tip of the Month

— By Lori Brown  @SQLSupahStah Available in SQL Server 2014 Enterprise Edition since can only be done using ONLINE rebuilds. Index rebuilds have been enhanced with the WAIT_AT_LOW_PRIORITY option. It is used to set the duration in minutes to wait as well as behavior when blocking happens. When setting WAIT_AT_LOW_PRIORITY you also must set MAX_DURATION […]

Continue reading

AlwaysOn – Monitoring and Alerting

By Ginger Daniel | Helpful Scripts , SQL Administration , SQL Server

–by Ginger Keys AlwaysOn Availability Groups is a great new feature of SQL 2012 and 2014. This feature is a combination of database mirroring and failover clustering, and it provides a strong level of high availability and disaster recovery; however there is no straightforward monitoring/alerting processes to go along with this new functionality. It is […]

Continue reading

The Pros & Cons of Hyperthreading

By Jeffry Schwartz | Advice , Miscellaneous , SQL Administration , Windows

— By Jeff Schwartz Hyperthreading is one of those features that most IT personnel have heard about, but many do not understand exactly what it does or why it may improve or degrade the performance of their servers. Although hyperthreaded processors have been available for many years, the confusion surrounding it is as commonplace today […]

Continue reading