SQL Server Performance Forum – Threads Archive

Win 2k Server, Quad Xeon, HTT enable-disable

HI, I am facing slowdown problem in database Server. its very slow and takes lot of time even to serve even a Single user. Following is the configuration of the Database Server. Dual Intel Xeon Processors 2×3.06 GHz with 512 L2 Advanced Transfer Cache
Advanced Transfer Cache, HTT and 533MGz system bus.
3×73.4 GB Ultra SCSI 10,000 rpm dr0ive 1 HPL
4 GB Ram, running Win 2k Server and MS SQL 2000 EE.
Raid Level 5 implemented
I tested the system using perfmon and profiler and following were the results. CPU usage was observed 35 % maximum
Avg Disk Queue Length was high, even for 5 seconds intervals.
Disk Time was High even for 5 seconds intervals.
Disk Read time was high and Disk Write time was also high
Processor Queue Lenght was normal Some stored procedures were indentified which causes system very slow, and while they were running Server was able to respond for a simple query until those stored procedures stop executing. After some reasearch it was found that stored procedures were recomipling most of time and hence putting compile locks. Also there were many Escalation locks. so this might be the reason for slow down. Plz help and tell me what are the possible reasons for solow down. what i see is that there is I/O problem but that can be due to excessive compiling of stored procedures. Further more I want to know that disabling HTT will benefit running SQL Server, that was new to me disabling HTT, Plz can you put light on enabling HTT and disabling HTT and its pros and cons.
If any body want to see one of stored procedure, plz let me know i will paste it on forum.
Thanks
Syed Abid

http://www.sql-server-performance.com/sql_server_performance_audit.asp – performance Audit for assessment. http://vyaskn.tripod.com/analyzing_profiler_output.htm – take help of this link to identify the performance issue. 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.

quote:Originally posted by abid.syed Further more I want to know that disabling HTT will benefit running SQL Server, that was new to me disabling HTT, Plz can you put light on enabling HTT and disabling HTT and its pros and cons.

We had quite a discussion on this topic recently. http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5250
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5262 In my opinion, we didn’t find a definite answer to this question, so I recommend making an experiment. — Marek ‘chopeen’ Grzenkowicz ‘You’re so cute when you’re frustrated.’ — Interpol

Thanks for helping me, i will perform the load test today. I have few question to ask, I was readinghttp://www.sql-server-performance.com/temp_tables.asp and here it says that we can avoid temp tables by using derived tables. I have some stored procedure that are creating temp tables and some of them also creating two temp tables in single stored procedure. Those stored procedures are used for Reports hence temp tables are created and populated with many many records. I was reading about derived tableshttp://www.sql-server-performance.com/jg_derived_tables.asp and here at the end I found following words from author, I recently read the chapter on advanced queries in the book, Professional SQL Server 2000 Programming, and it states that if your result is going to be large, then it might be better to create a temporary table with an index, because derived tables do not have an index. Don#%92t let the last statement scare you, I#%92ve seen a few queries go from using temporary tables or cursors to derived tables and the performance was increased by 50% or greater. As always, you will never know until you test! My question is that Can i used derived tables and my result is 500 to 1000 records or 10,000 records .. is that fine to used derived tables.. I mean what will be the limit of records for using derived tables.. Regarding HTT I was reading the topics regarding HTT and I found a link posted by choopeen,http://support.intel.com/support/platform/ht/os.htm. It clearly says that The following desktop operating systems are not recommended for use with Hyper-Threading Technology. If you are using one of the following desktop operating systems, it is advised that you should disable Hyper-Threading Technology in the system BIOS Setup program: Microsoft Windows 2000 (all versions)
Microsoft Windows NT* 4.0
Microsoft Windows Me
Microsoft Windows 98
Microsoft Windows 98 SE By all versions of Windows 2000 means that windows 2000 Server also doesn’t has support for HTT. I am using Windows 2000 Server for Xeon Servers, Please any one tell me that what are the disadvantages of not disabling HTT while running windows 2000 Server. And what if I change the the OS and used Windows 2003 Server on Xeon.. how much it will effect the performace of Server but changing the OS which supports the HTT.?? Further more, is there any free tool for Stress Testing.. if any one knows plz let me know abt tools for Stress Testing.. Thanks
Syed Abid

A couple of things: Generally (you should always test), derived tables or table variables are better IF you have under 10k records. Above that, temp tables are better (usually). <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Temp tables are especially better where you can get performance increases from indexes. Remember that the indexes have a write and creation cost associated with them also though. Also, on the HTT you need to read up on SP1, 2, and 3a for SQL Server 2000. Read the fix and release notes as related to HTT and Windows. Having said that, Windows 2003 is much better on handling HTT. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

quote:Originally posted by abid.syed Further more, is there any free tool for Stress Testing.. if any one knows plz let me know abt tools for Stress Testing.

The only stress tool for MS SQL Server I know about is Database Hammer (but there must be more such applications). I am not sure though whether it is free.
And unfortunately I’ve never used it, so I cannot tell if it is any good. http://www.sql-server-performance.com/resource_kit.asp — Marek ‘chopeen’ Grzenkowicz ‘You’re so cute when you’re frustrated.’ — Interpol

It depends what kind of stress testing you want to do: http://support.microsoft.com/default.aspx?scid=kb;en-us;231619
http://www.google.com/search?hl=en&ie=UTF-8&q=sql server 2000 stress test MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

]]>