The series so far:

1. The SQL Server Features that Time Forgot: Data Quality Services, Master Data Services, Policy-Based Management, Management Data Warehouse and Service Broker
2. More SQL Server Features that Time Forgot: Auto-shrink, Buffer-pool extension, Database Diagrams, Database Engine Tuning Advisor, and SQL CLR
3. Even more SQL Server Features that Time forgot: In-Memory OLTP, lightweight pooling, the sql_variant data type, stretch databases, transaction savepoints, and XML indexes
4. Still More SQL Server Features that Time Forgot: Active Directory Helper Service, Data Transformation Services, DBCC commands, English Query, Native XML Web Services, Northwind and pubs databases, Notification Services, SQL Mail, SQL Server Distributed Management Objects, Surface Area Configuration Tool, utilities, and Web Assistant

SQL Server is chock-full of features that support data management and facilitate data access. Most are respected both far and wide. Others are counted among the despised. A number of them fall into both camps, often among the same users, many of whom openly acknowledge their love/hate relationships. Few SQL Server features come out entirely unscathed.

In the first article in this series, we covered some of the more controversial of these features, such as Data Quality Services and Master Data Services, neither of which has a particularly large fan base. In this article, we continue the discussion with a look at another set of features, those that also have their critics and their devoted followers. Few issues are ever black-and-white, and SQL Server is no exception.

Autoshrink

As the name implies, autoshrink is a database option that controls whether the database engine automatically shrinks a database when it determines that shrinking is warranted. For each database in which autoshrink is enabled, SQL Server checks the database, shrinks it if needed, and then, after several minutes, checks the next database, working through them one at a time to avoid simultaneous operations.

The idea with autoshrink is, of course, that administrators have a simple way to automatically reclaim space in their databases, a worthy goal, especially if confronted by the potential for unavailable disk space. Certainly, novice DBAs will appreciate such simplicity. But as most SQL Server pros will tell you, this is a really bad idea, often causing more problems than it solves (if, in fact, autoshrink solves any problems at all).

Here’s the issue. Shrinking a database results in fragmented indexes. The more you shrink, the greater the fragmentation. If indexes become too fragmented, query performance suffers. As a result, if you shrink a database, you will likely need to rebuild its indexes. For this reason, some DBAs will schedule regular index rebuilds after enabling autoshrink, but this approach has its own downsides. Although this will clean up the indexes, it will also free up more space, which can cause autoshrink to kick in again, once more resulting in fragmentation and incurring all the performance hits that come with these processes and their aftermath.

In general, you should shrink a database only when it is essential to do so, and you should do it manually, then follow with an index rebuild. The autoshrink option should seldom be enabled, if at all. Even Microsoft recommends that the option be turned off, its use “carefully evaluated by a trained Database Administrator (DBA).” Many DBAs would suggest (in the strongest terms) that the option never be used, that in fact it be removed from SQL Server altogether, thus avoiding all those hits on performance and system resources and their mental wellbeing.

Buffer pool extension

Introduced in SQL Server 2014, the buffer pool extension feature provides a method for extending the database engine’s buffer pool beyond the available RAM. In this way, administrators can extend the buffer pool cache to nonvolatile storage, usually in the form of a high-performing sold-state drive, thus better controlling data paging, should the system hit its memory limits.

According to Microsoft, the buffer pool extension can help increase random I/O and transactional throughput, while reducing I/O latency. Unfortunately, when the feature was first introduced, it was available only to the Enterprise edition. Although this might have been useful in some cases, the buffer pool extension feature would have been far more noteworthy had it been included in the Standard edition, which limits the buffer pool to 128 GB.

Not surprisingly, the initial exclusion of the Standard edition put a lot of DBAs off, leaving the buffer pool extension with a fairly limited fan base. But Microsoft has since made the feature available to the Standard edition, making the extension a lot more useful to a lot more customers, especially those that need the extra boost in RAM power but do not want to invest in the exorbitant Enterprise licensing. Microsoft might still have some educating to do to get DBAs on board.

