SQLskills SQL101: Sequential Throughput

By: Glenn Berry
Posted on: April 30, 2017 4:02 pm

Over the past couple of months, SQLskills has embarked on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

The Importance of Sequential Throughput for SQL Server

A number of very common, important operations that are often executed by SQL Server are potentially performance limited by the sequential throughput of the underlying storage subsystem. These include:

  1. Full database backups and restores
  2. Index creation and maintenance work
  3. Initializing transactional replication snapshots and subscriptions
  4. Initializing AlwaysOn AG replicas
  5. Initializing database mirrors
  6. Initializing log-shipping secondary’s
  7. Running DBCC CHECKDB
  8. Relational data warehouse query workloads
  9. Relational data warehouse ETL operations

Despite this, I often see DBAs having to contend with extremely low sequential performance on their various database servers, to the detriment of their ability to meet their SLAs for things like RPO and RTO (not to mention their sanity). This being the case, what if anything can you do to improve this situation?

One thing you should do is to do some storage subsystem benchmarking with tools like CrystalDiskMark and Microsoft DiskSpd, to find out what the potential performance of each logical drive is on the underlying machine where your SQL Server instance is running.

You can also run some simple queries and tests from SQL Server itself to see what level of sequential performance you are actually getting from your storage subsystem (which is much harder for storage administrators, SAN administrators, and storage vendors to dispute). One example is running a full database backup to a NUL device, to see the ultimate sequential read performance from where your data and log files are located. Another example is running a SELECT query with an index hint to force the query to do a clustered index scan or table scan from a relatively large table.

Note: You should do these kinds of tests during a maintenance window or ideally, before a new instance of SQL Server goes into Production. Otherwise, your testing could negatively affect your Production environment or the other Production activity could skew your test results.

Beyond that, here are some general steps you can take to improve overall storage system performance:

  1. Make sure you have power management configured correctly at all levels (BIOS power management, hypervisor power policy, and Windows Power Plan)
  2. Make sure you have Windows Instant File Initialization enabled
  3. Make sure you are not under memory pressure (to reduce stress on your storage subsystem)
  4. Make sure you are using the latest version of SQL Server
  5. Make sure you have installed the latest Service Pack and Cumulative Update for your version of SQL Server
  6. Favor Enterprise Edition or Standard Edition (because it has better I/O performance)
  7. Use compression to reduce your I/O requirements (backup compression, data compression, and columnstore indexes
  8. Make sure your indexes are tuned appropriately (not too many and not too few)
  9. Keep your index fragmentation under control

You can watch my Pluralsight course SQL Server: Improving Storage Subsystem Performance to get more details about this subject. You can also read my article on SQLPerformance.com, Sequential Throughput Speeds and Feeds to get some more technical details about sequential throughput.

Posted in: Hardware, SQL101, Storage
2 Comments

SQLskills SQL101: Using DDL Triggers

By: Glenn Berry
Posted on: April 20, 2017 12:16 pm

Over the past couple of months, SQLskills has embarked on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

DDL Triggers

One very useful safety feature that was added to the product with the release of SQL Server 2005 is Data Definition Language (DDL) triggers. Even though they have been available for quite some time, I still don’t see that many people actually using them on their systems, which I think is a shame.

DDL triggers are described by the online documentation like this:

DDL triggers fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS.

Basically, when a T-SQL command does something that affects the metadata or schema of your database, you can capture and log some useful information about what was changed, what the change was, when it was changed, and who did it. Depending on how you configure the DDL trigger, you can capture things that actually are metadata changes (such as CREATE, ALTER or DROP) for things like tables, views, stored procedures, functions, and indexes. You can also capture things that I don’t really consider true metadata changes, such as an ALTER INDEX REORGANIZE command.

DDL Trigger Actions

Just like with a DML Trigger, you have to decide what happens when a DDL Trigger fires. For example, one action that Microsoft likes to use in their documentation is to simply have a ROLLBACK command, along with an error message that indicates what happened. This is designed to prevent someone (perhaps you) from accidentally making a terrible mistake such as dropping a table from a Production database.

DDL Trigger Usage

Another common usage is to simply log relevant information about all DDL changes that you decide to capture to a table that you create in each database. This can be very useful when multiple people have admin rights in your Production databases. Even if that is not the case, having a record of all DDL changes to a database can be very helpful.

Another use for DDL Triggers is to capture what is happening with your index maintenance. DDL commands such as ALTER INDEX REORGANIZE and ALTER INDEX REBUILD can be logged to help you analyze your index maintenance. For example, if you see the same index being reorganized or rebuilt on a frequent, regular basis, you might want to consider lowering the fill factor on that index to reduce how quickly it becomes fragmented, which will decrease how often it needs to be reorganized or rebuilt.

Conclusion

DDL Triggers can be very useful, and they are very easy to use for a number of different purposes. They are not as secure or as powerful as SQL Server Audit, but they are available in all editions of SQL Server, starting with SQL Server 2005. They are also much easier to set-up and use. I have an example of how to create a table to log some DDL changes, along with an actual DDL trigger to capture the changes, available here.

 

 

Posted in: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL101
3 Comments

SQL Server 2014 SP2 Cumulative Update 5

By: Glenn Berry
Posted on: April 18, 2017 7:28 am

Microsoft has released SQL Server 2014 Service Pack 2 Cumulative Update 5, which is Build 12.0.5546.0. There are 24 hotfixes in the public fix list. In my opinion, you should be on the SP2 branch by now. If you have not made that move, you should be making plans to get on SP2 as soon as possible. My blog post below highlights some of the more important fixes in all of the post-SP2 Cumulative Updates:

Performance and Stability Related Fixes in Post-SQL Server 2014 SP2 Builds

Microsoft has also released SQL Server 2014 Service Pack 1 Cumulative Update 12, which is Build 12.0.4502.0. There are 12 hotfixes in the public fix list for this CU. My blog post below highlights some of the more important fixes in all of the post-SP1 Cumulative Updates:

Performance and Stability Related Fixes in Post-SQL Server 2014 SP1 Builds

There is no corresponding CU for the RTM branch, since SQL Server 2014 RTM is no longer a supported Service Pack level. If you are still on the RTM branch (which is not a good thing), then my blog post below highlights some of the more important fixes in all of the RTM Cumulative Updates:

Performance and Stability Related Fixes in Post-SQL Server 2014 RTM Builds

Finally, if you are wondering why this matters, you might want to read my SQLskills SQL101: SQL Server Maintenance post to find out more about how SQL Server is serviced by Microsoft and why staying current is important.

 

 

Posted in: Cumulative Updates, SQL Server 2014
Be the first to Reply!