Thursday, 16 May 2013

1.03 - Changing The SQL Server Name After a Move

Well worth knowing. I have seen this a lot with VM's or with other sysprep'd machines.

1.02 - Slow Deletes

The devs sauntered over to me and asked if I could take a look at an issue of a delete that was timing out at the front end and whilst only removing 410 records.  After a bit of investigation (traces, execution plans, head scratching, etc...). I concluded that it was indeed taking a very long time on one particular part of the script. I ran it through the estimated execution plan and it chirped up and requested an index or two (which I also knew could add to the problem) and I addressed those. Unfortunately no impact at all. I then thought that perhaps the additional indexes and triggers on the tables may be causing the issue so I dropped the unused(ish) indexes and disabled the delete triggers. Still no effect. I then looked at the query plan and thought that perhaps the cached plan was not the optimal one and dropped that from the cache. Still no effect.
I ran the Sproc manually in rollback with the time, io and  xml statistics on to get a feel for what was going on and it appeared to be having a massive amount of logical reads on a child table, whilst taking 90+ seconds. Ah hah! There was a massive amount of reads (4million +) for what was a small delete. I created a non-clustered index on the foreign key that related to my parent table and hey presto! The logical reads now dropped to 1232 and the total SProc now took 183ms.
There are days when things don't always go right and days when they fall into place. This took longer than it should have done but the feeling of getting it sorted in the end is always a great high.
I hope this may help others on the same quest.

Thursday, 2 May 2013

1.01 - Map Postcodes in SQL Server Reporting Services

Another fantastic link to a subject that I have needed to look at this week. Sometimes things all fall into place at the right time. 
This link is to a well written and fantastically informative piece of work based around postcode mapping in SSRS. First time I have seen it noted in such easy down to earth steps - Well done the Purple Frog! Take a look; It has lots of possibilities.
Map postcodes in ssrs reporting services