Those ready to take the plunge can size the buffer pool extension up to 32 times the available memory. Microsoft recommends a ratio of 1:16 or less between the physical memory and buffer pool extension and suggests that a “lower ratio in the range of 1:4 to 1:8 may be optimal.” Keep in mind, however, that the buffer pool extension is not a replacement for increasing the amount of available RAM, assuming you haven’t already reached the maximum. RAM is still going to give you the best bang for your buck. Also note that the buffer pool extension feature comes with a number of other limitations, too many to go into here.

Database diagrams

The database diagramming feature in SQL Server Management Studio (SSMS) is undoubtedly one of the quirkiest features in there, but under the right circumstances, it can be a fairly handy tool, even if somewhat annoying. You can access the feature from Object Explorer, through the object structure associated with a specific database. The implication of this, for better or worse, is that each diagram is tied to that database, or more precisely, to the objects contained within the database. You cannot simply create a diagram without a set of associated database objects.

For basic tasks, the diagramming tool is relatively simple to use, mostly point-and-click operations that step you though creating and modifying tables and their relationships. At times, the tool can be a bit clunky and less than intuitive, with archaic graphic components lifted from the Macintosh SE era, but it’s adequate enough for small and simple schemas. Anything larger, and the tool is quickly out of its league, unless you’re focusing on a subset of database objects. The tool includes only basic features, nothing like you would find in a true modeling tool. If you think small and simple, you’ll have an accurate picture of the diagramming capabilities.

The biggest challenge with the tool and its diagrams is their unwavering association with the underlying database objects, which can be a good or bad thing, depending on your needs. For example, suppose you have a basic database already in place and you want to show colleagues your suggestions for additional tables or relationships, using savable database diagrams. Unfortunately, you cannot simply update the diagram and save it without changing the database itself. Instead, you must create a schema-only copy of the database and create diagrams based on that copy. If you want to present several alternatives, you must create a database copy for each one.

In the end, you might find it easier to use one of the free online tools to create your diagrams, such as those described in an article I wrote last year: Five Online Database Modelling Services. Fortunately, there are plenty of tools that provide an alternative to the SSMS diagramming feature, some online, some available for download, each offering different levels of services, with prices ranging from free to the obscene. One I think that shows particular promise is the SQL Server Database Modeler, which happens to be free.

For very basic projects, the SSMS diagramming capabilities should be fine, as long as you can work within the tool’s constraints. For many, it might be all they want or need, at least in some cases, as Tony Davis points out in his article A Database to Diagram for. Even so, the SSMS tool is far from being a full-fledged modeling tool. But it’s free and readily available, and that’s worth something.

Database Engine Tuning Advisor

The Database Engine Tuning Advisor offers free advice on how to optimize your database to improve query performance. Based on its analysis, the advisor might suggest that you create indexes, indexed views, or table partitions—whatever it takes to improve the performance of your processing workloads.

To the SQL Server novice, such sage and ample advice must seem like manna from heaven, but like most free advice, the advisor’s recommendations should be approached with caution. Its myopic focus on indexes and partitions, as they pertain to query performance, fails to take into account the queries themselves, as well as the table structures that the queries target.

As any experienced DBA will tell you, throwing more indexes at a problem is seldom an effective solution and can, in fact, make performance worse, especially if the sample workloads are not fully representative or if the actual workloads evolve over time. Unfortunately, this is the approach taken by the tuning advisor.

But the advisor has a more serious problem. Rookie administrators are likely to turn to the tool because they lack the expertise to address performance issues on their own and are looking for some type of external help, whether from a tool or a more experienced individual. However, for the advisor to be of any benefit, the administrator must be able to provide a truly representative workload, determine from a list of recommendations which are the best solutions, put the selected recommendations through rigorous testing, implement the changes into production, and then monitor the results. Do these sound like the type of tasks that should be left to the neophyte DBA?

Some database pros have recommended that advisor might be beneficial under very specific circumstances, accompanied by the necessary expertise, but many would prefer to see the feature permanently disabled or removed., choosing instead to conduct a proper analysis, rather than implement a lot of poor advice. It’s not that the tuning advisor is a bad thing. It’s just that a lot of experienced DBAs refuse to trust it with their databases.

SQL CLR

Since its introduction, the common language runtime (CLR) integration into SQL Server has been met with mixed reviews. Some database pros swear by it. Others avoid it altogether, in part because of security concerns (whether justified or not) and in part because some database developers prefer to stay within the familiar world of T-SQL, whether to avoid the complexities that come with CLR integration or to sidestep the mysterious world of .NET development.

