Intra-query parallelism caused your server command (process ID #51) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).
-
Error I was receiving :-
Server: Msg 8650, Level 13, State 127, Line 1
Intra-query parallelism caused your server command (process ID #51) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).
Background Information
SQL 2005 Enterprise Edition default Instance SP1
Windows 2003 Enterprise edition
4GB RAM + RAID 10 + 2 Controllers
Database is normalized to 3NF or even higher
Adequate indexes are present and peform extremly well.
The old error thats been bugging SQL 6.5, 7.0 and 2000 is back again !!
The application uses Java, EJB, SQL 2005, Reporting Services 2005, Actuate and Hibernate.
Possible Solutions :-
Microsoft recommends doing this :-
My 2 cents on this topic :-
The old error thats been bugging SQL 6.5, 7.0 and 2000 is back again !! And in my case, Performance tuning the application helped me a lot to get rid of this error.
HIBERNATE is a open-source Java based tool uses for mapping the Relational database model to a Object Oriented Database Model by using a Class.
So what happens is that each column (attribute of the tables in SQL) is mapped to a class of attibutes (which represent the columns in the OOPS World) and the relationship is stored in an XML File.
so when the application uses Hibernate, the intelligence built inside this tool generates its own queries (eg:- in a search criteria) And most of the time, it uses a crappy way of calling the views or querying the tables. It uses "sp_prepexec" eg:-
declare @P1 int
set @P1=NULL
exec sp_prepexec @P1 output, N'@P1 int', N'select top 50 this_.watch_list_id as watch1_1_0_, this_.fsp_entity_id as fsp2_1_0_,
this_.hrf ,
this_.risks ,
this_.total_tda,
this_.assigned_case_worker_id as assigned62_1_0_
from watch_list_data_vw this_
where this_.state_id = @P1
order by this_.hrf desc,
this_.risks desc,
this_.tda desc',22
select @P1
In my case, i cannot use sp_configure to set the parallelism to 1
I cannot use option (maxdop 1) as its a view and these hints cannot be used in a view.
All you have to do is know how to use the SQL Profiler, replicate the scenario in DEV and capture the results of the deadlock in a table. Then use this output to determine how many tables/views were used in the execution plan. After you determine the object names, open the Tuning wizard and feed it the trace table and let it analyze the tables that came up in the query. It will come up with the recommended indexing solutions.
In my case, it did come up with 4 composite indexes on 4 tables ( one on each ).
Also, it created 5 statistics on a few columns ............. and whoa.................
the error disappeared !!
I hope this post will help a few DBA's / Developers debug the issue and contribute to this post.
Thanks !
Kunal Gandre
Snr. DBA / Developer
Kindest Regards,Kunal Gandre
>
Snr. SQL DBA -
Interesting... thanks for sharing your experience...
Drop an email to MS guys to look at it or to file a bug...
MohammedU
Microsoft SQL Server MVP -
this raises some interesting points :-
1. views can be bad for performance, the concept of putting a bad query into a view for simplicity to make the schema easy doesn't resolve the problem of a poor query.
2. indexing was not adequate, I find poor queries and parallelism can usually be resolved with useful indexes - this isn't too difficult to understand - parallelism cuts in based upon the cost of a query, if the query is poorly optimised ( or indexed ) then the cost rises , for instance a select * on a large table which scans can tip the query cost over the parallelism cost and invoke parallelism causing blocking ( to its self ) and excessive io.
Sorry but I don't see anything unusual in your experience or why you'd want to contact microsoft. I've been performance tuning databases and applications for many years, this is just a part of P & T of a database.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Yes. You are right........... I did not contact microsoft for this problem.
The problem could have been eliminated in 3 ways :-
1.> using sp_configure to set the MAX PARALLELISM to 1
2.> Using HINT MAXDOP (1)
3.> Tuning the query....... and adding more indexes and creating more statistics on a few columns.
I was unable to go for option 1 as i do not know what will be the effect of turning the max parallelism to 1 ( w.r.t the application as the system is up and running for 3 yrs without any major issues )
I was unable to use option 2 as HIBERNATE does not give me any control over the kind of queries it is going to write. Eg:- the view which i wrote returns the results in 4 secs flat. But hibernate calls it using sp_prepexec . And moreever to limit the query results to 50 per page, HIBERNATE adds a TOP operator to the query it writes. HINT :- There are better ways to write this code. The query for this view should be cached in HIBERNATE and then it should return the results as per the 50 search results per page. But i am not a front end developer !! Just a DBA.....
Hopefully, this post will help a few people who might get this error to solve them.
Kindest Regards,Kunal Gandre
>
Snr. SQL DBA -
Turning off parallelism for the entire server is not a good thing as many other things may go slow, backups, index rebuilds, reports etc. etc. I have one server where I turned off parallelism, the majority of queries were table scanning so were thus generating parallel plans and causing lots of blocking. I've tuned most of the queries now but still haven't turned on parallism yet. With some of the newer boxes with dual core and HT I figure parallelism may well give issues! I have a new 4 way dual core box ( with HT ) that shows 16 processor threads.
Not being able to add hints to views is a problem I haven't found a solution to ( yet ) I perhaps need to think another way as I'm sure it's possible one way or another.
Indexing is a bit of a black art, but I will say that the 2005 advisor is much better than the 2000 wizard and it will give you a fighting chance to inmprove performance, it still doesn't always get it right though!
Parallelism is an oft posted subject and I'm sure it causes many problems.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I have tried this and it works fine........ created a function....... it works good. But i am in the last phase of QA and this will change everything !! So keeping quiet over it.
CREATE FUNCTION test_fn()
RETURNS @temp_table TABLE(
column1 INT,
column2 INT
)
AS
BEGIN
INSERT INTO @temp_table(column1,column2)
SELECT column1, column2
FROM culprit_view
WHERE column8 = 1
AND column9 IS NULL
OPTION (MAXDOP 1)
RETURN
END
GO
-----------------------
SELECT * FROM dbo.test_fn()
WHERE column1 = 99
-----------------------
> Not being able to add hints to views is a problem I haven't found a solution to ( yet ) I perhaps need to think another way as I'm sure it's possible one way or another.
Kindest Regards,Kunal Gandre
>
Snr. SQL DBA -
yes table valued functions are one way around this, although you have to be a bit carefull when you use them inside a complex join.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
SQL server has plan guides, which is designed exactly to solve the issue you are facing by a vendors queries. E.g. if the vender's client sends a quey:
SELECT * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC
You can create the follwoing guide to force it not to use paralleized plan:
sp_create_plan_guide @name = N'MyGuide1', @stmt = N'SELECT * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC', @type = N'SQL',@module_or_batch = NULL, @params = NULL, @hints = N'OPTION (MAXDOP 1)'
Please check BOL for details
-
yes this is a way and should work for defined queries but maybe of less help where the query varies. I've not used this technique so I guess I should do some testing!!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
This will work only on SQL 2005 ENTERPRISE and STANDARD Editions.
Is there an alternative in SQL 2000 for the same issue ?
Kindest Regards,Kunal Gandre
>
Snr. SQL DBA -
Kunal,
You said in your first post that you had this issue in SQL2005 Enterprise endition, and my answer is definitley for sql 2005.
For SQL 2000, I have no idea.
-
sadly not, although table valued functions are fine in 2000
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply