Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Performance testing and tuning for a beginner Expand / Collapse
Author
Message
Posted Monday, November 17, 2008 1:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 10, 2008 8:11 AM
Points: 2, Visits: 4
Great easy to understand article!
Post #603887
Posted Monday, November 17, 2008 2:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 4:46 PM
Points: 310, 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
Post #603948
Posted Monday, November 17, 2008 3:10 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 12:36 PM
Points: 256, Visits: 1,063
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



Post #603986
Posted Monday, November 17, 2008 10:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 01, 2010 4:19 AM
Points: 39, 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.

----==----
Post #604104
Posted Tuesday, November 18, 2008 12:53 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 22, 2013 1:05 AM
Points: 248, 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
Post #604139
Posted Tuesday, November 18, 2008 3:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2008 4:54 AM
Points: 1, Visits: 2
Not interresting article at all. It tells nothing... I'm so sad
Post #604188
Posted Tuesday, November 18, 2008 4:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 08, 2012 12:51 PM
Points: 1, 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.
Post #604226
Posted Tuesday, November 18, 2008 3:14 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 06, 2013 12:34 PM
Points: 480, Visits: 214
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.
Post #604732
Posted Tuesday, November 18, 2008 3:17 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 06, 2013 12:34 PM
Points: 480, Visits: 214
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.
Post #604736
Posted Wednesday, November 19, 2008 4:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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.
Post #605028
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse