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

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)