Mrdenny

As some readers already know, the Polish government is not on the best of terms with modern technology. We'd be damned, however, if that stopped us from trying- even if the end result is as much of a mess as Michał reports it to be.

The story began in 2008, when the government decided it needed some presence on this new hip thing called the Internet. And so, the Electronic Platform of Public Administration Services, or ePUAP for short, was born- a website serving to ease communication between public administration and Polish citizens. It went mostly unheeded until 2011, when the Trusted Profile functionality was introduced- and with it, the ability for people to do taxes, file applications, and submit other paperwork fully online.

Surprisingly, the website worked mostly fine. But soon after the first wave of interest, problems began to appear. Every update led to major downtime. Features such as password recovery would either break or have days-long delays. And, an investigation of a corruption scandal revealed that ePUAP- along with several other services- was the fruit of rather shady dealings.

Michał's story concerns an incident from a month ago, when the whole system crashed and burned. Most users were unable to log in, and the lucky ones who could found that their Trusted Profiles and personal data were missing. It turned out to be a major problem for everyone who elected to do their taxes over the Internet, since the system broke down just a few days before the April 30 tax deadline. Their only other options were to wait several hours in line at their local public offices, get hit with huge financial penalties, or write formal letters to the tax department describing how sorry they were.

The media caught the story, and managed to get a response from the Ministry of Administration and Digitization:

The work on a new version of ePUAP is underway. Maintenance-related downtime is expected. There's nothing alarming about it. Unfortunately, the tests we do can't be fully done over weekends.

Apparently, newfangled inventions such as "testing environments" haven't fully permeated the Iron Curtain. And so, this country-wide platform- holding the personal data of hundreds of thousands of people- is being tried in battle on production servers, during the year's most intense period of activity.

[Advertisement] Release! is a light card game about software and the people who make it. Play with 2-5 people, or up to 10 with two copies - only $9.95 shipped!

Note: This post was originally published only in our eBook, High Performance Techniques for SQL Server, Volume 2. You can find out about our eBooks here. Also note that some of these things may change with the planned enhancements to In-Memory OLTP in SQL Server 2016.

There are some habits and best practices that a lot of us develop over time with regard to Transact-SQL code. With stored procedures in particular, we strive to pass parameter values of the correct data type, and name our parameters explicitly rather than rely solely on ordinal position. Sometimes, though, we might get lazy about this: we might forget to prefix a Unicode string with N, or just list the constants or variables in order instead of specifying the parameter names. Or both.

In SQL Server 2014, if you are using In-Memory OLTP ("Hekaton") and natively compiled procedures, you might want to adjust your thinking on these things a little bit. I'll demonstrate with some code against the SQL Server 2014 RTM In-Memory OLTP Sample on CodePlex, which extends the AdventureWorks2012 sample database. (If you are going to set this up from scratch to follow along, please take a quick glance at my observations in a previous post.)

Let's take a look at the signature for the stored procedure Sales.usp_InsertSpecialOffer_inmem:

CREATE PROCEDURE [Sales].[usp_InsertSpecialOffer_inmem] 
	@Description    NVARCHAR(255)  NOT NULL, 
	@DiscountPct    SMALLMONEY     NOT NULL = 0,
	@Type           NVARCHAR(50)   NOT NULL,
	@Category       NVARCHAR(50)   NOT NULL,
	@StartDate      DATETIME2      NOT NULL,
	@EndDate        DATETIME2      NOT NULL,
	@MinQty         INT            NOT NULL = 0,
	@MaxQty         INT                     = NULL,
	@SpecialOfferID INT OUTPUT
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'us_english')
 
	DECLARE @msg nvarchar(256)
 
        -- validation removed for brevity
 
	INSERT Sales.SpecialOffer_inmem (Description, 
		DiscountPct,
		Type,
		Category,
		StartDate,
		EndDate,
		MinQty,
		MaxQty) 
	VALUES (@Description, 
		@DiscountPct,
		@Type,
		@Category,
		@StartDate,
		@EndDate,
		@MinQty,
		@MaxQty)
 
	SET @SpecialOfferID = SCOPE_IDENTITY()
END
GO

I was curious if it mattered whether the parameters were named, or if natively compiled procedures handled implicit conversions as arguments to stored procedures any better than traditional stored procedures. First I created a copy Sales.usp_InsertSpecialOffer_inmem as a traditional stored procedure – this involved merely removing the ATOMIC block and removing the NOT NULL declarations from the input parameters:

CREATE PROCEDURE [Sales].[usp_InsertSpecialOffer] 
	@Description    NVARCHAR(255), 
	@DiscountPct    SMALLMONEY     = 0,
	@Type           NVARCHAR(50),
	@Category       NVARCHAR(50),
	@StartDate      DATETIME2,
	@EndDate        DATETIME2,
	@MinQty         INT            = 0,
	@MaxQty         INT            = NULL,
	@SpecialOfferID INT OUTPUT
AS
BEGIN
	DECLARE @msg nvarchar(256)
 
        -- validation removed for brevity
 
	INSERT Sales.SpecialOffer_inmem (Description, 
		DiscountPct,
		Type,
		Category,
		StartDate,
		EndDate,
		MinQty,
		MaxQty) 
	VALUES (@Description, 
		@DiscountPct,
		@Type,
		@Category,
		@StartDate,
		@EndDate,
		@MinQty,
		@MaxQty)
 
	SET @SpecialOfferID = SCOPE_IDENTITY()
END
GO

To minimize shifting criteria, the procedure still inserts into the In-Memory version of the table, Sales.SpecialOffer_inmem.

Then I wanted to time 100,000 calls to both copies of the stored procedure with these criteria:

 
Using the following batch, copied for the traditional version of the stored procedure (simply removing _inmem from the four EXEC calls):

SET NOCOUNT ON;
 
CREATE TABLE #x
(
  i INT IDENTITY(1,1),
  d VARCHAR(32), 
  s DATETIME2(7) NOT NULL DEFAULT SYSDATETIME(), 
  e DATETIME2(7)
);
GO
 
INSERT #x(d) VALUES('Named, proper types');
GO
 
/* this uses named parameters, and uses correct data types */
 
