performance improvement with out accessing the data
-
hi good day all,
a simple question.
i want to improve my server(sqlserver) performance in retrieving them.
should i have to alter my table structures?
is there a way to do it without altering the table description?
if yes please send me infornmation
answers will be grateful
Praveen kishan.
-
Hi,
It really depends on your table design. If you have no indexes or poor indexes, this will make performance incredibly slow - as will the design of your database in general.
This is really a hard question to answer without you giving us specific query issues.
I would suggest that if you are experiencing a poor performing query, then post the query, the table's, indexes etc and we can suggest some possible solutions for you.
-
Aside from looking at the table, index structrue and queries againset the database/tables, well that's a touhg one. However ...
Here's my 'short list' for tuning:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round III
Profiler
Query Hints
Hint: Write scalable code to start with !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I don't wish to appear rude or offensive but to be honest reading your posts I'd probably say a training course would be beneficial.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
oops! I just realised my previous post might be taken the wrong way <grin > directed to Praveen.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Praveen,
I've been asked many times to improve the performance of "a server"...
Yeah, overall server tuning and correctly adding appropriate indexes and deleting unused indexes will certainly help performance. Yeah, they needed some maintenance plans as Rudy suggested and they needed defragging. Yeah, they needed to have a couple of log files moved to a different disk and to have some big stuff partitioned across disk heads. Yeah, a couple of them got performance boosts by adding more memory and increasing the size of Temp DB to a fixed limit of 9 GIG. And, yeah, even a couple of them got a performance gain by buying more processors and upgrading from the Standard Edition to the Enterprise Edition. Some even bought a new box.
But nothing, I mean nothing, will boost performance more than correcting bad database designs and rewriting...well... crap code. Case in point... one company I worked with had a batch job that would take 12 hours to sometimes fail on a 4 processor machine with 8gb of ram. They bought a new machine with 8 processors (with increased clock speeds), 12 gb of ram, and upgraded from the Standard Edition to the Enterprise Edition. The run time of the job dropped to 8 hours and didn't fail for almost a month. YIPPEE! Problem solved, right? Nope... their data continued to grow and after 6 months, they were almost right back where they started... definitely NOT a scalable solution. I showed them how to redesign and rewrite the code replacing all of the RBAR (pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row") with set based logic (basically, think "columns", not "rows"), how to get rid of views of views, how to get rid of functions of functions (got rid of most of the functions altogether!), how to get rid of most loops, and how to get rid of all explicit transactions. The job now runs in 10 minutes (NOT a misprint, folks) and hasn't failed in almost 2 years even though the data continues to grow.
What I mean by all of this, and if you read some of the other posts on this thread, there are few, if any, magic bullets that will suddenly turn crap code into performance marvels. It's understandable why any employer that thinks that way is having problems with the performance of their servers. It's usually NOT the server... it's the code and the design of the database. If those are bad, there's usually not much you can do to help overall performance without a rewrite of some or all of the code. Same holds true for GUI based code... if it's bad, performance will suffer greatly and no amount of hardware or server performance tuning will ever fix it. Proper indexing (sometimes means getting rid of indexes) will help but the real performance boost will come when some dedicated SQL wirehead rewrites the code instead of someone that has "4+ years Java with some SQL experience".
A more down to earth example is this... even if a car has the highest horse-power, most fuel efficient engine in the world, it won't get good mileage if theres sand in the transmission and the tires aren't properly inflated... eventually, the transmission will burn out and the tires will blow, as well.
--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) -
Colin --> no problemo
Jeff --> great analogy
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
are you sure that wasn't directed at me Colin....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply