Performance

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

How to optimize the dimension security performance using partitioning in SSAS Multidimensional

March 27, 2017 by

Introduction

In the articles How to partition an SSAS Cube in Analysis Services Multidimensional and Benefits of Partitioning an SSAS Multidimensional Cube, the concept of measure group partitioning is introduced and the advantages are clearly illustrated. One of the biggest advantages of partitioning is partition elimination, where only the partitions necessary to satisfy the query are read instead of all the data.

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

Indexing SQL Server temp tables

January 31, 2017 by

SQL Server temp tables are a special type of tables that are written to the TempDB database and act like regular tables, providing a suitable workplace for intermediate data processing before saving the result to a regular table, as it can live only for the age of the database connection.

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 »
David Alcock

Using sp_server_diagnostics

January 19, 2017 by

Troubleshooting SQL Server is all about gathering the right evidence. Ordinarily we utilise a variety of different methods and analyse their output to look for specific areas where we would focus our diagnostic efforts. We could for example, use the results of various DMVs to look at wait statistic and resource information to help us focus our investigation in a particular area of SQL Server.

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 »
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/restore, troubleshooting, managing their index’s, etc. and again remotely. So normally, I have setup 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 reorganizing 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 »
Daniel Calbimonte

SQL Server performance myth busters

December 21, 2016 by

Introduction

In this article, we will verify if the following statements are true or they are just myths:

  1. Truncate is faster than a delete and it consumes less space in the logs
  2. Do not use cursors to Create or drop multiple objects because it is very slow
  3. Do not use cursors to convert or process row values because it is very slow
Read more »
Sifiso W. Ndlovu

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, it has become customary that some of the highly anticipated items in every release of SQL Server includes discovering what new DMVs will be added. In this article I take you through my favourite top 5 DMVs to come out of SQL Server 2016.

Read more »
Daniel Calbimonte

3 ways to improve T-SQL performance

November 22, 2016 by

Introduction

When customers used to ask for advice to solve some T-SQL Problem, they would show me their scripts, stored procedures and cursors. I saw horrible things doing that job, some things I do not want to remember, and even some I cannot tell you about 😉 In this article, we will talk about performance problems when using while loops in T-SQL. We will also talk about problems with UNIONsand finally the use of JOINS of two or more tables using the where clause (ANSI 89) instead of using INNER, LEFT or RIGHT JOINS using the from clause (ANSI 92).

Read more »
Ahmad Yaseen

SQL Server 2016 Parameter Sniffing

October 19, 2016 by

SQL Server tries always to generate the most optimized execution plan for each stored procedure the first time that the stored procedure is executed. The SQL Server Engine looks at the stored procedure passed parameter values when compiling the stored procedure, the first execution, in order to create the optimal plan including the parameters and keep that plan for future use in the plan cache. This parameter analysis process is called the Parameter Sniffing.

Read more »
Ahmad Yaseen

Troubleshoot SQL query performance using SQL Server 2016 Live Execution Statistics

August 19, 2016 by

SQL Server Management Studio a graphical interactive that allows you to interact with the databases hosted on your servers. SSMS provides you with the ability to write, edit, execute, analyze and monitor your SQL queries. It also helps database administrators answer important questions about the SQL query performance, such as why a query is slow or why an index is not used. The answer to these questions can be found simply by tracking the Query Execution Plan.

Read more »
Ahmad Yaseen

SQL Server 2016 Trace flags modifications

August 9, 2016 by

SQL Server Trace Flags are special switches that are used to customize and control specific behaviors of the SQL Server Engine. Trace Flags can be defined in two forms; Session Trace Flags that are activated and visible at the current connection level only, and Global Trace Flags that are enabled and visible at the SQL Server Instance level and applied to all connecting sessions in that SQL Server. Global Trace Flags should be enabled globally in order to take effect, where some Trace Flags that can be either Global or Session Trace Flags can be enabled in the appropriate scope, and its effect will appear on the defined level.

Read more »
Ahmad Yaseen

The SQL Server 2014 Resource Governor

July 15, 2016 by

SQL Server Resource Governor was introduced in SQL Server 2008. This feature is used to control the consumption of the available resources, by limiting the amount of the CPU, Memory and IOPS used by the incoming sessions, preventing performance issues that are caused by resources high consumption.

Read more »
Daniel Calbimonte

Is this the end of SQL Profiler?

June 13, 2016 by

Introduction

SQL Server Profiler is still a tool used to monitor our relational databases and our multidimensional ones. We used for performance and security purposes. However, in the SQL Server 2016, they announced that the SQL Profiler will be deprecated in future versions.

Read more »
Ed Pollack

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, gauge accurately how databases are accessing data, and ensure that there are not inefficiencies in our code that cause excessive data to be cached.

Read more »
Kimberly Killian

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 that indexes are ever changing entities within your database or rather they need to be monitored closely and managed periodically to remain efficient. I cannot even count the times someone tells me “but we have index’s on this or that column and it was fine last month” and so on. All while they fail to realize or even tell me that the database just took on, updated or deleted 1,000,000 records for example, which would definitely change the footprint of the data, making the index’s unsound or in need of help. Even adding 50 new users that use the data differently could require new indexes. That being said, I decided to automate a quick and easy data gathering and reporting job that helps to answer these questions. Most of the time query performance questions can be answered by determining the fragmentation levels of index’s, if there are missing index’s, duplicate index’s, unused index’s and what are the heavy hitters in regards to queries and are queries running in memory or to disk and how many executions. My favorite thing to do with SQL Server is automate, automate and automate the tasks that are asked of me over and over.

Read more »