Do your SQL queries run too quickly? I thought not! Many DBAs don’t know where to start performance tuning. The August issue of SQL Server Pro suggests strategies for achieving peak performance. There's also an article on T-SQL Best Practices which is freely available online.

Do your SQL queries run too quickly? I thought not! Many DBAs don’t know where to start performance tuning. Top 10 SQL Server Performance Tuning Tips in the August issue of the online subscription-based monthly magazine SQL Server Pro has strategies for achieving peak performance. I strongly recommend you read both the article and its links if you want to improve the performance of your SQL queries.

The tips covered include:

• Stop Waiting Around. When SQL not running, the reason for the wait is recorded by SQL Server, examining these waits can improve performance. These waits can be decoded by using Tom Davidson’s paper
• Locate I/O Bottlenecks. I/O is often the primary reason for slow performance. You can examine IO via wait stats, DMF sys.dm_io_virtual_file_stats(), and disk related Perfmon counters. Causes include slow disks, disk placement, badly written SQL, and lack of good indexes
• Root Out Problem Queries. The DMV sys.dm_exec_query_stats aggregates query metrics (duration, time on CPU, waiting, reads, writes, executions etc) at individual SQL statement level – great for identifying longest running SQL. Can then optimise these
• Plan To Reuse. Change dynamic SQL to use stored procedures, for plan reuse, else as transactions increase you can get bad performance
• Monitor Index Usage. The DMF sys.dm_db_index_operational_stats(), details what has been used, how used, scans, singletons, inserts, deletes, updates, latches and locking
• Separate Data and Log Files. Important for both DAS but also for SAN. Separate random access (data) from sequential access (log)
• Use Separate Staging Databases. Can then use a simple recovery model, which speeds imports, and uses less CPUs, IO, and memory
• Pay Attention to Log Files. Log growth can be expensive, better to ensure you have the required free space. Also ensure you don’t have too many virtual log files (VLFs)
• Minimize tempdb Contention. tempdb can be used by all the other databases on the server, and it is often the most used database
• Change the MAX Memory Limit. Remember to leave at least 1 or 2GB of memory for other processes. Also consider the impact of multiple instances

In T-SQL Best Practices Itzik Ben-Gan outlines some important practices to follow and the reasoning behind them. Both parts of this article are freely available (i.e. without needing a subscription). In Part 1 his advice is:

• Think in sets. Adopt a non-procedural approach. Avoid cursors and loops.
• Think of NULLS and the Three-Valued Logic. NULLS are missing value, and you should always consider them.
• Avoid NOLOCK. Using NOLOCK is common practice, enables SQL to run faster, no locks. Cost is uncommitted and inconsistent reads. Use Read Committed Snapshot Isolation instead.
• Adopt Good Coding Practices:
  • Terminate statements with a semicolon
  • Avoid *
  • Always use schema-qualified object names
  • Have a style and use correct casing
  • Avoid the old-style join syntax

In Part 2 he continues:

• Think cloud (some language features are not allowed in the cloud, and heaps are not allowed).
• Date and Time Best Practices
  • Use language-neutral literals
  • Be careful about rounding errors
• Write standard code (as opposed to Microsoft proprietary SQL).
• Beware of Rules of Thumb (the answer is often ‘it depends’).
• Use Search Arguments

The article is useful for both new SQL developers and also for experienced developers that want a refresher. At times it can seem a bit pedantic e.g. don’t use the term ‘NULL value’ since NULLs don’t have a value. I may not agree 100% with the suggestions, since some may not always be practical in a commercial environment, but any content from Itzik is always worth reading.

This month’s editorial considers Has Microsoft Gotten Too Far Ahead of Its Customers? Currently Microsoft is pushing cloud (or cloud hybrid) usage and has already started highlighting the next version of SQL Server (2014). Unfortunately, most customers have yet to experiment with the cloud and are stuck on SQL Server 2005 or 2008. A convincing argument is made that while customers want the tech company to be leading edge, consideration should be given to the user’s current needs.

Saurabh Dhoble provides an extensive 16-page article relating to SSIS Deployment Strategy for SQL Server 2012. SSIS is a premier ETL tool, however there are often problems with configuration settings when moving packages across environments (e.g. from development to user testing). The article details how you can ease the deployment of SSIS projects to different environments, and manage their configuration with environment variables.   

In VMWare High Availability in SQL Server Denny Cherry discusses the High Availability options that are available as a bi-product of using VMWare’s vSphere on virtual machines. The advantages extend to all versions (e.g. SQL Server 2012) and editions (e.g. standard edition) of SQL Server. This should be particularly of interest for organisations that have a limited budget.  

A short series on database corruption begins with SQL Server Database Corruption, Part 1: What is Corruption? The series aims to discuss what corruption is, why you can’t prevent it, and how you can deal with it to ensure availability and uptime. In this first part, corruption is defined as the improper storage of 0s and 1s on the IO subsystem. Corruption is often caused by problem with IO subsystem e.g. drive, controller, or a driver. This 3-page article felt a bit light in content, I suspect it could have been condensed into a single page without loss. 

Most technologists eagerly look forward to new editions of their favourite software. There are some mouth watering features highlighted in New Features in SQL Server 2014, including

• In-memory OLTP Engine (AKA Hekaton). Expected performance gains of x10 to x50, and compiled stored procedures
• Improved scalability. Scale up to 640 logical processors and 4TB memory
• Windows Azure Integrated Backup (backup and restore to and from Windows Azure)
• Azure Integrated AlwaysOn Availability Groups (useful for DR)

For me, this month’s magazine was much more enjoyable than recent issues. This might be because it contained some core perennial topics of interest, i.e. performance and best practices. Additionally, the forthcoming SQL Server 2014 looks ‘interesting'.