How to secure your passwords with PowerShell

January 18, 2017 by

Introduction Do you have processes or scripts that require you to provide a password? Against the desires of your security officer, do you have to save those passwords in plain text, in your scripts? PowerShell offers a way that you can store a password or prompt the user for the information. You can then utilize […]

Top 5 New SQL Server 2016 DMVs for DBAs

December 20, 2016 by

Since 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, […]

Force query execution plan using SQL Server 2016 Query store

July 29, 2016 by

SQL Server Query Store is a new feature introduced in SQL Server 2016 that is used to automatically and asynchronously capture query execution history, statistics and plans, with minimal impact to overall SQL Server Performance. The Query Store feature makes performance problem troubleshooting simple; you can view the query execution plans changes and compare its […]

SSIS Pipeline performance counters guide

July 14, 2016 by

SSIS Pipeline performance counters SSIS Pipeline performance counters monitor the processes which are related to the execution of packages and the Data flow engine’s the most crucial feature, the (Data) Pipeline. Their measurements reveal in which way the memory resources are acquired during the execution of the SSIS packages, and also, show the amount of […]

PAGELATCH_NL

July 5, 2016 by

Description This wait type accumulates while SQL Server is processing pages in memory. The page is processed in Null mode. This mode is used internally and is not documented by Microsoft. Values in this type are rare to non-existent Resolved by DBAs Suggested solutions Examine and rectify other wait types on the instance (in particular […]

Efficient creation and parsing of delimited strings

July 5, 2016 by

Description Converting a delimited string into a dataset or transforming it into useful data can be an extremely useful tool when working with complex inputs or user-provided data. There are many methods available to accomplish this task; here we will discuss many of them, comparing performance, accuracy, and availability!

Troubleshooting some waits issues

May 10, 2016 by

Background On occasion, I’ll see waits that exceed what I expect well above normal and a few of them have some architecture and standards to consider following when troubleshooting, though like most waits’ issues, there can be other underlying factors that are happening as well. In this article, I investigate the three waits ASYNC_NETWORK_IO and […]

Insight into the SQL Server buffer cache

February 18, 2016 by

When we talk about memory usage in SQL Server, we are often referring to the buffer cache. This is an important part of SQL Server’s architecture and is responsible for the ability to query frequently accessed data extremely fast. Knowing how the buffer cache works will allow us to properly allocate memory in SQL Server, […]

Searching the SQL Server query plan cache

February 8, 2016 by

Whenever a query is executed in SQL Server, its execution plan, as well as some useful execution data are placed into the plan cache for future use. This information is a treasure trove of metrics that can allow some very useful insight into your server’s performance and resource consumption. Much of this information would be […]

What is causing database slowdowns?

February 2, 2016 by

Why is my database so slow? This query used to be so much faster. Why does it take so long to rebuild my index? How come it was fine last month? Every day I am asked these types of questions by clients. Every day! A lot of database developers and application developers do not realize […]

Best practices after installing Microsoft SQL Server

December 15, 2015 by

Introduction Working as a DBA, the simplest task requested from you is installing SQL Server. As a start you will check the server’s hardware and software specs to make sure that you can start the installation, checking which services will be installed and the security authentication type required. After this pre-installation plan, you will start […]

Performance tuning an SSAS Tabular model

March 19, 2015 by

Modeling for the xVelocity/Vertipaq engine is a completely different beast than modeling for your trusty multi-dimensional SSAS cubes. In-memory = blazingly fast; At least that’s what you would think. As Tabular models gain popularity with business users and developers alike, we’re starting to see that this isn’t always the case. We’re going to take a […]

SQL Server 2014 – Install a clustered instance – step-by-step (3/3)

March 12, 2015 by

This article is divided in three parts, if you started here, check the first and second part to find the previous steps. Now we need to define if the instance will work in “Windows Authentication Mode” or “Mixed Mode”. I usually choose “Mixed Mode” because there’s always an application that don’t work with domain accounts. […]

SQL Server Management Studio performance reports

July 24, 2014 by

Basic information about performance on a SQL Server instance is useful to at least scratch the surface of a performance issue. To be able to obtain this information, it’s not necessary to have a specialized monitoring tool that provides tiniest details. SQL Server Management Studio provides features that can quickly help.

Sequence objects feature in SQL Server

April 24, 2014 by

Introduction to Sequences Sequence is a new object that was introduced in SQL Server 2012 for a key-generating mechanism. It has been made available across all editions of SQL Server 2012. It was added as an alternative to IDENTITY columns feature that has been prevalent in the previous versions of SQL Server. Despite being newly […]