Grumpy Old DBA

Don't always believe your system procs - they may lie !

2010-02-22T11:47:00Z

It's sometimes interesting how a simple thing can catch you out! I have a set of routines which handle the process of restoring copies of databases on other servers, it also supports full blown log shipping but has various other functionality. Generally all my servers have identical file locations for SQL Server, e.g F: for data L: for logs etc. For one reason or another I have a server which has only one data drive so the logs are now on drive F: too. My restore database proc correctly generates...(read more)

If you have Enterprise Servers you should read this blog

2010-02-18T15:56:00Z

I feel that a DBA should have a good working understanding of the underlying server that SQL Server sits upon and the storage system(s) that it connects to, otherwise you may not know if you're being spun a yarn when you're trying to understand performance issues which may or may not be outside SQL Server. One of my favourite microsoft teams are the Core , http://blogs.technet.com/askcore/ Have a read it has some great stuff....(read more)

Print vs Select - security through obscurity ?

2010-02-10T12:09:00Z

Now here's an interesting snippet which I'm sure will provoke a few " ... and you didn't know that! " comments. I wanted to take the output from a stored procedure into a table using the insert into mytable exec proc; command but despite my best efforts this just wasn't happening. After a little investigation I discovered that the output from the procedure was being generated using the print command. My investigations showed that you cannot put the output into a table when...(read more)

Detach a database and take out a cluster.

2010-02-01T14:44:00Z

During testing I managed to take my SQL 2008 cluster offline by simply detaching a database. See http://msdn.microsoft.com/en-us/library/ms189128.aspx explaining the background. When you detach a database the ntfs file permissions go to the account that issued the detach, any other permissions, such as those to the SQL Service account are removed. This apparently is considered safer, when you attach, the permissions revert to the SQL Service so everything is ok. Well should your database mdf or ldf...(read more)

Index Rebuild Observations

2010-01-28T20:38:37Z

As part of a series of migration tests carrying out an all index rebuild on a user database has been used to compare relative performance. As the application is a true 7 x 24, there are no maintenance windows so index rebuilds are very intrusive and elicit user complaints. For SQL 2000 dbcc dbreindex is used, for SQL 2008 ( and SQL 2005 ) ALTER INDEX ALL xxx REBUILD is used. It wasn't the times which were particularly of interest but a difference in SQL 2008 between the index rebuild when the...(read more)

Setting Environment variables

2010-01-27T21:44:29Z

Now you may well ask what on earth windows server environment variables have to do with SQL Server, and it's a fair question and only relevant in certain circumstances. I'm currently running a series of replay profiler traces on a server, I need these to run in 24 hour periods, I don't want to run them across the network and I have a separate lun especially for such purposes. The problem is that for one reason or another the C: drive partition is very small, this means that the temporary...(read more)

Well there's a thing

2009-10-27T07:40:00Z

Just noticed this post from the PSS Team http://blogs.msdn.com/psssql/archive/2009/10/26/reduce-locking-and-other-needs-when-updating-data-better-performance.aspx never knew you could do double assignment x = y = z....(read more)

Greenwash and ROI

2009-10-25T20:48:56Z

First up let's make clear I do support most things green, or greener, however I feel that so much is talked green but misses critical information, e.g. the point of it in the first place. I was reading about the usual sucess story of how virtualisation had turned a situation green; reported savings were £20,000 p.a in power and £50,000 p.a. in staff savings as the infrastructure was now easier to manage! Of course to achieve this saving two sans had been installed and server sprawl reduced, let's...(read more)

When does 2+2 != 4 ?

2009-10-21T21:57:00Z

Disclaimer: Results and observations are from a specific series of tests using sql 2008 enterprise on windows 2008 enterprise. I've been working on a series of benchmarking tests for an application with the aim of showing scaling of the storage for future growth. Essentially we were attempting to prove that adding extra spindles to the storage pool would give extra performance. The one interesting point that emerged was that the initial test runs were throttled by the bandwidth of the fibre channel...(read more)

Windows 2008 build fun and games.

2009-08-17T17:59:11Z

I run a number of test servers at home, these are technically PCs running a server o/s, these currently number 6. I generally build these myself with a base spec of supporting 8GB ram in 4 slots, a minimum of a dual core proc and at least one x16 pci e slot. The latest builds ideally support 16GB ram and two pci e x16 slots. The boards should support IDE and SATA drives and offer hardware raid for the SATA disks, with an extra raid card I can usually support up to 12 disks per server. I've used...(read more)

Build Your Own Cluster

2009-06-03T10:44:00Z

In case you've all missed this you can now build a real cluster with just three machines http://blogs.msdn.com/clustering/archive/2009/05/14/9613924.aspx Windows 2008 Storage Server is now available for msdn and technet download, as the man says now's there's no excuse to not have your own cluster....(read more)

Storage – The Final Frontier

2009-05-04T18:53:00Z

This is the presentation for the UK SSUG that I gave the other night. The link below is a pdf of all the slides and the script / notes that I wrote to go with each slide. I’ve not edited this for style or anything – this is as I worked out what I’d say so some of it may not make complete grammatical sense. Please feel free to download. I hope to add some posts to clarify some of the sections, I’ve been working on one about IOPS for a while. Storage the final frontier pdf...(read more)

Parallel Worlds or slipped reality?

2009-03-30T20:35:00Z

I’ve been planning a post on the subject of parallelism for some considerable time but the inability to be able to present reproducible tests for illustration had been a major concern. Things change however, I picked up on an interesting post http://sqlblog.com/blogs/aaron_bertrand/archive/2009/03/21/six-reasons-you-should-be-nervous-about-parallelism.aspx I hope you’ll grant me a quick rant here < grin >, I spend much of my time involved in the performance tuning of databases and something...(read more)

How to determine that hardware DEP is available and configured on your computer

2009-03-19T20:39:00Z

Well here we are again with another post I’d been saving up until I resolved the images issue. DEP and how it affects memory on your SQL Server!!! Microsoft KB: 912923 MICROSOFT KB: 875352 ======================================================================================= Notes on these KB: I found there to be some confusion concerning DEP, when it’s enabled you do not need the /PAE switch to enable awe memory ( x32 ); you don’t need this switch with x64 at all; What this means...(read more)

x64 Memory Problems

2009-03-18T21:33:00Z

( I’m hopeful that Livewriter will enable me to publish a blog post which includes screen shots as this one has a large number! ) Please READ everything and the documentation with the download before you install this I’ve blogged about memory issues before, and I’ve posted quite a few times on forums possible solutions to memory issues with SQL Server. Configuring Windows 2003 x64 for SQL Server There seems to be far more misinformation concerning x64 memory than there ever...(read more)