First 2 Things to Look at For Slow Query

SQL Server Administration
1

Hi experts. What would you say the top 2 things to look at are for a poorly-performing query? And which tools to use?
Thanks.

2

hi

please see below link .. hope this helps

https://www.sqlshack.com/poor-sql-query-design-sql-query-performance-killer-basics/

3

You'll want to look at the execution plan of the query. If you are executing it manually, you can display the estimated plan, or display the plan after it executes, in SQL Server Management Studio. You can also use the free tool Plan Explorer from SolarWinds (formerly SentryOne Plan Explorer, I still consider Plan Explorer a SentryOne product). Plan Explorer is much better at pinpointing exactly where the problem areas are, it's a good investment of your time to use it and learn how it works.

Basic things to look for in a query plan:

  1. High row counts on rightmost tables - if the table is sending millions of rows, that will take more time and resources. If the final output of the query is millions of rows, then there's less you can do to tune it.

  2. JOIN operations where input row counts are much higher than output row counts - e.g. TableA sends 1 million rows into a JOIN with TableB, which also has millions of rows, but the output from that JOIN is only thousands of rows. The width of the lines in the graphical plan will give you a rough estimate of the row count, right-click or hover over the plan operators to get the actual row counts.

  3. Sort operators - these almost always send results to disk, and indicate that a useful index is not present. Sort operations occur with ORDER BY, UNION, and DISTINCT keywords in your query, the latter two ops are only to remove duplicate rows.

  4. Spool operators (eager or lazy) - these also write results to disk

Hope this helps. Plan Explorer can be downloaded here:

There's a demo or two on that page to get you started.

2 Likes