Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql performance

Posted on 2014-11-12
33
Medium Priority
?
204 Views
Last Modified: 2014-12-03
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 ?
0
Comment
Question by:Eprs_Admin
  • 18
  • 14
33 Comments
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 2000 total points
ID: 40437109
This is so general that first you need to check whole box for bottlenecks (CPU, Memory, Disk and Network).
You can do that by running a Performance Monitor. At the same time isn't a bad idea to run the SQL Profiler to check for Blocking Locks and long running queries.
And this is only the beginning...
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 40437182
Here is an  easy script I usually run to check for long running queries and blocking processes:

select	
  session_id
, start_time
, command
, sql_text.text as sql_text
, DB_NAME(database_id) as databaseName
, blocking_session_id
, user_name(user_id)		as	userName
, cpu_time
, query_hash
 

from	sys.dm_exec_requests

outer apply sys.dm_exec_sql_text(sql_handle) sql_text

order by cpu_time desc

Open in new window


Good luck.
0
 

Author Comment

by:Eprs_Admin
ID: 40437228
Hello,

I have installed the Performance Dashboard.
Here I can see over 70 locks and a lot of parallelism.

Can you explain the parallelism problem please ?
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40437244
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.
When well used it can be a perform boost but in the other hand can be a big bottleneck if you use it wrong.

To verify the MAXDOP configured in the SQL Server instance run the following in a New Query window:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism',
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;

Open in new window


Can't help you more without seeing the query and the respective query plan.
0
 

Author Comment

by:Eprs_Admin
ID: 40437374
the script comes with an error:

Meldung 102, Ebene 15, Status 1, Zeile 1 - this is german sorry.

But it says ,ther eis an error near the comma (,)
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40437381
It's right. Was a typo. Here the correct code without the extra comma:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism'
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;

Open in new window

0
 

Author Comment

by:Eprs_Admin
ID: 40437857
ok now it works.
But now all users are at home.
So it shows 0.
0
 

Author Comment

by:Eprs_Admin
ID: 40437862
during the day I had a lot of locks.
What can I do against these locks ?
0
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 2000 total points
ID: 40439370
0 (zero) it's the default value and means use all cores for parallelism. Maybe is not a good value for your query in particular but I can't help you more without seeing the query.

The Performance Dashboard didn't identify the queries causing the locks? You should start working on those queries.
0
 

Author Comment

by:Eprs_Admin
ID: 40439586
the queries are very long, which ones you need ?
0
 

Author Comment

by:Eprs_Admin
ID: 40439594
Here I have one long query.
What are the best MAXDOP settings and threshould settings ?

query
MAXDOP = half cores ?
Threshould = 50 ? -> But what is 50 ?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40439598
No one can't says a good value for a MAXDOP for a query only by looking to the code.
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?).

By the way, why do you think that query is the one that has bad performance?
0
 

Author Comment

by:Eprs_Admin
ID: 40442352
This query was found with the performance dashboard.
The SQL Server is physical.
It has 2 CPUs and 8 Cores.

By the way there is an article from Microsoft about this MAXDOP.
They speak about processors less than 8 then put it in.
But do they really mean processors or cores ?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40442362
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.
What you can do with that query in particular is to set MAXDOP=1 so the engine won't parallelize it.
0
 

Author Comment

by:Eprs_Admin
ID: 40464060
when I have 8 cores, why not to set to 4 ?
And can you explain the threshold value of costs ? What is 50 ?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40464077
when I have 8 cores, why not to set to 4 ?
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.

And can you explain the threshold value of costs ? What is 50 ?
Sorry, now you lost me. What you mean with this?
0
 

Author Comment

by:Eprs_Admin
ID: 40473194
How can I check if its NUMA on my server ?
0
 

Author Comment

by:Eprs_Admin
ID: 40473196
Sorry, now you lost me. What you mean with this?

Here I mean the costs of parallelism. This is also set to 5.
But can you explain this a little bit ? What is 5 ?
What the SQL is doing when a query comes and the costs are set to 5 ?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40473221
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.
0
 

Author Comment

by:Eprs_Admin
ID: 40474028
Is it not MAXDOP what you mean ?

Someone told me to put the costs to 50.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40474064
Sorry, now I saw your question about the threshold. Where are you using it?
0
 

Author Comment

by:Eprs_Admin
ID: 40475346
The standard settings on my sql cluster are :

MAXDOP -> 0
Costs -> 5

But I get  a lot of parallelism waits.
I have to change this.

My hardware is :
2 processors of INTEL XEON 5460 at 3.16 GHz

In total I see 8 CPUs in the task Manager.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40475355
Try to change MAXDOP to 4. If you still having locks then reduce it to 1.

Can't see what's COSTS->5
0
 

Author Comment

by:Eprs_Admin
ID: 40475436
Ok, when I understand the right way, I can choose MAXDOPS until 8.
But 1 is not logic, because this means just one CPU and then parallelism is off.
With MAXDOP=0 means they use default all processors and cores.
0
 

Author Comment

by:Eprs_Admin
ID: 40475438
The costs are the field over the MAXDOP field : cost threshold for parallelism
This is set to 5 by default. This means 5 seconds.
0
 

Author Comment

by:Eprs_Admin
ID: 40475439
Do I have to reboot, when I change MAXDOP or THRESHOULD ?
0
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 2000 total points
ID: 40475462
Ok, when I understand the right way, I can choose MAXDOPS until 8.
 But 1 is not logic, because this means just one CPU and then parallelism is off.
You should let the engine to chose to use or not the parallelism but since you are telling to always use it will but that doesn't mean that will work better.
From MSDN article (point 3):
"To distinguish between queries that benefit from parallelism and those that do not benefit, The Database Engine compares the estimated cost of executing the query or index operation with the cost threshold for parallelism value. Although not recommended, users can change the default value of 5 using sp_configure. "
So you shouldn't touch the costs option also.
Do I have to reboot, when I change MAXDOP or THRESHOULD ?
No.
0
 

Author Comment

by:Eprs_Admin
ID: 40475763
But I have to do something.
Always I have PARALLELISM WAITS, sometimes over 20 waits.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40475766
Did you try to run the query without the MAXDOP option?
0
 

Author Comment

by:Eprs_Admin
ID: 40476296
I cannot change anything in the query, because it comes from the application.
So MAXDOP is still default on my end = 0.
But I changed the THRESHOULD from 5 to 50.
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40476419
The threshold don't make nothing if the query has MAXDOP option set. The threshold is only to say to the engine when he can parallelize the queries. So you set it to 50, means that queries that takes less than 50 seconds won't be parallelized. Hope that is that you want.
0
 

Author Comment

by:Eprs_Admin
ID: 40478200
What I want is -> no waits.

When the threshould was on 5 I had a lot of waits.
Now I have no waits anymore.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40478255
When the threshould was on 5 I had a lot of waits.
 Now I have no waits anymore.
Means that the engine isn't parallelize the query so it's the same as MAXDOP=1
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question