Feb 252008

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

Feb 192008

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!).

Feb 192008

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.

Feb 172008

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!