CODEWITHSUNDEEP
sql-serversql-server-2008database-administration
Dasso
Dasso

Reputation: 141

Execution plan and SQL goes to hung state

I am using a SQL server 2008, which has databases mirrored in Synchronized mode.

I am trying to run some update stored procedures, with some nested joins and it runs fine (Obviously with a reduced performance compared to a server which is not mirrored).

The problem I am facing is that if I select the "show detailed plan" option. The query starts running and it virtually goes to a hung state and doesnt recover. I finally end task the SQL.

I have a public role for the databases and I cant access any stats.

Can you tell me what exactly (or in general) should I ask the DBA to look at?

The details of the SQL server is mentioned below.

Product - SQL Server Enterprise Edtn- 64 bit.

  1. OS - WIndows NT 6.0
  2. Memory -6143 MB
  3. Processor -2
  4. Maximum Server memory - 3072 MB
  5. Minimum server memory - 16 MB

Any help on guiding me to a right direction will be appreciated.

Regards, Dasso

Upvotes: 1

Views: 631

Answers (1)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Because

1) You have activated [Include actual execution plan] option and because

2) There is a WHILE statement

SQL Server will send to client - Sql Server Management Studio the actual execution plan of every SQL statement executed by every iteration of WHILE statement. So, if WHILE includes a simple UPDATE and it executes 100 iterations then Sql Server will send the execution plan of UPDATE 100 times!

You should decrease the number of iterations for WHILE or you could use estimated plans.

Upvotes: 1

Related Questions