Need to increase the capability and efficiency of your SQL server’s performance, but don’t know where to start? Use these expert performance tuning tips and you’ll see a measurable improvement in your query results!

What Is SQL Server and What Are Queries?

Developed by Microsoft, SQL Server (structured query language) is a relational database management system (RDBMS) that stores and receives data as requested by other software applications. This program allows you to gain insights across your transactional and analytical data, both on physical and virtual servers.

In order to access the information and insights stored in SQL Server, you must input a select query (an inquiry) and the data will be extracted in a readable, analyzable format. To perform an operation such as inserting, deleting, and updating, an action query must be used.

Know Your Tables, Rows, & Columns

First, make sure you’re in an actual table, not view or table-valued function. Next, check the row count to see the size of the logical set. Determine whether or not extra columns are involved because the more there are columns, the less optimal it may be to use certain index operations.

Some queries may benefit from using the PIVOT relational operator (table rotation) to turn the unique values of a column with multiple rows into multiple column values in the output . The opposite function may also be performed by using UNPIVOT.

Inspect Your Filters and Constraints

If the majority of a table rather than a smaller comparison group is returned from a query, further investigation may be required. You may need to examine the query filters WHERE, GROUP BY, and HAVING in a SELECT statement control. Other possible filters and constraints include the following:

  • =, < >, <, and > retrieve comparison operators.
  • BETWEEN and NOT BETWEEN retrieves a range.
  • IN and NOT IN retrieves a list.
  • LIKE and NOT LIKE retrieves pattern matches.
  • IS NULL and IS NOT NULL retrieves null values.
  • =ALL, >ALL, <=ALL, and ANY retrieves all records.
  • AND, OR, and NOT retrieve a combination of conditions.

Examine the Execution

An execution plan, the primary means of troubleshooting a poorly performing query,is the result of the optimizer’s calculations. It can identify the exact piece of SQL code that is causing a slow query to take place. It can also find out why a specific index isn’t getting used. To optimize your execution plan, automate the capture by using a SQL Server Profiler tool.

Adjust & Rerun

Depending on your findings, you may need to adjust your query. Do this one operation at a time, recording your results as you go. This makes it easier for you to determine the impact any of the changes you made had on the results. Look for performance inhibitors such as:

  • Code first generators
  • Abuse of wildcards
  • Scalar functions
  • Nested views
  • Cursors
  • Row-by-row processing

Continue recording your results and adjusting your query until you are satisfied with the final results.

If you continue to be unsatisfied with the results of your query, you can consider adjusting the indexes (clustered or non-clustered) to improve the data warehousing query performance.

Facilitate Tuning with a Performance Monitoring Tool

By using a continuous database performance monitoring tool, troubleshooting your databases will be much faster and easier.

You will have 100% visibility of application performance, get alerts when thresholds are violated, easily see execution plans, and have the capability of monitoring key metrics. Choose an analyzer that does not have a limit on number of users, is agentless, monitors databases on both virtual and physical servers, carries only a small load on monitored servers, and can be installed on your specific type of server.