SQL Server Performance Forum – Threads Archive

Questions about performance issues I’m having

Ok, let me start out with my configuration, and database info. The server is a Dell PowerEdge 6650, with 4x 2.0 Gig Xeons with 4MB Cache (If I remember correctly) (And Hyperthreading enabled). The box has 8GB of RAM (Setup correctly, SQL is using 7GB of it), and has dual Gigabit cards setup as Static Link Aggregation. The database is 16GB, and there about 600 users hitting it, and another 10 (serving up to 1600 remote processes/clients) misc servers pulling/pushing data to/from it. During the day, the system reports 80-100% cpu load. Running performance counters, I do not see much disk I/O. Network I/O is acceptable. Minimal/no Paging to disk. There are a ton of memory reads/writes, and of course the cpu load. So it appears that it is all CPU/Memory Bandwidth issues. After spending a month trying to fix queries, add indexes, I have managed to drop it from 98% average during the day to 89%. We just have a lot of stuff tied into this database. We have recently setup a log shipping server, and tried to redirect some queries to that server, but we always end up getting behind, because the log shipping cannot restore the data, as queries are almost always running. I’m running out of ideas on how to get this load down, or spread it out. We have started looking at a large 8-16 way itanium-2 box, but I have little experience in this area, and I’m not sure what kind of gains we would get out of it. I’m not a DBA, just a System Engineer forced to work on our SQL servers. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />, so if I am missing some obvious stuff, let me know, and I’ll try to fill it in.. I guess my main question is… Should we just get a bigger box? Since log shipping isnt working very well for us, I’m not sure how else to get this box running faster. We also expect the database to grow 60-80% a year, and would like something to last a few years. Anyone have any ideas? Thanks, Mike Wilkinson

Try to configure 1 processor for parallel execution.
Also try to disable Hyperthreading. If nothing works, and if all tuning work as done, then we can talk about others boxes.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

How are you trying to fix queries and indexes? Are you using profiler to identify long running, heavy read, and heavy CPU processes? Also, what does your buffer cache hit ration and cache hit ratios look like? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Does application take advantage of stored procedures? Have you checked sp recompilations? Are cursors used a lot?

Log shipping can used as hot standby server, as in case if any issues with primary server.
On the secondary server you must keep off any queries during the log shipping restore, so it is not a good idea to run queries continuously on a standby database. Try to fix the performance issues on live server and check the counters asked above.
http://vyaskn.tripod.com/analyzing_profiler_output.htm and refer to other performance tuning articles on this website. 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.

1.) How are you trying to fix queries and indexes? Are you using profiler to identify long running, heavy read, and heavy CPU processes? Also, what does your buffer cache hit ration and cache hit ratios look like? 1A.) I have been using profiler to look at heavy read / heavy cpu queries, and then checking indexes, to make sure they are optimal, and if possible, having development re-design the queries. My Buffer Cache, and Cache hit ratios are 99.837 (buffer), and 74.357 (cache) 2.) Does application take advantage of stored procedures? Have you checked sp recompilations? Are cursors used a lot? 2A.) The main application does not utilize a lot of stored procedures, but all the other servers / websites make heavy use of them. I have not checked sp_recompilations, not sure how too. and yes, cursors are used a lot. 3.) On the secondary server you must keep off any queries during the log shipping restore, so it is not a good idea to run queries continuously on a standby database. 3A.) I guess the main reason we setup log shipping was to get the 1 hour queries off the production/live box. 4.) Try to configure 1 processor for parallel execution.
Also try to disable Hyperthreading. 4A.) I’ll try these. I seem to remember trying turning off hyperthreading a while back, and ended up with a bunch of screaming people, but it is something to try this weekend, and make sure.
I have run through the performance checks (Basic hardware, and server config), and thought I had covered all my bases. I’ll run throught it again, to make sure, and try some of these suggestions. Thanks for all the help. Mike

quote:The main application does not utilize a lot of stored procedures, but all the other servers / websites make heavy use of them. I have not checked sp_recompilations, not sure how too.

add sp:recompile event in your trace.

quote:and yes, cursors are used a lot.

This is probably the main reason for high cpu load. You have to replace cursors processing with data set operations.

Lots of cursors are usually a sign of: 1) Badly designed databases (idiots who think normalization slows things down). 2) Oracle DBA’s and developers working with MS SQL Server. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] 3) People who don’t understand set-based programming or relational theory. 4) All of the above. You really can tune the system a lot by getting rid of these, if they are used all the time. I didn’t see where you listed the version and SP level of windows and SQL Server you are using. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

]]>