DECLARE 
	@p1 NVARCHAR(255) = N'Product 1',
	@p2 SMALLMONEY    = 10,
	@p3 NVARCHAR(50)  = N'Volume Discount',
	@p4 NVARCHAR(50)  = N'Reseller',
	@p5 DATETIME2     = '20140615',
	@p6 DATETIME2     = '20140620',
	@p7 INT           = 10, 
	@p8 INT           = 20, 
	@p9 INT;
 
EXEC Sales.usp_InsertSpecialOffer_inmem 
	@Description    = @p1,
	@DiscountPct    = @p2,
	@Type           = @p3,
	@Category       = @p4,
	@StartDate      = @p5,
	@EndDate        = @p6,
	@MinQty         = @p7,
	@MaxQty         = @p8,
	@SpecialOfferID = @p9 OUTPUT;
 
GO 100000
 
UPDATE #x SET e = SYSDATETIME() WHERE i = 1;
GO
 
DELETE Sales.SpecialOffer_inmem WHERE Description = N'Product 1';
GO
 
INSERT #x(d) VALUES('Not named, proper types');
GO
 
/* this does not use named parameters, but uses correct data types */
 
DECLARE 
	@p1 NVARCHAR(255) = N'Product 1',
	@p2 SMALLMONEY    = 10,
	@p3 NVARCHAR(50)  = N'Volume Discount',
	@p4 NVARCHAR(50)  = N'Reseller',
	@p5 DATETIME2     = '20140615',
	@p6 DATETIME2     = '20140620',
	@p7 INT           = 10, 
	@p8 INT           = 20, 
	@p9 INT;
 
EXEC Sales.usp_InsertSpecialOffer_inmem 
	@p1, @p2, @p3, @p4, @p5, 
	@p6, @p7, @p8, @p9 OUTPUT;
 
GO 100000
 
UPDATE #x SET e = SYSDATETIME() WHERE i = 2;
GO
 
DELETE Sales.SpecialOffer_inmem WHERE Description = N'Product 1';
GO
 
INSERT #x(d) VALUES('Named, improper types');
GO
 
/* this uses named parameters, but incorrect data types */
 
DECLARE 
	@p1 VARCHAR(255)  = 'Product 1',
	@p2 DECIMAL(10,2) = 10,
	@p3 VARCHAR(255)  = 'Volume Discount',
	@p4 VARCHAR(32)   = 'Reseller',
	@p5 DATETIME      = '20140615',
	@p6 CHAR(8)       = '20140620',
	@p7 TINYINT       = 10, 
	@p8 DECIMAL(10,2) = 20, 
	@p9 BIGINT;
 
EXEC Sales.usp_InsertSpecialOffer_inmem 
	@Description    = @p1,
	@DiscountPct    = @p2,
	@Type           = @p3,
	@Category       = @p4,
	@StartDate      = @p5,
	@EndDate        = @p6,
	@MinQty         = '10',
	@MaxQty         = @p8,
	@SpecialOfferID = @p9 OUTPUT;
 
GO 100000
 
UPDATE #x SET e = SYSDATETIME() WHERE i = 3;
GO
 
DELETE Sales.SpecialOffer_inmem WHERE Description = N'Product 1';
GO
 
INSERT #x(d) VALUES('Not named, improper types');
GO
 
/* this does not use named parameters, and uses incorrect data types */
 
DECLARE 
	@p1 VARCHAR(255)  = 'Product 1',
	@p2 DECIMAL(10,2) = 10,
	@p3 VARCHAR(255)  = 'Volume Discount',
	@p4 VARCHAR(32)   = 'Reseller',
	@p5 DATETIME      = '20140615',
	@p6 CHAR(8)       = '20140620',
	@p7 TINYINT       = 10, 
	@p8 DECIMAL(10,2) = 20, 
	@p9 BIGINT;
 
EXEC Sales.usp_InsertSpecialOffer_inmem 
	@p1, @p2, @p3, @p4, @p5, 
	@p6, '10', @p8, @p9 OUTPUT;
 
GO 100000
 
UPDATE #x SET e = SYSDATETIME() WHERE i = 4;
GO
DELETE Sales.SpecialOffer_inmem WHERE Description = N'Product 1';
GO
 
SELECT d, duration_ms = DATEDIFF(MILLISECOND, s, e) FROM #x;
GO
DROP TABLE #x;
GO

I ran each test 10 times, and here were the average durations, in milliseconds:

Average duration, in milliseconds, of various call methods

With the traditional stored procedure, it is clear that using the wrong data types has a substantial impact on performance (about a 4 second difference), while not naming the parameters had a much less dramatic effect (adding about 700ms). I've always tried to follow best practices and use the right data types as well as name all parameters, and this small test seems to confirm that doing so can be beneficial.

With the natively compiled stored procedure, using the wrong data types still led to a similar drop in performance as with the traditional stored procedure. This time, though, naming the parameters didn't help out so much; in fact, it had a negative impact, adding almost two seconds to the overall duration. To be fair, this is a large number of calls in a fairly short time, but if you're trying to squeeze the absolute most bleeding-edge performance you can out of this feature, every nanosecond counts.

Discovering the Problem

How can you know if your natively compiled stored procedures are getting called with either of these "slow" methods? There's an XEvent for that! The event is called natively_compiled_proc_slow_parameter_passing, and it doesn't seem to be documented in Books Online at this time. You can create the following Extended Events session to monitor for this event:

CREATE EVENT SESSION [XTP_Parameter_Events] ON SERVER 
ADD EVENT sqlserver.natively_compiled_proc_slow_parameter_passing
(
    ACTION(sqlserver.sql_text)
) 
ADD TARGET package0.event_file(SET filename=N'C:\temp\XTPParams.xel');
GO
ALTER EVENT SESSION [XTP_Parameter_Events] ON SERVER STATE = START;

Once the session is running, you could try any of the above four calls individually, and then you can run this query:

