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.
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 […]
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:
It 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 […]
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 […]
I have been using SSMS for a long time now, and over the period of time I have realized that it’s a very powerful IDE – however, there are lots of hidden features which are less known to lots of developers. In this article, I will be sharing some beneficial Tips and Tricks which will […]
SQL Server 2016 SP1 is released as announced by Microsoft. It comes with a bunch of new features and enhancements as a result of customer and community feedback. In this article, I will introduce you to these new features and enhancements.
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, […]
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 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 […]
SQL Server 2016 introduced officially on the 1st of June 2016. It comes with many new features such as Query Store that maintains the execution plans history for the queries with its performance data in order to detect any slowness in the query caused by the new plans, so that the administrator can force the use of […]
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 […]
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!
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 […]
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, […]
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 […]
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 […]
Background If an environment chooses to use snapshot or transactional replication, one useful exercise is to ask the technical end user (or client) what they think replication does. If you have access to a white board, you can even ask them to demonstrate what they think replication will do for their data. Generally, these technical […]
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 […]
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 […]
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. […]
Introduced on SQL Server 2014, the Buffer Pool Extension came to build up a new layer of cache, helping to maintain the “hot” pages accessible with less effort. With the introduction of Hekaton, the BPE has also an important role. In this article we will explore all the aspects of this new feature as well […]
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.
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 […]
Monitoring SQL Server performance is a complex task, as performance depends on many parameters, both hardware and software. It’s recommended to monitor these parameters proactively in order to prevent any potential performance degradation. However, this is not always the case. If the performance degradation happens, the first step towards the resolution is to determine the […]
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy