Today the subject of investigation is the Temporal Table, which is a new feature in SQL Server 2016. My focus will slightly be on how to use it in Data Warehouse environments, but there is some general information passing by as I write.
Read more »
How to automate SSAS tabular model processing in SQL Server 2016
March 6, 2017There are many ways to process your SSAS Tabular Model. This can be achieved in SSIS using the Analysis Services Execute DDL Task or manually, through Management studio GUI but to have a little fun & make the task more flexible I’m going to script this with ASSL/TMSL & build a notification round it. We can then schedule this as a step in a SQL agent job, call it from SSIS or PowerShell.
Read more »
The impact of Residual Predicates in a SQL Server Index Seek operation
March 6, 2017Abstract
It is common assumption that an Index Seek operation in a query plan is optimal when returning a low number of output rows. In a scenario involving residual predicates, an Index Seek operation could be reading a lot more rows than it needs into the memory, then each row is evaluated and discarded in memory based on the residual predicate and returns low number of output rows.
This article will explain the concept and the impact of Residual Predicates in a SQL Server Index Seek operation.
Read more »
How to reconnect to a SQL Server instance when all credentials have been lost
March 3, 2017The Problem
I came across a strange and rare situation at a client recently, where they asked me to resolve a problem on Microsoft SQL Server but did not give me any credentials to connect to the system. I asked them to state the actual problem and the reply was “we lost all credentials”. A number of questions started to generate in my mind. How are applications running? The Answer was “The application login is embedded in a DLL and it’s a third party application which we don’t have support”. How can you lose all the SQL Server credentials? The Answer “It was saved in a file and it was lost and the employee who knew it is no more working with the company”. Anyways, I had no choice but to have a solution for this strange problem.
Read more »
Configuring the Azure SQL Database Firewall
February 28, 2017Introduction
The Azure SQL Database firewall lets you decide which IP addresses may or may not have access to either your Azure SQL Server or your Azure SQL database.
When creating an Azure SQL Database, the firewall needs to be configured before anyone will be able to access the database. By default, no external access to your SQL Database will be allowed until you explicitly assign permission by creating a firewall rule.
Read more »
Hybrid Cloud and Hekaton Features in SQL Server 2014
February 28, 2017Introduction
Microsoft SQL Server 2014 is considered to be the first version that supports Hybrid Cloud by adding a lot of exciting new features.
In this article, I will cover some of the top new features in these main points including Hekaton and Hybrid Cloud enhancements:
Read more »
Benefits of Partitioning an SSAS Multidimensional Cube
February 28, 2017Introduction
In the article How to partition an SSAS Cube in Analysis Services Multidimensional, we explained how you can partition your measure groups in an SSAS cube. In this article, we’ll look at the expected benefits of the partition strategy. Time to reap the benefits of our hard work.
Read more »
How to partition an SSAS Cube in Analysis Services Multidimensional
February 28, 2017Partitioning is the concept where you divide your data from one logical unit into separate physical chunks. This can have several advantages, such as improved performance or easier maintenance. You can for example partition a table in a SQL Server database, but you can also partition your measure groups inside an Analysis Services (SSAS) Multidimensional cube. In this article, we’ll discuss how you can set-up partitioning. For more information about the expected benefits, take a look at Benefits of Partitioning your SSAS Multidimensional Cube.
Read more »
Discovering SQL server instance information using system views
February 28, 2017Introduction
Out of the box, SQL Server comes with a substantial and – release by release – ever-growing set of system tables, views, stored procedures and functions. There’s a good chance you’ve never directly used more than a handful of them. That’s certainly the case with me!
This is the first article in a series designed to explore this world that lives just below the surface of our everyday interactions with SQL Server through the same objects we create to enable the applications we write and support.
Read more »
How to use the SQL Server Database Experimentation Assistant (DEA) tool
February 24, 2017Introduction
This is my second article about Database Experimentation Assistant (DEA). Please refer to my previous article to see how to download and install DEA.
The Database Experimentation Assistant is the new A/B testing solution for SQL Server upgrades. It enables customers to gather performance insights for upgrades by customers to conduct experiments on production database workloads across two versions of SQL Server.
Read more »
How to use Windows hosted file shares to support SQL Server containers
February 23, 2017In a previous article, Automate Delivery of SQL Server Production Data Environments Using Containers, we introduced SQL Server containers for delivery of production data environments to development and QA teams. In this article we look at the methods used for working with SQL Server data, and use of file shares to support delivery of production databases with containers.
Read more »
SQL Server Common Table Expressions (CTE)
February 23, 2017What is a Common Table Expression
A Common Table Expression, also called as CTE in short form, is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. The CTE can also be used in a View.
In this article, we will see in detail about how to create and use CTEs from our SQL Server.
Read more »
Saving the Plan Cache storage using the Optimize for Ad hoc Workloads option
February 23, 2017When you run a query in SQL Server, the SQL Server Query Optimizer will draw the road map for that query, specifying the optimal way to execute it, which is called the query execution plan.
Generating the execution plan will take few milliseconds from the CPU cycles, which is negligible for one query or small load, but it will be considerable for a very heavy transactional workload. Because of this, SQL Server caches these generated plans in a special type of memory called the Plan Cache to eliminate the overhead generated by the query plan if the same query is executed again. When you submit your query to the SQL Server Engine, it will search in the plan cache if there is any existing execution plan that can be reused, if an available execution plan is found in the plan cache, the plan will be used to execute that query, otherwise, the SQL Server Query Optimizer will create a new plan and keep it in the plan cache for future use.
Read more »
How to analyze Storage Subsystem Performance in SQL Server
February 23, 2017Introduction
To improve performance, it is common for DBAs to search in each aspect except analyzing storage subsystem performance even though in many times, issues are, in fact, caused by poor storage subsystem performance. Therefore, I want to give you some tools and recommendation that you can use it to prevent your storage subsystem from being a performance issue for you.
Read more »
How to import flat files with a varying number of columns in SQL Server
February 22, 2017Ever been as frustrated as I have when importing flat files to a SQL Server and the format suddenly changes in production?
Commonly used integration tools (like SSIS) are very dependent on the correct, consistent and same metadata when working with flat files.
So I’ve come up with an alternative solution that I would like to share with you.
Read more »
When to Use SQL Temp Tables vs. Table Variables
February 21, 2017It is very beneficial to store data in SQL Server temp tables rather than manipulate or work with permanent tables. Let’s say you want full DDL or DML access to a table, but don’t have it. You can use your existing read access to pull the data into a SQL Server temporary table and make adjustments from there. Or you don’t have permissions to create a table in the existing database, you can create a SQL Server temp table that you can manipulate. Finally, you might be in a situation where you need the data to be visible only in the current session.
Read more »
How to Merge and Split CSV Files Using R in SQL Server 2016
February 21, 2017Introduction
From time to time, we may encounter the following scenarios when dealing with data processing:
Read more »
How to retrieve information about SSIS packages stored in MSDB Database
February 21, 2017Introduction
Nowadays, most mid-size companies have implemented a Data Warehouse (DWH) solution. This DWH can be designed using a set of tools (or features) from Microsoft SQL Server. One of them is SQL Server Integration Services, also known as SSIS.
Read more »
Monitoring SQL Server Deadlocks – the easy way
February 21, 2017SQL Server is a very powerful tool and wherever I go, I see the tool being way much underutilized. Some people even don’t know about the features which are already in the SQL Server and they have not used it, like SQL Server Extended Events. Though, Extended Events is a way more complex and detailed topic which I cannot cover in just one article I will discuss one of its very good utilities out of the countless.
Read more »
Monitoring Memory Clerk and Buffer Pool Allocations in SQL Server
February 20, 2017The following article applies to SQL Server versions 2008 +
Adequate memory is one of the most important factors for a well-functioning instance of SQL Server. By design SQL Server manages its own memory allocations via the SQLOS rather than having the servers Operating System perform this task.
Read more »
How to query data in a System-Versioned Temporal Tables in SQL Server
February 20, 2017This article will cover the querying of temporal tables in SQL Server by using the FOR SYSTEM_TIME clause and its four sub clauses AS OF, FROM TO, BETWEEN AND, CONTAINED IN. Also, we’ll cover how to clean up the history table to keep it a manageable size.
Read more »
Functions and stored procedures comparisons in SQL Server
February 20, 2017Introduction
In a previous article, Functions vs stored procedures in SQL Server, we compared Functions vs stored procedures across various attributes. In this article, we will continue the discussion. We will talk also about Table-valued functions and compare performance with stored procedures with table valued functions and scalar functions.
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 »
Sanitizing Inputs: Avoiding Security and Usability Disasters
February 17, 2017Introduction
In any application, we will likely have some need to control input data, either altering, filtering or otherwise changing text to fit our application’s needs.
Read more »
Top 5 new features in SQL Server for developers
February 15, 2017Introduction:
Microsoft SQL Server 2012 introduces many features that help database administrators, database developers, and BI developers.
In this article, I will cover some of the new features for database developers in these main points:
Read more »