;WITH x([timestamp], db, [object_id], reason, batch)
AS
(
  SELECT 
    xe.d.value(N'(event/@timestamp)[1]',N'datetime2(0)'),
    DB_NAME(xe.d.value(N'(event/data[@name="database_id"]/value)[1]',N'int')),
    xe.d.value(N'(event/data[@name="object_id"]/value)[1]',N'int'),
    xe.d.value(N'(event/data[@name="reason"]/text)[1]',N'sysname'),
    xe.d.value(N'(event/action[@name="sql_text"]/value)[1]',N'nvarchar(max)')
  FROM 
    sys.fn_xe_file_target_read_file(N'C:\temp\XTPParams*.xel',NULL,NULL,NULL) AS ft
    CROSS APPLY (SELECT CONVERT(XML, ft.event_data)) AS xe(d)
)
SELECT [timestamp], db, [object_id], reason, batch FROM x;

Depending on what you ran, you should see results similar to this:

DECLARE 
	@p1 NVARCHAR(255) = N'Product 1',
	@p2 SMALLMONEY    = 10,
	@p3 NVARCHAR(50)  = N'Volume Discount',
	@p4 NVARCHAR(50)  = N'Reseller',
	@p5 DATETIME2     = '20140615',
	@p6 DATETIME2     = '20140620',
	@p7 INT           = 10, 
	@p8 INT           = 20, 
	@p9 INT;

EXEC Sales.usp_InsertSpecialOffer_inmem 
	@Description    = @p1,
	@DiscountPct    = @p2,
	@Type           = @p3,
	@Category       = @p4,
	@StartDate      = @p5,
	@EndDate        = @p6,
	@MinQty         = @p7,
	@MaxQty         = @p8,
	@SpecialOfferID = @p9 OUTPUT;

DECLARE 
	@p1 VARCHAR(255)  = 'Product 1',
	@p2 DECIMAL(10,2) = 10,
	@p3 VARCHAR(255)  = 'Volume Discount',
	@p4 VARCHAR(32)   = 'Reseller',
	@p5 DATETIME      = '20140615',
	@p6 CHAR(8)       = '20140620',
	@p7 TINYINT       = 10, 
	@p8 DECIMAL(10,2) = 20, 
	@p9 BIGINT;

EXEC Sales.usp_InsertSpecialOffer_inmem 
	@p1, @p2, @p3, @p4, @p5, 
	@p6, '10', @p8, @p9 OUTPUT;

Sample results from Extended Events

Hopefully the batch column is enough to identify the culprit, but if you have large batches that contain multiple calls to natively compiled procedures and you need to track down the objects that are specifically triggering this problem, you can simply look them up by object_id in their respective databases.

Now, I don't recommend running all 400,000 calls in the text while the session is active, or turning this session on in a highly concurrent, production environment – if you are doing this a lot, it can cause some significant overhead. You are much better off checking for this kind of activity in your development or staging environment, as long as you can subject it to a proper workload covering a full business cycle.

Conclusion

I was definitely surprised by the fact that naming parameters – long considered a best practice – has been turned into a worst practice with natively-compiled stored procedures. And it is known by Microsoft to be enough of a potential problem that they created an Extended Event designed specifically to track it. If you are using In-Memory OLTP, this is one thing you should keep on your radar as you develop supporting stored procedures. I know I am definitely going to have to un-train my muscle memory from using named parameters.

The post How not to call Hekaton natively-compiled stored procedures appeared first on SQLPerformance.com.

I got a case recently where the customer had a SQL Alias setup but was having issues connecting to their application. Being in Business Intelligence Support we deal with plenty of connectivity issues and this is one topic of connectivity that does not get touched on a lot.
 
A SQL alias is an alternate name that can be used to make a connection. The alias encapsulates the required elements of a connection string, and exposes them with a name chosen by the user. Aliases can be used with any client application.

It started by getting this error message when trying to connect to SQL from a client application. For the sake of this write up we are going to use SQL Server Reporting Services (SSRS) as our application. We received an error similar to the following:

ERROR [08001] [Microsoft][SQL Server Native Client 10.0]TCP Provider: No such host is known. ERROR [HYT00] [Microsoft][SQL Server Native Client 10.0]Login timeout expired ERROR [01S00] [Microsoft][SQL Server Native Client 10.0]Invalid connection string attribute ERROR [08001] [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

From this we can tell we cannot connect to SQL Server because it could not find the server.
 
We tried a Universal Data Link (UDL) file to start with using the SQL Server name the customer provided, not knowing at that point it was actually a SQL Alias. Of Note, I always start with a UDL test because it is the simplest way to test connectivity. This gets the Customer’s application out of the picture.

Next we did a Ping on the IP of the SQL server and this was successful. This test rules out any DNS or name resolution issues in the environment.

So since Ping was successful, I went down the TELNET road. This allows us to see if anything is listening on the port we are expected to be on.  This could be impaired by a firewall or the listener not being there.

We know the connection was successful because we got a blank screen that looks like this…

This got me thinking, why does Ping and TELNET work but not the UDL. I tested the UDL with ODBC Driver, MS OLE DB Provider, and SQL Server Native Client (SNAC), of which none worked using the name. I even tested the UDL forcing the TCP protocol which failed also.

TCP:<server name>,<port #>
e.g. - TCP:SQL2008R2,1433)

None of this was making sense especially since the server looked correct in configuration manager based on the Customer’s comment that they were using an Alias and they said it looked correct.
 
Aliases can be found under SQL Server Configuration Manager in the SQL Native Client configuration area.

At this time everything looked Ok, so I had to do further research where a SQL Alias can reside.  During that research I found a bunch of references to Cliconfg.  What is Cliconfg you may ask? I had the same question! In short, Cliconfg is the old configuration tool that ships with Windows. You can find more info on Cliconfg here .
 
NOTE: Be aware that Cliconfg is missing an "I" in the config part. This is due to the old 8.3 naming conventions for files.
 
So on the Application we went to Start > Run and typed in Cliconfg and noticed that they were using an IP address instead of a name which we saw in SQL Server Configuration Manager of which the Customer indicated the IP listed was incorrect.
 
In Cliconfg we saw something similar to the following…

I know the SQL Server IP address is 10.0.0.4, but the alias in Cliconfg was configured for 10.0.0.272. So to help correct this we edited the Connection parameters and set Server Name to the actual name of the SQL Server instead of the IP Address.

After changing that in Cliconfg we were able to connect to the SQL Server using the UDL successfully. Then we went back to the application, in this example being SSRS, and it was also able to connect successfully.

Bitness matters!

