Press "Enter" to skip to content

Day: February 24, 2016

Columnstore And SSIS 2016

Published 2016-02-24 by Kevin Feasel

Niko Neugebauer mentions that with SQL Server 2016 and Integration Services 2016, clustered columnstore index insertion can get much faster:

To solve the performance problem I went straight to the DefaultBufferMaxRows setting and set it to be equal of the maximum number of rows in a Row Group – 1048576. Together with the AutoAdjustBufferSize setting it helps the actual current size of the DataFlow Buffer that will be used for transferring the data from the source to the destination table.

What should I say – it worked like magic:
I guess that with 2:09 Minutes the clear winner of this test is the configuration with AutoAdjustBufferSize set to True and the DefaultBufferMaxRows to 1048576. It took less then a half of the time with just AutoAdjustBufferSize activated and the insertion process was executed with the help of the Bulk Load API – meaning that we did not have to wait for the Tuple Mover or to execute it manually.

Doubling insertion performance is nothing to scoff, especially for something like columnstore tables, where we expect millions (or more) of rows to be inserted.

Comments closed

Finding Orphaned Users

Published 2016-02-24 by Kevin Feasel

Jon Morisi has a script to find orphaned users:

With the SQL Server 2005 end date approaching, you may find yourself migrating databases.  One of the gotchas with any database migration is orphaned users.  Below is a script I put together, and have been using for years, to help me resolve issues with orphaned users:

I’m not a fan of the sp_msforeachdb in there because there are issues which can cause “each” database to skip databases.  If you have installation authority, Aaron Bertrand’s sp_foreachdb is a better alternative, and if you need to do Insert/Exec calls, there’s a newer version which removes the INSERT/EXEC in that procedure.

Comments closed

Who Changed Their Passwords?

Published 2016-02-24 by Kevin Feasel

Kevin Eckart shows you users with recent password changes:

There may come a time when you need to generate a list of SQL logins and the last time their password was changed. By using the LOGINPROPERTY of the name in sys.server_principals we can generate such a list.

Note: This will only work with SQL logins.

If you want (or need) to know that passwords are being updated but can’t turn on password policies, this at least answers the initial question.

Comments closed

View Tuning

Published 2016-02-24 by Kevin Feasel

Randolph West had to tune a query taking 10-100x too long:

A stored procedure with a single @ProductID parameter would allow us to add WHERE ProductID = @ProductID to both derived tables, which would make the query really fast. In my testing, it ran in under 100ms.

The problem is that this would require numerous code changes to the existing system. Many of the queries that rely on the view also use additional JOINs and WHERE conditions, meaning that a stored procedure is impractical.

Enter the table-valued user-defined function, or TVF.

I’m glad that the TVF worked out for him, but personally, I’m not a big fan of functions.  In this case, though, it does the trick and leaves everyone happy.

Comments closed

Directory Name Is Invalid

Published 2016-02-24 by Kevin Feasel

Erin Stellato troubleshoots a cumulative update installation problem:

SQL Server Setup failure: The directory name is invalid.

The initial email didn’t have a lot of details, so I started asking questions to understand what version was being installed, the environment configuration, etc.  Turns out this was a two-node Windows Server Failover Cluster (WSFC) with multiple SQL Server 2012 instances installed, and one of the instances was still running on the node this person was trying to patch.  To be clear, the two nodes were SRV1 and SRV2, and the instances were PROD-A and PROD-B running on SRV1, and PROD-C which was running on SRV2.  This person was trying to install the cumulative update on SRV2.

The behavior Erin describes is a little bit crazy, but at least there’s a good explanation and way to solve the issue.

Comments closed

Moving Off Of 2005

Published 2016-02-24 by Kevin Feasel

Erik Darling has a short checklist of some things to check before upgrading SQL Server 2005:

MIND YOUR COMPATIBILITY LEVEL

When going to 2014 (as of today, 2016’s RTM hasn’t been announced yet), you’ll have to decide whether or not the new cardinality estimator suits you. There’s not a cut and dry answer, you’ll have to test it on your workload. If you’d like some of the more modern SQL features added to your arsenal, you can bump yourself up to 2012-levels to get the majority of them.

The interesting survey would be, among people who still have SQL 2005 installations, how many will move as a result of Microsoft declaring end-of-life for 2005.  My expectation is a fairly low percentage—by this point, I figure a at least a strong minority of 2005 instances are around for regulatory or compliance reasons (e.g., some piece of regulated software was certified only for 2005).

Comments closed