Query is not running

1

Hello all,
I have a query which used to run for 3-4 minutes and give result. today it is not running, it's just saying executing query and running for 16 hr and no result, still saying executing query. Can anyone please help me with suggestions like what should I look for. The SQL server was restarted yesterday. It's a production issue and need to be resolve ASAP.the query is below:

SELECT distinct V_SR_2352385_HIP_KNEE_SPINE.surgDate,
V_SR_2352385_HIP_KNEE_SPINE.caseNumber,
V_SR_2352385_HIP_KNEE_SPINE.MRN,
V_SR_2352385_HIP_KNEE_SPINE.Acct#,
Subquery.drugName,
Subquery.quantity,
Subquery.dosage,
Subquery.doseUnits,
Subquery.[route],
V_SR_2352385_HIP_KNEE_SPINE.AnesType
FROM pslive.MaghiarN.V_SR_2352385_HIP_KNEE_SPINE V_SR_2352385_HIP_KNEE_SPINE
INNER JOIN
(SELECT V_MEDICATIONS.caseNumber,
V_MEDICATIONS.drugName,
V_MEDICATIONS.quantity,
V_MEDICATIONS.dosage,
V_MEDICATIONS.doseUnits,
V_MEDICATIONS.[route]
FROM pslive.MaghiarN.V_MEDICATIONS V_MEDICATIONS) Subquery
ON (V_SR_2352385_HIP_KNEE_SPINE.caseNumber = Subquery.caseNumber) ORDER BY V_SR_2352385_HIP_KNEE_SPINE.surgDate

2

I would start by checking for blocking:

3

I checked and there's no blocking. Is there any other suggestions please?

4

Couple of other things:

1.Run the command

DBCC OPENTRAN('YourDatabaseNameHere');

Since you didn't find anything blocking, I don't expect that you will find something here, but simple enough to verify.

2 Download and install sp_whoisactive. Then run it, and that will give you additional information on what is going on on the system.

5

I just ran into this yesterday. The problem is that you need to rebuild stats on all tables used by the query. Since your query consists of a shedload of views, it may be simpler just to rebuild the stats throughout the database so you don't have to figure out all of the dependent tables in all of the views.

If you don't have a regularly scheduled job to update stats, then you really need to make one. Stats are even more important than any form of fragmentation that you may encounter because the Optimizer does NOT consider fragmentation. It uses Stats to figure out what the best plan is and if the stats are there but they haven't been updated for the latest data, they can be woefully incorrect resulting in truly whacko execution plans that take forever to run.

6

Might be that (dynamic) Stats Rebuild is Sync rather than ASync perhaps? (Sync, rather than ASync, is the default I think? but I've always found that ASync works better - no sense holding up all the queries when a Stats Rebuild is deemed necessary, presumably the immediately-preceding query ran "OK" with existing stats, and I'm OK that all FUTURE :slight_smile: queries will run better, but it make take "a long time" to rebuild the stats (not 16 hours though, so I am thinking this "might help" rather than "fix the problem")

7

In the case that occurred to me this past Friday, it wasn't any of that. A lot of data had been added to the table but it's a BIG table and the amount of data added wasn't quite enough to trigger the 20%+500 rule.

1 Like