Thursday, December 18


There's quite a jump between being proficient in T-SQL/SSMS and understanding the guts of SQL Server. For example, being able to backup and restore databases is a handy skill, but it's only the tip of the iceberg. Under the water is the differences between the recovery models, the different types of backups, and the effects that doing backups have on recoverability and growth/maintenance of logs.

As mentioned in my post on "ongoing education", it's helpful to have reference resources which talk to you like a human, and pictures are pretty helpful too. So I'd like to pass on this amazing site:

It'll get you up to speed on the nitty-gritty of backups and restores a lot faster than any book or course I've attempted slogging through.

Just check it out if you're needing help, especially if you use Red Gate's backup gear, as he knows the innards of that lot intimately and provides a great lot of code samples which have saved me quite a few hours over the last few days.

Monday, December 15

Modifying your TempDB setup

Today I was moving the tempDB on a new server from the drive where every other DB data file sits to its own dedicated drive, with the intention of preventing drive contention.

To cut a long story short (and anyway I'm not even sure how I got myself into such a tangle) here's a couple of annoyances and their fixes:

Annoyance #1 - Reducing the Initial File Size of TempDB: If you right click a database, go into Properties, and Files, you can see the Initial file size of the database. Let's say that someone else set this DB up, and you'd like the initial size to be smaller. This is only really a problem you're going to get with TempDB, which re-creates each time you restart the SQL Service btw. So you type in a new value, and hit OK. Well, not OK, because if you re-open that same GUI, you'll see that your changes have not been kept! ARGH!

Solution #1 - Issue a DBCC SHRINKFILE (N'file' , size in MB) where the size in MB is an amount LESS than the initial size you wish to set. Then, ALTER DATABASE tempdb MODIFY FILE (NAME = 'file', SIZE = new initial size)

Annoyance #2 - Reducing the Number of Database Files: So you've got TempDB split up into too many files. So you go into the Properties, Files etc and remove the file(s), hit OK and they file's disappeared from the hard drive. But upon restarting the SQL Service for whatever reason, the files get recreated on the hard drive!

Solution #2 - They've got to be explicity removed from the system catalogue it seems. ALTER DATABASE tempdb
REMOVE FILE (NAME = tempdev2, FILENAME = 'c:\tempdev2.ndf')

Little secrets.

Friday, December 5

Ongoing Education for the Terminally Lazy/Busy

Reading msdn articles and whitepapers and textbooks blows goats as far as I'm concerned. I'm interested for about 5 minutes, max.

However I’ve been learning a lot about SQL (as well as plenty of other things) since I got myself set up in the RSS world, subscribing to blog and site feeds in Google Reader.

Maybe I’m preaching to the choir on this blog entry about RSS, but amongst the DB people I know very few seem that savvy about efficient net usage.

These sites below publish regularly, and when you subscribe to them in Google Reader, you can get a heap of relevant information into your unread “inbox” instead of regularly checking sites to see if anything’s updated.

If you’ve got a gmail account, you’re crazy not to start using Reader, and it works great on mobile phones cutting down bandwidth hungry sites into nice little readable chunks.

So, below, are my favourite sites/blogs which update regularly with useful SQL goodies. At least check them out, and if you’re not already in the RSS feed world, get yourself subscribed to these guys in Google Reader and you’ll be picking up more info than ever in a digestible fashion.


For sites, check out: and

Tuesday, September 23

SQL Ninja, Hardware Concerns

It turns out that the title of my blog is also the name of a SQL injection attack generator program! I knew it was too good to be true, but did I bother Googling it first? No, that'd be like reading the manual.. 

Ah well, moving on with life.

Today's problem: you've got to provide a report on the state of health of your server hardware, and you've got to provide it quickly. 

Quickest way? The Real Time server stats in SQL Nexus will give you a look at the server performance as a current snapshot.

Time to hit PerfMon direct instead if you need to see more in-depth and chart it out over time, like say over the next hour. But what to monitor?

Measuring a database server's performance (as opposed to the performance of the database code and objects), is really down to looking at a few parts: the CPU, the disk system, & the RAM.

For the CPU: enable Processor - % Processor Time and System - Processor Queue Length. The lower for both of these the better, hopefully less that 50% for the former and less than 2 per CPU for the latter.

For the disk system: % Disk Time (aim for average less than 50%), Average disk queue (like the CPU, less than 2 per disk is good), average writes per second and average reads per second (both of these depend on the disk capacity, against which you'll need to compare and hopefully you'll be using less than 85%). 

For RAM: Memory - pages per sec (lower the better, less than 20 preferably) and Available Bytes (at least 10% of the total RAM). With a grain of salt taken, SQL Server Buffer Mgr - Buffer Cache Hit Ratio should be at least 99%, and the Page Life Expectancy should be above 300 seconds. I mention the grain of salt, as coding quality can affect these counters, an apparent lack of RAM may really be a case for proper indexing or another DB optimisation problem in disguise. On the other hand, RAM may be cheap relative to development costs..

Set these counters up, and save a log, polling every 15 seconds or less if there are performance problems (and be aware that running PerfMon will cause its own slight performance hit, as will saving the logs on the local disk if you have to). The log can be reloaded into PerfMon to see averages, maxes and mins and charting of the performance over the monitoring time. 

These counters aren't the be all and end all, but should be enough to spot if there's a problem in one or more of the main systems, and suggest how the server can be beefed up if necessary.

Thursday, September 11

Hello World

I just found the first cute little bit of SQL I ever wrote at work, on the 6th of October 2004;

USE Scott_Test
@TestNum = 1047
SELECT SHID_ID, SHID_Complete, SHID_SMPNo, SHID_Date, SHID_TotalShares, SHID_PortionAnalyzed,
SHID_complete, SHID_NoExtracts, SHID_EnteredBy, SHID_ReportSentDate,
SHID_ReportSentBy, SHID_Stage
AND SHID_Stage <> 0
SHID_ID, SHID_Date, SHID_InternalDeadLine, SHID_CompletionDate, SHID_Benchmark,
SHID_NoExtracts, Shid_Comment
AND SHID_Stage = 0

Wednesday, September 10

SQL Nexus Tool

Amazingly, even in SQL 2008 there's no performance testing/tuning GUI. But of course, you just need to know where to look to find free tools. Welcome to Codeplex.

SQL Nexus takes advantage of the SQLDiag command line tool that ships with SQL Server, sucking in DMV and Trace data to present in a great drill-through Reporting Services based interface.

Within an hour, I'd identified 2 hardware based issues with solid evidence to present to management.

Tip: Read the installation steps carefully; it's not an all-in-one install. Also, when directed towards a download of, look here for the download.

Monday, September 8

Measuring Performance of Stored Procedures

Lately I've been faced with dual problems: how to spot which stored procs are performing the worst within an application which has been released, and secondly, how to help developers write code that runs quickly before deploying it to production.

Of course you can run Traces, or have a look into System Views, but knowing the how is a lot different to knowing the whiches, whens and whys. For example, which trace events do you enable?

This article by Preethiviraj Kulasingham at SQL Server Universe set off a few light bulbs for me.