Yet another SQL Server enthusiast

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.

Advertisements

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/

2017-08-04

ITdevConnect 2017 conference / September 2017 / Bucharest

Filed under: Uncategorized — sa @ 12:51 PM

ITdevConnect 2017 conference is organized by ADCES in September 2017 @ Bucharest.

Topics: SQL Server, IoT, .NET Core, Xamarin

ITDEVCONNECT2017_y.png

2017-07-31

SQL Server 2017 New Features: STRING_AGG

Filed under: Uncategorized — sa @ 12:53 PM

If SQL Server 2016 introduced STRING_SPLIT, SQL Server 2017 comes with STRING_AGG:

STRING_AGG ( expression, separator ) 

[ WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] ) ]

This function should be used to concatenate string ([N]VARCHAR(n)) values on columns thus:

DECLARE @Products TABLE (
 ProductID INT PRIMARY KEY,
 ProductName NVARCHAR(50) NOT NULL,
 Category NVARCHAR(50) NOT NULL
);
INSERT @Products (ProductID, ProductName, Category)
VALUES 
 (1, 'Windows 7', 'OS'), 
 (2, 'Windows 10', 'OS'), 
 (3, 'Ubuntu 17.04', 'OS'),
 (4, 'SQL', 'Programming language'),
 (5, 'C#', 'Programming language');

SELECT STRING_AGG(v.ProductName, ', ') AS Agg1_NoOrder
FROM @Products AS v

SELECT STRING_AGG(v.ProductName, ', ') WITHIN GROUP (ORDER BY v.ProductName) AS Agg1_ASC
FROM @Products AS v

SELECT STRING_AGG(v.ProductName, ', ') WITHIN GROUP (ORDER BY v.ProductName DESC) AS Agg1_DESC
FROM @Products AS v

SELECT v.Category, STRING_AGG(v.ProductName, ', ') AS Agg2_GroupBy
FROM @Products AS v
GROUP BY v.Category

Results:

sql.server.string_agg.examples.within_group

Please do not confuse this new functionality with string concatenations on rows using + operator or using CONCAT / CONCAT_WS – concat with separator functions:

SELECT 'Windows 7' + ', ' + 'Windows 10' + ', ' + 'Ubuntu 17.04' AS Concat1
SELECT CONCAT('Windows 7', ', ', 'Windows 10', ', ', 'Ubuntu 17.04') AS Concat2
SELECT CONCAT_WS(', ', 'Windows 7', 'Windows 10', 'Ubuntu 17.04') AS Concat3

Results:

sql.server.string.concatenation

 

2017-07-25

SQL Server 2017 New Features: TRANSLATE

Filed under: Uncategorized — sa @ 3:54 PM

TRANSLATE(arg1, arg2, arg3) function is an extension of REPLACE function. It will replace within arg1 (the source strings) the characters from arg2 with the characters found on the same position (index) in arg3:

sql.server.2017.TRANSLATE.function

In this example, Ă will be replaced with A, Â with A, Î with I and so on. COLLATE is used to make sure that upper/lower chars (source text) are replaced properly (example: â is replaced with a and not with A).

2017-07-17

SQL Server partition elimination – a simple example

Filed under: Uncategorized — sa @ 11:07 AM

On a partitioned table, partition elimination (ref #1 – section Partition Eliminationref #1 – section Partition Elimination, ref #2) is an query optimization

  1. Available to predicates defined on partitioning key columns (DateKey in our example) and
  2. It allows to avoid a full table scan/full [clustered] index scan
  3. By scanning records from one or more partitions.

Example:

sql.server.partition.elimination

In this case

  • DateKey is the partitioning key of dbo.FactOnlineSales_Archive table and
  • WHERE clause includes one predicate defined on this column (DateKey = <constant>).

Execution plan shows how partition elimination works: Seek Predicates (green box) property of Clustered Index Scan includes one predicate (PtnId1000 = …) which is going to do the partition elimination (it limits the Scan operator to a single partition in this case). Also, please note the Predicate property (red box) of the same Scan operator which is going to filter all records from current partition in order to count only records for DateKey = ‘2007-01-04’.

Next Page »

Blog at WordPress.com.