Thursday, 30 December 2010

Your approach to query optimising

Optimising a query in SQL Server is a bit like trying to pimp your car. You might think logically that putting in an engine with more horsepower will increase the performance of your car when in reality there are other factors that might limit this or at least that might break! In the same way, using the execution plan, you can concentrate on changing a table scan to an index seek and feel victorious only to discover it has made little difference on the bottom line. This can be for a number of reasons:
  1. If you have many joins in the execution plan, then one of these might only be 0.5% and decreasing this to 0.25% although a lot for the one object adds up to little in the overall scheme of it. Consider simplifying the query or creating a smaller query to use in a specific place.
  2. If the number of rows or columns that you are querying is small, then there might not be a great deal of difference in performance between various indexes or the table itself.
  3. If you join on text/varchar/etc columns, you can incur a large overhead which includes not just the memory/IO itself required for more bytes of comparison but also the fact that you cannot include large text columns in 'index columns' if it would push the total size of key to more than 900 bytes. If you simply add it to the 'included columns' which is permitted, the index will not be used when joining on the text column. Try and use integer ids wherever possible.
  4. You might see "Index Seek" and think this is the best you can get but actually if this Index Seek is carried out on a large index, it might be much slower than it needs to be. You can try this by creating the bear minimum index for what you need and running the query again.
  5. Use the cost % information to decide where your bottlenecks are. If you have hundreds of joins all of less than 1%, then you are probably only able to achieve small improvements, whereas if one or more costs is significant (>10%), especially if it is under a table or clustered index scan, you can probably achieve large time savings by optimising these.
  6. As a last resort (or a first resort if you can!), there are two things you can do. Design things differently so you can simplify the query (reduce the number of joins, rows or columns) or otherwise run the query from a job and dump the results into a cache table which can be used in certain areas that can avoid running the query and can live with data that is a number of minutes or hours out of date.

Wednesday, 29 December 2010

Which SQL indexes are faster?

Even wondered what sort of index lookups are the best for your queries? What is the preferred speed order. Well, of course, it depends. In some cases there is little difference and it usually depends on whether the query optimiser thinks it will only need a subset of data from an index and also how much data is in the index compared to the main table. Bear in mind that just because you might get an Index Seek/Scan, it doesn't mean that's the fastest you can go. Using a large index for an index seek might be 10s of times slower than creating a smaller index with the required columns. Anyway, in order fastest to slowest, this is the best I can come up with:
Index Seek - A subset of an index. It's speed relates to how few columns you can include to get what you need and you will need some sort of 'where' clause for the optimiser to reduce rows as it joins. Make sure you include your WHERE clause columns and any JOIN columns in the 'index columns' you can then include other columns you want to select in the 'additional columns' area.
Index Scan - this scans the whole index and might be fast if it contains few columns and/or rows otherwise it is little better than a clustered index scan or table scan. If you have indexes that contain most columns from the underlying table, you are probably better off removing the index or redesigning your queries to not require so many columns. Also, ensure that you include where clauses as early as possible in your queries (i.e. not all at the outer level) which might promote an Index Scan to an Index Seek.
Key/RID Lookup - this might be very slow since it requires an index seek/scan followed by an index/clustered index/table lookup in which case it is not much better than a clustered index scan. It might otherwise be somewhere closer to the speed of an Index Scan if it comes from an Index Seek for a small subset of data. Generally best avoided by modifying/creating other indexes.
Clustered Index Scan - this is usually not much faster than a table scan, if at all, since the only difference is the clustered index is ordered by something which may or may not be useful for a particular query to use. The clustered index itself is however very helpful to speed up joins in general so don't delete the clustered index just because it isn't much better than a table scan. In the case of a small table with few columns/rows this index is probably as fast as it will get since more specific indexes will not be significantly smaller than the underlying table/clustered index. In this case, do not seek to replace Clustered Index Scans with Index Scans.
Table Scan - the base table is scanned, every row. The slowest and best avoided since it not only affects performance by itself but can also slow joins to other tables/views. It increases the amount of IO required even if you don't need all of the columns returned. This is because the data is paged into memory as it is being queried. A smaller set of data in an index takes up less memory and therefore less pages = faster.
I have also seen Clustered Index Seek and Table Seek. These are better than their Scan cousins but still best avoided if possible using an index. The only time clustered or table seeks might be the fastest you would get, like clustered and table scans, is if the underlying table is small or basic.

Making your queries faster

