Yet another SQL Server enthusiast

2017-11-19

Bucharest A.D.C.E.S. Meetup 2017-11-14

Filed under: Uncategorized — sa @ 8:51 PM

T-SQL script used for [What’s new in SQLServer 2017] presentation.

Advertisements

2017-11-17

Development IDEs for SQL Server instances on Linux

Filed under: Uncategorized — sa @ 3:07 PM

In order to connect to SQL Server instances running on Linux we have following options now:

  1. SQL Server Management Studio on Windows (Windows only; last release is SSMS 17.3; download)
  2. Visual Studio Code together with mssql extension (works on Windows, Linux and MacOS X; reference; download IDE + extension)
  3. SQL Operations Studio (works on Windows, Linux and Mac; reference; download).

Among other things, one functionality drew me attention: the shortcut F12 can be used to view object definition similar with Visual Studio:

SQL.Operations.Studio.01

SQL.Operations.F12.Shortcut.View.Definition.Studio.02

2017-11-11

SQL Server Management Studio 17.3 includes XE Profiler / Extended Events Profiler

Filed under: Uncategorized — sa @ 7:53 PM

After SQL Trace / SQL Profiler group of functionalities has been deprecated (with one notable exception: sp_trace_generatevent) with the recommendation to use Extended Events

SQL.Server.SQL.Trace.SQL.Profiler.deprecated

SQL Server Management Studio 17.3 (download) has a new profiler (named XE Profiler) but for extended events that includes two templates (Standard and SQL)

SSMS.17.2XE.Profiler

which are similar with the same templates from SQL Profiler:

SQL.Trace.SQL.Profiler.Standadard.TSQL.templates

2017-10-30

SQL Server table partitioning: REBUILD / REORGANIZE a single partition

Filed under: Uncategorized — sa @ 8:03 AM

One of the biggest advantage of table partitioning (starting with SQL Server 2016 SP1 this functionality is available also in Standard and Express editions) is the possibility to REBUILD / REORGANIZE a single partition:

ALTER INDEX IXC_ArrayOfIntString_SPID
ON dbo.ArrayOfIntString
REBUILD PARTITION = 52 /*WITH (DATA_COMPRESSION = NONE | ROW | PAGE)*/

ALTER INDEX IXC_ArrayOfIntString_SPID
ON dbo.ArrayOfIntString
REORGANIZE PARTITION = 55

Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql

2017-09-21

SQL Server 2017 New Features: sys.dm_db_log_stats

Filed under: Uncategorized — sa @ 7:34 AM

sys.dm_db_log_stats ( database_id ) returns a lot of useful information about database transaction log. Few examples:

  • active_vlf_count
  • active_log_size_mb
  • log_truncation_holdup_reason: the reason why database log wasn’t truncated.

A possible usage of this function would be to better manage [automated] log backups (maintenance plan) thus: instead of doing scheduled log backups (every 1 \ 5 \ 60 minutes – for example) even there are no database changes (CRUD) we could schedule database log backups every minute (for example) with the first step being a check of active log size to see the minimum amount of changes (for example 1 MB of data changes):

DECLARE @dbid INT = DB_ID('ProdDB01');SELECT * FROM sys.dm_db_log_stats ( @dbid ) AS f 
WHERE f.active_log_size_mb > 1 /*1 MB*/

If amount of changes is less than minimum (1 MB in this example) we could avoid taking log backups because more log backups means longer restore time.

2017-09-06

SQL Server 2017 New Features: Automatic tuning

Filed under: Uncategorized — sa @ 11:53 PM

Automatic tuning (or automatic plan correction) means that SQL Server can automatically switch to the last known good plan when the new execution plan is sub-optimal.

How ?

ALTER DATABASE current
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

A particular query can have (over time) one or more execution plans:
force-last-good-plan
In this example, the CPU time of fourth execution plan is higher compared with previous one (third execution plan). When AUTOMATIC_TUNING setting is ON, SQL Server will automatically switch to previous (third – green) execution plan.

References: https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning

2017-08-31

SQL Server 2017 New Features: Resumable Index Operations

Filed under: Uncategorized — sa @ 8:06 AM

SQL Server 2017 comes with a new functionality that allows to pause and resume online index operations (INDEX REBUILD) including resuming after a failure.

Example:

-- Start index rebuild
ALTER INDEX PK_FactSalesOnline on dbo.FactSalesOnline REBUILD 
WITH (ONLINE=ON, MAXDOP=1, RESUMABLE=ON) 
-- Pause
ALTER INDEX PK_FactSalesOnline on dbo.FactSalesOnline PAUSE
-- Resume index rebuild
ALTER INDEX PK_FactSalesOnline on dbo.FactSalesOnline RESUME

2017-08-22

SQL Server 2017 New features: SELECT … INTO … ON [FileGroup] and Importing CSV data with OPENROWSET

Filed under: Uncategorized — sa @ 8:35 AM

SELECT … INTO Schema.TargetTable ON [FileGroup] FROM …

It creates target table on specified filegroup. Previous behavior was to create target table on default filegroup.

Reference: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql

Importing CSV data with OPENROWSET

OPENROWSET function comes with a new parameter (FORMAT) to indicate file type (‘CSV’).

Reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql

Example:

CREATE DATABASE AppData
 ON 
 PRIMARY (NAME = N'AppData_PR', FILENAME = N'E:\DB\SQL\DATA\AppData_PR.mdf'), 
 FILEGROUP [CustomObjects] (NAME = N'AppData_CustomObjects', FILENAME = N'E:\DB\SQL\DATA\AppData_CustomObjects.ndf'), 
 FILEGROUP [Import] (NAME = N'AppData_IntegrationServices', FILENAME = N'E:\DB\SQL\DATA\AppData_Import.ndf')
 LOG ON (NAME = N'AppData_log', FILENAME = N'E:\DB\SQL\LOG\AppData_log.ldf')
GO
-- It changes default filegroup from PRIMARY to CustomObjects
ALTER DATABASE [AppData] MODIFY FILEGROUP [CustomObjects] DEFAULT 
GO

SELECT *
INTO dbo.ImportedCars_2017_08_22 ON [Import] -- New feature
FROM OPENROWSET(
 BULK N'D:\SourceFiles\cars-2017-08-22.csv',
 FORMATFILE = N'D:\SourceFiles\cars.fmt', 
 FIRSTROW = 2, 
 FORMAT = 'CSV' -- New feature
) AS cars;

2017-08-10

Filegroup Restoring and Join Elimination @ sqlservercentral.com

Filed under: Uncategorized — sa @ 9:24 AM

New article published on sqlservercentral.com: [Filegroup Restoring and Join Elimination]

2017-08-08

SQL Server charts – A simple solution

Filed under: Uncategorized — sa @ 4:31 PM

A really simple and quick solution to create charts with SQL Server is to REPLICATE following two Unicode chars: 9733 ★ and 9734 ☆.

Example:

SELECT *, REPLICATE(NCHAR(9733), Sales) + REPLICATE(NCHAR(9734), MAX(Sales) OVER() - Sales) AS Chart
FROM (VALUES 
 ('Alfa Romeo', 4),
 ('Audi', 2),
 ('BMW', 1),
 ('Citroen', 4),
 ('Dacia', 10)
) AS t(Car, Sales)

sql.server.t-sql.charts.stars

Reference: https://blog.crossjoin.co.uk/2017/08/04/obscure-mdx-month-recreating-the-star-ratings-measure-in-mdx-using-excel-functions/

Next Page »

Blog at WordPress.com.