Essential viewing for web developers

Every now and again I come across some amazing videos on YouTube that I think are relevant to all web developers. One author (video blogger? – not sure what they are called) who is particularly brilliant is Tom Scott. He wonderfully articulates how web technologies and their exploits happen, and how to ensure that you don’t fall in to the same traps as many systems/sites out there.

One of Tom’s most brilliant videos is his explanation of how a self-re-tweeting tweet worked and how to ensure that you don’t have the same issues on your text boxes when writing their contents back out to the screen.

Essential viewing videos for development teams:

Tom Scott – Cracking Websites with Cross Site Scripting – Computerphile

Tom Scott – Hacking Websites with SQL Injection – Computerphile

Dr Mike Pound – Advanced SQL Injection

Tom Scott – Cross Site Request Forgery – Computerphile

Tom Scott – How Not To Store Passwords

Tom Scott – Hashing Algorithms and Security


If you are a developer, and have any suggestions of videos that should be added to this list, please add a comment with the URL!

SQL Server check table fragmentation level

If you are using SQL Server, and discover that what should be a simple select query, or particularly multiple select queries that are unioned together with a sort are running slowly, then the issue is your table indexes. This is particularly the case when using Date and DateTime fields for sorting.

If you are trying to sort a table or multiple tables by Date, then it is a good idea to add an index on the Date column, it makes things significantly faster.

Here is the SQL Query to check all tables in your database and the levels of fragmentation of each:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

SQL Server – Increasing query performance for large reports

Its always a balance when creating reports, you want to produce an amazing report, but you dont want to cripple your server and make it unresponsive for other users whilst it is produced.

After a lot of research, we found that adding:


as the first line in a stored procedure greatly improves performance for other users as the stored procedure then doesn’t lock tables for reading when it is producing the report.

increase-chart-11As a real-world example, we wrote a system that could produce an annual statistical report that had to go through 1.1million records 18 times per row, and for around 40,000 rows, so a staggering 792 billion reads on the database. We optimised the query, but it still took around 40 seconds to produce, and during that time all the CPUs in the database server were at 100% and disk IO went through the roof, and the system performance suffered dramatically. Other users were left with the system appearing to hang for up to a minute whilst one user created the report.

By adding the transaction level statement we not only decreased the query time to under 30 seconds, but it also allowed all other users to continue to use the system and not notice any performance hang in the system as their queries were still be executed as needed since the tables were not locked.