ERROR : The query processor could not start the necessary thread resources for parallel query execution.
Fix/Solution/WorkAround:
There is no real solution for this error. This error happens due to server is very busy and it can be resolved itself when server load is reduced to acceptable level. There is no way to know what is acceptable level of load for server.
Reference : Pinal Dave (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
11 Comments. Leave new
(I assume we are talking abt production scenario coz in dev these things are not considered imp, it is just a testing plateform)
Pinal I would like to add something here. typically before a server is put into production we put several threshholds for different performance threads like CPU, HDD, Memory. what should be the threshhold.
once threshhold is acheived, an alert is generated automatically to the operators defined in sql.
there are different softwares used for setting up alerts and deciding the threshholds like NETIQ, MOM.
Please let me know if anybody wants more details on this.
Regards,
Amit Pandey
SQL Server DBA Support India
Hi!
I would like make better my SQL knowledge.
I red that many SQL books and still feel, that I am not a whise man
in SQL. What can you recommend?
Thanks,
Werutz
Brent Ozar has really good classes and is a hands on kind of teacher.
Brent is Awesome!
option (maxdop 1)
Hello sir,
while creating a table in php it gave me a query #1063 sayin that : #1063 – Incorrect column specifier for column ‘full_name’ .
it showed this :
Error
SQL query:
CREATE TABLE `darpan_darpan`.`profile` (
`full_name` TEXT NOT NULL AUTO_INCREMENT ,
`email` TEXT NOT NULL ,
`present_city` TEXT NOT NULL ,
`birth_date` DATE NOT NULL ,
`age` INT NOT NULL ,
`contact_no` BIGINT NOT NULL ,
`home_town` TEXT NOT NULL ,
`high_school` TEXT NOT NULL ,
`college` TEXT NOT NULL ,
`year` TEXT NOT NULL ,
`career_intrest` TEXT NOT NULL ,
`hobbies` TEXT NOT NULL ,
`future_plans` TEXT NOT NULL ,
`update` INT ON UPDATE CURRENT_TIMESTAMP NULL ,
PRIMARY KEY ( `full_name` )
) ENGINE = MYISAM CHARACTER SET latin1 COLLATE latin1_swedish_ci;
MySQL said: Documentation
#1063 – Incorrect column specifier for column ‘full_name’ .
your guidance will surely be helpful .
thanking you .
yours sincerely
Prasad
Hi Pinal and Amit
Can you please update me on this error “The query processor could not start the necessary thread resources for parallel query execution” whether we have any permenet solution for this…
Thanks in advance.
Sandeep
Though I am late in replying but pls check the following:
select scheduler_id, current_tasks_count, runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id 1, it would mean that the processor is heavily utilized.
Please check the processor affinity and MDOP settings.
In case they are not set to optimum you will need to change them.
Additionally you can set a performance counter to check how much is the utilization.
you can reach me at [Email addressed Removed] to get any further assistance.
Amit Pandey
SQL Server DBA
RBS
I will immediately grasp your rss as I can not find your email subscription link or e-newsletter service.
Do you have any? Please let me recognize so that I could subscribe.
Thanks.
Following has fixed my problem :
This error is from building execution plan, which is trying to parallelism complex queries. It is displayed immediately, query doesn’t wait for timeout. Simple queries work as a charm.
The solution was to disable Max degree of parallelism by setting it to 1. I’m not sure about performance effects of this setting, but as there are many concurrent queries on the server anyway, it shouldn’t be an issue.
Encountered this error when processing a ssas dimension on a complex view.
Works after changing dimension UnknownMember from None to Visible