Manual SP to Index Defrag a SQL 2000 database on SQL 2005 that run at spesific times at night  Read more...

Two weeks ago Andy started his wildly successful series on Worst Practices. This week he continues that series discussing why failing to use primary keys and clustered indexes are worst practices. Agree or disagree, read the article and join the discussion. One thing you'll have to admit, reading an article by Andy is a lot more interesting than reading Books Online!   Read more...

One of the issues you'll face with SQL Server is blocking which is caused by other processes that are holding locks on objects. Until the locks are removed on an object the next process will wait before proceeding. This is a common process that runs within SQL Server to ensure data integrity, but depending on how transactions are run this can cause some issues. Are there ways to get around blocking by using different indexes to cover the queries that may be running?  Read more...

Indexing a SQL Server database in some respects is considered both an art and a science. Since this is the case, what are some considerations when designing indexes for a new database or an existing one in production? Are these the same types of steps or not? Do any best practices really exist when it comes to indexing? Where does indexing fall in the priority list from an application or production support perspective?  Read more...

When is it okay to let SQL Server to make a statistic for columns in your queries, and when should you take those statistics and make an index out of them? What I do is to test all of my procs, and if they generate system statistics (_WA_SYS%), then I add an index to the table for the column in the statistic. But is this a good practice?  Read more...

Read more...

Optimize SQL Server non-clustered indexes and queries by considering index fields, compound indexes and SQL Server statistics' impact on non-clustered indexes.  Read more...

Read more...

Read more...

Paul Randal of SQLskills takes a look at lock escalation in SQL Server 2008  Read more...

One of the new features found in SQL Server 2005 is the ability to add additional columns, called Included Columns, to a non-clustered index. This article will explain the advantages of using included columns and what impact they will have on your database.  Read more...

Find why SQL Server clustered index design should be narrow and static and how clustered indexes affect many-to-many tables to improve database performance.  Read more...

Generate create statements for missing FK indexes, based on DRI  Read more...

Most DBAs are constantly looking for ways to tune their servers to run better. Joe Doherty brings us the second part of his series on indexes with an in depth look at how the indexes are stored in your database.   Read more...

Indexes directly affect the performance of database applications. This article uses analogies to describe how indexes work. The estimated execution plan feature of the Query Window is utilized to compare the performance of two queries in a batch.  Read more...

Most DBAs are constantly looking for ways to tune their servers to run better. Joe Doherty brings us the second part of his series on indexes with an in depth look at how the indexes are stored in your database.   Read more...

This article covers a few, more advanced topics about the usage of clustered indexes in SQL Server. Not only will I try to convince you of the absolute necessity of using clustered indexes, I'll also give you some tips on how to use them in a not so obvious context.   Read more...

Index Creation Guidelines for SQL Server can be pretty sparse. Usually there are a couple, clustered index for ranges, nonclustered, etc. Leo Peysakhovich has taken some time to write down his guidelines based on his experience for creating indexes and the rational for doing so. He's also taken a few minutes to look at which indexes NOT to create, something that might be worth knowing..   Read more...

Two weeks ago Andy started his wildly successful series on Worst Practices. This week he continues that series discussing why failing to use primary keys and clustered indexes are worst practices. Agree or disagree, read the article and join the discussion. One thing you'll have to admit, reading an article by Andy is a lot more interesting than reading Books Online!   Read more...

SQL Server 2000 has indexed views, which can greatly improve database performance. However there are a number of restrictions on building the view, including the restriction against outer joins. So how can this work? New author Jean Charles Bulinckx brings us a technique that can help you get around this restriction.   Read more...

Most DBAs are constantly looking for ways to tune their servers to run better. Joe Doherty brings us the second part of his series on indexes with an in depth look at how the indexes are stored in your database.   Read more...

Chris Kempster brings us a basic look at the database design topic of denormalization.  Read more...

One of the new features found in SQL Server 2005 is the ability to add additional columns, called Included Columns, to a non-clustered index. This article will explain the advantages of using included columns and what impact they will have on your database.  Read more...