SSIS Talk

Tonight is the first 2012 SSUG meeting for the Omaha area. The agenda for tonight is simply to have an open discussion on any topics you want to bring up – a current SQL issue you’re working on, upgrade problems, architecture questions, etc… I will be in attendance helping with the discussion.

Please register if you plan on attending so that the hosts can arrange enough food as well as to ensure you get registered for SWAG!

http://omahamtg.com/Events.aspx?ID=142

Saturday, August 27th, in Omaha, NE, we are hosting a SQL Saturday event that is promising to be a great time. This is a free event ($10 lunch fee if you want lunch on us) that helps promote our local SQL PASS chapter. Register here: http://www.sqlsaturday.com/91/eventhome.aspx

I will be presenting on two topics: SSIS changes in Denali, and Using Event Notifications for Monitoring SQL Instances.

The first session, SSIS Changes in Denali, is pretty self-explanatory. Expect that I will go through each of the major changes in demo form, with some discussions around each.

The second session around Event Notifications, will be quite a bit more advanced and will talk about using the Event Notifications infrastructure in SQL Server combined with Service Broker to implement a very robust monitoring system. This infrastructure allows for capturing all kinds of audit information such as creating a table, dropping an index, rebuilding statistics, blocking, deadlocks, etc… There’s quite a bit of information that can be captured. I will show how to set this up and how to consume the information for monitoring purposes.

Don’t miss your chance at seeing me speak (!) and most importantly don’t miss your chance at learning SQL Server topics for free. See you there!

An event flyer can be found here: http://www.ssistalk.com/Flyer_updated.pdf Please distribute as you see fit!

When trying to investigate the SQL error, “Error: 18456, Severity: 14, State: 38.” it can be frustrating when not a single log on the system will tell you what the actual problem is. In the case of state 38 (16 or 27 prior to SQL 2008) this error means the database specified in the client connection does not exist, or is offline. In a large shop with many various clients it can be hard to find out the source of the connection, so you’re option is to use the tools at your disposal.

The SQL Error Log does not help much at all. Here’s what’s in the SQL Error log:
2011-05-06 09:06:17.28 Logon Error: 18456, Severity: 14, State: 38.
2011-05-06 09:06:17.28 Logon Login failed for user ‘DOMAIN\ssrs.service’. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.0.147]

The Windows Application log is not much help either.
Login failed for user ‘DOMAIN\ssrs.service’. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.0.147]

In other words, useless for troubleshooting. The way to obtain the database name the client is trying to connect to is to use SQL Server Profiler.

To set up SQL Server Profiler, connect to the SQL instance where the error is occuring and then track the following events:
Errors and Warnings: User Error Message
Security Audit: Audit Login Failed

For these two events, make sure you capture at least the following columns: ApplicationName, HostName, LoginName, SPID (required), StartTime, TextData (where the message text will be), Severity, State, ClientProcessID, and Error.

Then run the trace. You’ll see the following data under the “User Error Message” event when the login failure occurs:
Cannot open database “DatabaseName” requested by the login. The login failed.
For the “Audit Login Failed” event you’ll see the following data:
Login failed for user ‘BLUENE\ssrs.service’. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.0.147]

Using this information (and the rest of the info in the other columns you selected), you should be able to go to the source and identify which process is trying to connect to the specified database and take appropriate action.

If you’re interested in the other states that error 18456 can generate, please visit fellow MVP Aaron Bertrand’s page on this topic for a very nice listing.

Attached is the SQL Profiler Trace Template for SQL 2008 instances if you want to import it instead of manually configuring SQL Profiler. To import the template, in SQL Server Profiler, go to File->Templates and select Import…

SQL_Login_Failures_Profiler_Template.tdf (860 bytes)

The first public CTP of the next major version of SQL Server is now available. Go and get it, kick the tires, and let the team know about any bugs you may find.

Some of the immediate changes to highlight in CTP1 are: SSMS is now based on Visual Studio, sequence generators, several new DMV/DMFs (including one to investigate the resultset of a query/sproc without actually executing it), centralized SSIS management, SSIS changes, and others.

http://www.microsoft.com/Presspass/press/2010/nov10/11-09PASS10PR.mspx?rss_fdn=Press%20Releases

Stay tuned here for some highlights in Denali CTP1, especially around SSIS.

For those of you that work extensively with sys.dm_exec_query_stats and associated plan cache DMVs, be aware that altering the server’s ‘max degree of parallelism’ setting will flush the query plan cache.

You may see this message in your server’s SQL Log file:
“SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘SQL Plans’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.”

In the case of MAXDOP changing, if you look before the above event happened, you should see that MAXDOP was changed and hence caused the flush.

This makes sense. If you are changing the MAXDOP setting, all current query plans are worthless as they’re out of date with respect to this parameter. Some plans may now be able to benefit from parallelism, whereas before they could not. Or if constraining MAXDOP, some parallel plans may not be able to work anymore. (Or some combination thereof.) The easiest way to rectify this issue is to simply flush the plan cache and let all plans recompile at their next execution.

A common situation for changing MAXDOP could be for maintenance operations, where during normal operations MAXDOP is constrained to 1, yet while doing weekly work during a maintenance window the DBAs may elect to set MAXDOP to 0 (unrestricted).

So, when you’re scratching your head wondering where your plan cache went, it could be due to administrative tasks.

Teradata offers the ability to build a query, and then use a QUALIFY statement that filters the resulting resultset. This is especially handy when using a ROW_NUMBER function where you are partitioning on a natural key (or whatever it may be) and you want to return only the rows where the ROW_NUMBER value is 1, for example.

In SQL Server today, this has to be done with derived tables/CTEs like so:

USE AdventureWorksDW
GO
/* Ignoring the fact that this table
   has a "Status" and an EndDate column */
;WITH CTE AS (
SELECT EmployeeKey,
       FirstName,
       LastName,
       MiddleName,
       Title,
       ROW_NUMBER() OVER (PARTITION BY EmployeeNationalIDAlternateKey
                              ORDER BY StartDate DESC) AS ROW_NUM
  FROM dbo.DimEmployee
)
SELECT EmployeeKey,
       FirstName,
       LastName,
       MiddleName,
       Title
  FROM CTE
 WHERE row_num = 1
 ORDER BY FirstName, LastName
GO

That’s easy enough to understand, of course, but the problem lies in the fact that you have to write a bunch of extra lines of code to accomplish this task.

Teradata has the QUALIFY option which would be represented like this:

 SELECT EmployeeKey,
        FirstName,
        LastName,
        MiddleName,
        Title
   FROM dbo.DimEmployee
QUALIFY ROW_NUMBER() OVER (PARTITION BY EmployeeNationalIDAlternateKey
                               ORDER BY StartDate DESC) = 1

Note how much cleaner that looks. QUALIFY has many advantages, but I’ll leave that as an exercise for you to review. In the mean time, if you would like to see such an option in SQL Server, please vote on Itzik Ben-Gan’s feature request: https://connect.microsoft.com/SQLServer/feedback/details/532474/qualify-request-for-a-new-filtering-clause

If you have something that you’d like to see blogged here regarding SSIS and/or SQL Server, please let me know and I’ll see what I can do.

Also, if you find a post here that you’re using to work on an issue in SSIS 2008, let me know if it isn’t working for you. It could be that I don’t have updated content for use in the SSIS 2008 environment. It’s easy enough to update, I just won’t be able to catch all of the items listed here.

One last thing – the annual MVP Summit is coming up in February. If you have something that you’d like for me to bring to the SSIS dev team in terms of feedback, feature requests, bugs, etc…, let me know and I’ll do my best to present them to the team.

A collection of 53 Microsoft MVPs (myself included) have wrapped up authoring one of the greatest SQL Server books yet to hit the shelves – SQL Server MVP Deep Dives.

This book brings together the experience from all corners of SQL Server: Design & Architecture, Development, Administration, Performance Tuning & Optimization, and Business Intelligence. Each section contains unique topics ranging from avoiding common query mistakes, running SQL Server on Hyper-V, to SSIS performance tuning.

The book is available to pre-order here: http://www.sqlservermvpdeepdives.com

(EDIT: Save 50% by purchasing the book from the above site by the end of September when you use the code, pop0928)

All author royalties – and this is what really makes this book stand out – are being donated to War Child International, a charity involved in bringing food, health, and happiness to kids across the world involved in war.

Many thanks to the editors for pushing this book through to completion: Paul Nielsen, Kalen Delaney, Greg Low, Adam Machanic, Paul S. Randal, and Kimberly L. Tripp

For more information on the book, continue reading:
(more…)

There seems to be some misinformation going around about how SQL Server works with ordered data, especially with how the data is *physically* stored, even with a clustered-indexed table.

Please see this post for more details on SQL Server ordering: http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

To add to that list a bit:

• Updates are (almost) the same as select queries, internally, in that updates are turned into selects internally, which means the ordering rules of selects are also at play
• Updates can have parallelism, meaning that updates do not have to preserve update order
• Update order can’t be guaranteed because clustered indexes can be rebuilt with a different order
• Ordering generally happens at the operator level within a query plan; not at the top
• Unless you’ve seen it in BOL or in a MS Blog post, don’t assume anything with respect to SQL Server ordering – even if your queries operate on a given order today!

The PASS team has assembled a unique opportunity for those wishing to expand their SQL Server knowledge, be it in database administration, business intelligence (including, of course, SSIS), blogging, etc…, can attend one or more sessions (out of a total of 24) for free by registering here: http://24hours.sqlpass.org/

No matter where in the world you are, there should be a session available that will appeal to your knowledge craving noggin, so don’t hesitate, sign up today.

The session is officially on Sept 2nd, 2009, but depending on your time zone, it may be Sept 1st or 3rd, so please be sure to check out their time zone information.