Be aware that client aliases can lead to connectivity issues if not configured correctly. These client aliases are really just entries in the registry. Also be aware that BITNESS MATTERS! The alias bitness will depend on what the bitness of the application is.  If the application is 32-bit then the alias needs to be 32-bit. 
 
From SQL server configuration manager the bitness is broken out in the GUI.

For Cliconfg there is actually separate application. One for 32-bit and one for 64-bit. By default if you go to start > run and type in Cliconfg on a 64-bit OS, you will get the 64-bit version.  If the application is 32-bit, and you added a 64-bit alias then your will not pick up the alias.  To get the 32-bit version of Cliconfg you can go to this path…

%SystemDrive%\Windows\sysWOW64\cliconfg.exe

Mark Ghanayem
Microsoft Business Intelligence Support

If you haven’t been on a month-long holiday to a secluded island with no access to phone, email, or computer (and if you did, I truly envy you), then you must have heard that we announced SQL Server 2016 recently! And, now we’ve released a public beta (or preview) “SQL Server 2016 CTP2” last week.

Download It Now!
This is a public preview, so it is not restricted to a few selected customers. It is available to all. Thousands have downloaded SQL Server 2016 CTP2 and are exploring the new features in it. You can download it now, if you haven’t already done so, from here: http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016.

Read About It!
CTP2 documentation is published on MSDN here: https://msdn.microsoft.com/en-us/library/dd631853(v=sql.10).aspx.

See It In Action Now!
TechNet Virtual Labs are here: https://technet.microsoft.com/en-us/virtuallabs/bb467605.aspx. These are self-paced guided labs that can help you ramp up quickly on the new technologies and features in SQL Server 2016 CTP2.

Want to Deploy Your Own Azure SQL VM with SQL Server 2016 CTP2?
Get it here: http://azure.microsoft.com/en-us/marketplace/partners/microsoft/sqlserver2016ctp2evaluationwindowsserver2012r2/. Alternatively, go to the Azure Portal, and select SQL Server 2016 CTP2 from the gallery. Hundreds of SQL Server 2016 CTP2 VMs are deployed in Azure.

Have A Question?
Post your questions on one of the SQL Server Forums.

Ready For More Serious Stuff?
Have an interesting new project, or want to validate SQL Server 2016 for your applications? We invite you to work closely with the SQLCAT team and the SQL Server engineering team. Sign up here: http://aka.ms/sqlvnext, if you would like to join our TAP (Technology Adoption Program) for SQL Server 2016. (When you click on this link, you will find no mention of SQL Server on that page. You may be confused or feel tricked , but bear with us and continue further, you will eventually submit your request for SQL Server 2016 TAP).

Ready For Even More Serious Stuff?
We have a state-of-the-art lab with modern hardware and storage dedicated just for you. Bring in your workload and test harness and test, learn and work with us alongside us. Explore the benefits of spending a week (or more, if you want and need) working alongside SQLCAT team and SQL Server engineering team on your application and on your data, design and tune your application for SQL Server 2016. Just let us know, and we will schedule it.

Ready For Production?
Ready for even more serious stuff? One of the most exciting part of our job is to take bleeding edge applications on bleeding edge technology and enable customers to deploy in production to dramatically reduce the time to value. CTP2 is ready for production deployment for several features, and we are actively working with several TAP customers to take their applications to production with CTP2. You need to be in the TAP program to exploit the extreme advantages of this accelerated adoption.

We Want To Hear From You
We would like to hear your feedback. What is working well for you and what is not?  Submit feedback through https://connect.microsoft.com/SQLServer/Feedback/. You can tweet us at @MSSQLCAT.

I've been doing quite a bit of SQL Server performance work lately, helping customers embrace the cloud. One great feature of Windows Server 2012 and above is the ability to implement a storage pool combining multiple disks. This greatly simplifies disk striping to distribute I/O and deprecates RAID configurations, as explained in more details in the "Performance Guidance for SQL Server in Windows Azure Virtual Machines" whitepaper from Microsoft.

In this case, the client has an A7 Azure VM which allows attaching up to four 1TB data disks. Another great Azure feature is that you only pay for what you use. What it means to you is that you cut cost because you have all of this storage available but you are charged only for the actual storage and not for the capacity.

In the process of configuring the storage pool, you might find that none or some of the disks show up preventing you from creating a new storage pool although you've attached them all right and they are there. How do you fix this horrible issue? Delete the disk partitions (also known as volumes). That's because you can only create a storage pool of uninitialized disks only.

1. Right Windows Disk Management.
2. Right-click on each disk and click Delete Volume.

Now you can create your storage pool. In this case, I've created one virtual disk that has three physical disks for a total combined storage of 3 TB.

Ever since data moved to the cloud, database administrators have wondered what it will mean for their jobs.

It doesn’t matter whether you are thinking about Infrastructure as a Service, Platform as a Service, Database as a Service, there is some aspect of what a database administrator does that may not exist in the future.

And then there’s the situation around Big Data, and the Internet of Things. We see videos of how organisations are using devices that produce data to help control things as large as the London Underground, and as personal as your fitness routine. It doesn’t matter whether the Thing is recording your heart beat or the vibrations of a tube train – the fact is that data is being produced to help our lives.

For a database administrator, this present a new set of concerns. Does the DBA of today need to be able to design and tune a system that leverages Windows Azure SQL Database? What about Parallel Data Warehouse? Hadoop? Apache Storm? StreamInsight? There are so many things that make it seem to the DBA that their skill set must adapt for them to survive.

But what I want to suggest is that the DBA should not be seeing these technologies as their domain to administer and tune, but their domain to consume and leverage.

Currently, database administrators in the SQL Server world use Performance Monitor counters to keep an eye on how disk, CPU, I/O, RAM, cache, etc., etc., are going. This data is polled regularly and stored, while SQL Server Agent alerts are configured to holler if there’s something noteworthy, such as a Transaction Log filling up. Extended Events allow all kinds of events to be monitored, with data about them examined, stored if necessary, reacted to, and more.

Once, we used SQL Server Trace, and a tool called Profiler. Profiler was frowned upon somewhat – its veracity for consuming data meant that using it to run a trace live would put an unnecessary load on the SQL instance being monitored. The better option was to configure a trace, and then use Profiler after the fact to see what was happening. Extended Events (XE) is seen in a different kind of light. An XE session runs, keeping an eye on things in a very decoupled way, and the stream of events that is produced can be hooked into using a number of different methods.

From the perspective of the DBA, these XE sessions become an “Internet of Things”-style environment. Imagine that each configured event is a smart device, sitting somewhere, producing data in some semi-structured form. Of course, we know the structure, but each event might have a different configuration, with different kinds of properties being available. The DBA consumes them using the same mechanisms as IoT technologies, and suddenly becomes a new kind of DBA-superhero. It’s not unheard of today, to hear of people looking after large numbers of databases.

The Internet of Things is here for database administrators. Extended Events are part of it.

@rob_farley 

PS: This post about Extended Events is part of the latest T-SQL Tuesday event, hosted by my friend Jes Borland (@grrl_geek).

For last month's T-SQL Tuesday, I wrote about some undocumented trace flags that help you babysit long-running backup and restore operations. This month, Jes Borland's topic is Extended Events, and I thought I would show new capabilities in SQL Server 2016 that largely make these trace flags obsolete.

If you're playing with CTP2 (you can download it here), you may notice a new category backup_restore and new event backup_restore_progress_trace:

Discovering a new event in CTP2's New Session dialog

Here's a quick and dirty XE session for capturing the data for this event (I've added comments for filtering to only backup or only restore operations; by default, both are included):

CREATE EVENT SESSION [Backup progress] ON SERVER 
ADD EVENT sqlserver.backup_restore_progress_trace
(
    ACTION(package0.event_sequence)
 
    -- to only capture backup operations:
    --WHERE [operation_type] = 0
 
    -- to only capture restore operations:
    --WHERE [operation_type] = 1 
)
ADD TARGET package0.event_file 
(
  SET filename = N'C:\temp\BackupProgress.xel'
); -- default options are probably ok
GO
 
ALTER EVENT SESSION [Backup progress] ON SERVER STATE = START;
GO

Now, let's say I run the following operations – create a database, put a bit of data back it up, drop it, and restore it:

USE [master];
GO
CREATE DATABASE floob;
GO
SELECT s1.* INTO floob.dbo.what 
  FROM sys.all_objects AS s1 
  CROSS JOIN sys.all_objects;
GO
BACKUP DATABASE floob TO DISK = 'c:\temp\floob.bak' 
  WITH INIT, COMPRESSION, STATS = 30;
GO
DROP DATABASE floob;
GO
RESTORE DATABASE floob FROM DISK = 'c:\temp\floob.bak' 
  WITH REPLACE, RECOVERY;

Now, we can query the data from the event target file:

;WITH x AS 
(
  SELECT ts,op,db,msg,es
  FROM 
  (
   SELECT 
    ts  = x.value(N'(event/@timestamp)[1]', N'datetime2'),
    op  = x.value(N'(event/data[@name="operation_type"]/text)[1]', N'nvarchar(32)'),
    db  = x.value(N'(event/data[@name="database_name"])[1]', N'nvarchar(128)'),
    msg = x.value(N'(event/data[@name="trace_message"])[1]', N'nvarchar(max)'),
    es  = x.value(N'(event/action[@name="event_sequence"])[1]', N'int')
   FROM 
   (
    SELECT x = CONVERT(XML, event_data) 
     FROM sys.fn_xe_file_target_read_file
          (N'c:\temp\Backup--Progress*.xel', NULL, NULL, NULL)
   ) AS y
  ) AS x 
  WHERE op = N'Backup' -- N'Restore'
  AND db = N'floob'
  AND ts > CONVERT(DATE, SYSUTCDATETIME())
)
SELECT /* x.db, x.op, x.ts, */ 
  [Message] = x.msg, 
  Duration = COALESCE(DATEDIFF(MILLISECOND, x.ts, 
             LEAD(x.ts, 1) OVER(ORDER BY es)),0)
FROM x
ORDER BY es;

For a backup, trace flag 3226 does not suppress any of the output captured by Extended Events. I've left out output columns, due to the filters, for brevity:

Event data for a backup operation

For a restore, you will see these rows:

Event data for a restore operation

If you're troubleshooting a slow backup or restore operation, you could easily filter on the duration, so that you only see events that took longer than n milliseconds, for example. The only thing I don't see in this output is any way to tell if instant file initialization was in effect during the restore – you still may need trace flag 3004, as described in my post for last month's T-SQL Tuesday.

Don't forget to stop the session (but feel free to keep the session definition on the server, so you can use it again):

ALTER EVENT SESSION [Backup progress] ON SERVER STATE = STOP;

I did not perform any performance tests or impact analysis, but in general, I would say that – like the trace flags – this isn't something you'd want running all the time, but only when troubleshooting a specific operation. It's a bit easier to set up this session and query the data, IMHO, than to turn on the trace flags and parse all of the output from SQL Server's error log.

The post T-SQL Tuesday #67 : New Backup and Restore Extended Events appeared first on SQLPerformance.com.

The operating system error 665, indicating a file system limitation has been reached continues to gain momentum beyond DBCC snapshot files.    Over the last ~18 months I have reproduced issues with standard database files, BCP output, backups and others.

We have posted previous blogs talking about the NTFS attribute design and associated limitations (665) as the storage space for the attribute structures becomes exhausted.

http://blogs.msdn.com/b/psssql/archive/2008/07/10/sql-server-reports-operating-system-error-1450-or-1452-or-665-retries.aspx

http://blogs.msdn.com/b/psssql/archive/2009/03/04/sparse-file-errors-1450-or-665-due-to-file-fragmentation-fixes-and-workarounds.aspx

Online DBCC has been susceptible to this limitation leveraging a secondary stream for copy-on-write activities.    The sparse nature of DBCC snapshot or a snapshot database can drive attribute exhaustion.

As space is acquired the disk storage location(s) and size(s) are stored in the attribute structures.   If the space is adjacent to a cluster already tracked by the file the attributes are compressed into a single entry, spanning the entire size.   However, if the space is fragmented it has to be tracked with multiple attributes.

The 665 issue can pop up with larger file sizes.   As the file grows it acquires more space.  During the space acquisition the attributes are used to track this space.  

Repro 1 – DBCC Snapshot or Snapshot Database

