5

I'm looking through the queries being executed by a third party application. The application was flagged as being slow by some of our end-users.

It seems to INSERT records into a HEAP, then DELETE them again after the session ends (without any selects happening on them?) But at the end of each INSERT statement there is a SELECT 0.

Am I seeing some kind of artifact from the query plan cache? Is it possible there is some parameter that's missing? All other parameters are clearly indicated as for example @P1.

There are 350 INSERTactions per minute and a DELETE of everything in the table ever hour. Which seems to be an OK case to use a HEAP, however if this SELECT 0 is actually selecting something from this heap, I'm wondering if I should ad a clustered index.

Update: Added a screenshot to show the select: SelectWithoutQuery

|improve this question
  • 1
    DELETE of everything in the table ever hour. If the table is not referenced by foreign key to any other tables, then a truncate would be much bet – Kin Shah Nov 26 '14 at 13:44
  • 1
    SELECT 0 could be used as some type of confirmation of the records that were inserted without actually selecting the columns. Good luck figuring out why an application is doing that. – Max Vernon Nov 26 '14 at 13:45
  • @Kin unfortunately I don't have access to the application, which is doing the deletions. – Reaces Nov 26 '14 at 14:07
  • @MaxVernon How would that work exactly? When I run SELECT 0 all I'm getting back is... 0? – Reaces Nov 26 '14 at 14:08
  • surely it is not only SELECT 0 ??? If so, that is totally useless; I assumed you meant something like SELECT 0 FROM schema.table... and just didn't include the unnecessary details. – Max Vernon Nov 26 '14 at 14:18
1

An execution plan is only going to have the code that was last compiled and used, to my knowledge. In troubleshooting applications myself I have come across where a statement I was looking at through the execution plan was actually written with sp_executesql and so was dynamic T-SQL that contained a bit more logic than the end result.

You don't specify the version you are working with but I would suggest using a server-side trace or Extended Event session to map out the sequence of commands being passed so you fully understand what is being done in your situation.

My first thought is the application (or the developer) wrote the insert statement and in some form or fashion is doing a check on something (i.e. maybe errors). In that check if it is good they are simply passing what ends up looking like SELECT 0, where if an issue occurred it might return SELECT 3910 that would mean something to that application. You are only going to see the full logic (if any) behind all of it by doing a trace on the commands.

|improve this answer
  • The version is SQL server 2005, so unfortunately no extended events. I also hesitate to use profiler because of the overhead it might incur (the database server is shared by several applications) – Reaces Nov 26 '14 at 18:30
  • There is no might about the overhead, Profiler and Extended Events both have their own level of overhead that is seen based on what you are trying to capture. That is the general reason you can filter both down to the database and evens you want, and do not run it forever. Unless you have access to the folks that wrote the application it is your only option. As well, I have run profiler on extremely active systems and there are times it has to be done if you want to find the answer. – user507 Nov 26 '14 at 19:05
  • I've contacted the person who made this particular app (he still works for us indirectly on a different project, so I'm in luck). If all else fails, I'll turn to profiler. – Reaces Nov 27 '14 at 9:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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