SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance testing and tuning for a beginner


Performance testing and tuning for a beginner

Author
Message
rtreff
rtreff
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 4
Great easy to understand article!
Dcarlson
Dcarlson
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
Points: 442 Visits: 244
Good Job! Thanks for the input.

----------------------------------------------------------------------------
"No question is so difficult to answer as that to which the answer is obvious." - George Bernard Shaw
Gail Wanabee
Gail Wanabee
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3141 Visits: 1476
This seems so fundamental that I almost think I shouldn't mention it but I will anyway.

When designing a database, its indexes, and the queries that access it, do everything you can to:
1. not use the database at all (I've known programmers who used it for logging when a flat file would have been much more efficient.),
2. avoid physical disk writes (they are significantly slower than disk reads),
3. avoid physical disk reads.

Your hard drives are the slowest components on your system by several orders of magnitude.

Do everything you can to design for the use of the system and database caches. This will speed up everything. This means lots of memory dedicated to SQL Server and correct indexing of all search fields.

And no correlated sub-queries - period.

LC
changbluesky
changbluesky
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 150
so glad to join this article and saw many splendid ideas about the performance tunning tips..
But we should think about the fragmentation about the database table.It will reduce the performance more if more fragmentation exists in the table.
You can check the fragmentation of the table through the view sys.dm_db_index_physical_stats. The view returns size and fragmentation information for the data and indexes of specified or view.

Syntax:
sys.dm_db_index_physical_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | NULL | 0 | -1 | DEFAULT }
, { partition_number | NULL | 0 | DEFAULT }
, { mode | NULL | DEFAULT }
)

The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. So, you can decide whether you need rebuild or reorganize the index of the table to reduce the framentation.

----==----Hehe
Kari Suresh
Kari Suresh
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1682 Visits: 373
Nice article. Really good one.

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
sqlservercentral-943251
sqlservercentral-943251
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 2
Not interresting article at all. It tells nothing... I'm so sad
cliffm
cliffm
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 20
This was an excellent beginner’s article. Performance tuning is an art in itself.

It’s particularly confusing when your first starting out, as there is such a wide variety of issues to consider, not only in identifying which measurements are important but also identifying the correct measures to take to improve performance.

The article has also encouraged other users to post their experiences and knowledge of performance tuning which will also be useful for the beginner.

I look forward to reading the next article.
Alberto dbLearner
Alberto dbLearner
Say Hey Kid
Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)

Group: General Forum Members
Points: 669 Visits: 233
Interest Thing you’re proposing: Not to use the database at all, just cache. But how? I encourage you to write an article about that.
Alberto dbLearner
Alberto dbLearner
Say Hey Kid
Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)Say Hey Kid (669 reputation)

Group: General Forum Members
Points: 669 Visits: 233
crainlee2 (11/17/2008)
This seems so fundamental that I almost think I shouldn't mention it but I will anyway.

When designing a database, its indexes, and the queries that access it, do everything you can to:
1. not use the database at all (I've known programmers who used it for logging when a flat file would have been much more efficient.),
2. avoid physical disk writes (they are significantly slower than disk reads),
3. avoid physical disk reads.

Your hard drives are the slowest components on your system by several orders of magnitude.

Do everything you can to design for the use of the system and database caches. This will speed up everything. This means lots of memory dedicated to SQL Server and correct indexing of all search fields.

And no correlated sub-queries - period.

LC






Interest Thing you’re proposing: Not to use the database at all, just cache. But how? And what if you don’t have enough memory? I encourage you to write an article about that.
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)SSC Guru (105K reputation)

Group: General Forum Members
Points: 105105 Visits: 9671
I think he is talking about a case to save let's say the application's user's parameter or stuff like that, where the usage of the db is not strickly required. This is more a case of correct analysis and planning. If the apps needs the db, then use it, otherwise it might be worth it to use the users' local machine to save some workload on the server.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search