The copy-on-write logic acquires new space as pages are dirtied in the main database.  As writes occur to the snapshot target more attributes are used.  The space allocations and clusters are by definition and design stored in spare locations.

Repro 2 – Database files

I can insert data into a database with a smaller auto grow size, each acquiring new disk space.  If the disk space is not acquired in strict, cluster adjacent order the growth is tracked with separate attributes.   After millions of grows from a large import or index rebuild (usually around 300GB) I can exhaust the attribute list and trigger the 665 error.

Repro 3 – BCP

BCP extends the output file in the process of simply writing to the file.  Each block written has to acquire disk space and it is reliant on adjacent disk space allocation to accommodate a large file.

One customer was trying to use a copy of BCP per CPU and the query option to partition the output streams and improve the export performance.   This can work very well but in this case it back-fired.  The output location was shared and as each copy of BCP was writing data it caused them to leap frog each other on the same physical media.   Each of the BCP output streams quickly exhausted the attribute storage as none of them were acquiring adjacent storage.

What Should I Do?

Testing has shown that defragmentation for the physical media may help reduce the attribute usage.   Just be sure your defragmentation utility is transactional.  (Note:  defragmentation is a different story on SSD media and typically does not address the problem.  Copying the file and allowing the SSD firmware to repack the physical storage is often a better solution.)

Copy - Doing a file copy may allow better space acquisition.

Use SQL Server 2014 for DBCC online operations.  SQL Server 2014 no longer uses the secondary stream but a separate, sparse file marked for delete on close.  This may reduce the shared attribute storage required by a secondary stream.

Adjust the auto growth to acquire sizes conducive for production performance as well as packing of attributes.

Format the drive using the larger NTFS metadata, file tracking structures providing a larger set of attribute structures. https://support.microsoft.com/en-us/kb/967351

Use ReFS instead of NTFS.  ReFS does not contain the same design as NTFS.

Adjust utility write sizes, for example the BCP buffer size change:   I just discovered that the fix list for SQL Server 2012 SP2 does not appear to contain the fix information.   The fix changes the bcpWrite (internal API used by ODBC driver and BCP.exe) from a 4K write size to 64K write size.

Backup – Carefully plan the number of files (stripe set) as well as transfer and block sizes.

Bob Dorr - Principal SQL Server Escalation Engineer

In a previous blog, I talked about SQL 2014’s new Cardinality Estimator (new CE) and trace flags 9481 and 2312 can be used to control which version of Cardinality Estimator is used.

In this blog, I will modify a real scenario customer hit to show you that how you can use these trace flags to spot issues related to new CE and how SQL 2014 SP1 can help.

Problem

Customer reported to us that a particular query ran slowly on SQL 2014.  After troubleshooting, we determined that new Cardinality Estimator grossly over-estimated.  We compared the plans using trace flag 2312 (new CE) and 9481 (old CE).

Here is a simplified repro without revealing customer’s table and data.  Customer’s query is more complex.  But the issue is the over-estimation of a particular join that produced a bad query plan overall.

The script below inserts 1 million rows in table t.  c1 is primary key.  c2 has duplicates( each number repeats 100 times).

create table t (c1 int primary key, c2 int)
go
set nocount on

go
begin tran
declare @i int
set @i = 0
while @i < 1000000
begin

insert into t values (@i, @i % 10000)
set @i = @i +1

end
commit tran
go
create index ix on t(c2)
go
update statistics t
go

How many rows do you think the following query will return?  It will return 100 rows.  Note that I used querytraceon 2312 to force new CE on SQL 2014.  But if your database compatibility level is 120, you will get new CE without having to use the trace flag.  Again, the previous blog has instructions how to control new and old CE with trace flags and database compatibility level.

select t1.c2 from t t1 join t t2 on t1.c2 = t2.c2
where t1.c1 = 0 option (querytraceon 2312)

Do you think the query below will return more rows or less rows than the query above?  Note that I added an AND clause “t1.c1 <> t2.c1”.   This should have made it more restrictive and return no more rows than the previous query.  It actually returns 99 rows because there is one row filtered out by t1.c1 <> t2.c1.

select t1.c2 from t t1 join t t2 on t1.c2 = t2.c2 and t1.c1 <> t2.c1
where t1.c1 = 0 option (querytraceon 2312)

Let’s take a look at optimizer estimates.

The first query has very accurate estimate.

Take a look at the estimate below for secondary query.  The query has 99 rows returned but the estimate is 1,000,000 rows.    The difference is that I added one more AND predicate in the secondary query (t1.c1 <> t2.c1).  It should have cut down the estimate.  But it actually made it much larger.

Note that the same query has fairly low estimate by forcing old CE with trace flag 9481

select t1.c2 from t t1 join t t2 on t1.c2 = t2.c2 and t1.c1 <> t2.c1
where t1.c1 = 0 option (querytraceon 9481)

It is this behavior that made customer’s original query much slower. This is a bug in new Cardinality Estimator.

Customer called in for help tuning the query.  First we had them revert to old Cardinality Estimator by trace flag 9481 which made query fast.   We knew that we had quite a few fixes in this space and asked customer to apply SP1 on a test machine.  But the query was still slow.   So we went ahead and collected statistics clone and started to look at query in house.   We were able to reproduce the issue where new Cardinality Estimator had very high estimate but old Cardinality Estimator has low estimate even on SP1

We thought it’s a new bug after SP1.  But as we looked at the fix more closely, it required trace flag  4199 to be enabled.  In fact all optimizer fixes require 4199 to activate.    After enabling 4199, SP1 was able to estimate correctly.  Customer tested their original query on SP1 with trace flag 4199 and it ran fast.

Solution

You need to apply SP1 but you must also enable trace flag 4199 in order to activate the fix.

SQL Server 2014 Service Pack 1 made various fixes on new Cardinality Estimator (new CE).  The release notes also documents the fixes.

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus

About me: Kaushal Shah

I am a Microsoft® Certified Solutions Associate (MCSA) for SQL Server 2012,Currently working in Microsoft BI space.I always keen to learn new technology and new skills.I have worked within different sectors of business.I have worked in Wholesale Sport industry,Retail sector,E-commerce and lately in Healthcare.

In this post I am trying to explain commonly used Dimensions Types used in Data warehouse.I have put examples with this also to make it more clear.

What are the Dimensions in Data warehouse?

A dimension table consists of the attributes about the facts. Dimensions store the textual descriptions of the business attribute. Without the dimensions, we cannot measure the facts and facts are just disordered Numbers.In Business, Customer, Products, Buyers information can be different dimensions.

Let’s walk through commonly used types of dimensions.

Types of commonly used Dimensions in data warehouse Design

• Conformed Dimensions
• Junk Dimensions
• Role-playing Dimensions
• Slowly Changing Dimensions
• Degenerated Dimensions

Conformed Dimensions

A Dimension that is used in multiple locations is called conformed dimensions. A conformed dimension may be used with multiple fact tables in single database, or across multiple data marts or Data warehouses.

I.e. Above shown Customer and Product Dimensions are Conformed Dimensions as they are connected to Shipment Fact table, Sales Order Fact table, and Service Request Fact table.

Junk Dimensions

A junk dimension is a collection of random transaction codes flags and/or text attributes that are unrelated to any particular dimension. The junk dimension is simply a structure that provides a convenient place to store the junk attributes.

I.e.:  Assume that we have a gender dimension and marital status dimension. In the fact table we need to maintain two keys referring to these dimensions. Instead of that create a junk dimension which has all the combinations of gender and marital status (cross join gender and marital status table and create a junk table). Now we can maintain only one key in the fact table.

Role-playing Dimensions

Role Playing Dimensions are the Dimensions which often used for multiple purposes within same database.Here same dimension key is associated with more than one foreign key in the fact table in the database for the different purposes.

I.e.:  In Date dimensions, [FullDateAlternateKey] is associated with [Orderdate key], [Duedate key], and [Shipdate] key in the fact table to solve different purpose in Data warehouse.

Slowly Changing Dimensions

This is widely used Dimensions type. It is the dimensions where attribute values changes with time. There are various types of Slowly Changing Dimensions (SCD) based on how business manages this dimensions.

Types of SCD

TYPE 0: It is the dimensions where we do not change attribute values at all. They are rarely used. I.e. Employee birth date

TYPE 1: In this type, Old value of attribute is overwritten by new values of attribute and no history kept

I.e Customer City where company decided to show only current one.

In this case previous city name London is replaced by new city name Edinburgh.

TYPE 2: In this type we tracks historical data by creating multiple records for a given Natural key (business key) in the dimensional tables with separate surrogate key and/or different version numbers. Unlimited history is preserved for each insert.

I.e. Customer City where company decided to have historical data then we will have to add an extra row with column to identify the Current/Historical attributes value by start and end date columns.

TYPE 3: In this type, we tracks changes using separate columns and preserves limited history.it is limited to how many columns we want to add in dimension table.

I.e. Customer City where New columns “previous City” and “Current City” being added.

TYPE 4: In this type, we keep all or some historical data in separate table and current data stays in main Dimension table. Both historical and current dimension table joined to fact table with same surrogate key, this will enhance the query performance. This type    used very rarely.

I.e. we create new table to store previous Customer City and Current Customer City in Historical table with Created date And Current Customer city in Current dimension table.

Degenerated Dimensions

A degenerate dimension is a dimension which is derived from the fact table and doesn’t have its own dimension table.

In Data warehouse this Dimension often used to show drill through capability where in the report you can see how aggregated no came up.

I.e. Invoice no can be stored in the fact table and then used as separate dimensions for the drill through purpose to find out what invoices are part of total buying cost in report.

So Dimensions are one of the pillar of the data warehouse.Choosing right one can define future of the data warehouse.It always good to use right type

Hope this post gives some insight and information around data warehouse design.

Has anyone came across Rapidly Changing Dimensions??

Any suggestion and/or comment always welcome.

In the next post,I will walk through  Different Types of Facts and Fact tables.

Happy Learning !!!

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Guest Post by Ken Cenerelli

Developers love to write code and a great many also like to write blog posts. However, it can be hard to find time to publish blog articles and to keep your site updated. I want to use this space to share an idea that I have been trying recently to motivate myself to put out more content.

I have often said that every developer needs a developer’s blog. As Scott Hanselman eloquently states, Your Blog is The Engine of Community. The benefits of having your own site can be immense:

• It is a place where you can share your opinions on technology.
• You can showcase your skills and display your side projects.
• Demonstrates that you can learn on your own time and that you are abreast of current technologies.
• That you care about the developer community and you want to give back.
• All of your posts are searchable and your work is easily accessible to future employers.
• It shows that you can communicate your thoughts in a professional manner.
• Serves as a portal to all your social media profiles.

I have been blogging more lately and I have recently adopted the theory of “write once, publish anywhere”. Based on the developer’s axiom of “write once, run anywhere”, it is the idea of producing one item but being able to use it at least two times. Another analogy that I can relate it to is simulcasting in TV. The television episode is created once and then it is distributed to multiple TV stations.

My blogging activities are trying to replicate this philosophy as I want to reuse my articles in several places. First I look for third-party sites that I can submit to initially. Once it appears on the site I then take the same piece and re-blog it to my own site several days later. This way I create content for two sites. I get the benefit of my work being listed on another’s site, RSS feeds, Twitter announcements, etc. and they get fresh material for their pages. As well, I am still providing new content to my own blog.

The nice thing is as .NET developers and Microsoft technologists there are a lot of places where you can contribute articles. The following links are sites you can use to post content to:

• Canadian Developer Connection
• TechNet Wiki
• MSDN Magazine
• Code Magazine
• CodeProject
• InformIT
• Just Azure
• PowerShell Magazine
• DotNetCurry.com
• Simple-Talk
• SQL Server Pro Magazine
• SQL Server Central

In addition, if you search for the term “Write for us” (in quotes) combined with the topic you wish to talk about you will generally find a blog or magazine looking for submissions. Granted, you do not want to author for just anyone so vet the site first. Read a few excerpts and see if the items are of value.

If none of the links above appeal to you then reach out to your favourite website or blogger and ask if they accept guest pieces. They just might surprise you and say yes. Then, once you have found the one you want — go for it! Work with the editors to get it just right for their audience. Even if your content is rejected in the end you will still have a post you can use on your own site.

