As DBAs, we all get to the point where we are asked to setup a new server for a specific environment. Setting up a new server is not a big thing but giving the answer to the question that “how well it will work” might be tricky.
Read more »
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 »
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 »
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 »
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 »
In this article, we will go through the different ways to set the Max Degree of Parallelism option. Before doing that, it is helpful to understand the concept of parallelism, how it works and what the MSXDOP is.
Read more »
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 »
In this article, we will verify if the following statements are true or they are just myths:
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 »
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 »
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 »
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 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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
Database 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 »
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 »
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 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 »
© 2020 Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy
