On the weekend, Amanda came out of the backyard claiming that we were growing tomatoes. I didn’t believe her - at first. We had a big tomato plant growing out from underneath the house. As long as the dogs don’t get to them, we should have a bumper crop! Not bad for not even trying!
I’ve spent some time tonight updating the look of the site, and am now quite happy with the clean appearance. It’s nothing terribly original, but it’s looking sharp, and there’s fewer images. I’ll continue making some tweaks, but nothing earth shattering for now.
I also noticed that the Google Ads on the sidebar are advertising SQLBenchmarkPro at the top. This is a product I work with daily, and it’s invaluable for tracking the workload on your SQL Server database server, and how that workload changes over time. You can see the performance characteristics of the most resource-intensive queries on the server, and it lets you know exactly which queries you should tune to give your server relief. It’s definitely worth checking out! (Disclaimer: I’m closely involved with the developers of SQLBenchmarkPro, but it’s still a fantastic product!)
Edit: the ads are gone now, but you can visit http://www.sqlbenchmarkpro.com.
I discovered a usage of TOP that I didn’t know about today. I wanted to return the TOP x rows, where x was a number between 0 and 30. I tried SELECT TOP @x, but with no luck. Mentioning this to Rob later resulted in the revelation that you can specify SELECT TOP (@x). Or, if you wanted to be more involved, you can try SELECT TOP (SELECT MAX(Schema_id) FROM sys.objects) FROM sys.objects. Single parentheses are required for an expression to work.
The silly examples culminated until we had this little gem:
create table #nums (num int primary key);
insert #nums values (1)
insert #nums select count(*) over() + num from #nums
insert #nums select count(*) over() + num from #nums
insert #nums select count(*) over() + num from #nums
insert #nums select count(*) over() + num from #nums
insert #nums select count(*) over() + num from #nums
select *
from #nums n
cross apply
(select top (n.num) * from sys.objects) o
where n.num < 10
Tonight was the February meeting of the Melbourne SQL Server user group. As usual, copious amounts of beer and pizza were provided, and I had a a great time. Rob Farley was speaking on new features of SQL Server 2008, namely the topics of MERGE, Table Valued Parameters, and the Resource Governor. He also showed how Powershell is integrated into SSMS, and how easy it is to now navigate through instances, databases, and all properties of objects within. VERY cool. Books Online is also looking much improved — the documentation levels for the Resource Governor looked excellent!
Briefly, MERGE allows you to compare (join) a table with a rowset, and apply an action to each row in the table, depending on whether a row was matched or not (or matched with extra conditions). This allows you to insert a row if it doesn’t exist, or update the existing row, removing the need for two separate statements to UPDATE, check if @@rowcount = 0, and then INSERT.
Table Valued Parameters simply allow you to pass a table to a stored procedure. There are a number of restrictions, but it’s a feature that could be very useful.
The Resource Governor allows you to dynamically control CPU and memory resources to grant some users or applications a higher priority for CPU and memory than others. This allows the DBA to scale back any class of users that are hammering the system, or, as Rob suggested, grant your boss’s queries more priority (or less if you really need to justify a faster server!).
Over the past 8 months, I’ve been performing a lot of SQL Server health checks (if you would like one done, drop me a line at jim AT jimmcleod DOT net), and this involves an analysis of the most read-intensive queries that are being performed by SQL Server. SQL Server’s architecture runs off 8 KB pages - database data files are made up of 8 KB pages (allocated as 64 KB extents, or 8 pages), and a read-intensive query is one that performs a high number of page reads to return the required information.
Some queries might require reading a large number of pages in order to get the correct data for the query, but inefficient indexes can blow out the number of pages read. It’s not unusual to see queries being executed that perform 50 GB of reads against a 10 GB database, effectively visiting the same pages over and over. It obviously takes more CPU time to process a higher number of pages, but it’s worse when you add the speed of the disk in as a factor. If your query performs a table scan of a table containing 100 MB of data, SQL Server will need to read all 100 MB (12,800 pages)off disk and into memory (unless it’s already cached). The query execution will effectively halt while waiting for all the pages to be loaded into memory, and each page will then be scanned looking for those rows that match the where criteria. Also, while loading in this 100 MB of data, another 100 MB has been pushed out of the cache, meaning SQL Server must load that data back from disk when it’s needed again.
If that wasn’t enough, high read counts are also major causes of blocking. A process is blocked when it is forced to wait for a resource to become available. Most important to the current topic is waiting for a page to be lifted from disk into memory as discussed above, or waiting on locks to be removed. For example, if a process is scanning a table looking for rows, and another process wants to update a single row, the update process will be blocked until the first process has finished, even if those two processes are after completely separate rows. (Note that this example doesn’t go into isolation levels, which makes blocking much more interesting.)
So, how can we see how many reads are used, and how can we tell if those are excessive? There are two easy ways to find out the number of reads - Profiler, or SET STATISTICS IO ON. More on these in the next post.
It’s been a while since I’ve posted, but that’s because I received a new job during January…. Fatherhood! Not too much to say. It’s humbling, amazing, and a little scary at this stage - and it’s probably worse for Amanda! I finally feel like a responsible adult! And, I may be biased, but he’s pretty cute 
Welcome, Lachlan!
As I was walking through the office this afternoon, I heard my boss call out in his best worst-English-accent “James! You didn’t tell me you had a blog! What else haven’t you told me about?”. My replied of “I have cooties?” didn’t elicit a positive response, but now I know he’s onto me, so more content it is!
At least I know that now someone other than my wife is reading this!
I did my Microsoft 70-444 Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005 today, and successfully passed. Rob Farley mentioned my success on his blog here. Just one more exam to do, 70-443, and I’ll have the MCITP.
I should probably add some content if he’s going to be forwarding traffic my way!
There’s a little known backup product available for SQL Server, which blows the competition out of the water. Hyperbac, created by the guys that originally created Litespeed (now owned by Quest Software) and started the SQL Server backup compression craze, have come back to create a new product that they would have written the first time around.
All existing compression backup utilities use the SQL Server Virtual Backup Device Interface (VDI) interface for backups. This is an API published by SQL Server to enable backup/restore related events to be processed by 3rd party vendors. The backups are then compressed and saved to disk. The main drawback of this method is it requires extended stored procedures to be stored on the disk, and takes a portion of the SQL Server MTL to perform the backups.
Hyperbac, however, sits on the operating system level and activates whenever it sees the SQL Server process trying to write a file with a specific extension (which is configurable). It then compresses the backup stream on the fly, and writes it in a zip-compatible file, allowing anyone to uncompress the backup simply by using a zip tool - leaving you with the original native backup that SQL Server would have made. When restoring, the processed is reversed. The major benefit is that SQL Server isn’t even aware of Hyperbac’s presence, and simply uses native commands to operate the backup. A side benefit of this is if Hyperbac fails to engage, you still end up with a native backup (of course, if you’re banking on your backup to be compressed, you might not have enough room free…)
As a result of being zip-compatible, the file sizes are a little bigger than can be performed by Litespeed et. al, but the speed of which the backups can be performed is where Hyperbac shines. At one client’s site, Litespeed can back up their 50 GB database in 1 hour 20 minutes, compressing down to 6 GB. Hyperbac will compress down to 8 GB, but will do it in just over 5 minutes.
Licencing is server-based, not SQL instance- or CPU-based, and backup encryption is also available. There are no licencing requirements to restore the data - only to perform backups, making Hyperbac a most competitive product. It is also available for Oracle, DB2 and MySQL, and trial versions are available.
And no, I’m not getting paid for this review!
