A way to make this query faster

dickhoogland 2014-02-06 11:05:18

This qeury is generated by a .NET application.
The sort takes 44 procent of the cost, but leaving out the SORT only saves 0.5 seconds.
The total query takes about 8 seconds and returns +/- 20.000 rows

Thanks for your assistance.

Dave Ballantyne 2014-02-06 11:30:18
The 44% figure you see is not meant to be a real metric, and this is quite normal.

There are a lot of scans occuring in the plans, these may be able to be turned into seeks. There is a missing index warning being shown too.

Perhaps a bit more relevantly how are you measuring the 8 seconds ? Management studio is slow at drawing the data, and in my experience this leads to a lot of wild goose chases.

Run the statement in management studio but turn on the "Ignore results after execution" option in the query options, that gives a more accurate measure.

The result set itself is fairly large, query analyzer reports it as 291MB, quite sizeable ? How fast can your network support a file transfer of that size ?

dickhoogland 2014-02-06 13:42:58
Thanks for your input. With the option Ignore results after execution the query takes about 2 seconds.

The missing index is an index with a lot of included columns in there. DTA also suggested this and it saves about 9 percent. I've created it, but it did not save a lot of time or I/O.

Will talk to the developers to tune this query so we do not have to send 291 Mb over the network to the clients.

Many thanks for your help.

Dick

Dave Ballantyne 2014-02-06 14:54:14
Great, sound like that is your issue. FWIW SQL Server will be showing a wait state of ASYNC_NETWORK_IO during this time.
dickhoogland 2014-02-07 09:18:29
I saw the wait with "sp_WhoIsActive".

I've learned a lot yesterday and the developers will take a good look how to change the application.

Thanks a lot again!