Simon Liew

The impact of Residual Predicates in a SQL Server Index Seek operation

March 6, 2017 by

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

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 AnswerIt 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 »
Minette Steynberg

Configuring the Azure SQL Database Firewall

February 28, 2017 by

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 »
Koen Verbeeck

How to partition an SSAS Cube in Analysis Services Multidimensional

February 28, 2017 by

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 »
Gerald Britton

Discovering SQL server instance information using system views

February 28, 2017 by

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 »
Vitor Montalvão

How to use the SQL Server Database Experimentation Assistant (DEA) tool

February 24, 2017 by

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 »
Paul Stanton

How to use Windows hosted file shares to support SQL Server containers

February 23, 2017 by

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 »
Ahmad Yaseen

Saving the Plan Cache storage using the Optimize for Ad hoc Workloads option

February 23, 2017 by

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 »
Mustafa EL-Masry

How to analyze Storage Subsystem Performance in SQL Server

February 23, 2017 by

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 »
Aamir Syed

When to Use Temporary Tables vs. Table Variables

February 21, 2017 by

It is very beneficial to store data in temporary 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 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 temporary 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 »

Monitoring SQL Server Deadlocks – the easy way

February 21, 2017 by

SQL 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 »
Page 2 of 2712345...1020...Last »