Need advice on SQL Server Performance

Question

Post reply

Need advice on SQL Server Performance

Little Nick

SSCrazy

Points: 2202
More actions
October 6, 2010 at 11:26 pm

#229136

Hi,
My current system as follow
1. Windows 2003 Server Enterprise Edition 32 bit
2. SQL Server 2005 Enterprise Edition 32 bit
3. Strengthen by RAID 5
4. SQL Server now hosting 5 different application database
5. All DBs resided in SAN (connected via high speed fiber channel)
My current problem as follow
1. At this level, SQL Server looks have an extra overhead that hurts performance. Transactional activities which usualy requiring few seconds now requiring few minutes and this is unreasonably slow from user experience perspective.
Simptom
1. In monitor performance, SQL Disk average queue length sometimes hit to 100%
My question is,
1. Before upgrade the server, Windows Server, SQL Server, what steps need to be taken
2. As a result, we will know this is because of the server, Windows Server and SQL Server
Need advice

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

If performance decreased suddenly, I would investigate what changed before thinking of an hardware update.

Do you have a performance baseline to compare your current activity with?

Did the transactions per seconds rate raise enormously?

Did your scan vs seek rate change?

Are there other processes performing disk activity?

That said, a new shiny server hardware won't hurt. 😉

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

I forgot to mention that if you are experiencing high disk queue, a new monster server connected to the same storage will run as slow as your current server.

What does the storage team say? If you are the storage team, what does the SAN vendor say?

-- Gianluca Sartori

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

IMO , its always worth starting with tuning your TSQL code ( including indexing strategies and statistics).

If you have poorly performing SQL , you will have poorly performing SQL no matter what hardware you throw at it.

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

Clear Sky SQL
My Blog[/url]

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 4

Little Nick (10/6/2010)
Hi,
My current system as follow
1. Windows 2003 Server Enterprise Edition 32 bit
2. SQL Server 2005 Enterprise Edition 32 bit
3. Strengthen by RAID 5
4. SQL Server now hosting 5 different application database
5. All DBs resided in SAN (connected via high speed fiber channel)
My current problem as follow
1. At this level, SQL Server looks have an extra overhead that hurts performance. Transactional activities which usualy requiring few seconds now requiring few minutes and this is unreasonably slow from user experience perspective.
Simptom
1. In monitor performance, SQL Disk average queue length sometimes hit to 100%
My question is,
1. Before upgrade the server, Windows Server, SQL Server, what steps need to be taken
2. As a result, we will know this is because of the server, Windows Server and SQL Server
Need advice

Based on the symption (average queue length hitting 100%) it looks like the queries running against your server are fetching a lot of unwanted data (SELECT * instead of SELECT Col1, Col2, Col6, ... and so on). That brings us to Dave's point about looking at the T-SQL first. I agree with Dave in the fact that bad SQL is bad SQL everywhere - no matter what hardware it is running on.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Little Nick SSCrazy Points: 2202 More actions · Answer 5

Gianluca Sartori (10/7/2010)
I forgot to mention that if you are experiencing high disk queue, a new monster server connected to the same storage will run as slow as your current server.
What does the storage team say? If you are the storage team, what does the SAN vendor say?

I'm not clear in this issue. Can you elaborate more sir?

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Little Nick (10/7/2010)
Gianluca Sartori (10/7/2010)
I forgot to mention that if you are experiencing high disk queue, a new monster server connected to the same storage will run as slow as your current server.
What does the storage team say? If you are the storage team, what does the SAN vendor say?
I'm not clear in this issue. Can you elaborate more sir?

I mean that if your storage is slow, you can't expect a new blindingly fast server attached to the same storage to be faster than the old crappy server.

If you have a disk bottleneck, changing the CPU won't help.

Have you checked your disk response times? Generally speaking, when you see times higher than 8 ms, your storage is not performing well.

-- Gianluca Sartori

grahamc SSCertifiable Points: 6762 More actions · Answer 7

Something worth looking at is "write cache" on the SAN... I have had the same in the past where all-of-a-sudden performance falls through the floor (in a very bad way). Turned out to be the battery had a failed. On the HP side of the world its called:

"Smart Array Battery Back Write Cache Enabler"

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

grahamc (10/7/2010)
Something worth looking at is "write cache" on the SAN... I have had the same in the past where all-of-a-sudden performance falls through the floor (in a very bad way). Turned out to be the battery had a failed. On the HP side of the world its called:
"Smart Array Battery Back Write Cache Enabler"

Nice catch, Graham.

If reads are still good and writes have become a pain, it could be a symptom.

-- Gianluca Sartori

grahamc SSCertifiable Points: 6762 More actions · Answer 9

Gianluca Sartori (10/7/2010)
grahamc (10/7/2010)
Something worth looking at is "write cache" on the SAN... I have had the same in the past where all-of-a-sudden performance falls through the floor (in a very bad way). Turned out to be the battery had a failed. On the HP side of the world its called:
"Smart Array Battery Back Write Cache Enabler"
Nice catch, Graham.
If reads are still good and writes have become a pain, it could be a symptom.

Well we had this fail on a SAN that was used as shared storage for virtual SQL boxes and quite literally both the reads and writes suffered terribly, in the region 10-15x slower. Took the SAN team 2 days to realize :hehe:

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

I'd look at the other wait states and queues on the system. You know you're getting lots of disk i/o's, but why? You need to figure out what is causing the system to slow down and then attempt to address it. Check out this MS White paper for how to use this approach.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Little Nick SSCrazy Points: 2202 More actions · Answer 11

Little Nick

SSCrazy

Points: 2202

October 7, 2010 at 9:42 am

#1233716

tq. all answer is my inspiration

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 12

Little Nick (10/6/2010)
Hi,
My current system as follow
1. Windows 2003 Server Enterprise Edition 32 bit
2. SQL Server 2005 Enterprise Edition 32 bit
3. Strengthen by RAID 5
4. SQL Server now hosting 5 different application database
5. All DBs resided in SAN (connected via high speed fiber channel)
My current problem as follow
1. At this level, SQL Server looks have an extra overhead that hurts performance. Transactional activities which usualy requiring few seconds now requiring few minutes and this is unreasonably slow from user experience perspective.
Simptom
1. In monitor performance, SQL Disk average queue length sometimes hit to 100%
My question is,
1. Before upgrade the server, Windows Server, SQL Server, what steps need to be taken
2. As a result, we will know this is because of the server, Windows Server and SQL Server
Need advice

Find out who added the bad index. 😉 Seriously. I've been through this many times. The typical problem is that someone adds a new index to make a report run faster without understanding the impact of what they've just done to inserts and updates.

Either that or... it's a bad battery just like someone said.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 13

There are a metric butt ton of things that can affect database application performance! Hire a tuning professional for a few days. You get mentored on what/how to monitor, how to address a variety of classes of problems, and your systems get spiffied up. You could hunt-and-peck on forums for months and not get as much assistance applicable to your particular situation as you will get with my recommendation.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Little Nick SSCrazy Points: 2202 More actions · Answer 14

Little Nick

SSCrazy

Points: 2202

October 8, 2010 at 8:28 pm

#1234585

tq to all.