The first step is to find which of the queries are slow performing. Most of the times it is only after the client or end user starts complaining that the application is slow.
The right way to go about this is to think about performance when you go about designing the database, tables, stored procedures and indexes.
SETTING AND CHOOSING INDEXES
There are only two types of indexes available Clustered and Non clustered indexes.
There can only be one clustered index and that is how the actual data will be stored in the database. You can have several non-clustered indexes according to MSDN the maximum number is around 249 indexes.
To give a real world explanation of clustered and non-clustered index, think of a yellow book. The index at the end is the non-clustered index, it gives a pointer to the where you can find the actual data.
Some of the golden rules while going about optimization are
(1)Selectivity A good rule for non-clustered index is the percentage of records that are unique, the higher the value the better the selectivity. For example a column of type boolean will have good selectivity since it will have a 50% unique selectivity on the account of possible values being True or False.
Keep in mind that setting a non-clustered index might not always be better since it will have to perform an actual lookup on the clustered index to find the actual data.
If the index is going to perform multiple lookups then there can be instances where an actual table scan might perform better.
(2)Exception to Selectivity When foreign keys come into picture the rule of selectivity can be thrown out of the window. Why foreign keys and not other columns is because it improves the join performance as it performs a MERGE JOIN internally. More on the different types of joins later.
This brings up the point that selectivity is not everything,and the question you should ask is how often is this field going to be used.
(3)Index decrease performance Though a well designed index speeds up performance they can be slow while trying to update data. Anytime data is modified any index related to that data also needs to be updated.
(4)Don't Accept the Default Clustered index when you define a key as primary by default SQL Server sets this column as a clustered index.
A clustered index should be defined on a column that participates in a range query for example a column that takes part in a between, <, >, MAX, MIN and COUNT aggregates. The reason why this works well is because the query can do directly to the data and keep reading till it gets to the end.
(5)Keep Index Keys Short
The larger an index key is, the harder a database has to work to use the index. For instance, an integer key is smaller in size then a character field for holding 100 characters. In particular, keep clustered indexes as short as possible.
There are several approaches to keeping an index key short. Try to limit the index to as few columns as possible. While composite indexes are useful and can sometimes optimize a query, they are also larger and cause more disk reads for the database. Choose a data type for an index column with less number of bytes required for each data type. Integer keys are small and easy for the database to compare. In contrast, strings require a character-by-character comparison.
As a rule of thumb, try to avoid using character columns in an index. Integer columns will always have an advantage over character fields in ability to boost the performance of a query.
INDEX MAINTENANCE
Keep in mind that while you go about setting indexes the problem of maintenance for indexes can be something that can cause you nightmares down the line after the database has grown significantly. Two main things to consider are Index fragmentation and Page Splits.
(1)Page Splits
COMING UP - FRAGMENTATION
COMING UP - GUUID EXAMPLE FOR BAD FRAGMENTATION
The SQL Server Query Optimizer is a cost-based optimizer. It analyzes a number of candidate execution plans for a given query, estimates the cost of each of these plans and selects the plan with the lowest cost of the choices considered. Indeed, given that the Query Optimizer cannot consider every possible plan for every query, it actually has to do a cost-based balancing act, considering both the cost of finding potential plans and the costs of plans themselves.STC Technologies|STC Technologies
ReplyDelete