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.
| Author |
Topic |
|
miranwar
Posting Yak Master
125 Posts |
 Posted - 2007-03-06 : 08:19:05
|
| Hi we have an Company Search SP that is constantly being called in our Global Web application. Now we have had a serious issue with Blocking over the last two weeks. I’ve had a look at the SQL 2000 database via profiler and have done a filter of all queries lasting more that 20 seconds. The company Search SP seems to be the main culprit of the longest running transaction in the database with average time of 76 seconds. This SP is constantly pounding the system. Looks like people are doing a full criteria search on the website, and the query brings back on average 22000 rows. The SP uses Dynamic SQL and also contains a few columns that contain text columns in the final result set.The SP builds up a dynamic SQL and inserts it into a temp table. It then updates numerous fields in the #temp table before returning the final result set. My question is there any way I can minimise the time of the query so that no excessive locks are created? I’ve had a look at all the indexes all seems to be Ok I’ve updated the stats for all the tables with a full scan. I’ve been running the following query below in analyzer to check what type of locks are created when running the SP and seems to be mostly CXPacket Wait Types with no blocks the wait time varies between 15 and 27 seconds. CXPacket as I understand is to do with running parallel queries. I have further revisited profiler and looks like the Parallel query is put into action when The SP inserts into the temp table and updates it. Could this be a problem with contention in the temp DB database? If so how could I fix this?Your thoughts on this would be appreciated.Cheers,WHILE 1<2BEGINIF EXISTS(SELECT blocked, spid, waittime, lastwaittype, waitresource FROM master..sysprocesses WHERE waittime > 5000 --The wait time is measured in milliseconds AND spid > 50SELECT blocked, spid, waittime, lastwaittype, waitresource FROM master..sysprocesses WHERE waittime > 5000 --The wait time is measured in milliseconds AND spid > 50WAITFOR DELAY '00:00:2'END |
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-03-07 : 13:43:47
|
| if you have a desktop version of sql 2005, you could run the stored procedure through the tuning advisor. try this once...EXEC sp_configure 'max degree of parallelism, 1GORECONFIGURE with override |
 |
|
|
|
|
|
|
|
CXPACKET Wait Types Slow Query