The following script allows you to quickly create a fragmented index for testing:
-- drop test table if exists
IF (OBJECT_ID(N'akawn_t',N'U') IS NOT NULL)
DROP TABLE akawn_t;
CREATE TABLE akawn_t (c1 float PRIMARY KEY, c2 nchar(1000));
--insert 100 records
INSERT INTO akawn_t
SELECT RAND(), '1';
-- delete some records
DELETE FROM akawn_t
WHERE CAST((c1 * 100) AS smallint) % 2 = 1;
-- view fragmentation
'[' + OBJECT_SCHEMA_NAME(a.[object_id]) + '].[' + OBJECT_NAME(a.[object_id]) + ']' AS table_name
,b.[name] AS index_name
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) a, sys.indexes b
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
AND a.avg_fragmentation_in_percent > 1;
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
Below is a screen shot from CTP 2.0
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.
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.
'[' + OBJECT_SCHEMA_NAME([object_id]) + '].[' + OBJECT_NAME([object_id]) + ']' AS table_name
,[name] AS index_name
So watch this space.
As per https://docs.microsoft.com/en-us/azure/sql-database/sql-database-advisor-portal create/drop indexes can now occur automatically.
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.
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.
The release announcement is here.
“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 :)
Cumulative Update 9 for SQL Server 2012 SP3.
Cumulative Update 6 for SQL Server 2016 RTM.
Cumulative Update 3 for SQL Server 2016 SP1.
The Cumulative Update 3 for SQL Server 2016 SP1 enables DML query plan to scan query memory-optimized tables in parallel.
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.