SQL Server Hall of Shame

Shame - @SeniorDBA

In a recent post by Adam Machanic, he asked his followers to send him the items they thought were the worst features of SQL Server. The list he developed is called the “SQL Hall of Shame”. He put together the following list:

  • In-Memory OLTP
  • English Query
  • Data Quality Services (DQS)
  • Master Data Services (MDS)
  • Auto-Shrink
  • Auto-Close
  • Auto-Grow
  • Notification Services (SSNS)
  • Query Notifications
  • Buffer Pool Extension (BPE)
  • Management Data Warehouse (MDW) / Data Collector
  • Lightweight Pooling / Fiber Mode
  • JSON
  • SQL Server Management Studio (SSMS)
  • Connect to SSIS from SQL Server Management Studio
  • Columnsets (and Sparse Columns in general)
  • Utility Control Point (UCP)
  • Raw Partitions
  • Uninstall
  • Service Broker (SSB)
  • Not Freeing Allocated Memory Except Under Pressure
  • Database Engine Tuning Advisor (née Index Tuning Wizard)
  • Virtual Interface Adaptor (VIA) Network Protocols
  • Mirrored Backups

If you read the article by Adam Machanic, you’ll get the detail for each item on the list.


Free Download: SQL Server Management Studio 17

SQL Server

SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.

Microsoft has announced the latest release of SQL Server Management Studio (SSMS) in May. This release features improved compatibility with previous versions of SQL Server, a stand-alone web installer, and toast notifications within SSMS when new releases become available.

Get it here:

Download – The version number for the latest release is 14.0.17099.0

New in this release

  • Over 100 connect issues fixed
  • SQL Server PowerShell module moved out of SSMS and into the PowerShell gallery
  • Icons updated to support high-DPI display modes
  • Numerous performance improvements in Object Explorer
  • WSUS support for upgrading to future 17.X versions
  • Improved Multi-factor authentication support
  • Enhancements to Availability Group functionality (Support for SQL Server on Linux, direct seeding, endpoint URL handling, and more)
  • Showplan enhancements including new feature to analyze actual existing showplan to help diagnose issues
  • New DAX Query Windows
  • Numerous other enhancements for Power Query support

Query SQL Server Using PowerShell

PowerShell - SeniorDBA

The ability for you to query a SQL Server instance from PowerShell might actually be helpful, especially if you don’t have access to an instance of SQL Server Management Studio.

In this article by Grant Fritchey we see a simple way to connect to and execute a SQL Server query using PowerShell.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
# Get the connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = 'Server=WIN-3SRG45GBF97\DOJO;Database=WideWorldImporters;trusted_connection=true'

# Retrieve test data
$BillToCustomerCmd = New-Object System.Data.SqlClient.SqlCommand
$BillToCustomerCmd.CommandText = "SELECT DISTINCT i.BillToCustomerID
FROM Sales.Invoices as i;"
$BillToCustomerCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $BillToCustomerCmd
$BillToCustomerList = New-Object System.Data.DataSet

# Set up test query
$SQLCmd = New-Object System.Data.SqlClient.SqlCommand
$SQLCmd.Connection = $SqlConnection
$SQLCmd.CommandText = "DECLARE @sqlquery NVARCHAR(MAX);
SET @sqlquery
= N'SELECT si.StockItemName,
FROM Sales.Invoices AS i
JOIN Sales.InvoiceLines AS il
ON il.InvoiceID = i.InvoiceID
JOIN Warehouse.StockItems AS si
ON si.StockItemID = il.StockItemID
WHERE i.BillToCustomerID = @BillToCustomerID;';

SET @parms = '@BillToCustomerID int';

EXEC sys.sp_executesql @stmt = @sqlquery,
@params = @parms,
@BillToCustomerID = @btc;"

# Run the tests
foreach($row in $BillToCustomerList.Tables[0])
$SQLCmd.Parameters["@btc"].Value = $row[0]
$SQLCmd.ExecuteNonQuery() | Out-Null

Read the entire article here.

Presentations Using SQL Server Management Studio

Presentation - SeniorDBA

Any time you are doing a presentation in front of a group of people, you need to make sure they can see what you are doing. This is usually accomplished by projecting the image of your Windows desktop onto a large screen using a projector or television. The issue is Windows will often show that desktop image using the defined resolution for someone sitting a few feet away from the screen instead of someone who may be twenty feet of more away from the image.

The solution of this issue is to adjust the Windows resolution to allow of this issue, but some products don’t deal well with the changes, like the SQL Server Management Studio (SSMS). In this article by John Paul Cook we learn a little about how to manually adjust the display settings inside of SSMS to make the images a little easier to read.

Presentations Using SSMS - SeniorDBA

There is also a brief mention of the free Microsoft ZoomIt utility. I use the built-in Magnifier utility  in Windows 10. This utility uses simple shortcuts to make life simple:

  • Windows (logo) key & Plus (+) key: This combination zooms the screen.
  • Ctrl & Alt & F: Shows full-screen view.
  • Ctrl & Alt & L: Shows the lens view.
  • Windows (logo) key & Esc: Exits utility and returns to normal

I have done several presentations, usually to a technical crowd, and I can never get the display anywhere close to “perfect” for displaying the details I want to show. I resort to a practiced routine of zooming into my Transact-SQL text, in-zooming and re-zooming to the next part of the screen I want them to look at, then using ALT-Tab to switch to a different application, etc. It doesn’t matter, in my opinion, what you do as long as the final impression of the presentation is meaningful to the observer.

My focus it making sure the crowd learns what I’m trying to teach and they understand what I did (and how I did it) to get the same results I showed during the presentation. I’m careful to make sure, by practicing ahead of the actual event, that everyone will be able to see the presentation clearly. I also make sure the switching between in-zoomed and zoomed doesn’t confuse the observer.

You can get additional SQL Server presentation tips here.

Free Download: SQL Server Management Studio 16.5

SQL Server

SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.

Microsoft has announced the latest preview of SQL Server Management Studio (SSMS) using build number 13.0.16000.28.

Get it here:

Download – The version number for the latest preview is 13.0.16000.28 (16.5)

New in this release

  • Fixed an issue where a crash could occur when a database with table name containing “;:” was clicked on.
  • Fixed an issue where changes made to the Model page in AS Tabular Database Properties window would script out the original definition. Microsoft Connect Item: 3080744
  • Fixed the issue that temporary files are added to the “Recent Files” list.
    Microsoft Connect Item: 2558789
  • Fixed the issue that “Manage Compression” menu item is disabled for the user table nodes in object explorer tree.
    Microsoft Connect Item: 3104616
  • Fixed the issue that user is not able to set the font size for object explorer, registered server explorer, template explorer as well as object explorer details. Font for the explorers will be using the Environment font.
    Microsoft Connect Item: 691432
  • Fixed the issue that SSMS always reconnect to the default database when connection is lost.
    Microsoft Connect Item: 3102337
  • Fixed many of high dpi issues in policy management and query editor window including the execution plan icons.
  • Fixed the issue that option to config font and color for Extended Event is missing.
  • Fixed the issue of SSMS crashes that occur when closing the application or when it is trying to show the error dialog.

This release of SSMS supports the following platforms when used with the latest available service pack:
Windows 10, Windows 8, Windows 8.1, Windows 7 (SP1), Windows Server 2012 (64-bit), Windows Server 2012 R2 (64-bit), Windows Server 2008 R2 (64-bit)

SQL Server Management Studio – August 2016 Release

SQL Server

Microsoft has announced the latest generally-available (GA) quality release of SQL Server Management Studio (SSMS) for SQL Server 2016. This fully-supported release is available for download here.

New or Updated Features in Version 13.0.15700.28:

  • ‘Active Directory Universal Authentication’ authentication option.
  • ‘Create database’ dialog to streamline creation of Azure SQL databases.
  • Initial beta support for high-resolution displays.
  • Several bug fixes.
  • New Extended Events templates.

SQL Server Management Studio – July 2016 Release

SQL Server

Microsoft has announced the first monthly update of SQL Server Management Studio (SSMS) following the release of SQL Server 2016. This fully-supported release is available for download here.

New or Updated Features:

  • Support for Azure SQL Data Warehouse in SSMS.
  • Significant updates to the SQL Server PowerShell module. This includes a new SQL PowerShell module and new CMDLETs for Always Encrypted, SQL Agent, and SQL Error Logs.
  • Support for PowerShell script generation in the Always Encrypted wizard.
  • Significantly improved connection times to Azure SQL databases.
  • New “Backup to URL” dialog to support the creation of Azure storage credentials for SQL Server 2016 database backups. This provides a more streamlined experience for storing database backups in an Azure storage account.

  • New Restore dialog to streamline restoring a SQL Server 2016 database backup from the Microsoft Azure storage service. The dialog eliminates the need to memorize or save the Shared Access signature for an Azure storage account in order to restore a backup.

  • Improved support for SQL Server 2016 (1200 compatibility level) tabular databases in the Analysis Services Process dialog.
  • Bug fix in SSMS query designer to allow adding tables to the designer if a user doesn’t have SELECT permissions on them.
  • Bug fix in PowerShell module to enable loading of “SQLAS” extension.
  • Bug fix in the SSMS editor window to allow drag-and-drop open of Sql files.
  • Bug fix in Profiler to fix Profiler crash when exiting.
  • Bug fix in SSMS to prevent crash when trying to edit a join link in the SSMS table designer.
  • Bug fix in SSMS to enable database script generation for db_owner role members.
  • Bug fix in SSMS editor to remove the delay in closing a query tab if the server has gone offline.
  • Bug fix to enable Backup option in SQL Server Express databases.
  • Bug fix to add IntelliSense support for “TRY_CAST()”, and “TRY_CONVERT()” functions.
  • Bug fix in Analysis Services to correctly show the Data Feed provider for multi-dimensional Analysis Services models.