SQL Server Performance Forum – Threads Archive

sql query slow on a quad processor

We have 3 SQL server – 2.4Ghz 1 g ram – dual xeon 2Ghz 4 g ram – quad xeon 3G hyper trade 8 g ram the faster machine for our query is 2.4 single processor and the slowest is the quad can you tell me why ???? eg: we have 12 store proc. On the first : 10 secondes and 1 secondes everytime we run those SP after On the third : 14 secondes and 14 secondes everytime we run those SP after Thanks

---

First, is your testing fair? In other words, are the databases exact? Are the SQL Server setting exact? Have you updated statistics on all the databases recently? Is the load the same on all the servers during testing? If everything is identical, are the execution plans the same, or different. If everything is identical, the plans should be identical. If they are different, then something is different among the servers, and by taking a close look at them, you may be able to see what differently is going on. Also, if the SP runs on a single thread, multiple CPUs won’t help performance, and the faster CPU speed will win out. So, if the single CPU server has a faster CPU than the other servers, and the SP only generates a single thread, then it should run faster than the multiple CPU servers, assuming everything else is the same. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

---

i would first disable HT, run the tests again, then disable parallel processing and rerun the test

---

Joe is right on shot, correct the problem rather than adding up resources. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

<RANT>
SET MAXDOP = 1 on that particular query. Disabling HT across the board doesn’t fix the problem. It fixes the symptom. Can you post the procedure you’re using to test this?
</RANT>
MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Hi, i disabled the HT, good for that our performance is better how can i disable parallel processing ?? and SET MAXDOP = 1 no difference between the other.
thanks

---

Enterprise Manager, Database properties, processor, set 1 processor to parallel processing.<br /><br />BTW<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />errick and Joe, I see severals post about HT and both have differents opinions. Please would you development each idea, because I don’t know about it.<br /><br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br />

---

What OS, SQL Server version, OS Service Pack, and SQL Server Service Packs are you running supporttja?
Luis, the reason I’m so adament about this is because we spent weeks studying it, reading white papers, and testing it on our systems. Enabling hyper-threading caused us some problems as well. Having the right service packs and updates on everything fixed some of them. Tuning other queries and batch processes fixed others. By tuning out queries that had problems with it and overall tuning/monitoring of the system, we were able to see a substantial increase in the overall performance of our database system with hyperthreading. Now, it’s not even a thought to turn it off. There are some processes that might run slower with hyperthreading enabled. Overall though, you will see an increase in the performance. SQL Server is one application that really utilizes well a multi-processor environment. It will try to push things through high and wide utilizing both memory and processors to achieve the best overall system performance. Note that some single processes might suffer. That’s a fair trade for the overall increase in performance though. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Thanks Derrick.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Derrick, Have you tested the Full-Text Search with HT ? I’m running an SQL server that is dedicated to a Full-Text Search activities It has Windows 2000 Advanced Server SP3 , SQL 2000 Enterprise Server SP3.
Do you think it might improve the performance if I upgrade it to Windows SP4 ?
Saludos, Pablo

---

HT & parallel execution plans together are seriously bad news, otherwise, i am not convinced HT leads to meaningful performance gains, the System CPU decreases, but thats because its being averaged across all logical CPUs, there is no clear proof the system throughput increases with HT across a broad range of queries.

---

Thanks Joe.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

I think a lot of things might improve with an upgrade to Windows SP4 Pablo. You really shouldn’t be running SQL Server on a server that doesn’t have the latest OS Service Pack. BTW, for the people who have had trouble with HTT, have you tried increasing your max worker threads? You might want to give that a try and see the results. –there is no clear proof the system throughput increases with HT across a broad range of queries. Are you kidding me???? Proof, proof, proof. Please???? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Derrick, I know it’s allways a good thing to be updated, but these are mission critical servers, and they’re running just fine, I you could point me to a fix or feature in SP4 that enhances the performance it would be great. Saludos, Pablo

