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 »Performance
Microsoft SQL Server database deployment process
December 14, 2016Introduction
Database deployments are critical tasks that can affect negative in on performance in production. In this article we’ll describe some performance related best practices for database deployments.
Read more »3 ways to improve T-SQL performance
November 22, 2016Introduction
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 »How to create and configure SQL Server Agent Alerts
October 24, 2016Introduction
If you have ever wanted to run a job dependent on a certain performance condition, then this article is for you.
Usually SQL Server agent jobs are configured to run on a schedule. But what if instead of a schedule you want a job to be executed when a certain performance threshold is exceeded?
Read more »SQL Server 2016 Parameter Sniffing
October 19, 2016SQL 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 »Troubleshoot SQL query performance using SQL Server 2016 Live Execution Statistics
August 19, 2016SQL 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 »SQL Server 2016 Trace flags modifications
August 9, 2016SQL 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 »The SQL Server 2014 Resource Governor
July 15, 2016SQL 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 »Best practices to configure the index create memory setting in SQL Server
June 15, 2016Introduction
The Index Create Memory setting is an advanced SQL Server configuration setting which the SQL Server database engine uses to control the amount of maximum memory which can be allocated towards the creation of an index. In this article, we will take a look at the steps to resolve the below mentioned error message.
Read more »Is this the end of SQL Profiler?
June 13, 2016Introduction
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 »Insight into the SQL Server buffer cache
February 18, 2016When 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 »What is causing database slowdowns?
February 2, 2016Why 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 »Tips and tricks for SQL Server database maintenance optimization
January 11, 2016Database maintenance is very important, a critical part of our database administrators’ daily tasks. However, this aspect is frequently underestimated which could lead to performance problems and respectively angry, unhappy customers. In this article, we will take a look at the different maintenance operations we have in SQL Server and how we can optimize them and take the maximum out of each.
Read more »The tempdb database, introduction and recommendations
January 5, 2016Introduction
Many times the people focus on the user databases to improve the performance, but sometimes the problem is not the user database itself. Sometimes the problem is the tempdb.
In this article, we will give a brief introduction about the tempdb database, show how to create some temporary objects there and show how to improve and monitor it.
Read more »Creating an automatic View to an In-line table function conversion stored procedure
December 22, 2015Introducing the Problem
We have a series of views in our organization’s application database. The views definitions are all static queries that consist of few or many condition clauses.
When the application first started with the initial data, the views operated normally, but after a while, after a period where data volume increased, the application’s performance time, through the views usage, degraded.
Read more »Filtered indexes: Performance analysis and hidden costs
December 11, 2015Introduction
Filtered indexes are well documented, as they have been around in SQL Server for almost six years now. Despite their longevity and usefulness, discussions of them tend to be very simple overviews using simple queries and not digging too deeply into more precise costs and benefits. This article is inspired by a production problem that cropped up recently involving a filtered index that illustrated that general knowledge of their function was not as complete as it should have been.
Read more »Using custom reports to improve performance reporting in SQL Server 2014 – running and modifying the reports
September 12, 2014Using custom reports to improve performance reporting in SQL Server 2014 – the basics
September 8, 2014Performance Dashboard Reports in SQL Server 2014
July 29, 2014SQL Server Management Studio performance reports
July 24, 2014Poor SQL query design – a SQL query performance killer – the basics
May 23, 2014Read more »
Inaccurate SQL Server statistics – a SQL query performance killer – updating SQL Server statistics
April 28, 2014Inaccurate SQL Server statistics – a SQL query performance killer – the basics
April 22, 2014SQL Server alerts in troubleshooting performance issues: How to create and use SQL Server alerts
March 15, 2014Before creating a SQL Server alert, make sure that Database Mail and SQL Server Agent properties are properly configured Read more »