Press "Enter" to skip to content

Category: Administration

Maintaining SSISDB

John McCormack was in a jam:

I made 2 unsuccessful attempts at running the SSIS Server Maintenance Job. However, after several hours of processing and still no available free space in the database, I knew the job wasn’t coping with the sheer number of rows it had to delete. The deletes all happen from the parent table (internal.operations) and then all child tables using using cascading deletes. This approach maintains referential integrity but is not great for performance.

Due to this, I needed a new approach to the maintenance of SSISDB. As we hadn’t maintained these tables for 13/14 months, I was asking too much of SQL Server to let me delete everything at once. 

Read on for the solution.

Comments closed

Snapshot Isolation

Gerald Britton takes us through snapshot isolation in SQL Server:

Snapshot isolation avoids most locking and blocking by using row versioning. When data is modified, the committed versions of affected rows are copied to tempdb and given version numbers. This operation is called copy on write and is used for all inserts, updates and deletes using this technique. When another session reads the same data, the committed version of the data as of the time the reading transaction began is returned.

By avoiding most locking, this approach can greatly increase concurrency at a lower cost than transactional isolation. Of course, “There ain’t no such thing as a free lunch!” and snapshot isolation has a hidden cost: increased usage of tempdb.

Gerald covers both varieties, Read Committed Snapshot Isolation and proper Snapshot Isolation. RCSI is definitely worth understanding in almost any environment, and even Snapshot Isolation has its uses.

Comments closed

Calculating Memory Consumption by Object

Max Vernon has a script to help you figure out which objects are consuming the most buffer pool space:

SQL Server caches object data in memory in the buffer pool. Understanding memory consumption by object can be crucial for performance. For instance, you may have a large logging table consuming 90% of the buffer pool. Moving older rows out of the logging table might allow you to reduce memory consumption if you really only care about the last 2 weeks of log records. The script below takes a snapshot of sys.dm_os_buffer_descriptors, then links it to the objects in each database on the instance. The output shows which objects are in memory, along with how much memory is being consumed by each object.

Click through for the script.

Comments closed

Distributed Transactions on Linux

Tejas Shah and crew announce distributed transactions with SQL Server on Linux:

With SQL Server 2017, a new era was heralded with SQL server being available to deploy on Linux (and Linux based container) systems. While all functionality of the SQL Server engine were brought over as is to SQL Server on Linux, some of the functionality which depended on Windows system processes such as distributed transactions (which relies on MSDTC service) were not brought over immediately.

Well, now your wait is over.

Comments closed

SQL Assessment API

Ebru Ersan announces a public preview of the SQL Assessment API:

SQL Assessment API is a new mechanism to evaluate configuration of your SQL Server for best practices. The API methods are used by means of a SQL Server Management Object (SMO) extension and new cmdlets in SqlServer PowerShell module. API is delivered with a ruleset that is highly customizable and extensible. It can be used to assess SQL Server versions 2012 and higher, both on Windows and Linux.

Looks like you can customize rules as well. I wonder if it will work better (or have more support) than Policy-Based Management. I’m also clocking how many minutes before dbatools supports this…

1 Comment

Gaps in Identity Columns

Josh Simar doesn’t like gaps in identity columns brought about by rollbacks:

At the end of this you can see that those records were in the table before the rollback as evidenced by the now 2000 records in the table. However we ran our identity checker after the rollback and we’ll see some interesting results because of that.

Checking identity information: current identity value '2000', current column value '1000'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The rollback did absolutely nothing for our identity and left it as is.

The short answer is that this is expected and reasonable behavior. Don’t expect identity integers to be sequential; the best you’ll get (assuming no resets or overflows) is a guarantee that they’ll be monotonically increasing. I left the long answer as a comment, currently awaiting moderation.

Comments closed

Undocumented Commands Can Change

Thomas Rushton warns us that undocumented commands in SQL Server are liable to change without notice:

I don’t have every version available to test, but it does appear that the record indicating Containment State is mis-spelled in SQL Server 2012 as “dbi_ContianmentState”, and then corrected in SQL 2014 and later. It’s a good job I’m not relying on it for anything.

So, yes, don’t rely on undocumented functions – as they may change without notice.

If there’s a documented method, use that one. If the only method available is undocumented, you can still use it, but be sure to test it with each release—that is, major release, service pack, or cumulative update.

Comments closed

Automated Alert Emails

Max Vernon shows how you can use the SQL Server Agent to send automated e-mails on alerts:

SQL Server Agent provides a great mechanism for sending alerts via email in response to various events. Once you’ve setup Database Mail, and configured a SQL Server Agent Operator, you should add alerts for severe errors that affect the health of your SQL Server. Creating Alerts can be tedious, but automating Alerts is simple, with the easy code below that automates creating alerts in response to critical events. Automating alerts is important because it provides a standardized Alert configuration that can be used by all the SQL Servers in your organization.

Read on for the script.

Comments closed

Why Transaction Logs are Zero-Initialized

Paul Randal explains why the transaction log needs to be zero-initialized before SQL Server starts up:

It’s all to do with crash recovery. SQL Server knows where crash recovery has to start for a database, but not where it ends – i.e. SQL Server does not persist the ‘most recent LSN’ for a database anywhere. This means it has to work out where the end of the log is (and by end, I mean the most recent log record persisted on disk, not the physical end of the log file).

Read on for the detailed explanation.

Comments closed

Azul Java in SQL Server 2019

Travis Wright announces support for Azul Systems’ Java distribution in SQL Server 2019:

In September 2018, Microsoft announced a new partnership with Azul Systems, a leading Java open source contributor and distributor. This partnership allows for all Azure customers to use Azul’s Zulu for Azure – Enterprise distribution of Java for free with support jointly provided by Microsoft and Azul. That’s right – supported for free.

Today, we are announcing that we have extended that partnership to cover SQL Server. Starting in the SQL Server 2019 community technology preview (CTP) 3.2 that was released today, we are including Azul System’s Zulu Embedded right out of the box for all scenarios where Java is used in SQL Server – in PolyBase, Apache Spark, Java extensibility, and more. There is no additional cost beyond what you pay for SQL Server.

This is interesting. We’ll have to see if the CTP 3.2 installation doesn’t ask for JDK 1.8 anymore and just installs the Azul Systems version.

Comments closed