0

We have a client who has been having performance issues. One of the developers is saying that a select * from a table is taking ~50 seconds to perform the query (no joins just straight select with no fields). The table has 166 columns (I know that is probably over the top, but this is from an ERP software that the developer integrates with) and around 400,000 rows.

Now considering the crazy number of columns and 400k records, 50 seconds seems about right for me.

The SQL Server is running on a Hyper-V VM and has decent hard drive read/write speeds, the hard-drive specs are: 16 x 600gb 15RPM 6.0Gbps SAS drives in Raid 10.

The VM performs well and has plenty of RAM, just interested to see if any experts can chime in whether they think that query is performing within reason?

3
  • 2
    It's not just SQL Server but also the network and client processing that contribute to the overall elapsed time. Run the query from SSMS locally and remotely and compare the execution times.
    – Dan Guzman
    Nov 22, 2015 at 15:37
  • 1
    Post execution plans as well if you can. These help us understand how SQL Server is fetching the data on such a wide table where SELECT * should never be used.
    – Fastidious
    Nov 22, 2015 at 16:04
  • 1
    @Fastidious IIRC there is only one way to process an all-columns, no-predicate query and this is detected during "trivial plan" analysis. It will be a table scan. Nov 30, 2015 at 1:59

4 Answers 4

2

It's quite a few rows, and the table is quite wide - so I'd be interested to how much data is being pulled back. You can get a good idea by capturing it in SQL Server Profiler, or from identifying the SPID, then watching the logical_reads column in sys.dm_exec_requests just before the query completes on modern versions of SQL Server. This measures 8KB pages, so divide by 128 and 1024 to get the approx size in GB. You'll probably find it's quite a chunky read like 100GB! And 100GB will always take some time to come off disc!

Following a more business approach would be having SLAs. Hopefully, it might be documented that this business action (eg "add customer") should take a maximum of 2 seconds for the system, and that business action (eg "find address") would also take 2 seconds. As long as the app is meeting or exceeding these requirements, a select * from the entire table won't matter.

1

166 fields is a lot, but i guess it depends on the tuple size. But it sounds pretty reasonable around a minute for 400k rows.

Have you tried the running statistics on the query to see how much physical/logical reads are being done?

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

This will return some statistics and is only from the client side, so it doesn't change anything on the server. I read this in a post about query performance before here: Tsql-measure-performance-improvements

An idea would be to calculate your tuple size, or do a average tuple size by dividing the table size with #rows. How many of those can be contained in a 8KB page and from there you could at least estimate the number of pages that needs to be read. Along the answer Peter Schofield suggested.

sp_spaceused 'Table Name' / Number of rows
1

Running SQL Server on a VM, I have seen upwards of 30% performance degradation, primarily due to a general IO operations bottleneck, even on a overpowered server. The problem is that in a virtual environment there are multiplicative demands on the physical system bus, and this is shared by the physical and virtual network interfaces, disk and network systems.

When I had run performance monitor, with this as the only VM in operation on the host, I did not see capping in virtual or physical disk, network, or memory, and that's mainly because IO is keeping any of these from peaking. The true cause of the problem is the path from the physical disk surface to the SQL process involves many more IO hops than on bare metal. These performance issues will be evident even if running SSMS locally.

If you want to document the virtualization overhead or rule this out as an issue, you can temporarily put SQL on a bare metal server (the host or one with equivalent specs) turn off any hyper v, and run the benchmark.

The Amazon cloud has an interesting solution with metered IOPS, which gives you control over this. It looks like Microsoft is providing a comparable control allowing storage quality of service guarantees, but I don't know if it can do more than divvy up the limited performance situation you have to begin with. Personally I've found success with the Amazon solution and have also used bare metal as a solution in these situations.

0

SELECT * always not best practice, but SELECT * without any a additional WHERE or LIMIT it bad at all

any queries have several parts:

  • search (filter data)
  • sort result
  • return result

in You case last part work with full table, which with 166 column and 400k rows could be really big and in Mb

Plus - other processes if present can take table resources and make possible table locks.

This construction could be used for some cumulative reports, but still better exclude all not necessary columns

Not the answer you're looking for? Browse other questions tagged or ask your own question.