June 2010 - Posts

Do you manage your SQL Server with rdp sessions?

I was just having a catch up on FeedReader and I found this gem from the Windows Cluster Team  http://blogs.msdn.com/b/clustering/archive/2010/06/23/10028855.aspx

If you use rdp sessions and currently manage them with remote desktop manager I think you'll find this is light years ahead! ( I always open my sessions with mstsc as I found desktop manager left connections and was basically not very user friendly )

My colleagues have reacted with enthusiasm so I figure it must be good!  I see that Andrew Kelly http://sqlblog.com/blogs/andrew_kelly/default.aspx  has also found this.

So which null equals this null, that null? maybe this null, or is it this null?

Tuning takes many routes and I get into some interesting situations and often make some exciting finds, see http://sqlblogcasts.com/blogs/grumpyolddba/archive/2010/05/17/just-when-you-thought-it-was-safe.aspx for an example.

Today I encountered a multitude of Foreign Key constraints on a table, now FKs are often candidates for indexes and as none of the defined keys had an index it required a closer look. I view foreign key constraints as somewhat of a pain, excessive keys can cause excessive related io, I've encountered a few cases where this happens, sadly due to the nature of client data it's not something I can publish, however examining this table I noted that all the columns were nullable, a contradiction in terms in my world unless of course you have a range of nulls! Nullable columns also create larger indexes, the main reason for my dislike of them, nulls.

Sadly of course there is another reason for adding foreign key constraints to a database and that's so you can retrospectively draw an ER diagram with the built in tools, always useful to be able to see how you put the schema together afterwards!

If you've always been wishing you had a design tool but always found them just too expensive to justify the expenditure may I recommend ModelRight 3  http://www.modelright.com/  I've been using this a while now, I admit I don't use it much as I'm supporting existing databases, but sometimes it's handy to model a set of tables when you're working with complex data sets, but no nullable foreign keys!

Given the choice 8 out of 10 Optimisers prefer.........

Did you know that included columns do not partake in the uniqueness of a unique index? ( see below )

A few months ago we upgraded our major production system from SQL2000 to SQL2008, this has allowed me to apply some of the index tuning techniques I devised for SQL2005 way back when to the current environment now we're confident we have no unexpected surprises to surface.

Amongst the techniques I use is to pull information from the dmvs to find tables ( and indexes ) which are getting high numbers of scans. The main aim is to then seek out effetcive indexes to turn the scans to seeks, this will help reduce io for selects and hopefully reduce contention where shared locks may hinder data changes. I also view the number of updates as this helps me find those tables which don't suffer high volumes of changes, tables which rarely change can support more aggressive indexing to improve data extraction.

see my previous post  http://sqlblogcasts.com/blogs/grumpyolddba/archive/2010/05/19/analysing-indexes-reducing-scans.aspx

I should point out that this is just one way I approach tuning, reducing lookups ( bookmark lookups in old language ) and scans ( table / clustered index scans ) has to give positive benefits.

Anyway amongst the highest scans with no seeks was a very narrow table, an identity plus two integers, this table was showing approx 2,000 scans per minute on the clustered index. Essentially this table is used to link data, it doesn't get updated very often.
I took a judgement on what secondary index I thought would be required and created an index on one column, let's call it CustomerID and included the other column, we'll call this LocationID, the combination of the two columns is unique, although there are no constraints or indexes to enforce this.

After applying the index I watch how the indexes are being used, in this instance the scans on the table stopped and the new index showed seeks. Now I should point out that this technique can only be applied to a live database I am index tuning not query tuning, I have no idea where the table is used in the application, there are just under 2,000 stored procedures and this table is used in 7 of them. This isn't about poor execution, high io or contention this is 100% about how the table is being accessed.
Experience has taught me that sometimes the only way to resolve performance issues is to work in the live database, it's not something I prefer but how many times have you taken a poorly performing query from production to test/dev only to discover it runs perfectly ? I've even restored a backup to point in time in an attempt to reproduce a badly performing query, it's never worked btw.

I digress, but thought I should make it clear what I'm doing and why. Having created what appeared to be the ideal index idle curiosity prompted me to create another index but with the columns reversed, LocationID include CustomerID. To my surprise this index was quickly used too, along with the original secondary index, in all this time there has been no access to the base clustered index/table at all.

At this stage I investigated if I could make the index unique, OK so maybe it just had never ocurred to me before but columns which are included do not partake in the uniqueness, so my unique index creation failed.
Solution, drop the included columns and make indexes on both columns.

Now I have 4 indexes on the two columns,  CustomerID+LocationID,  LocationID+CustomerID,  CustomerID include LocationID,   LocationID include CustomerID   plus the original clustered index on the identity column.

Which index(s) get used ? Well the included indexes are ignored and the optimiser now uses only the two conventional indexes. What is interesting in all of this is that given any one of the four indexes on their own the optimiser uses that index and never touches the base table, where there is a conventional index which has the same column order as an included index the included index is ignored.

From my view I've removed another table which was only being scanned, however the table is small so I suspect that for a much larger table things might turn out different. Originally there were close to 100 tables out of 600 odd which were showing scans and/or lookups without seeks, I've cut this to under 80 so far, generally I have to find all the stored procedures which use a table and step though every query, many of the procedures are 1,000s of lines and very complex so a slow task. The complexity of some parts of the system make conventional query analysis almost impossible, but that's another story on how I approach that.


Posted by GrumpyOldDBA with no comments

Analysing Indexes - count *

In my presentations on indexing I have always said that you should explore the advantages of covering your clustered index with a secondary index. In circumstances where you might want to just return values form the PK ( assuming it's your clustered index ) a secondary index will be more efficient especially when the row size is wide. Any operation on a clustered index will always return the entire row, so select ID from dbo.mytable where ID is the clustered PK integer will return not just the ID value to cache but the entire row. Use a secondary index and it's just the column.

So what happens when you perform a count(*) on a table?  Well if you don't have any secondary indexes the result is a table scan, however cover your secondary index and the count(*) will use the secondary index. On big tables this is a massive saving in io.

In fact generally the optimiser will search for the most effective index for not only a select(*) but also a select ( columnname ) . Each secondary index has the size of the clustered index added to it, but a secondary index on a column within a clustered index does not get this overhead.

see  http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/05/26/covering-clustered-indexes.aspx 

and http://sqlblogcasts.com/blogs/grumpyolddba/archive/2007/09/27/analysing-indexes-part-4-size-does-matter-3.aspx

so even if you issue a select count(mycolumn2) from dbo.mytable where you've indexed an int column the optimiser will use a covered secondary index on an int clustered PK - very strange!

I suggest you create a suitable table and maybe populate it with a million rows, then create some indexes and run some count(*) to see what happens. 




Posted by GrumpyOldDBA with no comments
Filed under: