SQL Server performance tuning

Neeraj Prasad Sharma
C:\Users\Neeraj\Desktop\TOP\IMAGES\FinalSerialNL.JPG

SQL Server TOP clause performance problem with parallelism

April 13, 2017 by

Basics of TOP Operator

The TOP keyword in SQL Server is a non-ANSI standard expression to limit query results to some set of pre-specified rows. As an argument, it takes 0 to positive Bigint (9223372036854775807) and anything beyond or less gives an error message. TOP without an order by clause, in production, seems buggy because it can produce results in any order depending on the current schema and execution plan. You can also specify a percent with TOP expression which returns only the expression percent of rows from the result set. Float expressions used in TOP percent are rounded up to the next integer value.

Read more »
Minette Steynberg

Understanding the XML description of the Deadlock Graph in SQL Server

March 29, 2017 by

Introduction

In my previous 2 articles, What is a SQL Server Deadlock and Understanding the graphical representation of the SQL Server Deadlock Graph, I discussed what a deadlock is, how SQL Server handles deadlocks, some tips on how to reduce deadlocks and ultimately what information you can glean by just looking at the graphical representation of the Deadlock Graph.

In this article, I will look at what information is available in the XML description of the Deadlock Graph. The XML contains information which is not visible in the graphical representation of the Deadlock Graph, which makes it necessary for us to look at the XML description.

Read more »
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 »
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 »

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

Using Memory-Optimized Tables to Replace SQL Server Temp Tables and Table Variables

January 24, 2017 by

TempDB usage can be considered as a performance bottleneck for workloads that use temp tables and table variables intensively resulted in heavy IO usage. A valuable alternatives for the temp table and table variable are SCHEMA_ONLY Memory-Optimized tables and the Memory-optimized Table Variable, where the data will be completely stored in the memory without the need to touch the TempDB database, providing the best data access performance. SCHEMA_ONLY Memory-Optimized table and the Memory-optimized Table Variable are stored only in the memory with no related component in the disk. It involves no IO activity or TempDB utilization. It can also participate in the transactions without the need to log the transactions.

Read more »
Ed Pollack

SQL Server Job Performance – Reporting

January 19, 2017 by

Description

Once collected, job performance metrics can be used for a variety of reporting needs, from locating jobs that are not performing well to finding optimal release windows, scheduling maintenance, or trending over time. These techniques allow us to maintain insight into parts of SQL Server that are often not monitored enough and prevent job-related emergencies before they become emergencies.

Read more »
Ed Pollack

SQL Server Job Performance – Tracking

January 19, 2017 by

Description

Keeping track of our SQL Server Agent jobs is a very effective way to control scheduling, failures, and understand when undesired conditions may be manifesting themselves. This is a brief journey towards a solution that provides us far greater insight into our jobs, schedules, and executions than we can glean from SQL Server by default.

Read more »

How to install the SQL Server Utility Control Point and configure multiple instances for monitoring

January 16, 2017 by

As we progress in our career as a SQL Server Database Administrators we encounter situations where managing and monitoring large number of SQL Server is the daily job requirement. Monitoring SQL Server is a very vast and detailed area of study. Out of all the monitoring the very basis and critical monitoring in the Resource Utilization Monitoring for SQL Server.

Read more »
Kimberly Killian

SQL Server Index vs Statistics a consultants woes…or rants

December 29, 2016 by

As a DBA, I am often asked why is something performing slow, what and why statistics need to be updated or what will cause them to be “off”. My initial question to clients when they pose these questions to me is what changed on your end? Did the data change significantly and did the rebuild or reorganize index job run? Before I get into the answers to these questions from my clients, let me give you some background. So, just to clarify, for most of my clients, I work as a remote part-time DBA, that being said, I manage their database from every aspect including setting up servers, backups/restored, troubleshooting, managing their index’s, etc. and again remotely. So normally, I have setup a jobs that will manage their index’s ranging from a weekly rebuild or even sometimes I use one that I’ve designed that makes a choice to either rebuild or reorganize an index based on fragmentation level. The “general rule of thumb” is reorganize the index for fragmentation from 5% to 29% and rebuild when 30% plus. Those are pretty standard numbers I did not make them up.

Read more »
Page 1 of 712345...Last »