Thursday, April 30, 2009

UDF Performance Issues

Excellent post by MVP Gail Shaw on how user defined functions can hurt performance by introducing row-by-row processing.

http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/

r

Wednesday, April 29, 2009

Off-Topic : Rockbox

Have given an old Iriver H340 a new lease of life with new firmware...


http://www.rockbox.org/


(Highly recommended)

Free Scrum Reference Card

http://refcardz.dzone.com/refcardz/scrum

Windows 7 : Release Candidate

Is out tomorrow apparently...

http://www.vnunet.com/vnunet/news/2241098/windows-rc-release-date

Thursday, April 23, 2009

Off-topic : Iphone : 25 Most wanted Tips

Storing this here too i'm afraid, apologies if you haven't been assimilated to the iphone crew.

Am slowly warming to it after years of Windows Mobile devices.

http://www.hongkiat.com/blog/25-most-wanted-tips-for-iphone-3g/

Wednesday, April 22, 2009

Saturday, April 18, 2009

Language Support in Management Studio

Had a mad moment today trying to work out why a colleague could see characters from eastern languages in Management Studio and I could not.

Turns out the setting is machine based and located in Control Panel (Regional & Language Options), not Management Studio itself...

Wednesday, April 15, 2009

SQL 2008 : Backup Compression Default

Set server option to turn on sql 2008 backup compression by default...


USE master;
GO
EXEC sp_configure 'backup compression default', '1';
GO
RECONFIGURE WITH OVERRIDE;
GO

Monday, April 13, 2009

Reporting Services 2008 : Achieving A4 !

Achieving A4 print size in BIDS, Reporting Project.

Bizarrely, achieving A4 that didnt spill across multiple pages when printed was quite fiddly.

Report Properties :

Page Setup :

Sunday, April 12, 2009

Log Growth despite db being in simple mode

My database is in Simple mode but the log won't stop growing!

On the box I am seeing this on, the system cannot keep up. Checkpoint conditions (to control the log) are simply not happening fast enough. After some reading, I've found this is because it is a high volume import system. The constant importing means the sql is not issuing a checkpoint to control log size :(

Why is my log autogrowing? >
http://sqlblog.com/blogs/linchi_shea/archive/2009/04/12/a-race-condition-in-the-simple-recovery-mode-why-is-my-log-autogrowing.aspx

Manually issuing a checkpoint >
http://sqlblog.com/blogs/kalen_delaney/archive/2008/08/29/specifying-checkpoint-duration.aspx

Wednesday, April 8, 2009

SSRS 2008 : Windows Firewall Config

Really easy this, open port 80 in Windows Firewall to allow access to Reporting Services via your web browser >

SQL 2008 SP1

Was released yesterday...

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19

SSRS 2008 : Reporting Services 2008

Had my first experience of installing Reporting Services 2008 last night.

I have 3 observations -
  1. It did not require a local MS SQL instance.
    I was able to install it and chose to place the ReportServer and ReportServerTempDB databases on another server.

  2. It no longer requires Microsoft's Internet Information Services (IIS) as a web server.

  3. Report Builder seems to reply on domain security hence I need to build my SSRS instance in the business domain, rather than the development one.

Monday, April 6, 2009

sp_syspolicy_purge_history

On new sql 2008 installs there is a SQL Server Agent job called ' sp_syspolicy_purge_history '.


It is a cleanup routine for Policy based Management in SQL 2008 and simply executes msdb.dbo.sp_syspolicy_purge_history .

Nothing to worry about!

Debugging data throughput!

Debugging data throughput.

After frustratingly watching the slow progress of moving some large .VHD files around. i needed to go back to basics and prove how good (or bad) the data transfer was.

The maths :
Taking my 1Gbit network card (1024Mbit/sec)

Divide by 8 for MB/sec (megaByte/sec) 1024 / 8 = 128MB/Sec.

128 MB/sec is therefore the theoretical maximum throughput of a Gigabit network card.

In the OS however, the copy speeds I am seeing are 60MB/sec.

This is down to >

Disks : The capabilities of the I/O subsystems being read from and written to.
Network : Other Network Traffic e.g. Broadcast traffic
Network : The overhead caused by packet size (i.e. breaking data into small packets - look at using Jumbo packets to improve)