Let’s Get Meta at SQL Saturday Orange County 2017

I’m presenting a session at SQL Saturday, Orange County on April 1, 2017!  Join me for my one hour session, “Let’s Get Meta: an Overview of ETL Frameworks using Biml”.

Do you have SSIS projects that have grown unwieldy, become difficult to troubleshoot or suffer from performance issues? Or maybe you’re getting ready to start a new ETL project and want to learn more about how you can gain efficiency while adding value to your solution?

I’ll be discussing the concept of an ETL framework and the potential elements that can be incorporated within one. We will look at the advantages of implementing a framework based on metadata and look at some database design options for storing that metadata.

To start you off on your framework building journey, I’ll introduce Biml and BimlScript and provide some simple code demos to showcase how Biml can be used to help automate your SSIS package generation. This session will be geared for those with a beginner to intermediate knowledge of SSIS and SQL. While we won’t be teaching Biml in great detail, you’ll get an overview of the technology and pointers on where to learn more.

Hope to see you there!

Row Counts in SQL Upgrade

Row Counts Incorrect after Upgrading to SQL Server 2016

When Row Counts in SQL Server Go Bad

When it comes to getting table row counts in SQL Server, there are a few different options available.1 Some are more accurate than others and it turns out that others can be even less accurate after an upgrade. .

As part of a particular ETL process, I’ve got a row count check at the end of my data transfer that verifies that the Source and Target row counts are equal. Some of my tables have almost one billion rows, and running a SELECT COUNT(*) on every table was taking too long (not to mention the potential for locks). The databases I’m working with are not OLTP databases, so there are no writes going on other than my ETL. Microsoft does say that sys.partitions.rows provides an approximate number of rows, but up until now, the following query has gotten me accurate row counts when used at the end of my ETL process:

SELECT ta.name as TableName,  SUM(Pa.rows) as RowCnt
FROM   
    sys.tables ta INNER JOIN  
    sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID    
WHERE 
    ta.is_ms_shipped = 0 AND 
    pa.index_id IN (1,0) 
GROUP BY  ta.name

This week, I upgraded to SQL 2016 and all of the sudden the same query on the same tables at the same time with the same indexes started giving me incorrect row counts in about 10% of my tables.

MSDN Documentation to the Rescue?

According to the MSDN page, DBCC UPDATEUSAGE “reports and corrects pages and row count inaccuracies in the catalog views”. So I ran DBCC UPDATEUSAGE. But still no match on my row counts.

After a bit more digging… I found the critical piece that I was missing. Apparently, though I don’t feel Microsoft does a great job of making this clear, DBCC UPDATEUSAGE only updates the page inaccuracies – ir doesn’t fix row count inaccuracies unless you specify the “WITH COUNT_ROWS” argument.

So I ran DBCC UPDATEUSAGE(myDBName) WITH COUNT_ROWS against my newly upgraded database and everything was right in the world. Well, Trump is still running for President, so I suppose it didn’t fix everything, but my row counts are accurate once again, so I’ll settle for that… for now.

1. Jes Borland (@grrl_geek) wrote a great article on those here, so I won’t go into those details.

Senior SQL Developer

For the last 12 years I have worked as the Senior SQL Developer at ACSS, an aerospace manufacturer in Phoenix Arizona. My responsibilities included:

  • Design, Develop, and Implement Custom ERP Solutions
  • SQL Server Development and Design
  • Infor Visual ERP Implementation and Administration
  • Business Intelligence Analysis and Design
  • SQL Server Reporting Services (SSRS)
  • Manufacturing, Quality Management, Supply Chain and Financial Reporting
  • .net Application Development
  • Legacy Application Support including VB6 and Crystal Reports 8.5
  • SOX Compliance

If you are interested in talking with me about a Reporting project that you have I am always interested. Call me at 602-618-5821 or click here to email me.