Create a test fragmented index

The following script allows you to quickly create a fragmented index for testing:

USE tempdb;
-- drop test table if exists
IF (OBJECT_ID(N'akawn_t',N'U') IS NOT NULL)
BEGIN 
DROP TABLE akawn_t;
END
--create table
CREATE TABLE akawn_t (c1 float PRIMARY KEY, c2 nchar(1000));
GO
--insert 100 records
INSERT INTO akawn_t 
SELECT RAND(), '1';
GO 100
-- delete some records
DELETE FROM akawn_t
WHERE CAST((c1 * 100) AS smallint) % 2  = 1;
GO
-- view fragmentation
SELECT
'[' + OBJECT_SCHEMA_NAME(a.[object_id]) + '].[' + OBJECT_NAME(a.[object_id]) + ']' AS table_name
,b.[name] AS index_name
,a.avg_fragmentation_in_percent
,a.index_type_desc
,a.*
,b.*
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) a, sys.indexes b
WHERE 1=1
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
AND a.avg_fragmentation_in_percent > 1;

image1

Why don’t more DBAs use extended events

I suspect instead of a keep it simple stupid (KISS) process it is more slow long and painful (SLAP)
 
Say you simply want to view live login failures.
 
Let’s start the wizard
image1
We only want to look at one type of event
image2
First issue, no Login Failures found, but ‘process_login_finish’ looks to be the correct option, so lets add it to selected events.
image3
The capture global features, is what I think causes the most pain. If you select all options (because you don’t know what you’ll find) you’ll get a nice big error message when you get right to the very end so now you have to go back (painful)
image4
image5
The better option would be to alert the poor DBA when the limit is hit (remove the pain). I spend about 5 minutes attempting find what this limit is on the web before giving up. Testing, having 16 global fields let me finally save without the error.
 
Next came the login failure test:
image6
image7
Nope not showing.
 
I’ll try again in another couple of months when my optimism returns.

SQL Server 2017 CTP 2.1 Feature Selection

As mentioned, Reporting Services has now been extracted from the available features that can be installed.
 
Below is a screen shot from CTP 2.1
image1
Below is a screen shot from CTP 2.0
image2
Again, I suspect this is a backward step. I’d rather have SSMS, RS as part of the installer. Then allow the end-user to apply patches to get it up to date. Sending users to all parts of web to install SQL sounds crazy to me.

SQL Server 2017 auto create/drop indexes

It appears that Azure really is the testing ground for new features.
 
As shown in the previous blog the addition of auto index maintenance in Azure, we now see new columns in the sys.indexes view in the SQL Server 2017 CTPs.
image

SELECT 
'[' + OBJECT_SCHEMA_NAME([object_id]) + '].[' + OBJECT_NAME([object_id]) + ']' AS table_name
,[name] AS index_name
,auto_created,is_ignored_in_optimization,suppress_dup_key_messages
FROM sys.indexes;

So watch this space.

Azure SQL Database auto create/drop indexes

As per https://docs.microsoft.com/en-us/azure/sql-database/sql-database-advisor-portal create/drop indexes can now occur automatically.
image1
Extracts from https://docs.microsoft.com/en-us/azure/sql-database/sql-database-advisor
 
Create Index recommendations
Create Index recommendations appear when the SQL Database service detects a missing index that if created, can benefit your databases workload (non-clustered indexes only).
 
Drop Index recommendations
Drop Index recommendations appear when the SQL Database service detects duplicate indexes (currently in preview and applies to duplicate indexes only).
 
As it’s currently only non-clustered and duplicate indexes, the potential issues when a change is made should be limited. It is indicated that the recommendation will be reverted if performance was negatively impacted by the change. What is not currently clear, is what period of time or amount of monitoring will need to pass before a negative performance change will be reverted.

What SQL Server 2017 should have added

Looking at the current features being added to SQL Server 2017 there doesn’t appear to be anything to get overly excited about.
 
There’s nothing that makes you think ‘wow we need to upgrade NOW!’
 
From my perspective the following should be easy no brainers to get DBAs motivated to upgrade:
 
1. The ability to undo transactions from the transaction log / log backups. Geez, this has been around for years by 3rd parties. Surely it’s time to include it, just like native compressed backups (best thing since sliced bread).
 
2. Writable snapshots. We’ve got read-only snapshots so we are half way there. Just think of the space savings and ability to quickly use cloning for test/dev environments.
 
3. The ability to easily fail-over/fail-back log shipped databases. Man, how long has the good old reliable log shipping been around for and that dreaded pain of a fail-over test.
 
4. The ability to attach a database to an older version. Surely there could be a simple flag that says, hey I may need to go back to version x so don’t give me funky xyz features. Exporting/importing data is just painful.
 
5. Wow, wouldn’t it be great if we had a fail-over solution that didn’t drop the client connection.
 
Re-reading the above, possibly Microsoft just needs to focus on making existing features great, before pursuing new features and leaving DBAs again feeling that they could have been better i.e. JSON.

SQL Server 2017 CTP 2.1 released

The release announcement is here.
 
Interestingly,
“In CTP 2.1, we moved Reporting Services installation from the SQL Server installer to a separate installer. This is a packaging change, not a product change; access to SQL Server Reporting Services is still included with your SQL Server license. The new installation process keeps our packages lean and enables customers to deploy and update Reporting Services with zero impact on your SQL Server deployments and databases.”
 
As mentioned previously, installs look to be getting a lot more complicated. I can see why Microsoft would do this for easier internal development, but for an end-user experience, this is likely not ideal.
 
On a side note ~11 months ago SQL Server 2016 CTP 2.1 was released :)

Pre-patching SQL Server

Before you apply a SQL Server patch, it is usually advised to perform the below:
1. Ensure that there are no user/application connections to the SQL Server instance.
2. Ensure there are no SQL agents jobs, that may kick off just before you start the patching.
3. If this is a Virtual Machine (VM), take a snapshot otherwise, ensure you at least have a backup of the system databases.
4. Restart the server. (This one step surprisingly usually resolves 99% of patch application issues and it’s not mentioned by Microsoft). Just stopping/starting the services or reviewing/resolving the locked files warning is typically less effective than a server restart.
 
Of course if you are using Database Mirroring, AlwaysOn, Clustering … there are other considerations, and typically these involve patching the Passive instance to minimize the outage window. Once again, restarting the passive server prior to patching usually ensures the smoothest patching experience.
 
Once patching is completed, it is also recommended to again restart the server. This final restart can give you the confidence that future restart issues are likely not related to this patch.
 
On none HA instances, these server restarts will likely increase the outage window, but it could prove to be the difference between a successful or failed patching attempt.

SQL Server Facets

You don’t often hear about references to Facets in SSMS, however this is a great feature which allows you to review and even change the SQL Server configuration.
images1
I often use it to quickly access the good old SQL Server 2005 Surface Area Configuration settings.
image2
Take a look at the various Facets available and you may be pleasantly surprised.
image3