Tag Archives: SQL Server

SQL Server –

SQL Server related posts and scripts.

SQL Server Queries: Writing queries for performance – Part 2

Writing an efficient query

An efficient query is the result of following a few simple rules and patterns. Part 1 of the series outlined general goals when creating a query. This part of the series gives concrete rules for writing efficient queries.

The Rules

  1. Use the minimum number of tables while meeting business requirements
  2. Only return the columns needed / specify the columns to be used
    1. Never use SELECT *
    2. This reduces network traffic, client processing and ensures you won’t get extra columns returned in your query if the base tables are modified
  3. Add a WHERE clause if possible
    1. Directly reduces I/O and network traffic
    2. Use sargable arguments (arguments that can effectively use an index)
      • =
      • >
      • <
      • <=
      • >=
      • BETWEEN
      • IN
      • LIKE ‘Text%’
    3. Avoid non-sargable arguments
      • <>
      • NOT IN
      • NOT LIKE
      • LIKE ‘%text’
      • LIKE ‘%text%’
      • OR
      • Functions in WHERE clause or JOIN
      • Avoid use of functions in clause
  4. Use “SET NOCOUNT ON” for all stored procedures
  5. Use stored procedures or parameterized queries
    1. Allows your execution plan to be re-used
    2. Especially important when the query is executed multiple times
  6. Use consistent formatting
    1. Create guidelines and be sure your developers follow them
  7. Verify the query plan
    1. Indexes
    2. Seeks versus scans
    3. Implicit conversions
    4. Join types
  8. Avoid cursors / RBAR
  9. Avoid query hints and index hints (let the query engine do its’ job and adjust the plan as the data changes over time)

 

SQL Server Queries: Writing queries for performance – Part 1

Troubleshooting query performance can be a frustrating task for developers. The goal of this series is to break down the steps to analyze a query and provide concrete actions that can be used to create performance oriented queries.

When trying to improve the performance of a query you will almost always be working on some or all of the following high level tasks:

  • Minimize network traffic
  • Minimize physical I/O
  • Minimize logical I/O
  • Minimize CPU usage
  • Perform set-based operations

Remember – queries are optimized in relation to the current system and database. This means that performance begins before a single line of code is written and before a single byte of data is inserted. Disk setup, network setup, server setup, data model, indexes and other databases on the system all impact performance (this also means that your query impacts the other queries or even entire databases running on the same server).

Writing efficient queries also means less troubleshooting in the future.

Changing SQL Server Database Owners

In a production SQL Server environment the database owner is usually carefully maintained and consistent. This isn’t always the case on development servers or on servers that you may inherit from other organizations. This script was written originally for a development environment with multiple database owners – one of the developers left the project and their user account needed to be removed.

If you just need to change a single database owner use the SQL command (change the database name and owner of course):

ALTER AUTHORIZATION ON DATABASE::[AdventureWorks2012] TO [sa]

If you need to change the owner on multiple databases or if you just want to see the current owners on the databases you can use the script referenced here – ADMIN.Database.Owners.Change.

Check out the SQL Server BOL entry on ALTER AUTHORIZATION to see the security requirements and other restrictions.

Be sure to check out our other SQL Server scripts here – I-Tech SQL Server Scripts.