Proponents of CLR integration rave about this feature because it makes it possible to create objects such as stored procedures, triggers, data types and user-defined functions in managed code, opening up vast reservoirs of procedural capabilities not easily achieved with T-SQL alone. Although some might argue that managed code has no place within the relational structure, it’s difficult to deny the benefits that the CLR integration can potentially offer a data-driven application. Even so, database teams have not seemed particularly enthused about jumping on the CLR bandwagon, for whatever reasons that might be.

It should be noted, however, that based on the comments floating around in cyberspace, security concerns have indeed played a significant role in in lack of CLR integration. Up to SQL Server 2016, enabling CLR has meant opening up the database to external assemblies—and all the threats that come with them. At the same time, some insist that CLR integration permits nothing to occur within the database that is not already available to basic T-SQL, in terms of increasing security risks.

But those debates have been put to a rest, at least for now, because Microsoft has revamped CLR integration in SQL Server 2017. The process of implementing CLR is now far more complex than in previous SQL Server versions and its use is far more restrictive. Teams that have already implemented CLR assemblies and are planning to migrate to SQL Server 2017 will have plenty to keep them busy. It’s doubtful that Microsoft would have made these changes if there had been nothing to all those concerns about security.

Even if CLR integration is more secure in SQL Server 2017, database folks might end up feeling even more reluctant to implement CLR integration because of the added complexities and restrictions. Those who did not want to be bothered before might be less inclined to give it a go in 2017. Then there are those who have made up their minds not to trust CLR no matter what Microsoft has changed.

Uninstall

Few would jump to the aid of the autoshrink option or the Database Engine Tuning Advisor. Many, in fact, would be thrilled to see either go up in flames. At least they can ignore those features and encourage their colleagues to do the same. But few can ignore SQL Server’s uninstall capabilities. At some point, nearly everyone working directly with the product will have to remove it—and take the beating that comes with it. For many, there is no SQL Server feature that causes as much frustration or consternation or outright fury than the product’s pseudo uninstall capabilities.

This is not a new problem. Uninstalling SQL Server has been a painful ordeal since its first release. True, the process might not be as bad as it once was—when removing an instance often meant reinstalling the operating system and every application along with it—but uninstall can still bring with it an assortment of challenges. If you run into database folks who refuse to try out a SQL Server release candidate, chances are, they’ll tell you it’s not about untested features or system stability. It’s about the misery of having to uninstall the blasted thing.

At least with virtual machines or containers, you can just blow away the environment when you’re finished and start with a fresh one. Although this is still time-consuming, you can at least be sure you’ve gotten rid of the offending instance. Even now, as late as SQL Server 2016, an uninstall can have unexpected consequences, with artifacts scattered hither and yon, left to impact existing instances and those installations to come.

Given that Microsoft has accelerated its SQL Server delivery schedule (2016 and 2017?), it seems that the people there have just as much to gain from an uninstall makeover as all those developers and administrators who have to endure the process. Given how much time Microsoft has had to get this right, it’s about time they did.

It’s bad enough that a SQL Server installation can be so tricky at times, especially when implementing such features as R Services, but it’s even worse when you have to work so hard to get rid of an instance after you’re done, often paying the price long after the fact. Even if it’s not as bad as it used to be, you can’t blame those of us who remain apprehensive. We’ve been tempted with promises of smooth transitions before. Usually, it’s cybercriminals trying to take down our systems, but in this case, it’s Microsoft.

For better or worse…

Few SQL Server features slip by the database community without generating some controversy or, at the very least, strong opinions, with some even asking the same question: ‘What the hell was Microsoft thinking?’ No doubt you’ve had plenty of your own thoughts about SQL Server features. Feel free to comment on any we’ve covered here or on any features that have caught your imagination. The more the merrier.

SQL Server is a complex suite of technologies and functionalities, all of which continue to evolve and transform, as does the industry that surrounds it. In such an environment, controversy and opinions are inevitable. That’s what keeps things interesting. Perhaps we should look at SQL Server not just as a database management product, but also as a source of entertainment. It might not just make us more productive, but also keep us amused and discussing our different opinions about its many aspects.