---

Enhances the performance????? Applying service packs has absolutely nothing to do with fixing performance. It’s about securing your server and being a responsible administrator. Please read the release notes and apply the patch. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

The Question: Parallelism Must Be Set To Automatic to get full advantage of SQL Server 2000. SQL Server 2000 cannot use Parallel Query Processing, this is an unofficial bug, not confirmed and by doing this the queries (transactions) proceeds without any delay or errors will not occur. So the recommendation is to run the Transact SQL script below or use the Enterprise Manager. sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
sp_configure ‘max degree of parallelism’, 1
GO
RECONFIGURE
GO
Please post if this solved your problem?
BooksOnline:
Parallel Query Processing
max degree of parallelism Option
Setting Configuration Options
How to configure the cost threshold for parallelism Best Regards
– Member of The Professional Association for SQL Server, SQLPASS Denmark –
– Stability instead of incremental improvements –

---

what disk and controller (memory, etc) has each server ? since is imposible to be slowest in your quad machine, it appears to be a io problem you have run it one or two or three times, to see if the cache is loaded ?

---

Can any one tel me wht is QUAD processor??? Rajiv
SQL-DBA

---

It means a server with four processors. Quad = 4, like if your wife is unfortunate enough to have quadruplets, it means you’ve got 4 babies. Tom Pullen
DBA, Oxfam GB

---

The best way to do your test is by clearing your cache (DBCC FREEPROCCACHE) and knowing syscacheobjects very well. YOu have to have intimate details about your cache to do ay such testing. I would highly recommend this article but pick what you need:-
http://www.sql-server-performance.com/rd_data_cache.asp. Thanks

---

And pay close attention to compiled vs iteraions of execution plans in syscache…. table

---

I talked to Gert Drapers at PASS and specifically asked him about this. He said while Windows 2003 was more efficient handling parallel processing and hyperthreading, there were still great benefits to using it with Windows 2000. You just have to tune for it. So…….this is a bunch of rubbish. Facts please. Don’t tell people to set parallelism to 1 on a multi-processor server. That’s stupid; and anyone who has used a multi-processor server knows this. To sound like a parent….shame on you for posting things like this!!!! MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Have you tried disabling parallel processing, and what was your result? Best Regards
– Member of The Professional Association for SQL Server, SQLPASS Denmark –
– Stability instead of incremental improvements –

---

First of all I can see you have been talking with Gert Drapers and I know he will be informed of this issue by several important SQL persons. I was aware that this subject could cause reflections, but your way handle it I can’t approve, that’s why I haven’t got further comment’s. Ask Gert Drapers again when he have been in Denmark. Best Regards
– Member of The Professional Association for SQL Server, SQLPASS Denmark –
– Stability instead of incremental improvements –

---

It should be added that parallelism is not without problems. It can cause self-deadlocking. Microsoft do not guarantee that it will work all the time. For some people, it is more cost-effective to switch it off server-wide, and do without the potential benefits of it, than to go through every single piece of code that runs on their server and check that it works correctly under parallel execution. Tom Pullen
DBA, Oxfam GB

---

I agree with Tom 100%. Its all about testing, testing and testing. I wish there was a simple answer to it?

---

