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
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 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
SSC Eights!
SSC Eights! (949 reputation)SSC Eights! (949 reputation)SSC Eights! (949 reputation)SSC Eights! (949 reputation)SSC Eights! (949 reputation)SSC Eights! (949 reputation)SSC Eights! (949 reputation)SSC Eights! (949 reputation)

Group: General Forum Members
Points: 949 Visits: 1359
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 Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 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
Mr or Mrs. 500
Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)

Group: General Forum Members
Points: 586 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
Mr or Mrs. 500
Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)

Group: General Forum Members
Points: 549 Visits: 229
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
Mr or Mrs. 500
Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)

Group: General Forum Members
Points: 549 Visits: 229
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-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36907 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