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
Thursday, 30 April 2009
Wednesday, 29 April 2009
Off-Topic : Rockbox
Have given an old Iriver H340 a new lease of life with new firmware...
http://www.rockbox.org/
(Highly recommended)
http://www.rockbox.org/
(Highly recommended)
Wednesday, 22 April 2009
Off-topic : Windows Mobile 5.0 - Deleting all Text Messages
Had too many and it slowed right down.
The option to delete them all is there (but hidden)...
http://msmobiles.com/news.php/3851.html
The option to delete them all is there (but hidden)...
http://msmobiles.com/news.php/3851.html
Saturday, 18 April 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...
Turns out the setting is machine based and located in Control Panel (Regional & Language Options), not Management Studio itself...
Wednesday, 15 April 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, 13 April 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 :
Bizarrely, achieving A4 that didnt spill across multiple pages when printed was quite fiddly.
Report Properties :
Page Setup :
Sunday, 12 April 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
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, 8 April 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 >
SSRS 2008 : Reporting Services 2008
Had my first experience of installing Reporting Services 2008 last night.
I have 3 observations -
I have 3 observations -
- 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. - It no longer requires Microsoft's Internet Information Services (IIS) as a web server.
- 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.
Tuesday, 7 April 2009
Monday, 6 April 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!
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)
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)
Thursday, 2 April 2009
Subscribe to:
Posts (Atom)