SQL Server Expert Blogs

The DBA Detective: Disturbing Developments

Simple-Talk on SQL - Sun, 03/30/2014 - 17:00

Originally one of the articles in the first DBA Team series, Grant wonders what Raymond Chandler or Dashiell Hammett would have done if asked to write technical articles for Simple-Talk. He came up with the DBA detective, hard-boiled Joe Dee Beay

14 SQL Server Indexing Questions You Were Too Shy To Ask

Simple-Talk on SQL - Mon, 03/24/2014 - 17:00

One of the first things one needs to understand well about SQL Server is indexes, but somehow many of the basic questions don't often get asked or answered much on forums. Rob Sheldon answers some of these shy-making questions about SQL Server Indexes and indexing: the ones we somehow feel silly asking in public, and think twice about doing so.

SQL Server Indexing Questions We Were Too Shy To Ask

Simple-Talk on SQL - Mon, 03/24/2014 - 17:00

One of the first things one needs to understand well about SQL Server is indexes, but somehow many of the basic questions don't often get asked or answered much on forums. Rob Sheldon answers some of these shy-making questions about SQL Server Indexes and indexing: the ones we somehow feel silly asking in public, and think twice about doing so.

High Performance Relational Division in SQL Server

Simple-Talk on SQL - Sun, 03/23/2014 - 17:00

Relational division is used in SQL to select rows that conform to a number of different criteria. It is a neglected but effective technique for a number of tasks. Although the SQL can appear daunting and complex, it can perform very well if you reduce the rows as much as possible before applying the final logic. Dwain Camps explains how, and shows the performance gains.

Using whoami to check for instant file initialization

Tibor Karaszi - Wed, 03/12/2014 - 04:22
Read this if you want to read more about instant file initialization (IFI). In an earlier blog post , I posted a script that uses trace flags, created a dummy-database and then sp_readerrorlog to check for IFI. Another option is to use the OS command...(read more)

AlwaysOn Availability Groups - What Not to do when Adding Databases

Simple-Talk on SQL - Sun, 03/09/2014 - 17:00

SQL Server's AlwaysOn Availability Groups provide a very resilient way of providing High-availability for SQL Server databases, but there are inevitable limits to their capacity. How many databases can you reasonably add? It depends on the resources available and the workload, but you can come up with a reasonable estimate as Warwick Rudd explains

Applying The Scientific Method to SQL Server Performance Tuning

Simple-Talk on SQL - Sun, 03/09/2014 - 17:00

Database administrators tend to rely more on science than intuition, and may therefore seem exasperatingly cautious to other IT people. A relational database is a complex machine, however, that is asked to deal with intricate problems. When things go wrong, it usually takes knowledge, deduction and a systematic approach to track down the root core of problems and to determine the best fix.

The PoSh DBA – Specifying and Gathering Performance Counters

Simple-Talk on SQL - Thu, 03/06/2014 - 17:00

If you are needing to keep tabs on a number of servers and applications in a Windows domain then performance counters provide the bedrock of information. It is important to identify the counters you need and gather baseline data to allow you to create alerts when abnormal conditions occur. When it comes to monitoring SQL Server, don't guess, collect.

How often do you rebuild your heaps?

Tibor Karaszi - Thu, 03/06/2014 - 04:46
Never? You are not alone. None of the maintenance solutions I use includes functionality to rebuild a heap, and that includes Maintanance Plans and Ola Hallengren's maintenance solution. "So why would you want to rebuild a heap, when it isn't sorted in...(read more)

Setting max server memory

Tibor Karaszi - Thu, 03/06/2014 - 01:27
If there is one server setting that is close to universal to configure, then it is probably the "max server memory" setting. The setting is documented here . There are plenty of articles out there on this subject. The purpose for this article is for me...(read more)

On Handling Dates in SQL

Simple-Talk on SQL - Wed, 03/05/2014 - 17:00

The calendar is inherently complex by the very nature of the astronomy that underlies the year, and the conflicting historical conventions. The handling of dates in TSQL is even more complex because, when SQL Server was Sybase, it was forced by the lack of prevailing standards in SQL to create its own ways of processing and formatting dates and times. Joe Celko looks forward to a future when it is possible to write standard SQL date-processing code with SQL Server.

Do you clean up your Database Mail log tables?

Tibor Karaszi - Wed, 03/05/2014 - 02:20
Database Mail has a couple of log tables in the msdb database. These can become large over time. I've seen MSDB databases over 1 GB in size, where normal size is less than 50 MB (heavy usage of old SSIS deployment model excluded). Unfortunately Maintenance...(read more)

Seven SQL Server Under-Used Utilities

Simple-Talk on SQL - Mon, 02/24/2014 - 17:00

There are more than ten useful command-line applications that are either associated with, or are distributed with, SQL Server. Some, like BCP are used often, whereas others like LogDumper, almost never. However, they all have their uses and several become important as part of script-based automation of tasks. It is definitely worth knowing what is lurking in your tools\binn directory.

Rapid SQL Server Database Deployment

Simple-Talk on SQL - Thu, 02/20/2014 - 17:00

With the right tools, it is possible to rapidly deploy upgraded databases. By deploying early in development, it's easier to iron out any potential problems that the new deployment could cause in production long before it's time to actually release the software. Tony Davis explains.

Fixing Cache Bloat Problems With Guide Plans and Forced Parameterization

Simple-Talk on SQL - Wed, 02/19/2014 - 17:00

Imagine it. You've been asked to fix a dire performance problem with a SQL Server database. You find a severe case of 'Cache Bloat' due to ad-hoc queries, but you can't fix the code itself. What should you do? Specify forced parameterization? Perhaps a better idea would be to use guide plans.

Calculating Gaps Between Overlapping Time Intervals in SQL

Simple-Talk on SQL - Thu, 02/13/2014 - 17:00

There are a number of real-life reporting tasks in SQL that require a 'gaps and islands' analysis. There are a number of techniques around that work, but finding ones that scale well makes for a tougher, but interesting, challenge.

Do you want improved performance?

Tibor Karaszi - Thu, 02/13/2014 - 04:48
Can you survive a few lost transactions if your server does a "hard shutdown"? If so, check out SQL Server 2014 and "Delayed Durability". A cornerstone in SQL Server's transaction handling has up until 2014 been "durability" for a committed transaction....(read more)

Guerrilla Project Management for DBAs

Simple-Talk on SQL - Wed, 02/12/2014 - 17:00

All DBAs need to engage in a little project management to help make sure everything runs smoothly. In this extract from the book Tribal SQL, David Tate explains his system for managing workload, coleagues, and projects, and how not to be just "the guy who says no".

SQL Server Indexed Views: The Basics

Simple-Talk on SQL - Tue, 02/11/2014 - 17:00

Views are a valuable tool for the SQL Server Developer, because they hide complexity and allow for a readable style of SQL expression. They aren't there for reasons of performance, and so indexed views are designed to remedy this shortcoming. They're great in certain circumstances but they represent a trade-off, and they come with considerable 'small print'. Jes Borland explains.

SQL Server SEQUENCE Basics

Simple-Talk on SQL - Wed, 02/05/2014 - 17:00

The SEQUENCE statement introduced in SQL Server 2012 brings the ANSI SQL 2003 standard method of generating IDs. This is a great relief to database professionals as it solves some problems what are awkward to solve with the IDENTITY property. Joe Celko explains the basics of using a SEQUENCE