Performance Problem

Question

Performance Problem

peace2007

SSCrazy

Points: 2154
More actions
April 26, 2008 at 1:50 am

#123466

Hi,
In one of our servers all inserts in all databases takes too much time (almost application hangs). As I'm new in DBA field, please let me know what details you need to recognize the problem.
Maybe helpful to add that SQLTRACE_BUFFER_FLUSH takes 99.9% waiting
Thanks

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

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

Ramji29 Hall of Fame Points: 3832 More actions · Answer 1

HI,

Can you give some more information related to your server including the hardware configuration, sql server version and service pack, no of databases along with the sizes, no of users etc.

Rohit

peace2007 SSCrazy Points: 2154 More actions · Answer 2

Sure 🙂

hardware configuration: Win Server 2003 and I don't know more cause I don't have enough permission to get access to it

sql server version: SQL 2005 Enterprise Edition and SP1

no of databases: 4 DBs with almost 13GB each and 8 DBs with less than 100MB each

Everyday, around 100 DB update are performed on each DB scheme.

Besides, each DB has around 8 users. No DB mirroring or shipping log is defined; only auto shrink is true and an automatic backup at midnight when nothing is done on servers is performed.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

The first 3 things I'd do is...

1. Update to sp2 and apply the latest cumulitive update (#7, I believe).

2. Turn off autoshrink. If it keeps growing, it's because it needs to. Set it to a reasonable size and leave it alone. Everytime it grows, it frags your database and, possibly, the underlying file on the operating system. Set it, degrag the DB, and get the boys in OPS to defrag the hard disk. Don't ever use a percentage growth... set it to some reasonable fixed value.

3. Same thing with TempDB... set it to something large on boot up. We have a tera-byte database and we set TempDB to 12 gig. It takes 73 fragments using the defaults of 10% growth and 1 meg starting size just to grow to 1 Gig. Avoid that and set TempDB to be a couple of gig on bootup.

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

peace2007 SSCrazy Points: 2154 More actions · Answer 4

peace2007

SSCrazy

Points: 2154

April 26, 2008 at 10:34 pm

#807536

Thanks a million Jeff 🙂

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

You bet... let us know how you make out on this problem.

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

peace2007 SSCrazy Points: 2154 More actions · Answer 6

I've just started to do as your comments step by step though I have to wait for another department to install SP2.

I'll surely update you of the result.

By the way, is there any formula to calculate the proper initial size for tempdb?

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

Some have tried to come up with a formula... I've not been very successful at it because it really isn't database size dependent. It's impacted more by what the largest query size is (internal number of rows spawned) and coding technique/style. If folks use a lot of cursors and single queries with a bazillion joins, then you'll need bigger than average. Same goes with folks that incorrectly over use temp tables and table variables (although their usage is key to "Divide'n'Conquer" performance in many cases). The easiest way for me, so far, is I'll usually start one out at 1 or 2 gigs depending on a "gut feeling" about the company... I'll keep track of the size for a month (gotta have month end usage in there somewhere). Then, again, on a gut feeling, I'll add 10 or 20% to that to accommodate a couple of months of potential growth and set the intial size to that. For growth, (another gut feeling), I set it to either 250 MB or 500 MB. I do the same with the LDF for TempDB.

So far, I've been pretty lucky... not with the size but with convincing the customer of how worth it it is to dedicate the right amount of disk space to TempDB. Too many folks try to conserve disk space used by TempDB and it kills them when it continues to grow back to what it needs to be. Setting TempDB to be "right sized" on boot up is the second best thing you can do to an SQL Server... adding the right amount of memory is the first but not by much.

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 8

Hope following will help to resolve your issue.

http://www.devprise.com/2007/12/10/sqltrace_buffer_flush-performance-metrics-on-sql-server-2005/

http://www.themssforum.com/SQLServer/SQLTRACEBUFFERFLUSH/

http://sqlblog.com/blogs/linchi_shea/archive/2007/01/16/performance-impact-of-enabling-page-checksum-and-default-trace.aspx

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Chandrachurh Ghosh Hall of Fame Points: 3001 More actions · Answer 9

can you enquire about the hardware config. from the relevant department?

Also, what are the access rights assigned to you?....and are you the DBA?

It is very important before starting any sort of performance tuning, because I don't want the DBA in trouble( evn if it is you, you will be in trouble).....also few activities might hang the server because of low processing power and RAM, causing problem again, wherein you need to use the DAC....a dedicated connection for the admin.....available from 2005 onwards....

So, before doing a R & D, check-out the hardware configuration........

Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

Get and read these two documents: http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc and http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx.

Setup and execute track_waitstats_2005, which can be found in the above. It will help you narrow down what is causing the delays.

Also look up the use of sys.dm_io_virtual_file_stats to see if you have I/O issues (most servers I review for my perf-tuning clients do have I/O problems).

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

peace2007 SSCrazy Points: 2154 More actions · Answer 11

Chandrachurh Ghosh (4/28/2008)
can you enquire about the hardware config. from the relevant department?
Also, what are the access rights assigned to you?....and are you the DBA?
It is very important before starting any sort of performance tuning, because I don't want the DBA in trouble( evn if it is you, you will be in trouble).....also few activities might hang the server because of low processing power and RAM, causing problem again, wherein you need to use the DAC....a dedicated connection for the admin.....available from 2005 onwards....
So, before doing a R & D, check-out the hardware configuration........

Well.. my story is as follows 🙂 I'm just employed and am supposed to be DBA (I need gaining more knowledge as well as experience) since it's my first weeks of working in this co., I don't have access to main servers yet but since one of my colleagues has informally asked me resolve this problem, I'm trying to help him. Thanks for your comments I'll try to inquire hardware config. as well.

peace2007 SSCrazy Points: 2154 More actions · Answer 12

Thank you SSChasing Mays and SSC Veteran for the links 🙂 I'll go through them and let you know the result

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

While you're at it - make sure auto-close is turned off as well...

I've had a few installs that somehow "like" to create new databases with autoclose turned ON....not a good thing (it will shut the database down every time it's not being actively used - very very bad for performance).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

peace2007 SSCrazy Points: 2154 More actions · Answer 14

peace2007

SSCrazy

Points: 2154

April 29, 2008 at 10:10 pm

#808767

auto close is turned off :rolleyes: