Forgot your password?

Home > About Us > Modern Signal Blog >SQL Server Query Performance

SQL Server Query Performance

This page houses tips and tricks for improving query performance in SQL Server.

POSTED BY: Adam Polon

After running into some very quirky results from seemingly simple queries, I have found the following suggestions to make an enormous difference in query performance. 

Of course, you should always start by making sure that your query is optimized using standard practices, such as the following:
  • Make sure fields that are searched over frequently have indexes assigned.  This only makes sense if the field will return less than 10% of the table, so don't index status fields if there are only 5 of them, etc.
  • Make sure the data type for a field is appropriate.  For instance, if your field contains numeric data, avoid using a varchar field and CASTING AS INTEGER.
In some cases, switching around the order of fields in WHERE clauses makes a difference, but I usually do not find this to solve my issues.

Here are some things to try next.

---------------

Debug sluggish queries in SQL Server Management Studio by toggling the “Include Actual Execution Plan” button.  This will allow you to see the execution plan – including any table scans, index seeks, etc, to identify slow points in the query.

---------------

Review the nightly database maintenance plan to ensure that indexes are rebuilt on a nightly basis. 

-----------------

I believe that the rebuilding of indexes on a nightly basis makes the following item unnecessary, but it is listed here just in case in helps where other avenues do not. 

Defragment indexes on large tables. To do this, use the following command:

DBCC INDEXDEFRAG (database_Name, 'table_name', index_name)

Calling this command on key data tables made a huge difference in performance for a few queries that were performing slower than expected.  Queries literally went from 45 seconds to 2 seconds.

It would be a good idea to schedule these commands to run on a nightly or weekly basis.

----------------

Run Update Statistics on key data tables.  In conjunction with DBCC INDEXDEFRAG, I have seen huge performance enhancements as a result of running this.  To do this, use the following command:

update statistics table_name;

It would be a good idea to schedule these commands to run on a nightly or weekly basis.

---------------

Please let me know if you wish to add any additional tips and tricks to this page.


Testimonials

  • I felt as if my company was their only client. They responded to my needs quickly and efficiently despite short turn around time and intense demands.

    - Teaching Strategies, Inc.

  • Modern Signal worked with us to understand our needs and figure out what solution would work best for us. Our Lighthouse CMS is perfectly suited to our website goals. When we later needed to modify the CMS, they again took the time to understand exactly what was  needed and then built that functionality rather than delivering a cookie cutter solution.   

    - Ecosystem Investment Partners

  • Modern Signal understands our business - from future needs to current limitations - so their solutions are always scalable, solid, and service-oriented.

    - National Association of Home Builders

  • Modern Signal significantly enhanced our site to be more efficient and user-friendly. They provide excellent customer service with timely and cost-effective solutions.

    - Center for Medicare Education

  • This was by far the smoothest website redevelopment I have ever experienced. Modern Signal was a wonderful company to work with and we greatly value our working relationship. 

    - National Association of Student Financial Aid Administrators

  • Modern Signal has been a great partner for us for over the past 10 years.  As our business grew and our needs changed, Modern Signal was able to work with us to adjust our website platform in the ever-changing online world.  Their service and response level has been second to none, and we've been never been happier with our relationship with them.

    - Charm City Run

  • We wouldn’t have gotten where we are today without your support over the years.  Modern Signal has always been a great partner to us.

    - Kirk Gillis, Managing Director at Zoom Tanzania

  • I love working with Modern Signal! Their CMS is very easy to use and they are incredibly responsive to questions or challenges I bring them.

    - NALP

  • Modern Signal has a professional staff that was very responsive to our needs during all phases - scoping, developing, implementing and maintaining - of our project.  We have been pleased with their ability to deliver quality work on time and on budget. If given the opportunity, I would work with them again.

    - The National Center for Safe Routes to School