SQL Locks that sp_who2 would not respond

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL Locks that sp_who2 would not respond

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-09-29 : 09:43:12

Every day i check processes to see if any locks but this time i could not do it in EM...so i got query analyzer and issue sp_who2 that took for ever to come back...i ran sql profiler could not see anything i saw that sp_who2 took about 10000 ms as i had duration of greater than 3000 on.....

I then ran my sp_block that shows me what is blocked i then killed this off and the next one.

I found it was one user applicaiton impromptu and i killed every process off..that fixed it.

I asked developer....he then been looking at profiler and sees

(sp_unprepare) in profiler.

Again the developer runs this impromptu report and it locks up again.

I did some research and if i am right the sp_unprepare
is controlle by API The prepare/execute
model of executing SQL statements is supported by the Microsoft OLE DB Provider for SQL Server and the SQL Server ODBC driver.

So i am wondering how to resolve the report locking up sql and what to do...

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-09-29 : 13:31:04

Sounds more like tempdb is getting creamed, instead of blocking. See if there are any perfmon counters that detail space usage in tempdb. Also, check the report to make sure there are no (accidental) cross joins in it.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-09-29 : 15:42:45

None of the databases grew..or tempdb......im waiting for the users script or select .....

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-09-29 : 16:11:22

I monitored one of the reports not the one that locks up just another report.
basically each time impromptu fires it does

SET NO_BROWSETABLE ON
declare @P1 int
set @P1=14
exec sp_prepare @P1 output, NULL, N'select T1."VCHR_NO" AS "c1", T1."FY_CD" AS "c2", T1."PD_NO" AS "c3", T1."INVC_ID" AS "c4", T1."INVC_DT" AS "c5", T1."INVC_AMT" AS "c6", T1."PO_ID" AS "c7", T1."PO_RLSE_NO" AS "c8", T1."VEND_NAME" AS "c9", T1."S_STATUS_CD" AS "c10", T2."VCHR_LN_NO" AS "c11", T2."VCHR_LN_DESC" AS "c12", T2."QTY" AS "c13", T2."UM_CD" AS "c14", T2."LN_CHG_CST_AMT" AS "c15", T2."PO_ID" AS "c16", T2."PO_LN_NO" AS "c17", T2."TRN_DISCR_TOT_AMT" AS "c18", T2."TRN_DISCR_UNIT_AMT" AS "c19", T2."TRN_DISC_AMT" AS "c20", T2."TRN_EXT_CST_AMT" AS "c21", T2."TRN_LN_CHG_CST_AMT" AS "c22", T2."TRN_LN_CHG_TAX_AMT" AS "c23", T2."TRN_LN_CHG_USE_AMT" AS "c24", T2."TRN_NET_AMT" AS "c25", T2."TRN_SALES_TAX_AMT" AS "c26", T2."TRN_TOT_BEF_DC_AMT" AS "c27", T2."TRN_UNIT_CST_AMT" AS "c28", T2."TRN_USE_TAX_AMT" AS "c29", T2."TRN_RECOVERY_AMT" AS "c30", T3."ACCT_ID" AS "c31", T3."ORG_ID" AS "c32", T3."PROJ_ID" AS "c33" from "DeltekCP"."DELTEK"."VCHR_HDR_HS" T1, "DeltekCP"."DELTEK"."VCHR_LN_HS" T2, "DeltekCP"."DELTEK"."VCHR_LN_ACCT_HS" T3 where T1."VCHR_KEY" = T2."VCHR_KEY" and T3."VCHR_KEY" = T2."VCHR_KEY" and T3."VCHR_LN_KEY" = T2."VCHR_LN_KEY" and T1."PO_ID" = ''as1843'' and T1."FY_CD" = ''2006'' and T1."PD_NO" = 5', 1
select @P1
exec sp_execute 14
exec sp_unprepare 14

So i wonder ............what is going on.......would it be say if you did it many time running same report ..it locks

What is the sp_unprepare doing releasing from memory or something

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-09-29 : 16:18:20

sp_unprepare simply releases server resources, and likely does not have a large amount of CPU, Reads, or Duration associated with it in Profiler. You are looking for a line with sp_execute ## as the text, and a large amount of CPU/Reads/Duration. Look back a few lines for the actual query, which you already have seen is the sp_prepare command. sp_prepare gets the server side cursor ready for execution, and usually does not take up large amounts of CPU/Reads/Duration. sp_execute runs the query. A bit of a pain to troubleshoot, since you have to look back for the actual query text.

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-09-29 : 16:21:09

The posted query seems to have all the joins it needs (no tables left out). If you pick through Impromptu's famous formatting, you can get this:


from "DeltekCP"."DELTEK"."VCHR_HDR_HS" T1, 
  "DeltekCP"."DELTEK"."VCHR_LN_HS" T2, 
  "DeltekCP"."DELTEK"."VCHR_LN_ACCT_HS" T3 
where T1."VCHR_KEY" = T2."VCHR_KEY"    -->  T1 connects to T2
  and T3."VCHR_KEY" = T2."VCHR_KEY"    -->  T3 connects to T2
  and T3."VCHR_LN_KEY" = T2."VCHR_LN_KEY" -->  T3 connects to T2 even more.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-09-30 : 07:36:16

I have another look through profiler and see and check the cpu this time...i never had time to before it was almost a shutdown .....
Thanks if i get any more info i let you all know.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-09-30 : 07:37:46

I did notice the EXCHANGE lastwaittype.....that i will be investigating.
I did have quest on so i go back a have a good look around.......
Cheers

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-10-06 : 09:54:53

Ok ..i now captured the results.

I happend to have my script blocker on from microsoft.....which produced
PWAIT_CXPACKET – Waiting on packed synchronize up for exchange operator (parallel query)

Had sql profiler on ..and permon on..
Memory Page Faults Sec 46
Memory Transition Faults Sec 42
System Processor Queue Length 0
Physical Disk Disk Transfer Sec 7.9
SQLServer: SQL Statistics: Batch Requests/Sec 195
SQLServer: SQL Statistics: Batch Requests/Sec 338
SQL Server Buffer Mgr: Page Life Expectancy 2351
SQLServer: SQL Statistics: SQL Compilations/Sec counter. 2

I done the research on the PWAIT_CXPACKET and it says it to do with parallelism and to disable parallelism...

I get these locks probably 3 times a week.

Any suggestions.

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-10-06 : 10:01:21

The parallelism is not a problem in and of itself. Odds are, someone has submitted "The Query that ate Sheboygan" on your server. Turning off parallelism on the server will turn it off for queries that benefit from parallelism, as well. In order to get by, you could reduce parallelism to n - 1, where n is the number of your processors. That way, there should be a free processor to continue with the rest of the query load, but it will extend the process that is running you under. Does Impromptu keep any kind of access/query log?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources