none
Can i go ahead including hint: Option (MAXDOP 1) in my Backend Stress Test

    Question

  • I am performing backend (SQL Server2008) stress testing using VSTS2008.I am using some select quries in my Database Unit testing and using this in my load Test script.

     

    I have run the load test for 200, 100 and 50 Virtual users.Each time i am getting below errors.

     

    Error 1:DatabaseUnitTest1.DatabaseTest1 threw exception: System.Data.SqlClient.SqlException: The query processor could not start the necessary thread resources for parallel query execution.(error Count 1000)

     

    As the description says, processor could not start the necessary thread resources for parallel query execution, so, there might be a cause that, Parallel queries execution is much slower. So i have included hint: Option (MAXDOP 1) in the Database Unit Test.

    Hint: Option (MAXDOP 1)

    The Max Degree of Parallelism or MAXDOP is a configuration indicating how the SQL Server optimizer will use the CPUs. Option (MAXDOP 1) is a hint. This will run the query much faster. When SQL Server uses parallelism, it runs slower. This is a server wide configuration that by default uses all of the CPUs to have the available portions of the query executed in parallel. So the query is executing much faster and there is no thread allocation issue.

     

    Again i ran the load test script starting 50, 75,100,150,200,250,300,400 users. The entire test run successfully without any performance related issues (Previous error message is not coming now).

     

    Can i go ahead with this approach? Can I analyze my database server performance by including hint: Option (MAXDOP 1) in my load test?

    Wednesday, September 08, 2010 1:21 PM

Answers

  • Hello,

    Are those Select – Queries representative the real workload the server will have?

    Could you please set MAXDOP as configured by default again, restart MSSQLServer service, run your test workload again, and then run the following T-SQL code and post in this thread the results:

    SELECT TOP 15 *
    FROM sys.dm_os_wait_stats
    ORDER BY wait_time_ms DESC

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, September 09, 2010 2:16 AM
    Answerer

All replies

  • Hi,

     

    I am performing backend (SQL Server2008) stress testing using VSTS2008.I am using some select quires in my Database Unit testing and using this in my load Test script.

     

    I have run the load test for 200, 100 and 50 Virtual users. Each time i am getting below errors.

     

    Error 1:DatabaseUnitTest1.DatabaseTest1 threw exception: System.Data.SqlClient.SqlException: The query processor could not start the necessary thread resources for parallel query execution.(error Count 1000)

     

    As the description says, processor could not start the necessary thread resources for parallel query execution, so, there might be a cause that, Parallel queries execution is much slower. So i have included hint: Option (MAXDOP 1) in the Database Unit Test.

    Hint: Option (MAXDOP 1)

    The Max Degree of Parallelism or MAXDOP is a configuration indicating how the SQL Server optimizer will use the CPUs. Option (MAXDOP 1) is a hint. This will run the query much faster. When SQL Server uses parallelism, it runs slower. This is a server wide configuration that by default uses all of the CPUs to have the available portions of the query executed in parallel. So the query is executing much faster and there is no thread allocation issue.

     

    Again i ran the load test script starting 50, 75,100,150,200,250,300,400 users. The entire test run successfully without any performance related issues (Previous error message is not coming now).

     

    Can i go ahead with this approach? Can I analyze my database server performance by including hint: Option (MAXDOP 1) in my load test?

     

    • Moved by Darren GosbellMVP Wednesday, September 08, 2010 1:31 PM This question does not relate to Analysis Services (From:SQL Server Analysis Services)
    • Merged by Tom Li - MSFT Thursday, September 09, 2010 12:21 PM duplicate thread
    Wednesday, September 08, 2010 1:22 PM
  • Hi,

     

    I am performing backend (SQL Server2008) stress testing using VSTS2008.I am using some select quires in my Database Unit testing and using this in my load Test script.

     

    I have run the load test for 200, 100 and 50 Virtual users. Each time i am getting below errors.

     

    Error 1:DatabaseUnitTest1.DatabaseTest1 threw exception: System.Data.SqlClient.SqlException: The query processor could not start the necessary thread resources for parallel query execution.(error Count 1000)

     

    As the description says, processor could not start the necessary thread resources for parallel query execution, so, there might be a cause that, Parallel queries execution is much slower. So i have included hint: Option (MAXDOP 1) in the Database Unit Test.

    Hint: Option (MAXDOP 1)

    The Max Degree of Parallelism or MAXDOP is a configuration indicating how the SQL Server optimizer will use the CPUs. Option (MAXDOP 1) is a hint. This will run the query much faster. When SQL Server uses parallelism, it runs slower. This is a server wide configuration that by default uses all of the CPUs to have the available portions of the query executed in parallel. So the query is executing much faster and there is no thread allocation issue.

     

    Again i ran the load test script starting 50, 75,100,150,200,250,300,400 users. The entire test run successfully without any performance related issues (Previous error message is not coming now).

     

    Can i go ahead with this approach? Can I analyze my database server performance by including hint: Option (MAXDOP 1) in my load test?

    • Merged by Tom Li - MSFT Thursday, September 09, 2010 12:21 PM duplicate thread
    Wednesday, September 08, 2010 1:23 PM
  • Hi,

     

    I am performing backend (SQL Server2008) stress testing using VSTS2008.I am using some select quires in my Database Unit testing and using this in my load Test script.

     

    I have run the load test for 200, 100 and 50 Virtual users. Each time i am getting below errors.

     

    Error 1:DatabaseUnitTest1.DatabaseTest1 threw exception: System.Data.SqlClient.SqlException: The query processor could not start the necessary thread resources for parallel query execution.(error Count 1000)

     

    As the description says, processor could not start the necessary thread resources for parallel query execution, so, there might be a cause that, Parallel queries execution is much slower. So i have included hint: Option (MAXDOP 1) in the Database Unit Test.

    Hint: Option (MAXDOP 1)

    The Max Degree of Parallelism or MAXDOP is a configuration indicating how the SQL Server optimizer will use the CPUs. Option (MAXDOP 1) is a hint. This will run the query much faster. When SQL Server uses parallelism, it runs slower. This is a server wide configuration that by default uses all of the CPUs to have the available portions of the query executed in parallel. So the query is executing much faster and there is no thread allocation issue.

     

    Again i ran the load test script starting 50, 75,100,150,200,250,300,400 users. The entire test run successfully without any performance related issues (Previous error message is not coming now).

     

    Can i go ahead with this approach? Can I analyze my database server performance by including hint: Option (MAXDOP 1) in my load test?

     

    • Merged by Tom Li - MSFT Thursday, September 09, 2010 12:21 PM duplicate thread
    Wednesday, September 08, 2010 1:26 PM
  • This is probably more a question for the SQL Server database engine forum but I suspect a moderator will move this thread there shortly.

    Having said that, I would argue that setting the maximum degree of parallelism to 1 kinda defeats the purpose of the load test. Essentially you have found the point where your system breaks (stress testing not load testing) and as such now need to throttle back the activity to get the system to run such that you can then continue your load testing and monitor the performance metrics.

    Essentially, are you going to set MAXDOP to 1 in production as well? If not, then you are not load testing on a representative system. If you are then fair enough but you are possibly killing general performance of this query based on a loaded system instead of throttling the general activity on the system. (i.e. It is quite possible that other queries will raise this error if you generate enough activity, so will you set those to MAXDOP 1 too? Where would you stop?)

    See the following for more information on this error: http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/1901f6e1-2dc3-4763-874d-d5420345eb3f

    Wednesday, September 08, 2010 1:44 PM
  • Making MAXDOP 1 will not give you the real performance in a multi core environment as all the tasks in execution plan will go sequentially without parallelism.

    I would recommend you to keep MAXDOP <No of Processor in your box> and try

    Wednesday, September 08, 2010 1:44 PM
  • Ideally your test box and the production box should have the same number of processors and physical RAM. If not, then it is not an identical load test.

    This errors happens because you are unable to spawn child worker threads for your query. Your efforts should be focused towards finding out why this query is going parallel in the first place and check if that can be prevented.

    Unless you are using connection pooling, such a load test is not scalable. If you concurrently spawn 200 concurrent threads which need to start parallel execution, then it is quite possible, to run out of worker threads because the number of available worker threads is limited. However, there is a queuing mechanism that is implemented in SQL Server for worker threads which allows work requests to be queued and also worker threads to be reused. It might be possible that the query itself that you are testing requires some amount of tuning from either a query re-write or indexing standpoint and then you might be able to run your load (not stress) test with a large number of concurrent users. But it is highly unlikely that 400+ users will start executing the same query at the same nano-second concurrently. There is always a slight difference in the start times from a CPU clock cycle standpoint which allows SQL to maintain the queues and workloads appropriately.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.wordpress.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Wednesday, September 08, 2010 3:41 PM
  • Hello,

    Are those Select – Queries representative the real workload the server will have?

    Could you please set MAXDOP as configured by default again, restart MSSQLServer service, run your test workload again, and then run the following T-SQL code and post in this thread the results:

    SELECT TOP 15 *
    FROM sys.dm_os_wait_stats
    ORDER BY wait_time_ms DESC

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, September 09, 2010 2:16 AM
    Answerer
  • Assuming you are going to change your application, yes. The other option is set max degree of parallelism to 1 for the entire SQL Server instance with sp_config. Although this will affect ALL queries including backup/reindex jobs etc (unless you overide MAXDOP in those jobs).
    Thursday, September 09, 2010 4:13 AM
  • Hi,

    I totally agree with Stephen rather than going with MAXDOP on each query level try to set it at Instance level and then run your test and see how it works.

     


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/
    Thursday, September 09, 2010 4:41 AM
  • Yes, I agree..You need to do a through analysis on your Server performance with using hints and also without using hints to find out what is the exact issue?

    If the issue is not coming always then it might be some thing hardware related issue. So please do a through analysis and go ahead with correct solution.

    thanks,

     


    - MS (We are what we repeatedly do, Excellence, then, is not an act, but a habit.)
    Thursday, September 09, 2010 5:25 AM
  • 400 users. The entire test run successfully without any performance related issues


    Do you really believe you will not have performance issues with 400 users? What is @@version? HW platform? Disk configuration?

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Thursday, September 09, 2010 5:34 AM
    Moderator
  • Hi amar1921,

    I agree with the other two posters, in that the problem would seem to be with your Benchmarking/Performance testing tool, rather than SQL Server.

    Personally however I would not be comfortable applying a server wide configuration change that could potentially impact overall performance, that is by setting MAXDOP to 1, without extensive testing beforehand. Interestingly this practice is sometimes used for OLTP workloads but you have not provided any background to your specific type of workload produced by your application.

    Out of interest, What Edition of SQL Server 2008 are you running in the background for your testing?


    John Sansom | SQL Server DBA in the UK
    Thursday, September 09, 2010 6:21 AM
  • Parallelism may or may not slow down perfromance. For example if you rebuild the index SQL Server uses parallelism to perform the operation as fast as possible. On the other hand there are specifict queries where it hurts performance and you need to understand where to use that hint and where does not.
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, September 09, 2010 8:46 AM
    Moderator
  • My test lab needs to be a replication of Production environment. In the production code MAXDOP1 has not been used. So i have excluded this from my Database Unit Test and executing my Load Test Script.

     

    Thanks

    Amar

    Wednesday, September 15, 2010 5:52 AM