Extended Events provide a way of unintrusively monitoring what's going on in a SQL Server instance. Unlike SQL Server Profiler and SQL Trace, it has little performance impact. Now, in SQL Server 2012 SSMS, it is relatively easy to use, as Robert Sheldon shows.… Read more
Everyone's SQL Server nightmare: The instance will not start. If such a problem strikes you, keep calm, follow Gail's advice, and you'll soon be back up and running. In the meantime, practice these steps so as to be ready! … Read more
There are seven common antipatterns in TSQL coding that make code perform badly, and three good habits which will generally ensure that your code runs fast. If you learn nothing else from this list of great advice from Grant, just keep in mind that you should 'write for the optimizer'.… Read more
SQL Server AlwaysOn provides a high-availability and Disaster-recovery solution for SQL Server 2012. It makes use of existing SQL Server features, particularly Failover Clustering, and provides new capabilities such as availability groups. Warwick Rudd explains the basics and shows you how to implement it.… Read more
In this excerpt from his book Troubleshooting SQL Server: A Guide for the Accidental DBA, Jonathan Kehayias provides a guide to identifying the causes, reacting to, and ultimately preventing the dreaded deadlock.… Read more
It's easy to create a database nowadays with point-'n-click, but if you've left your database's autogrowth settings at their default, you may hit problems in the future. Why? What do I do about it? Read on!… Read more
When healing a sick SQL Server, you must forget the idea that there could ever be a simple correspondence between symptom and disease: The art of troubleshooting is much more the art of discovering, and assembling, the various pieces of the puzzle so that you have a complete understanding of what is going on inside of a server… Read more
Sean Duffy recently had an unwelcome encounter with Exchange Server Back Pressure, which cut off his message flow due to a lack of space on the server. To make sure it didn't happen again, he found a way to automatically monitor all his servers from afar, with a little PowerShell magic.… Read more
To understand how to write SQL code for SQL Server that performs well, it is important to appreciate how the query optimizer works. Ben Nevarez explains the essentials, in a broad sweep through a complex subject, in an article taken from his new book 'Inside the SQL Server Query Optimizer'.… Read more
Since the introduction of SQL Server 2005, there is a simple lightweight trace that is left running by default on every SQL Server. This provides some very valuable information for the DBA about the running server, but it isn't well-documented. Feodor reveals many of the secrets of this facility and shows how to get reports from it.
… Read more
SQL Server is able to make implicit use of parallelism to speed SQL queries. Quite how it does it, and how you can be sure that it is doing so, isn't entirely obvious to most of us. Paul White begins a series that makes it all seem simple, starting at the gentle level of counting Jelly Beans
… Read more
SQL Server Statistics assist the query optimiser to calculate the best way of running the query. Holger describes every common way that things can go wrong with statistics, and how to put matters right.
… Read more
Sometimes, all a DBA needs, to help with day-to-day work, is a checklist of best-practices and dos and don'ts. It provides a handy reminder. Brad has come up with a new update to his famous checklist… Read more
The Transaction Log provides the means by which either the complete set of tasks of a database transaction are performed or none of them are. It ensures that , via rollback, only valid data is written out to the database, and it allows transactions to be played back to recreate the system state right before a failure. Robert Sheldon explains the various basic tasks involved in managing the transaction log.… Read more
In this article, taken from Chapter 5 of his great new book, SQL Server Tacklebox, Rodney describes in his own unique style how he, as a working DBA, goes about troubleshooting
problem queries, and investigating various types of locking and blocking
problems. In the process, he passes on valuable tips learned from
practical, and sometimes stressful, experience.… Read more
To tackle performance problems with applications, you first find the queries that constitute a typical workload, using SQL Profiler: Then, from the trace, you find the queries or stored procedures that are having the most impact. After that, it is down to examining the execution plans and query statistics to identify queries that need tuning and indexes that need creating. You then See what effects you've had and maybe repeat the process. Gail explains all, in a two-part article.… Read more
With SQL Server Profiler, it is easy to discover all those queries that are running slowly. Once poorly performing queries are identified, there comes the harder task of speeding them up. Once again, the Profiler can provide a lot of information that can help diagnose and resolve these performance problems. Brad describes this essential part of the process of performance-tuning in an article taken from a chapter to his new book, Mastering SQL Server Profiler.… Read more
Learning how to read and analyze execution plans takes time and effort. But once you gain some experience, you will find them an essential skill for getting to grips with performance issues in SQL Server Queries. Grant here describes the range of execution plans for different simple SQL Queries.… Read more
Designers of database systems will often assume that the use of a clustered index is always the best approach. However the nonclustered Covering index will usually provide the optimum performance of a query. … Read more