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 »
Introducing 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 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 »
Read more »
Before creating a SQL Server alert, make sure that Database Mail and SQL Server Agent properties are properly configured Read more »
Why is SQL Server alerting important?To be aware of what’s going on with your system and how SQL Server performs, monitoring is necessary. However, just monitoring might not be enough. Being notified about a performance issue whenever a specific SQL Server counter meets a threshold value, or a specific event occurs, boosts monitoring usability. These notifications enable DBAs to be aware of every important system performance change as soon as it happens, and therefore they can act immediately to fix the issue and prevent further problems Read more »
sys.dm_os_performance_counterssys.dm_os_performance_counters is a system Dynamic Management View (DMV) that returns one row for each SQL Server performance counter. It’s useful for obtaining information about current performance counter values. These counter values are also shown in Windows Performance Monitor, besides other operating system counters Read more »
Read more »
In the previous part of this series, we presented the processor metrics that indicate hardware component problems that can affect SQL Server performanceIn this part, we will present processor metrics that are not necessary in every performance monitoring scenario, but are useful for deeper investigation and troubleshooting Read more »
In SQL server processor performance metrics – Part 1 – The most important cpu metrics, we described the Processor Queue Length and two most important processor time counters – Processor % Processor Time and Process % Processor timeIn this part, we will continue describing important time processor counters Read more »