<div>
Here is an &nbsp;easy script I usually run to check for long running queries and blocking processes:<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-40437182-1"><span>select	</span>
<span>  session_id</span>
<span>, start_time</span>
<span>, command</span>
<span>, sql_text.text as sql_text</span>
<span>, DB_NAME(database_id) as databaseName</span>
<span>, blocking_session_id</span>
<span>, user_name(user_id)		as	userName</span>
<span>, cpu_time</span>
<span>, query_hash</span>
<span> </span>
<span></span>
<span>from	sys.dm_exec_requests</span>
<span></span>
<span>outer apply sys.dm_exec_sql_text(sql_handle) sql_text</span>
<span></span>
<span>order by cpu_time desc</span>
</code></pre>
<br />
Good luck.
</div>


<div>
Hello,<br />
<br />
I have installed the Performance Dashboard.<br />
Here I can see over 70 locks and a lot of parallelism.<br />
<br />
Can you explain the parallelism problem please ?
</div>


<div>
Parallelism can be explicit (you set it in the query with MAXDOP option) or implicit (the engine will decide to use it or not depending on the conditions) and it's used to split a query execution in many task as existing CPU/core or as many is set by the MAXDOP option.<br />
When well used it can be a perform boost but in the other hand can be a big bottleneck if you use it wrong.<br />
<br />
To verify the MAXDOP configured in the SQL Server instance run the following in a New Query window:<br />

<pre><code class="code-10 nolinks" id="code-20-40437244-1"><span>EXEC sp_configure 'show advanced options', 1;</span>
<span>GO</span>
<span>RECONFIGURE WITH OVERRIDE;</span>
<span>GO</span>
<span>EXEC sp_configure 'max degree of parallelism',</span>
<span>GO</span>
<span>RECONFIGURE WITH OVERRIDE;</span>
<span>GO</span>
<span>EXEC sp_configure 'show advanced options', 0;</span>
<span>GO</span>
<span>RECONFIGURE WITH OVERRIDE;</span>
</code></pre>
<br />
Can't help you more without seeing the query and the respective query plan.
</div>


<div>
the script comes with an error:<br />
<br />
Meldung 102, Ebene 15, Status 1, Zeile 1 - this is german sorry.<br />
<br />
But it says ,ther eis an error near the comma (,)
</div>


<div>
It's right. Was a typo. Here the correct code without the extra comma:<br />

<pre><code class="code-10 nolinks" id="code-20-40437381-1"><span>EXEC sp_configure 'show advanced options', 1;</span>
<span>GO</span>
<span>RECONFIGURE WITH OVERRIDE;</span>
<span>GO</span>
<span>EXEC sp_configure 'max degree of parallelism'</span>
<span>GO</span>
<span>RECONFIGURE WITH OVERRIDE;</span>
<span>GO</span>
<span>EXEC sp_configure 'show advanced options', 0;</span>
<span>GO</span>
<span>RECONFIGURE WITH OVERRIDE;</span>
</code></pre>
</div>


<div>
ok now it works.<br />
But now all users are at home.<br />
So it shows 0.
</div>


<div>
during the day I had a lot of locks.<br />
What can I do against these locks ?
</div>


<div>
the queries are very long, which ones you need ?
</div>


<div>
Here I have one long query.<br />
What are the best MAXDOP settings and threshould settings ?<br />
<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w46/882825/query-text.JPG" target="_blank" title="query (89 KB)"><img alt="query" class="file-block lazyload" style="max-width: 586px;" data-src="https://filedb.experts-exchange.com/incoming/2014/11_w46/882825/query-text.JPG" /></a><br />
MAXDOP = half cores ?<br />
Threshould = 50 ? -&gt; But what is 50 ?
</div>


query-text.JPG

<div>
No one can't says a good value for a MAXDOP for a query only by looking to the code.<br />
You need to provide the query plan and the server configuration (it's a VM or physical machine? Has hyperthreading on? How many CPU? How many cores?).<br />
<br />
By the way, why do you think that query is the one that has bad performance?
</div>


<div>
This query was found with the performance dashboard.<br />
The SQL Server is physical.<br />
It has 2 CPUs and 8 Cores.<br />
<br />
By the way there is an article from Microsoft about this MAXDOP.<br />
They speak about processors less than 8 then put it in.<br />
But do they really mean processors or cores ?
</div>


<div>
They mean cores. But if your server has hyper threading then you need to count only half of the cores and if it's NUMA architecture then you should only count the cores for one CPU.<br />
What you can do with that query in particular is to set MAXDOP=1 so the engine won't parallelize it.
</div>


<div>
when I have 8 cores, why not to set to 4 ?<br />
And can you explain the threshold value of costs ? What is 50 ?
</div>


<div>
<blockquote>
when I have 8 cores, why not to set to 4 ?
</blockquote>
If it's a Quadcore NUMA architecture, then it's recommend to set the value to 4. If not, 8 it's the recommend value but since it's only a recommendation, you need to make tests to see if with 4 the performance is better.<br />
<br />

<blockquote>
And can you explain the threshold value of costs ? What is 50 ? 
</blockquote>
Sorry, now you lost me. What you mean with this?
</div>


<div>
How can I check if its NUMA on my server ?
</div>


<div>
<blockquote>
Sorry, now you lost me. What you mean with this?
</blockquote>
<br />
Here I mean the costs of parallelism. This is also set to 5.<br />
But can you explain this a little bit ? What is 5 ? <br />
What the SQL is doing when a query comes and the costs are set to 5 ?
</div>


<div>
Means that it will parallelize the query to run in 5 cores at same time. If the server has 8 cores, five of them will be used to run the query and the others three cores will be available for other tasks.
</div>


<div>
Is it not MAXDOP what you mean ?<br />
<br />
Someone told me to put the costs to 50.
</div>


<div>
Sorry, now I saw your question about the threshold. Where are you using it?
</div>


<div>
The standard settings on my sql cluster are :<br />
<br />
MAXDOP -&gt; 0<br />
Costs -&gt; 5<br />
<br />
But I get &nbsp;a lot of parallelism waits.<br />
I have to change this.<br />
<br />
My hardware is :<br />
2 processors of INTEL XEON 5460 at 3.16 GHz<br />
<br />
In total I see 8 CPUs in the task Manager.
</div>


<div>
Try to change MAXDOP to 4. If you still having locks then reduce it to 1.<br />
<br />
Can't see what's COSTS-&gt;5
</div>


<div>
Ok, when I understand the right way, I can choose MAXDOPS until 8.<br />
But 1 is not logic, because this means just one CPU and then parallelism is off.<br />
With MAXDOP=0 means they use default all processors and cores.
</div>


<div>
The costs are the field over the MAXDOP field : cost threshold for parallelism<br />
This is set to 5 by default. This means 5 seconds.
</div>


<div>
Do I have to reboot, when I change MAXDOP or THRESHOULD ?
</div>


<div>
But I have to do something.<br />
Always I have PARALLELISM WAITS, sometimes over 20 waits.
</div>


<div>
Did you try to run the query without the MAXDOP option?
</div>


<div>
I cannot change anything in the query, because it comes from the application.<br />
So MAXDOP is still default on my end = 0.<br />
But I changed the THRESHOULD from 5 to 50.
</div>


<div>
What I want is -&gt; no waits.<br />
<br />
When the threshould was on 5 I had a lot of waits.<br />
Now I have no waits anymore.
</div>


<div>
<blockquote>
When the threshould was on 5 I had a lot of waits.<br />
&nbsp;Now I have no waits anymore. 
</blockquote>
Means that the engine isn't parallelize the query so it's the same as MAXDOP=1
</div>


<div>
<div class="content wysiwyg-content">
Hi Experts,<br />
<br />
i have an SQL 2008 cluster in my network and the user tell me a very bad performance.<br />
Sometimes they wait 5 minutes to finish a task.<br />
<br />
Can you help me and guide me to find these performance killers ?
</div>
</div>


Hi Experts,

i have an SQL 2008 cluster in my network and the user tell me a very bad performance.
Sometimes they wait 5 minutes to finish a task.

Can you help me and guide me to find these performance killers ?

sql performance

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Windows Server 2008 and Windows Server 2008 R2, based on the Microsoft Vista codebase, is the last 32-bit server operating system released by Microsoft. It has a number of versions, including including Foundation, Standard, Enterprise, Datacenter, Web, HPC Server, Itanium and Storage; new features included server core installation and Hyper-V.