0

This is an Azure SQL database. It's a small table, really. I'm not doing a SELECT * FROM. I'm naming all of the columns in the table.

The table has PK with a clustered index. It also has a non-clustered index with two columns in it.

Originally, the SELECT statement took 39 seconds to run. But after I did a REORGANIZE on both of the indexes, it now takes 1:02. So, I made things much worse. (Luckily this is a DEV table.)

How can I at least revert back to the 39 seconds I started at? And, what else should I be looking for to explain the slowness?

If it helps, here's the Execution Plan.

enter image description here

And I also fired up SQL Profiler and ran a trace, but it returned so much data, and to be honest I don't know what I'm looking for in the results.

Here's the result of SELECT @@Version

Microsoft SQL Azure (RTM) - 12.0.2000.8
Jan 3 2019 00:14:33
Copyright (C) 2018 Microsoft Corporation

Dale K
  • 25,246
  • 15
  • 42
  • 71
Casey Crookston
  • 13,016
  • 24
  • 107
  • 193

2 Answers2

1

Perhaps rebuild the index? Creating a covering index - one that contains all the columns being selected - would allow the query to run instantaneously. Beyond that do any columns contain very large blobs? I'd look into the disk IO performance as well. That seems unusually slow.

jspcal
  • 50,847
  • 7
  • 72
  • 76
  • Thanks! It's an Azure SQL database, so I'm not sure how to look into the disk IO performance. As for a covering index, I'm not sure what that means, but I'll go Google it! – Casey Crookston Feb 18 '19 at 22:11
  • A covering index is a non-clustered index that has all the fields the query needs. – abestrad Feb 18 '19 at 22:15
  • One thing that you should be aware of is that too many indexes also causes issues, so make sure you keep a balance on how many indexes you create for a particular table. – abestrad Feb 18 '19 at 22:17
  • He must be using a covered index, otherwise the explain plan will join by rid again with the table, and in the image everything is recovered from the index – James Feb 18 '19 at 22:18
  • Regarding the too many indexes, it makes problems inserting, updating and deleting data, not querying the data. The worse on this should be the explain plan doesn’t choose the best index to resolve the query – James Feb 18 '19 at 22:21
  • Agree, at the end affects the whole system, SQL Server has to maintain all of these indexes which can slow things down and indexes also require additional storage – abestrad Feb 18 '19 at 22:23
  • So everything looks good in your query. As @abestrad says, check that you are not retrieving huge fields like blob, and also bear in mind other activity in the database that could be impacting the database performance – James Feb 18 '19 at 22:24
  • Any Performance recommendations listed in azure? https://learn.microsoft.com/en-us/azure/sql-database/sql-database-advisor –  Feb 18 '19 at 22:51
  • 2
    I'd probably work the problem this way. First, I'd select just the PK column(s). If performance is good, I'd continue to add columns until performance degraded and then I'd see what that told me. If performance is bad, I'd check to see if it's a network issue, so I'd do a "select * into #sometemptable from " and, if that is fast, suspect a network transmission problem. If the select into is still slow, I'd have to start getting more serious.
     – BoCoKeith Feb 19 '19 at 01:20
  • Thank you all! You've given me lots to work on. I'll report back after I've used the tips you all shared. @BoCoKeith, if it is a network issue (which I kind of suspect) how can I quantify this to bring something concrete back to my team? – Casey Crookston Feb 19 '19 at 14:30
  • Azure SQL databases use data transfer units (DTU) which can be managed in the Azure portal – SUMguy Feb 19 '19 at 22:47
0

As per the comment by BoCoKeith, I think that network latency seems to be the issue. It's spotty and not consistent. But, this query will consistently run in 00:00 seconds:

SELECT * INTO #sometemptable FROM MyTable

While this takes anywhere from 00:20 to 01:40

SELECT * FROM MyTable
Casey Crookston
  • 13,016
  • 24
  • 107
  • 193