Do you know of other sites that accept Microsoft-based articles? If so, feel free to add them in the comments!

I have noticed that there is a general sense of mystery around Azure Cloud Service. Some equate it to Cloud Services in general while others get confused with the relation of Azure Cloud Service and Virtual Machines while some one else gets confused about using Azure Web Apps (formerly Websites) with webjobs over Azure Cloud Service. If you have been in a similar dilemma as I have been in the past, I will try and clarify the confusion and get you started on Azure Cloud Service through this tutorial series.

Demystifying Azure Cloud Services – Part 1

In Part 1 of this tutorial I covered an overview of some of the misconceptions that people have about Azure Cloud Services in general. From now, each subsequent part will demystify or inform how Azure Cloud Services are related to other services present in Azure. This post is about Azure WebApps.

Yes, you can develop and host websites using Azure Cloud Services as well as Azure WebApps ( a part of Azure App Services now). And yes, both are Platfrom as a Service (PaaS) offerings. In the spectrum of PaaS offerings, there is a difference in the amount of control that is given to you. If you want more granular control, you would want to look into Azure Cloud Service where as if you want just hosting services, you should look into Azure WebApps. Lets delve into this a little deeper to understand the functionality of each and how they differ.

Azure WebApp 

There is enough documentation about Azure WebApp and I will not delve into the details of this but will position a comparative approach. Azure WebApps (AWA) is a PaaS solution to develop websites. Within AWA, there are a number of open source, CMS, blog, e-commerce frameworks available as templates that you can directly use to develop your website. You can also take advantage of scaling,  load balancing, continuous deployment using Git, Github and have multiple deployment slots such as staging and production slots. In short, all your web development and hosting needs can be accomplished using WebApps.

However, lets get under the hood and understand what is happening. The diagram below is taken from Microsoft Azure Websites: Quick-and-Easy Hosting as a Service. The article explains in detail how this works. Just to keep it brief, the idea is that AWA is built on the PaaS platform of Azure – kind of an additional layer of abstraction to provide a PaaS which is built on PaaS. As you can see, it internally uses stateless worker roles, blobs and relational data bases.

It is important to understand that in AWA, you do not need to care about or worry about all these details. It is managed and taken care of internally. All you need to do is design and develop, deploy and scale your webapp as per need. This is the best option for you to get started, host an existing website, migrate another website to this scalable platform which also features near instant deployments. You can run background processes using Azure Webjobs and easily integrate databases with your websites. You can essentially do everything you want with Azure WebApps but if your webapp is dependent on the environment hosting it, in that case Azure WebApp may not be your first choice.

Azure Cloud Service for WebApps using WebRole

Now, let us look at Azure Web Roles. As I had explained in part 1, Azure had started with the notion of Cloud Services which was synonymous to hosting services. Web Roles were used to host websites which were essentially compute stateless instances. Worker Roles on the other hand were used to process background processes for an application. Hence, web roles were designed to be the front end, taking care of user interaction while worker roles takes care of the backend tasks. From an architectural point of view, Web roles were supported on Windows Server instances with IIS installed as they hosted the website, where as Worker Roles were supported by Windows Server instances without IIS. The communication between them was accomplished using Queues or Service Busses which I will cover in detail in subsequent posts as a part of this tutorial.

Hence, you could think of Azure Cloud Service to be providing you with all the features that Azure WebApps provide. How does it differ? Well, by the virtue of using Web and Worker roles, you still have some control over these instances. You can remote into these instances, define startup tasks for these instances and also define complex network integrations using Cloud Services. But keep in mind, what you gain in control and flexibility, you lose in speed of deployment and additional management overhead. There is always this tradeoff.

Conclusion

There is a third option to host websites on Azure and that is by using pure Infrastructure As a Service (IaaS) in the form of Virtual Machines where you install your environment to deploy your website and then host / deploy your website. But this is in the other end of the spectrum which gives you complete control and flexibility, but then again this is at the cost of time to deploy and added overhead of management. Azure AppService, Cloud Services and Virtual Machines comparison provides a pretty detailed comparison of the various services to host websites on Azure. It also lists out typical scenarios and maps service to scenario which is very helpful and does help understand the concept well. In the next post I shall cover Virtual Machines and Cloud Services and how they are related. Stay tuned and share your Azure stories with me @AdarshaDatta.

The Problem

A colleague of mine has been doing some data centre failover testing for a customer recently and ran into an issue with VMware’s Site Recovery Manager (SRM) 5.8 running on vSphere 5.5 U2. When attempting to perform a recovery, and you’re running Linked Mode, and the protected site is off-line, the recovery may fail. The upshot of this is “The user is unable to perform a recovery at the recovery site, in the event of a DR scenario”. Here’s what it looks like.

The Reason and Resolution

You can read more about the problem in this VMware KB article: Performing a Recovery using the Web Client in VMware vCenter Site Recovery Manager 5.8 reports the error: Failed to connect Site Recovery Manager Server(s). In short, there’s a PowerShell script you can run to make the recovery happen.

Conclusion

I don’t know what to say about this. I’d like to put the boot into whomever at VMware is responsible for this SNAFU, but I’m guessing that they’ve already had a hard time of it. At least, I guess, there’s a workaround, if not a fix. But you’d be a bit upset if this happened for the first time during a real failover. But that’s why we test before we handover. And what is it with everything going pear-shaped when Linked Mode is in use?

I waffled on some time ago about using proper certificates in your vSphere 5.5 environment. You can read about some of how to do that here. Eric has a nice summary of the steps here. I got a call recently from the customer about a few things and they mentioned some issues with vSphere Replication 5.8. Turns out I’d forgotten about vSphere Replication when I’d gone through the certificate replacement process, as it was done as a PoC. The fix is simple: power off the appliance and power it on again. VMware has a KB for most every situation, including this one – VMware vSphere Replication appliance no longer able to communicate with the VMware vCenter Server after changing the vCenter certificates (2063955). It also helps that I’m a bit late to this particular party.

The next step should be to replace the certificates on your vSphere Replication infrastructure as well. I was going to put together a post on that too, but it’s probably simplest if you read the VMware KB – Configuring CA Signed Certificates for VMware vSphere Replication (2080395). Friedrich also has a great post on some of the basics – including the certificate replacement process – here.