SQL performace issues problem

[Future00]

Heya

I'm not the dba where I work but got a question about issues they are having. We have in house apps we run that are database intensive. We're running 2000 servers with quad procs and raid-10 and 16 gig ram I believe. Well the problem is the disk que lengths still manage to reach 100. What would be some ideas to get a lot better performance out of these servers? The databases are huge we have to keep records available all the way back to the beginning of time. Our dba's are kinda clueless when it comes to hardware. They just know how to keep the databases going and keeping up with the blocks and stuff.

Ryan

MCP/MCDST/MCSA 2003

 

[Jamfool]

I believe if you read the following article you will give you some ideas:

http://www.sql-server-performance.com/performance_monitor_counters_io.asp

But basically some solutions to reduce io problems are:

1. adding drives to an array (if you can)
2. getting faster drives,
3. adding cache memory to the controller card (if you can)
4. using a different version of RAID
5. getting a faster controller
6. reducing the load on the server.

 

[Future00]

We're already running a Raid-10 with 10 disks all Ultra 320's 15k rpm. Not sure what the controller cards are since there not my servers. Just trying to help these guys out some.

Ryan

MCP/MCDST/MCSA 2003

 

[yelworcm]

Any way you can get a trace of some of the queries that are being run, and see if you can feed that to the index tuning wizard? I smell a lot of table-scans in that system. See if you can eliminate some.

 

[timscronin]

i agree with yelworcm, run profiler and find your worst offenders, then look at the indexes and the tsql

 

[Future00]

The main dba admin is on vacation this week. I'll get him to check that stuff out.

Thanks

Ryan

MCP/MCDST/MCSA 2003

 

[SQLSister]

There are tons of books on performance tuning. Much of the time the problem is not hardware but how the data is being accessed or how the system is designed. The larger the databse the more important it is to do anything to make queies, stored procs etc more efficient.

Some things to think about. Eliminate cursors. These are hugh time wasters and can almost always be done ina set-based manner. Never process records one record at a time. Often programmmers do this to reuse existing sps that insert or update one record but this is a poor practivce in a large database when you need to run multirow inserts or updates. In my current job I cut18 hours off one delte process by chaning it from a cursor to a set-based process.

Are you doing searches using like '%sometext%'? These cannot use indexes efficently. Chaning to full text indexing may make this more efficient or requiing the user to provide the first character willallow the indexes to be used.

Use derived tables where possible. Denormalize to avoid joins on multimillion record tables.

Look at ways to pull the fewest number of records before you join to other tables. Never ever use select *.

Keep transaction logs and indexes on separate drives from the main data.

If you have some dat that is infrequently needed, move it to an archive database and then provide a separte way to acces that data when needed. Usually historical data past a certain time frame is only accessed a few times a year at most and is retained for audit purposes. Moving it out of the tables will lower the number of records and make access to the freqently accessed data more efficient. Also consider a data mart for reporting type data that doesn;t need to be up-to the minute. By having reports run against a datamart, the main database will not have as many large data set queries running against it and that will make performance better.

There's plently more to be done, as I noted, there are whole books written onthis subject.


Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[Future00]

Thanks,

I sent all that info off to the dba so he can check it out when he gets back in the office next week.

Ryan

MCP/MCDST/MCSA 2003