If you run SQL Server management studio, you can include the execution plan with your query (Ctrl-M), it displays a network of how the tables and views join and more importantly, what types of scans are used to get the data. It also shows lines that are proportional to the number of rows being operated on. If you can see any big lines, then possibly your join condition is too generous (and you are using the WHERE clause incorrectly to fix the join) or maybe you do actually want to select tons of data in which case big lines might be acceptable.
As far as table and index scans are concerned, there are really two levels of understanding, one of which will help massively and the other is perhaps when you want to squeeze the last drops of performance out of the query once you have made the major changes.
Let's first consider the easier deductions from the execution plan. If you see any "table scan" elements on your diagram, that is generally bad and unless you have a good reason, you need to create an index on the table that can be used (even if your table is small - it can decrease join performance noticeably). What a table scan means is that the query has to scan all columns in the whole table to find and match rows. Virtually every table should contain a primary key column(s) and this creates an implicit clustered index so at very least you should be scanning the clustered index. If you do not have a primary key (suspicious!), you can still create indexes and it is these that will remove table scans as well as removing clustered index scans which are slightly better than table scans but include all columns still and can be slow also. By creating an index you should be able to include a few key columns which will dictate the sort order (which can help with join performance) but also (SQL Server 2005) you can include additional columns which the query can use but which will not unduly slow the creation and update of the index since they do not dictate ordering. If you have an index on TABLE with, e.g. columns A and B and your query says something like select A from TABLE where B = 2 will use the index directly. You will get an index seek ideally (a subset of rows from the full index, less than about 15%), which is very fast, or perhaps an index scan which has to read all rows but is preferable to table scans since the amount of data being used is a subset of the table data.
The second level of performance comes when your indexes are not quite right and you get strange items like "Bookmark Lookup" or "RID Lookup" which are both similar and caused by the same basic problem. Imagine in your example above you did select A, C from TABLE where B = 2. Although the index can be used to find the matching rows which is fast, in order to return column C, the query needs to find the primary key in the index (which is included by default) and then using this it needs to scan the clustered index or the base table if there is no clustered index in order to find the data for column C. This is a bookmark lookup (or an RID lookup if the base table has no clustered index - it is a 'heap'). These are also slow but possibly faster than a clustered index scan or a table scan. You might find sometimes that the query optimiser decides to not bother using the index and assumes you might as well go straight to the base table. To fix this issue, you would need to include column C into your index or create a new index with A, B and C in it.
Bear in mind that there is always a trade-off between the number of indexes and the insert/update/delete performance of the table as well as the amount of memory required depending on the size of the index which is related to the size of the underlying table. Generally, it is better to add a column into an existing index rather than create a whole new index which is slightly different. If one index has the same columns as another (even if the other index has a few more) then it is redundant e.g. Index1 = A, B, C; Index2 = A, B means that Index2 is redundant since all of the information is already available in Index1.
Also, the actual performance gain depends on the indexed columns relative to the base table columns. If your index had 5 of 6 available columns from the table then there will be little difference between an index scan and a table scan. Also, if you are selecting a text field from a table but using a WHERE clause which is NOT based on the text field, you might find it quicker to split the index into one without the text field (which reduces the size of the index and speeds it up) and then create a second index with just the text field in which the query should be able to use to get the resulting text field for each matching row.

Friday, 17 December 2010

Why Knowledge and Design is important for Software

My company bought a new company in the last year and we have created a new web application to provide some functionality based on an existing system but created from scratch so we could use as many best-pratices as we could from the start.
Currently the new company only uses a small amount of the existing functionality but plans are afoot to increase this. One of the things I have been working on is in the quotation part of the system where the business selects the equipment for a quotation, adds certain services and then produces tender documentation. Our current system has around 22,000 quotes (some of which are test quotes) and one of the luxuries this provides is a set of genuine test data to try out any designs we create for the new company to see whether it scales well. Let's be honest, how many times have we implemented something by putting in a few rows of test data and then deployed it into a system that might end up with 10s or 100s of thousands of rows?
Anyway, there have been two things that have been obvious with our major re-design of the table layout and which have produced an increase in performance of around 5 times for the same data set running on a virtual test server with much less memory and CPU than the live server - pretty impressive. The first thing is that you need knowledge to write good software! This might seem obvious but someone might understand databases at a basic level but without understanding things like indexes, foreign keys and referential integrity, at best you will write something average, at worst it will simply not perform or scale and you won't understand why. Foreign and primary keys are not just for integrity but allow certain queries to perform much faster because of assumptions the system can make about the number of rows that will join in a query. The second thing is that we spent time in design. To be honest it was a Visio diagram that was sent round and which we all looked at. We considered the various objects in the current system and how they would work in the new system, which led to additional columns and the like but a large reduction in the number of tables required and therefore a reduction in joins and a performance increase in queries. Why? Well although you can partially think and design while you implement, in reality, you will never make large changes and see the big picture while adding tables into a database. If we had dived straight in, we would probably have something much closer to the old system whereas the new design is radically different and more efficient in the process. The few days of time we spent checking out the design has saved some time in implementation, a lot of time in potential rework and loads of performance. It seems counter-intuitive to be designing things and not just getting on and doing it but to be honest nowadays, implementation only needs to take a few days once the design has been completed.
Knowledge and Design - don't take shortcuts.

Wednesday, 15 December 2010

Poor passwords

