Every once in a while, SQL Server database administrators find themselves in disagreements with their application developer counterparts – particularly when it comes to some of the latter’s Transact SQL (T-SQL) developmental practices. One of my first observations when I joined my current employer is that almost all T-SQL scripts written by application developers uses the NOLOCK table hint. However, from the interactions that I have had with these esteemed developers it doesn’t seem like they understand how the NOLOCK table hint works. Furthermore, although they seem to be aware of a distinction between NOLOCK and the WITH NOLOCK table hint, they again do not seem to comprehend how the two differ from one another. In this article, I explore the internal workings of the NOLOCK table hint and examine the implications of omitting the WITH keyword.
Read more »Sifiso Ndlovu
Sifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu
Latest posts by Sifiso Ndlovu (see all)
How to Avoid Package Design Flaws When Sourcing Data From Flat Files
May 10, 2017As developers of SQL Server Integrations Services (SSIS) solutions, we have more than likely configured Flat File Connections as sources in our Data Flow Tasks. Whilst any unforeseen changes to the structure and formatting of flat files will invariably cause SSIS packages to fail, as developers we can still do a lot in reducing unnecessary SSIS package failures relating to data coming out of flat files. In this article, we offer recommended development practices to some flat file source errors that occur as a result of poor SSIS development practices.
Read more »
How to use Columnstore Indexes to improve your Data Warehouse Staging Environment
May 4, 2017My team and I were recently tasked with refactoring older data marts, particularly those that were created with SQL Server 2008 in mind. As we all know, SQL Server has undergone significant changes since the release of SQL Server 2008. One of those changes relates to the introduction of columnstore as an alternative to the traditional B-tree index (rowstore). Whilst most of the existing documentation relating to columnstore seem to focus on the benefit of columnstore against data warehouse workloads, in this article I argue that the usage of columnstore index should not be limited to facts and dimensions instead let’s introduce it in our data warehouse staging environments too.
Read more »
The Return of Standalone Installer for Team Explorer 2017
April 19, 2017The last time I wrote an article relating to Team Explorer, I used it to express my unhappiness with Microsoft’s decision to “break from the norm” and not provide us with a standalone installer for Team Explorer 2015. Such a decision affected development teams that uses Team Explorer to store, collaborate and manage SQL Server-related solutions (i.e. T-SQL scripts, SSRS, SSAS, SSIS) into TFS source control. Well, the good news is that it looks like Microsoft has finally heeded the call of bringing back the standalone installer for Team Explorer as Visual Studio 2017 (available from release 26403.00) now contains a standalone Team Explorer 2017 installer.
Read more »
How to use Package Parts to develop easily maintainable Data Warehouse solutions
March 24, 2017Whilst discussing the evolution of SQL Server Data Tools in my previous article, I touched on a new feature, Package Parts, which is available in SSDT 2015. This article takes an in-depth analysis of the Package Parts feature, particularly, as it relates to Data Warehouse development.
Read more »
The evolution of SQL Server Data Tools (SSDT) for Business Intelligence development
March 9, 2017By all accounts, the introduction of SQL Server Data Tools (SSDT) in SQL Server 2012 was a watershed moment for many SQL Server developers. For better or for worse, SSDT as an IDE for business intelligence development changed – amongst other things – the way we deployed our SSIS packages (i.e. package vs project deployments), simplified Tabular Model development, and also introduced us to the SSISDB. Likewise the replacement of Business Intelligence Development Studio (BIDS) with SSDT had its detractors who were noticeably not very happy that in addition to installing SQL Server 2012 you still had to do a separate download and installation of BI templates for SSDT (previously, BI templates in BIDS were available as soon as you installed SQL Server 2005/2008). Although SSDT-BI is still being offered as a separate installation, subsequent releases of SSDT have included several enhancements changes that should go a long way to winning the hearts of its critics. In this article we conduct a comparative analysis of all versions (up until 16.5) of SSDT and identify all the major improvements that have been introduced in the BI templates.
Read more »
Top 5 Deprecated Features in Reporting Services 2016
February 17, 2017It’s not often that I write negative articles surrounding SQL Server’s latest release but ever since we upgraded one of our BI boxes to run SQL Server Reporting Services 2016 (SSRS 2016), I have picked up on some frustrations from my team when using the upgraded Report Manager portal due to the unavailability of features that used to exist in versions prior to SSRS 2016. I have since realized that in spite of the many exciting features and improvements introduced in SSRS 2016, there is a downside to this latest version of reporting services that is likely to leave many administrators frustrated.
Read more »
How to resolve Excel Timeline Filter Errors caused by SQL Server OLAP Data Sources
January 31, 2017
Over the course of my career, I have developed and deployed to production several business intelligence solutions. These solutions have been consumed using numerous data visualisation tools. One of those data visualisation tools, is Microsoft Excel – which remains a popular data visualisations tool for many of my end users. Like many software products, newer version releases of Excel usually introduce new features. One such feature is the Timeline filter control which was first introduced in Microsoft Office 2013. The Timeline filter control enables end users to visually filter Excel PivotTables using fields defined as dates. The Timeline filter has several benefits compared to the traditional Excel dropdown filter. Figure 1 illustrates one of those benefits whereby the Timeline filter allows end-users to easily apply a filter (i.e. using a scroll bar) again a PivotTable. It also has useful annotations that describes what you have filtered on – in this case, we have chosen sales period between February and March 2011.
Read more »
How to administer SQL Server Reporting Services (SSRS) subscriptions using PowerShell
January 23, 2017In the article Report Subscription Changes in SQL Server Reporting Services 2016, I covered several changes to standard and data-driven subscriptions that were introduced in the release of SQL Server 2016. However all of those changes related to administering report subscriptions using a GUI (i.e. Report Manager Portal, SSRS Configuration Manager). The release of SQL Server Core, SQL Server on Linux and enhancements on Windows PowerShell, reinforces the growing shift by Microsoft to have SQL Server professionals adopt scripting as one of the ways to carry out their daily tasks. In this article we continue to explore SQL Server Reporting Services (SSRS) subscription feature by discussing some of the Windows PowerShell commands that can be utilised to administer report subscriptions.
Read more »
Report Subscription Changes in SQL Server Reporting Services 2016
December 30, 2016What makes SQL Server 2016 one of my favorite SQL Server release since the release of SQL Server 2012 is the increased number of exciting new features that have been introduced. Whilst my article Top 5 New SQL Server 2016 DMVs for DBAs reviewed some of these new features albeit from a DBA point of view, in this article I continue to review SQL Server 2016 but from the perspective of an SSRS administrator in relation to the changes in report subscriptions.
Read more »
Top 5 New SQL Server 2016 DMVs for DBAs
December 20, 2016Since its initial release that was part of SQL Server 2005, Dynamic Management Views (DMV) changed – mostly for the better – the role of database administration (DBA) within SQL Server-based environments. They improved the efficiency of DBAs regarding database server monitoring, issue diagnoses, and subsequent performance optimisation. As a result of this positive reception, it has become customary that some of the highly anticipated items in every release of SQL Server includes discovering what new DMVs will be added. In this article I take you through my favourite top 5 DMVs to come out of SQL Server 2016.
Read more »
How to setup SQL Agent Job alerts to include SSIS catalog errors
December 7, 2016I must have been one of the first people who abandoned BIDS as soon as SSDT was first introduced. Although I have never regretted that decision, I do sometimes feel that SSDT has introduced an unnecessary additional layer of troubleshooting package execution failures. Regardless of whether your SSIS packages are deployed using BIDS or SSDT, the common scheduling mechanism used to run those packages is usually the SQL Server Agent. This is where you are likely to encounter additional layers of troubleshooting SSDT-deployed packages. This is because unlike BIDS where package execution details were stored in the same database (msdb) as were the execution details of SQL Server Agent job, the advent of SSDT came with an introduction a new database – SSISDB – which is used to, amongst other things, store package execution details. Subsequently, in order to retrieve details of package execution, we ought to query the SSIDB database.
Read more »
Two methods for restoring a data warehouse/data mart environment
November 8, 2016Implementing best data warehouse designs and practices such as data lineage reduces the need to ever have to restore an entire relational data warehouse. However, sometimes there are instances whereby you have inherited poorly designed data warehouse environments that leaves you with no other options but to perform an entire database restore in an event of a sudden disaster. I recently found myself in a similar situation of having to recover one of my data mart following a data integrity issue wherein all data of a type 1 dimension was updated/overwritten using an incorrect source file. In this article I take a look at how different approaches can be utilised to restore the compromised SQL Server-based data mart back to its “good state”.
Read more »How to filter multidimensional OLAP cubes in SSRS reports
September 28, 2016Ever since the early days of my career, SQL Server Reporting Services (SSRS) has been one of my preferred data visualization tools simply because end users and developers alike use it for free. Although a majority of my SSRS solutions have been based off a relational dataset that uses Transact SQL (T-SQL), I have also produced several reports that used Multidimensional Expressions (MDX) to connect and retrieve data from SQL Server Analysis Services (SSAS) multidimensional OLAP cube. Recently, I found myself having to refactor some of these SSAS based SSRS reports, particularly converting a single value SSAS-populated parameter into a multi-value parameter. In this article, I explore how you can go about making these changes using SSRS query designer’s design and query modes.
Read more »
How to stop a runaway SSIS package
September 13, 2016There are many reasons for terminating a long running SSIS Package. Picture a scenario whereby an inexperienced DBA/developer accidentally kicks-off a monthly job instead of a daily job, inadvertently impacting SQL Server performance. Whatever the reasons, you are more than likely to encounter a situation in which you have to temporary stop a long running process.
Read more »How to use SQL Server Reporting Services (SSRS) to execute SQL Agent Jobs
August 22, 2016Every production ETL (Extract, Transform, Load) solution is often intrinsically linked to a scheduling mechanism that is used to execute that ETL solution. In a SQL Server-based environment, SQL Server Agent is one of the scheduling mechanism that can be utilized to schedule an execution of ETL solutions such as SQL Server Integration Service (SSIS) packages. In the organization that I currently work for, we’ve had several instances (for various reasons) whereby as the data team we’ve been required to provide a platform for business users to execute an ETL at their own convenience (i.e. on-demand). In this article, we will demonstrate on how we went about delivering self-service ETL execution requirement.
Read more »Top 5 T-SQL functions introduced in SQL Server 2016
August 1, 2016One of the exciting things to look out for in a release of SQL Server are the new built-in T-SQL functions that are usually introduced. Some of the reasons for the new functions may be because SQL Server is catching up to other competitors, attempting to stay ahead of the competition or simply responding to SQL Server feature-requests from the Microsoft Connect site. In this article, I take you through my top 5 T-SQL functions released in SQL Server 2016.
Read more »Convert SQL Server results into JSON
July 12, 2016In my article, Warehousing JSON Formatted Data in SQL Server 2016, we had a look at available T-SQL options for converting JSON data into rows and columns for the purposes of populating a SQL Server based data warehouse. The increased popularity of JSON in modern web applications may create a requirement for data teams to expose some of their data to client applications (i.e. reporting tools, web services etc.) in a JSON format. In this article we take a look at how such a requirement can be implemented by data teams using SQL Server 2016 FOR JSON clause
Read more »Warehousing JSON formatted data in SQL Server 2016
June 22, 2016In this article, I continue to review the exciting features available in SQL Server 2016. One such feature is the long awaited T-SQL support for JSON formatted data. In this article we take a look at how JSON support will impact data warehouse solutions.
Read more »Single package deployment in SQL Server Integration Services 2016
May 31, 2016SQL Server 2016 has some exciting enhancements to SQL Server Integration Services. In this article, we take a look at how the issue of single package deployment into an SSIS Catalog has been resolved with this SQL Server release.
Read more »New Excel slicer feature for report users in Office 2016
May 9, 2016Whilst researching for the article Report filtering: Excel slicers vs SQL Server Reporting Services (SSRS) parameters, I discovered a new Excel Slicer feature in Microsoft Office 2016 that allows users to select/deselect multiple items without having to hold down the control (Ctrl) keyboard key. For some people, this may not be a sufficient reason to upgrade to Office 2016 but for developers of business intelligence (BI) solutions, this new feature further enhances the experience of consumers of BI solutions.
Read more »Report filtering: Excel slicer vs SQL Server Reporting Services (SSRS) parameters
May 4, 2016This article explores the report filtering using Excel Slicer and SQL Server Reporting Services.
Read more »Meaning of the 5127 database snapshot error code
April 15, 2016Long before I turned to Data Warehousing and OLTP replica environments such as Operational Data Stores as a form of data redundancy strategies, I had been using database snapshots. During that time, the analytics and reporting teams were directed towards a database snapshot as a data source for their development. Another benefit of having a database snapshot, is the ease of knowing that should unintended changes be committed in source, you could easily revert the operation by restoring source database off the snapshot.
Read more »TFS tools for managing SQL Server development
March 29, 2016In today’s world of DevOps and ALM practices, there is often a strong emphasis on the visibility and traceability of the work we perform towards software development. This means that as developers, we often have to account (to clients, business owners, project teams etc.) for a list of development tasks that will be undertaken to deliver a given project requirement or user story. Amidst several tools that can be used to facilitate such a practice, Team Foundation Server (TFS) is one such tool that can be used by developers to keep track of their work items.
Read more »Working with ragged right formatted files in SSIS
March 16, 2016In the world of SSIS development architecture, preference should be given to extracting data from flat files instead of non-Microsoft relational databases. This is because you often don’t have to worry about driver support and compatibility issues in your SSIS development/server machine that is often attributed to non-Microsoft database vendors. In fact, I’ve been in several situations whereby we cannot upgrade to another version of SSIS (i.e. BIDS to SSDT) due to the lack of external vendor driver compatibility issues in the newer versions of SSIS.
Read more »