Database performance down

Question

Post reply

Database performance down

Tara-1044200

SSCoach

Points: 15951
More actions
March 2, 2010 at 9:43 am

#219557

I am observing a database perfoming very slow for all queries when compared to other database(7 times bigger) on the same server perofrming faster . I trid executing same queries on all datbases and observed this is the only database performing very bad.
I did rebuilt all indexes but still same issue, what other troubeshooting steps i can take and how to find the reason for it?

Viewing 8 posts - 1 through 8 (of 8 total)

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

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

compare execution plans between the poorly performing database and one that is performing as expected.

You are likely missing indexes if it is performing slowly (much the same as in your other thread about the same topic).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 2

Are the databases identical? If not, it's down to the code & structure of the database in question. I'd try setting up a trace and capturing queries for a time as they go by, aggregate the data and start tuning. You could also query the sys.dm_exec_query_stats DMV to see what the longest running or most resource intensive queries are and work from there.

You could also start looking at the wait states to determine what's causing things to slow down. Maybe the other databases are hogging the resources and this one is just starving.

"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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

There are chances that tables from that database involve in busy/heavy transactions.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Paul White SSC Guru Points: 150468 More actions · Answer 4

For general advice, and a solid methodology that works, read This Microsoft White Paper.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 5

Paul White (3/3/2010)
For general advice, and a solid methodology that works, read This Microsoft White Paper.

BWAA-HAAA!!! 😛 The very first item in the "Common scenarios to avoid in OLTP" section states...

"High frequency queries with lots of joins can be too normalized for high OLTP scalability."

I guess that's Microsoft's politically correct way for saying "Normalize 'til it hurts, denormalize 'til it works." :-):-D:-P:hehe:;-)

--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)

Paul White SSC Guru Points: 150468 More actions · Answer 6

Jeff Moden
"Normalize 'til it hurts, denormalize 'til it works.

Yep, good advice! 🙂 😀 😛 😉 :w00t: 😎 :hehe: :angry: :crazy: :doze: :laugh: :rolleyes: :satisfied: :blink: :ermm: :crying: :pinch: :kiss: :Wow: :Whistling: 🙁 :alien: ➡ :blush: :discuss: :exclamation: :exclamationmark: :sick: :smooooth:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Tara, there are about a kajillion things that could be at issue here. Some of the most likely include: missing indexes, data skew, parameter sniffing, blocking, IO config of the databases, different server utilization during different runs, stale statistics.

Give a performance tuning expert 30 minutes with your system and he/she can almost certainly tell you why the difference exists and what to do about it. 🙂

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