SQL Server Buffer Pool in action March 9, 2017 by David Alcock SQL Server retrieves data from two areas; memory and disk. As disk operations are more expensive in terms of IO which means they are much slower SQL stores and retrieves data pages from an area known as the Buffer Pool where operations are much faster. Read more »
The evolution of SQL Server Data Tools (SSDT) for Business Intelligence development March 9, 2017 by Sifiso W. Ndlovu By 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 »
How to work with filegroups in SQL Server and migrate data between them March 8, 2017 by Kaloyan Kosev As you may already have figured out, the default settings in SQL Server are not always the best. Such is the case when you are working with new user databases; usually you get a single data (*.mdf) and transaction log (*.ldf) file. The data file resides within the PRIMARY file group; the only one we have so far, and it will store all of our databases objects, system objects, user tables, user stored procedures and all other objects. In some cases this default configuration may be good enough for you, but let us cover why would you prefer a different configuration in your production environment. Read more »
How to create indexes on SQL Server computed columns March 8, 2017 by Ahmad Yaseen A SQL Server Computed Column is a virtual column that is not stored physically on the table, unless it is previously specified as PERSISTED. A computed Column value is calculated using a specific expression that can be constant, function, data from other columns on the same table or a combination of these types connected together by one or more operators. Read more »
How to import/export JSON data using SQL Server 2016 March 7, 2017 by Marko Zivkovic JSON is an abbreviation for JavaScript Object Notation. JSON is very popular and currently the most commonly used data exchange format. Most modern web and mobile services return information formatted as JSON text, all database web services, web browsers (Firefox, Internet Explorer) return results formatted as JSON text or accept data formatted as JSON. Since external systems format information as JSON text, JSON is also stored in SQL Server 2016 as text. You can use standard NVARCHAR columns to store JSON data in SQL Server 2016. Read more »
Temporal Table applications in SQL Data Warehouse environments March 7, 2017 by Hans Michiels 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, 2017 by Craig Porteous There 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, 2017 by Simon Liew Abstract 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, 2017 by Musab Umair The 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, 2017 by Minette Steynberg Introduction 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, 2017 by Mustafa EL-Masry Introduction 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, 2017 by Koen Verbeeck Introduction 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, 2017 by Koen Verbeeck Partitioning 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, 2017 by Gerald Britton Introduction 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, 2017 by Vitor Montalvão Introduction 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, 2017 by Paul Stanton In 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, 2017 by Syed Shanu What 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, 2017 by Ahmad Yaseen When 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, 2017 by Mustafa EL-Masry Introduction 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, 2017 by Brian Bønk Rueløkke Ever 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 »