A news story today warned of thousands of internet users being asked to change passwords after a hack exposed login details. What was worrying was how many people used passwords such as 123456 or "password" which are clearly not secure in the slightest. What was interesting was that it took a hack to make various providers ask their users to change their passwords rather than simply not allowing them in the first place.
Quite clearly a brute force attempt at someones password will start with around 20 common words, phrases or number combinations, it would appear that thousands of people would fall foul of that.
What do we do? Firstly, if we allow people to choose their own passwords, we simply either blacklist certain words like "password" and/or we use a minimum password strength such as one letter, one number, one capital etc (although this can annoy people if taken to an extreme).
The other thing we should do is run a tool against our current users' passwords and work out which ones are not secure and then reset those accounts, sending an email or a page messages saying that your password is too insecure.
The other thing that seems sadly lacking is simple intrusion detection so that, e.g. 3 attempts at a password locks out the account for a period or permanently until reset. Even if you use a simple password, if the tool only gets 3 chances to crack it, it is unlikely to succeed.
Come on people, let's be proactive and not be tomorrow's headline.

Tuesday, 14 December 2010

Don't just secure your apps, manage their security

Assuming you are one of the few people who genuinely secure their web apps using all the best known security practices like validation, authentication, authorisation etc, there is a good chance that you have ignored the common but important factor called Time.
Securing an app is fine but over time 2 things happen. Things change and things are forgotten. Why is this important in a web app particularly? Well if you used SHA-0 to hash your passwords a few years back because you knew it was the latest and greatest hashing algorithm, what are you going to do now it has been found to be less secure? Sure, you might have heard about the weaknesses and you might know that your system needs updating, but how does your organisation know that this issue will be picked up and addressed in the future?
Most organisations rely on people just knowing these things. They rely on the experience of developers or architects who have been around a while but this is simply not good enough. It is not good enough to be secure now when you consider how things can change.
In a similar way, people forget things like the fact that I used login X to access database Y for reason Z so that when someone else comes across it, they might re-use the login for something else, elevate its priviledges for some reason and weaken the overall security. How should they know to leave it alone?
A management system is required, any management system to begin with, but something which is adapted over time to be useful and useable. Make it unuseable and guess what? people won't use it. This needs to record information about the system including but not limited to security information. You might list the logins, their purpose and what access they should or shouldn't be allowed to have. It could list securables and secrets on the file system and what security they have been setup with. It should be expressly prohibited for ANYONE to modify a security setting without a review and without recording it in the management system.
It honestly could be setup on a wiki or anything as simple as that which allows regular review and work arising to be allocated. You can even flag things which we might know to be issues for the future such as encryption systems and hashing mechanisms, all of which become weaker over time.
If you don't manage security in your apps, your app is at best good for now but could break down rapidly as new exploits are discovered and no mechanisms exist to trace these to your own systems.

Tuesday, 7 December 2010

KDE4 Network Manager fails after upgrade

Not sure exactly when but I did an upgrade to KDE4 and the kernel and in the process my networking stopped working, wireless and wired. It worked fine in recovery mode and I did another update in case there was a fix but no dice.
Fortunately I have a friend who knows the score, he told me to:
sudo nano /etc/NetworkManager/nm-system-settings.conf

and set managed=true instead of false. I then had to:
sudo nano /var/lib/NetworkManager/NetworkManager.state

and set NetworkingEnabled=true. I then had to restart the service:
sudo service network-manager restart

and it was all good again. Well done whoever made that happen in a package upgrade! Most people, including me, would not easily find out what has happened and would conclude that Windows works better even if we hate M$.

Working on a live database

Even if you have a test system to try out some new functionality, at some point it needs to be deployed to live. If you are lucky, this is done by copying the up-to-date test system over the top of live but in reality, most of us have to modify the live database directly, perhaps during a small time-slot and then it is expected to work.
There are a number of problems that are associated with live databases, specifically SQL server in my case, that are worth knowing before you go and break something.
  1. If you can, avoid altering or removing any tables/columns in the first place. If you can avoid this and perhaps leave a few dead things in place, you are less likely to break something.
  2. If you can use some tools like Red Gate to search for references to your objects, it can help you track down what might break. Red Gate also make a tool for moving objects from a test to a live database in a GUI rather than manually.
  3. For any objects that are accessed by something external to the database, mark these with schemas so you know that there might be knock on effects to other systems. e.g. webapp.procUserFetch
  4. Avoid selecting * from anything since this hides whether a specific column is referenced or not.
  5. If you are adding a new column, make sure it is nullable, even if it ultimately won't be. This avoids any existing inserts failing due to the lack of a value for the new column.
  6. Try not to rename columns if you really don't need to. You can always alias them in a select statement.
  7. Adding new tables, procs and views is safe
  8. If you change a table structure, you must use sp_refreshview on the views that reference it otherwise their cached query plan can be out-of-date and you won't know until some funny data is returned in the select.
  9. If you are deleting objects, rename them first or move them to a dead schema so that they can quickly be restored if there are any problems. You can put the rename date in their new name and after a week or so, delete them permanently.
  10. If you can, script the new or changed objects and store them in a repository so you can track and blame changes that are made.
  11. Use the format procNameVerb when creating stored procedures so that like procs are listed together e.g. procUserInsert rather than procInsertUser.
  12. Have a backup of the database if you are making major changes so that you have a last-resort way of falling back to a known-working copy.