It can use parallelism, but not always and you have to test a lot for finding the best way for your transactions to run without any delay if you do not use serial processing. The way I see the "Bug": After reading/collecting and by knowledge of cases I have to say that the SQL Server have a problem:
The problem is that the SQL Server 2000 mainly uses serial processing and when the load increases it uses parallelism and this can cause several problems, cause it seems like the SQL Server have problems finding out which threads to process unless you realy have a powerfull machine, which can handle kind of "concurrency batchjobs", and you know by lots of analyzing that a given process can make use of the parallelism functionality it could be an advantage. I say that the SQL Server 2000 have problems finding out when to use "serially" and "parallelly" processing and even the "cost threshold for parallelism", fails. Microsoft tells in Books online the following about using "serially" and "parallelly" processing.
"
What is the number of concurrent users active on the SQL Server
installation at this moment?
SQL Server monitors CPU usage and adjusts the degree of parallelism at
the query startup time. Lower degrees of parallelism are chosen if CPU
usage is high. Is there sufficient memory available for parallel query execution?
Each query requires a certain amount of memory to execute. Executing a
parallel query requires more memory than a nonparallel query. The
amount of memory required for executing a parallel query increases
with the degree of parallelism. If the memory requirement of the
parallel plan for a given degree of parallelism cannot be satisfied,
SQL Server decreases the degree of parallelism automatically or
completely abandons the parallel plan for the query in the given
workload context and executes the serial plan. What is the type of query executed?
Queries heavily consuming CPU cycles are the best candidates for a
parallel query. For example, joins of large tables, substantial
aggregations, and sorting of large result sets are good candidates.
Simple queries, often found in transaction processing applications,
find the additional coordination required to execute a query in
parallel outweigh the potential performance boost. To distinguish
between queries that benefit from parallelism and those that do not
benefit, SQL Server compares the estimated cost of executing the query
with the cost threshold for parallelism value. Although not
recommended, users can change the default value of 5 using sp_configure. source: parallel queries (books online)
Anyway:
I should have written: "I have noticed that it could cause problems using Parallelism", but I wanted to see people’s reaction and I was hoping on a fair discussion. I know that this subject soon will be discussed by several important SQL DBA in Denmark, including Gert Drapers which works for Mark Souza in SQL Development Team. I’ll promise to make a followup if anyone are interested? Best Regards
– Member of The Professional Association for SQL Server, SQLPASS Denmark –
– Stability instead of incremental improvements –

---

Its good to be logical. However, I have found that there are always some mysteries hidden in this wonderfull world of RDBMS. In case of sql, its double folded as OS is closly intergrated with the Relational engine. So its best to leave this a mystry. I have seen issues with parallel esp when it is related to HT when the OS could not diffrentiate with logical/shadow CPU. So again, it all boils down to trial and error(unless you are a group of people who knows everything about the wonderfull world of computing). I wish it was easy to explain these mysterious myths.

---

This article gives a clearly notice of the "Parallelism" problem. http://www.winnetmag.com/SQLServer/Article/ArticleID/42675/42675.html: When sharing TPC-C full disclosure information with my customers, I refer most to the configuration setting for max degree of parallelism (MAXDOP). Many customers are surprised that almost every published Microsoft TPC-C score has the MAXDOP set equal to 1. This setting means that SQL Server won’t use a parallel execution plan for any query. You might ask, "Aren’t parallel queries faster than a serial counterpart for an execution plan?" The answer to that question, of course, is, "It depends." The TPC-C benchmark measures performance for an online transaction processing (OLTP) workload, and most OLTP workloads don’t benefit from parallel queries. For example, if a particular expensive parallel plan decides to chew up all eight processors in the middle of a peak transaction-processing time, your overall throughput can dramatically drop. I usually recommend that my customers set the MAXDOP value equal to 1 (disabling parallelism) for most OLTP workloads. I recommend you do the same unless you’ve performed serious in-depth testing to prove that keeping parallelism enabled is the right choice for your environment. Even then, your testing becomes obsolete and meaningless if you introduce new queries, which can change your well-thought-out plans. It’s better to disable parallelism for OLTP workloads. I also speaked with Kimberly Tripp from SQLskills, she told me it is commen to use MAXDOP=1, which would be the same as using the TSQL I suggested in the first place: "
sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
sp_configure ‘max degree of parallelism’, 1
GO
RECONFIGURE
GO
" Derrick Leggett: you are more than welcome to ask Gert Drapers again if you still mean that I am stupid and should be ashamed… Best Regards
– Member of The Professional Association for SQL Server, SQLPASS Denmark –
– Stability instead of incremental improvements –

---

]]>