Locking !!

[sendba]

Hai All

1. Previously the Max Degree of parallelism was set to 4 and when i set it to 0 my Whole database was DeadLocked.Please let me know wat will be the issue and how to solve this.I used SP_CONFIGURE for changing the Max Degree of parallelism.

2. I have created new indexes on a tables is that necessarry to create statistics manually or else SQL Server will take care of it. If not,How to Update the statistics for a particular table.

Thanks in Advance

Regards
Senthil

 

[NoCoolHandle]

I dont' know about 1. so I will attach 2.

If your table had rows when you built the index it has statistsics for the table.

However you will need to update them from time to time. YOu can do this by rebuilding your indexes periodicly (which should be done to keep fragmentation at bay) or by issueing a
sp_updatestats
or
CREATE STATISTICS

 

[vongrunt]

1. Turn it back to 4

Deadlock on thread or lock resources? And how many processors (physical, virtual) your system has anyway?

2. Server will take care of it, unless database is configured otherwise. See sp_dboption for more details, options auto create/update statistics.

To manually update stats on table (still good idea because autosampling is not perfect), use UPDATE STATISTICS - with FULLSCAN, ALL options or something.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[sendba]

My SQL Server Box is having 16 CPU with 32 CPU hyperthreading and 32 GB RAM.

Please let me know should i create a statistics for each and every Indexed Column or create one statistics for the whole indexed column

Regards
senthil

 

[Moptop]

Hello
I'm not sure that this will help, but . . .
We have issues with SQL2000SP3 where queries deadlock themselves with the "max degree of par..." message. We found that the problem is that SQL escalates locks to actually lock the physical areas of the disk. To work around the problem we have found that adding clustered indexes to all tables seems to segregate the tables better and the deadlocking problem goes away - although we still have other parallelism issues.
Yours
Mark Elvin

 

[hmckillop]

We had the same problem of threads deadlocking, we are lucky enough to have a gold contract with Microsoft, so we got in a SQL expect from MS.
He informed us that the parallel setup of SQL will cause deadlocking on parallel threads and in some cases it becomes unrecoverable.

Best bet is to disable parallel processing
Have a look at

http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=42675&DisplayTab=Article

"I'm living so far beyond my income that we may almost be said to be living apart