3

Perhaps a stupid question but since I don't know how the databaseengine really works under the hood I don't know the answer.

More specifik I've learnt that the sql always tweaks the query so that it is the most optimized for the server. Not only just optimizing speed but also resource management.

So could could it be that my tuning of the query has a greater impact on server resources than the original long running query and can I see that somehow?

If so what are the usual ways to bring down the server trying to tune a query?

Eg. if I have a query that runs for 10m and I optimize to 30s could those 30s have a greater impact on the server performance than the 10min query?

user3532232
  • 129
  • 2

2 Answers2

0

Query can consume more server resources when executing faster.

For example, recreating index with SOTRT_IN_TEMPDB = ON impacts tempdb.

This option increases the amount of temporary disk space that is used to create an index, the option could reduce the time that is required to create or rebuild an index when tempdb is on a set of disks different from that of the user database.

https://msdn.microsoft.com/en-us/library/ms188281.aspx

Paweł Tajs
  • 1,353
  • 8
  • 16
  • Ok. But if I use mainly basic functionality eg table variables and different join orders, join on variables this cannot be the case, yes? – user3532232 Jan 30 '17 at 15:29
0

There are many ways in which a query that runs faster can have a greater impact on overall server performance during it's run, even to the extreme degree you ask about.

For example, a cursor-based update to an entire table that takes 10 minutes may take 30 seconds a single update statement, but with massive impact during those 30 seconds on both IO and blocking/deadlocking of other queries reading/writing to the same table.

And for read querying the time difference may not be as extreme, but consider a query that used to run serial, but which goes parallel when rewritten. In such a case, I believe (but am not totally sure) that the query will be given most free CPU cores when it starts, and other incoming queries will need to queue for the remaining cores until the large query completes. And the parallel IO consumption may slow other queries as well.

T.H.
  • 903
  • 5
  • 7