SQL Server can be an extremely powerful tool is used correctly. Databases are now essential to most applications and websites and when the data stored becomes large and complicated, if your SQL queries and structure is not up to scratch, things could start going wrong.

So, keeping your SQL Server running at its best is down to a number of factors. First the hardware the SQL Server is running on plays an important role in how fast and efficient your queries will be executed. Of course 16GB of memory will process a query faster than 1GB of memory. However, you could have all the memory in the world, but if your actual query is very in-efficient it could be like having no memory at all. So secondly, how you write your queries is something you should always look at. Before writing a query you should think about things such as: is this query scalable, can it handle large amounts of data well and is there a faster more efficient way of doing it.

When writing SQL Queries, the simpler the query is, the less problems you are going to face when speed and efficiency come into play. For example, instead of joining 7 tables on to your initial table query and slowing down searches, can you store the data from some of these tables in the first table? Can the data be cached for a certain period. Sometimes your data will be changing so often, that caching or saving the data temporarily will become useless and more time consuming. So when this is not possible try using Indexes on your tables. Indexes are a form of string a rows position in a table relevant to certain column values and primary keys.

When using indexes on tables, execution times can be considerably cut. But only when they are used properly. Indexing a table in SQL Server doesn't mean you stick all your columns on it, it works best if you strategically choose the columns you are going to searching and joining on. Remember Indexes index the the location of a row based on column values, so when searching on a column it would be handy to have that column in your index. So if you were running a query and you where always filtering on columns one and two. You would add these to the indexed columns on your index. On Indexes there are also "Included Columns". These are the columns that are included in the index but are there purely for storing purposes. You should put columns here if you are returning them from the query.

Staying on the topic of Indexes, Indexing uses storage space on your SQL Server, and they are stored in "pages". Pages are sets of files used to store index information. How these pages are filled and created can play a vital role in optimizing your SQL Query. To start tweaking this, we use FILLFACTOR and PADIDNEX. You will find these options in your Index settings. FILLFACTOR is how much a "page" is filled. If a FILLFACTOR is set to 80, then the "page" will only ever be 80% full. This comes in useful when data is updated regularly as there will always be 20% in the page to change and insert data. Thus limiting the amount of page breaks. A page break is when a page is stopped and a new one is created. This eventually will have an impact on how fast your indexes are when the amount of pages increases.

When using Indexes, an SQL query can return over 1 million (1,000,000) rows within a matter of seconds. That is quite impressive. But this relies on well indexed tables, well structured queries and capable server hardware. So get those tables indexes and optimized!