Aaron Bertrand

How do the links in YOUR copy of SQL Server 2008 Books Online work?

AaronBertrand — Sat, 19 Jul 2008 23:55:00 GMT

I am just taking a quick show of hands; and maybe some information, if you are willing. (And if this hasn't happened to you, I apologize for taking up your time.)

I have had various problems on multiple installations of Books Online throughout the entire Katmai / SQL Server 2008 cycle. They have been on both 32 and 64-bit; with / without IE8 installed; and with / without Visual Studio installed.

Currently I have one instance where IE8 is not installed, BOL is alongside 2005 on Server 2003, and every single link is not clickable at all. Just no response... I can only navigate to items through the index or search results. On a similar machine the links work, but every single one leads to a page that says, "Sorry, no topics were found for the selected link." Again I must navigate via index or search results. Which is really too bad, because some of those links are much tougher to track down. And now I came across a much more isolated case where on a Vista SP1 machine with IE8 installed, a certain link leads to this "sorry" page from one topic, but not from another topic (the difference is an xmlns attribute in the link, which I think may be the cause of the problem).

You can see what I am talking about in more detail in the following Connect items:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=290461

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=357008

(I have some other items about messed-up formatting in IE8, and a lot of others have submitted the same. But I am more curious here about the links, as the formatting issue is being addressed.)

I can't imagine that this kind of thing happens differently across every single machine / VM I use, but it doesn't happen to anyone else? If you have had any issues clicking links in Books Online, please drop a note here providing any specific information we can relay back to Microsoft so they can try harder to reproduce these issues. I mean really, I am not making this stuff up... please help me escape from this madness!

Thanks!

Performance Advisor : A Quick "Two Thumbs Up"

AaronBertrand — Tue, 15 Jul 2008 20:48:00 GMT

I have been playing with Performance Advisor, the new companion to the very popular Event Manager software from SQL Sentry. Right off the bat, I can tell you that this tool should have Quest and Idera shaking in their boots, as it is really going to give them a run for their money. While a late comer to this segment of the market, Performance Advisor has definitely been worth the wait.

Rather than a big long bulleted list detailing every last feature, or demonstrating the differences between the products, I would rather highlight the three features that I like best so far, and that are already saving me time.

Disk Activity

This tab provides a visual depiction of your disks, the database files on each disk, and read/write activity. It allows you to quickly identify I/O issues as they are happening. Dashed lines will show current activity, and a tooltip over a database file tells you which database it represents, and whether it is data or log. Like the arrows between operators in a graphical execution plan, thicker dashes mean heavier I/O and latency, with the addition of movement and color as visual cues to warn you about bottlenecks.

Top SQL Analysis

Like it sounds, this shows the most resource intensive statements, and you can quickly rank by specific resource type (e.g. CPU, or I/O, or duration). Filtering and sorting are effortless, and all this without having to go and manually run a trace. The best part about this is that I can see individual statements (including actual parameters), or I can "normalize" and aggregate the statements to have placeholders for parameters (like you can do to some extent now, with a lot of manual work, or by using a tool like ClearTrace).

Dashboard

Very rarely do you come across the perfect balance of form and function; but, these folks have done it here, in my eyes. The dashboard has pretty much all of the useful metrics that I would want to see at a glance, and best of all, it is SEXY. You can switch between current metrics, and a different view which shows historical trends. The latter allows you to quickly see peaks and valleys, and determine whether the current scenario for any metric has been normal over the given time range. And their QuickTrace functionality means I will likely never need to run Profiler again.

I encourage you to take a look and, if you like what you see, download the trial!

Very important SQL Server update

AaronBertrand — Tue, 08 Jul 2008 21:32:00 GMT

There is a patch available for four elevation of privilege vulnerabilities recently discovered in SQL Server.

From http://www.microsoft.com/technet/security/bulletin/ms08-Jul.mspx:

This security update resolves four privately disclosed vulnerabilities. The more serious of the vulnerabilities could allow an attacker to run code and to take complete control of an affected system. An authenticated attacker could then install programs; view, change, or delete data; or create new accounts with full administrative rights.

Microsoft Baseline Security Analyzer can detect whether your computer system requires this update. The update may require a restart.

Get the update here:

http://www.microsoft.com/technet/security/bulletin/ms08-040.mspx

A little Management Studio "oops"

AaronBertrand — Mon, 07 Jul 2008 16:38:00 GMT

For those of you who connect to database servers where you are not in full control over all databases, or where some of your databases are ever offline (or auto-closed), the new version of Management Studio that is shipping with SQL Server 2008 is going to bring you some surprises, when you try to present a list of databases in Object Explorer.

It seems that the ability to do so is hinged upon the columns that are set up in Object Explorer Details by default. In the case I came across yesterday, the offender was "Collation." The problem is that for a database that is offline or has been auto-closed, collation comes back as NULL. Well, that's not really the problem... the real problem is that SSMS throws its hands in the air when it comes across NULL for these values, and assumes this is NULL because you don't have permission. So it throws up this error:

And then refuses to show the data for ANY database, instead of just hiding the one(s) that caused the error. And this is true whether or not you have Object Explorer Details even open (my guess here is that the contents of OED are cached behind the scenes, even when it is disabled... which I speculate may be part of the reason behind the sluggishness that many have complained about). You can read more about this in Connect #354322 and in Connect #354291.

For those of you that connect to databases that are hosted by 3rd party providers, some of which are accustomed to leaving as many databases in auto-close mode as possible, you are first going to have to go to Object Explorer Details, right-click the column header list, and un-check Collation. At this point, if you refresh the Databases node in Object Explorer, you should again see all of the databases on the server, even those where you don't have access. Strangely enough, OED still shows you some other data by default, that it probably shouldn't (e.g. recovery model, last backup, owner).

I have been pushing for the deprecation of AutoClose for a while (see Connect #238888), but so far it hasn't gained any traction.

A twist on this that I think is an important problem is that if you add a column to the Object Explorer Details view, say Data Space Used, if you fail to meet permissions requirements on even one database in the list, you get the same error shown above, and NONE of the data is displayed for any database at all. Couldn't they just put N/A or leave the value blank, for the database(s) where you are not allowed to see this information? I don't think they have really thought this solution through, since they are ignoring an entire market of people who use shared hosting for SQL Server. If I have five databases on a host and there are twenty others, I should be able to see the data space usage for MY databases. I posted a Connect item about this of course, too, and would appreciate your support in making it more visible:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=355238

sys.dm_exec_requests

AaronBertrand — Tue, 01 Jul 2008 21:44:00 GMT

One of the dynamic management views (DMVs) that is very useful in troubleshooting query performance is sys.dm_exec_requests. The documentation around this DMV, however, is quite lacking in two areas.

percent_complete

This column shows the "percent of work completed for certain operations, including rollbacks." Okay, great, now could you tell us WHICH certain operations? Through experimenting with commands that I thought could be slow and/or interesting, I came up with this list of commands that *do* report percent_complete:

backup / restore
dbcc checkdb / checktable / etc.
dbcc shrinkdatabase / shrinkfile
dbcc indexdefrag
alter index reorganize
rollback operations

And this list of commands which I thought might report percent_complete, but do not:

create / drop database
create / drop index / statistics
alter index rebuild
waitfor delay / time
drop table
truncate table
any DML operations whatsoever

Do you know of anything that should be in either list?

samples

In the SQL Server 2005 topic, there are three samples which are just not very good IMHO. Worse yet, in the SQL Server 2008 topic, the samples have been removed completely.

The first one explains how to find the query text for a running batch, and it basically says run the following:

SELECT * FROM sys.dm_exec_requests;

Then pick the spid you are interested in, copy the value from the sql_handle column, and paste it into this query:

SELECT * FROM sys.dm_exec_sql_text(< copied sql_handle >);

Yuck! How about doing this in one step:

SELECT
   [spid] = r.session_id,
   [database] = DB_NAME(r.database_id),
   r.start_time,
   r.[status],
   r.command,
   /* add other interesting columns here */
   [obj] = QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.[dbid]))
   + '.' + QUOTENAME(OBJECT_NAME(t.objectid, t.[dbid])),
   t.[text]
FROM
   sys.dm_exec_requests AS r
CROSS APPLY
   sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE
   r.session_id <> @@SPID
   AND r.session_id > 50
   /*
   -- optionally:
   AND r.session_id IN (< list of interesting spids >)
   */
;

The second sample does something similar with transactions. Run a select * from dm_exec_requests, take the transaction_id, and copy it into a query against sys.dm_tran_locks. How about:

SELECT
   [spid] = r.session_id,
   [database] = DB_NAME(r.database_id),
   r.start_time,
   r.[status],
   r.command,
   [obj] = QUOTENAME(OBJECT_NAME(t.resource_associated_entity_id, r.database_id)),
   /* add other interesting columns here */
   t.request_mode,
   t.request_type,
   t.request_status
FROM
   sys.dm_exec_requests AS r
LEFT OUTER JOIN
   sys.dm_tran_locks AS t
ON
   r.transaction_id = t.request_owner_id
WHERE
   t.request_owner_type = N'TRANSACTION'
   AND r.session_id <> @@SPID
   AND r.session_id > 50
   /*
   -- optionally:
   AND r.session_id IN (< list of interesting spids >)
   */
;

And finally, the third sample in the 2005 docs shows you how to get all of the sessions that are blocked. How about retrieving both the blocked *and* blocking processes? While this is a much more convoluted example and it involves many more objects, it certainly does a better job of showing off the power of the DMVs:

WITH blocking_info AS
(
   SELECT
       [blocker] = wait.blocking_session_id,
       [waiter] = lock.request_session_id,
       b_handle = br.[sql_handle],
       w_handle = wr.[sql_handle],
       [dbid] = lock.resource_database_id,
       duration = wait.wait_duration_ms / 1000,
       lock_type = lock.resource_type,
       lock_mode = block.request_mode
   FROM
       sys.dm_tran_locks AS lock
   INNER JOIN
       sys.dm_os_waiting_tasks AS wait
       ON lock.lock_owner_address = wait.resource_address
   INNER JOIN
       sys.dm_exec_requests AS br
       ON wait.blocking_session_id = br.session_id
   INNER JOIN
       sys.dm_exec_requests AS wr
       ON lock.request_session_id = wr.session_id
   INNER JOIN
       sys.dm_tran_locks AS block
       ON block.request_session_id = br.session_id
   WHERE
       block.request_owner_type = 'TRANSACTION'
)
SELECT
   [database] = DB_NAME(bi.[dbid]),
   bi.blocker,
   blocker_command = bt.[text],
   bi.waiter,
   waiter_command = wt.[text],
   [duration MM:SS] = RTRIM(bi.duration / 60) + ':'
       + RIGHT('0' + RTRIM(bi.duration % 60), 2),
   bi.lock_type,
   bi.lock_mode
FROM
   blocking_info AS bi
CROSS APPLY
   sys.dm_exec_sql_text(bi.b_handle) AS bt
CROSS APPLY
   sys.dm_exec_sql_text(bi.w_handle) AS wt;

So, now what?

Well, I complained about these issues to some extent in the following Connect items:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=354545

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284207

The former is fairly new, and I am awaiting some response. The latter was closed as "fixed" but this made no sense -- the topic has actually taken a step backward since I lodged my initial complaint. I re-opened it earlier today.

OT : Buck Woody talks about the environment

AaronBertrand — Tue, 24 Jun 2008 17:29:00 GMT

Buck makes a quick mention of the "green" efforts at Microsoft in his blog, and points us to the Environment site that Microsoft has created to show off what they are doing for a cause that is important to all of us. There are several tidbits in here that can help you do your part, as well. Please pass the word along.

My biggest beef with RC0 so far

AaronBertrand — Fri, 06 Jun 2008 20:55:00 GMT

As Andrew Fryer pointed out earlier today on his blog, RC0 for SQL Server 2008 has been released to MSDN and TechNet subscribers. What is RC0? It may be the last public push we see before the product RTMs later this year. It is feature complete, and from this point forward, only major show stoppers will likely be addressed before then.

I am writing to plead for your votes on a very important issue that came up for me using the new Activity Monitor, which is a show stopper for me. There is a great new Activity Monitor which has a lot of new features, such as launching Profiler directly from a SPID list, and launching a showplan from a list of "Recent Expensive Queries." This latter feature is completely broken for me, when connecting to a SQL Server 2005 instance. I get an unhandled exception:

I am hoping that you find this an important issue as I do... most notably because we will likely be upgrading our workstation client tools long before we upgrade all of the servers we manage.

So, I am asking for your votes in the following Connect item:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=349494

SQL Server has a new logo

AaronBertrand — Tue, 03 Jun 2008 21:51:00 GMT

SQL Server's new logo has been published:

Courtesy of Wesley. And I have taken a screen shot of the new splash screen for Management Studio:

More on the recent rash of SQL injection attacks (update 6/3)

AaronBertrand — Sat, 31 May 2008 03:42:00 GMT

Fellow MVP Steve Kass and Microsoft's Buck Woody have some links and advice about preventing SQL injection attacks not only from affecting your data but also from affecting your users. You can see the information here:

http://stevekass.com/blog/2008/05/31/read-this-if-you-serve-up-web-pages-from-sql-data/

And here:

http://blogs.msdn.com/buckwoody/archive/2008/05/30/sql-injection-attacks.aspx

I agree with Steve wholeheartedly here. Having your data compromised is one thing... you learn from it, you fix it, you move on. But aiding in the distribution of whatever payload is in all of these <script> files that you are unwittingly unleashing on your viewers is something you should try to avoid at all costs. Unless you are storing your actual HTML content and layout in the database (which is usually a no-no), there is no reason you should ever blindly throw data from the database into a web page without first making sure that all HTML tags (like <BR>) are replaced with characters that make them non-rendering (like <BR>).

Another excellent resource is the following article:

http://blogs.technet.com/swi/archive/2008/05/29/sql-injection-attack.aspx

Are you (or is your team) nervous about SQL Server 2008?

AaronBertrand — Tue, 20 May 2008 21:21:00 GMT

As you may know, I have been heavily involved in testing a lot of the new features in SQL Server 2008, and am likely as excited as anybody about its release. Since I work at a fairly progressive company, I spoke with two of my superiors today - independently - and I was surprised at the results. My proposal was, for the project that we are currently working on, that we hit the ground running by deploying SQL Server 2008 when we are ready to launch. Based, of course, on successful testing and adequate performance of RC0 (when we get it) in the meantime, and that the product ships on time.

My immediate superior was all for it. Having attended one of my presentations on the new features, he knew about some of the benefits we would enjoy pretty much right out of the box -- page/row/backup compression, filtered indexes, date columns, change data capture, the list goes on. And he is all for deploying the next CTP to our QA environment for serious functionality / load testing and analysis, with the intention of being on the "early adopter" side of the curve when the product ships.

His boss, however, is a lot more cautious. Not only is he uninterested in deploying SQL Server 2008 right away; he is not even interested in looking at it until SP1 is out, and tested, and has about a month of serious market penetration. Which, by rough calculations, based on the TPC benchmark publication date and the new servicing model, should be sometime in February or March of next year.

All of this stemming from the long-standing tradition of never installing a dot-oh release of a Microsoft product. Personally, I found the RTM of SQL Server 2005 a hell of a lot more stable than SP2. (And SP1, IIRC, did little in terms of "fixing" anything except that they finished the database mirroring functionality.) Service packs in SQL Server 2000 don't exactly give us great confidence, either. YMMV.

What I was hoping for was a balanced response, somewhere in the middle. Like, okay, we won't deploy the day it is out, but we will perform all the necessary tests, including upgrade scenarios, and consider it within a few months of release. For me, 9 months is a LONG time to wait (and no, it has nothing to do with having children :-)). But are you facing similar superstition? Do you feel that way yourself? I am curious how others are progressing in the "let's upgrade" battle. Tell me your stories! Do you align with my boss' boss? Or do you have any suggestions for changing his mind?

Do you want IntelliSense to support SQL Server 2005?

AaronBertrand — Mon, 19 May 2008 22:59:00 GMT

Currently, the plans for IntelliSense are to support SQL Server 2008 *ONLY*... since it works against SQL Server 2005 in the February CTP, I was very surprised to learn that it is being dropped by RTM (and possibly by RC0). This is mainly because of the time frame of the release and the difficulty of catering to multiple dialects (sometimes there are going to be false positives, and there are even possibilities that it will miss obvious issues). You can read more about this issue at The W Blog.

Being that IntelliSense isn't even going to be complete when SQL Server 2008 ships (mostly just supporting SELECT operations), I could live with a disclaimer that says, when you are working against a downlevel version, it is not always going to be 100% accurate. Could you? If so, I urge you to cast your vote on Connect (at publish time, only 11 people have voted):

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=341872

When was my database / table last accessed?

AaronBertrand — Tue, 06 May 2008 05:14:00 GMT

A frequently asked question that surfaced again today is, "how do I see when my data has been accessed last?" SQL Server does not track this information for you. SELECT triggers still do not exist. Third party tools are expensive and can incur unexpected overhead. And people continue to be reluctant or unable to constrain table access via stored procedures, which could otherwise perform simple logging. Even in cases where all table access is via stored procedures, it can be quite cumbersome to modify all the stored procedures to perform logging.

SQL Server 2008 will offer Server Auditing for all actions, and this can be logged to a file, or to the Windows Application or Security Log. You can do something as narrow as record when a specific login queries AdventureWorks.Person.Address.City, and as wide as recording information about every query against every database on the entire instance. Here is a quick sample that audits all select queries against Person.Address in the AdventureWorks sample database:

USE master;
GO
CREATE SERVER AUDIT Test_Server_Audit
    TO FILE ( FILEPATH = 'C:\Audits\' );
GO
ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = ON);
GO

USE AdventureWorks;
GO
CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit
    FOR SERVER AUDIT Test_Server_Audit
    ADD (SELECT ON Person.Address BY PUBLIC)
    WITH (STATE = ON);
GO

SELECT *
    FROM Person.Address;
GO

SELECT *
    FROM fn_get_audit_file('C:\Audits\*', NULL, NULL);
GO

USE AdventureWorks;
GO
ALTER DATABASE AUDIT SPECIFICATION Test_Database_Audit
    WITH (STATE = OFF);
GO
DROP DATABASE AUDIT SPECIFICATION Test_Database_Audit;
GO
USE master;
GO
ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = OFF);
GO
DROP SERVER AUDIT Test_Server_Audit;
GO

For those of us who don't want to wait for SQL Server 2008 and cannot use stored procedures to log select activity, there is another answer: the DMV sys.dm_db_index_usage_stats, introduced in SQL Server 2005. By showing the last read and write to a table, this DMV allows us to answer the questions we couldn't before:

when was database x accessed last?
when was table y accessed last?

We can answer the question about access to a database simply by aggregating the data in the DMV to the database level:

USE AdventureWorks;
GO

SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO

WITH agg AS
(
    SELECT
        last_user_seek,
        last_user_scan,
        last_user_lookup,
        last_user_update
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        database_id = DB_ID()
)
SELECT
    last_read = MAX(last_read),
    last_write = MAX(last_write)
FROM
(
    SELECT last_user_seek, NULL FROM agg
    UNION ALL
    SELECT last_user_scan, NULL FROM agg
    UNION ALL
    SELECT last_user_lookup, NULL FROM agg
    UNION ALL
    SELECT NULL, last_user_update FROM agg
) AS x (last_read, last_write);

Switching focus to each table is accomplished by adding the object name to the GROUP BY (and as Jerry pointed out, this will require SP2 to use OBJECT_SCHEMA_NAME(), otherwise you can join against sys.tables and sys.schemas):

USE AdventureWorks;
GO

SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO

WITH agg AS
(
    SELECT
        [object_id],
        last_user_seek,
        last_user_scan,
        last_user_lookup,
        last_user_update
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        database_id = DB_ID()
)
SELECT
    [Schema] = OBJECT_SCHEMA_NAME([object_id]),
    [Table_Or_View] = OBJECT_NAME([object_id]),
    last_read = MAX(last_read),
    last_write = MAX(last_write)
FROM
(
    SELECT [object_id], last_user_seek, NULL FROM agg
    UNION ALL
    SELECT [object_id], last_user_scan, NULL FROM agg
    UNION ALL
    SELECT [object_id], last_user_lookup, NULL FROM agg
    UNION ALL
    SELECT [object_id], NULL, last_user_update FROM agg
) AS x ([object_id], last_read, last_write)
GROUP BY
    OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id])
ORDER BY 1,2;

One word of note is that sometimes an UPDATE can look like a simultaneous read and write. For example:

USE AdventureWorks;
GO
UPDATE Person.Address SET City = City + '';
GO
SELECT *
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID()
    AND index_id = 1
    AND [object_id] = OBJECT_ID('Person.Address');
GO

See that for index_id 1, last_user_scan and last_user_update are identical and fairly recent.

Another note is that unless a view is indexed, you cannot reliably track access to a view -- instead the references to the underlying tables are updated in the DMV.

UPDATE - Mike C# and dave ballantyne brought up a great point that applies to all DMVs: the values do not survive a SQL Server restart, or detach/attach, or even Auto-Close. So, if you restart your server and then want to see when something was last accessed, all objects will either be NULL or very recent. One way to work around this is to create a SQL Server Agent job that polls the DMV periodically, and stores a snapshot of the data. This way you can have a running history of "last access" and maybe roll it up once per day (or whatever granularity is suitable).

Even when SQL Server 2008 is released, auditing of some kind will be required if you want more information, such as a history of who ran which queries. And if you are looking for more details about information that has been added, updated or deleted, you are going to want to look into the Change Tracking and/or Change Data Capture features. But in the meantime, this DMV provides a quicker and much lighter-weight approach to at least determining when your data was accessed last.

Performance / Storage Comparisons : MONEY vs. DECIMAL

AaronBertrand — Sun, 27 Apr 2008 22:12:00 GMT

As you may already know, I am not a big fan of the MONEY data type, because of its inflexibility, accuracy problems, and the expectations the name of the type evokes in new users. If I had my way, MONEY would become a synonym for DECIMAL in SQL Server 2008 (allowing for specific precision and scale), and be removed in the following version. Of course there are people out there that either don't feel as strongly as I do, or feel the opposite -- that MONEY should be here to stay.

After a recent discussion about the pros and cons of using MONEY vs DECIMAL for storing currency (and even non-currency) data, curiosity got the better of me. One of the arguments for the MONEY data type was performance. No supporting data was provided, of course. So I decided to conduct some tests myself. I wanted to measure how MONEY compared to DECIMAL data types both in their original implementations and using new technologies available in SQL Server 2005 (VARDECIMAL storage format) and SQL Server 2008 (page and row compression).

The person arguing for MONEY showed the space used by MONEY compared to the same information stored in a DECIMAL(20,4) column. Not all that surprisingly, the latter was slightly larger. But is that the whole story? No, for two reasons. One is that the *performance* of these choices was not compared, and the other is that DECIMAL(20,4) is not a very realistic requirement for storing currency data. Unless you are storing the pricing information for luxury yachts or aircraft carriers, in which case you can probably drop the decimal places altogether and use INT or BIGINT. For the rest of us, a better choice would be DECIMAL(8,2) or DECIMAL(10,2).

I created 11 databases, each with a single table containing a single column:

Keeping these tables in separate databases allowed for isolation of several factors and measurements, including database level settings, log growth, data file size and even backup time.

Next, I populated the table in each database with approximately 390,000 rows of varying length decimal data (based on calculations against object_id from a triple cross join of sys.objects on itself), and measured the insert times and storage requirements. Here is how they stacked up:

Then I performed an update that affected all rows, making sure that roughly 20% of the rows would have a significant change in significant digits (e.g. by adding 1,000,000). Here is the performance comparison, as well as how the data and log were affected:

Next I compared the time and cost of performing a SELECT COUNT(*) with a WHERE clause against the column:

And finally, I performed native and compressed backups of each database, comparing execution time and output size:

The following chart summarizes everything performance-wise. The orange with the dot means that database performed the best; the x on the red background means it performed the worst.

And this chart summarizes all things size-wise:

Of course, there is nothing overly definitive here. DECIMAL(10,2) with row compression enabled got the most "first place" metrics, while MONEY with no compression and VARDECIMAL types never finished near the top of the class. But you can judge from the results for yourself, and make decisions based on your own priorities.

[UPDATE]
Alex asked for some metrics on more complex operations like SUM(). I ran some tests using both SUM() and AVG(). The logical reads of course are the same as all the others, and the scan costs remained unchanged as well. But as for the observed performance of both calculations (compute scalar cost was identical for both operations), see the following chart:

Again, this was an average over 10 tests. Note that I did not append these results to the summary charts I delivered above. And sorry about the slightly different-looking screen shot.
[/UPDATE]

Please take into account that these tests were performed on a dual-core laptop computer, and the database files were created on external storage. There are many other tests I could have run to glean more performance and storage data, against a much larger data set, and using production-class hardware, but for the scope of this post I just wanted to glance at the most basic operations. I repeated these tests 10 times from start to finish, so each metric taken is an average of 10 tests (in a lot of cases they were the same every time).

This was a very tedious exercise to perform. If you would like to perform your own tests, with your own sample data, and on your own hardware, I will more than gladly share my scripts. I'd post them here right now, but they are scattered and not distribution-friendly at this point.

Call a spade a spade! (SQL injection, or IIS vulnerability?)

AaronBertrand — Sat, 26 Apr 2008 16:06:00 GMT

In a recent blog post, Dancho Danchev mis-labeled a recent IIS vulnerability as a "massive SQL injection attack."

Let's be honest here. Yes, this alert needs attention. But this is not a new SQL injection vulnerability. It is simply an exploit in IIS that lets malicious users access your source code. If your database is already open to SQL injection attacks by anyone who can access the file system on your web servers, then yes, SQL injection is just waiting for the next vulnerability to your file system. However, if you protect your database server(s) from SQL injection in the first place, then no IIS vulnerability will magically become known as a SQL injection attack.

Never mind that half the IIS servers in the world probably don't even connect to SQL Server, and of the remainder, not all are vulnerable to SQL injection. The ones that are vulnerable are that way because the web developers and/or DBAs have been sloppy and allowed for practices that help make SQL injection possible.

Call it what it is; don't sensationalize it. And instead of trying to create panic, provide a little education! How do you prevent an IIS vulnerability from becoming a SQL injection attack? There are plenty of things you can do. Some of them are pretty obvious, or have been discussed previously, but I'll recap the ones on my list:

Do not expose your SQL Server to the Internet directly
While in some cases you can't avoid this (shared database servers at a hosting provider, for example), if your server-side code yields a public address, or enough information that the public address can be easily determined, then you are opening yourself up. All someone needs is read access to your config file or ASP page in order to obtain credentials to access your SQL Server from anywhere. Talk to your network administrator about keeping SQL Server behind your firewall.
Make your passwords strong
Ideally, your applications will use Windows authentication, but if you must use mixed authentication modes, then make sure your SQL Authentication passwords are "strong" passwords. It is very hard to be completely immune to a dictionary attack, but you can make it much more difficult by using a 16-character password with mixed case and alphanumerics, like '$QL$erver_r0ck$!', as opposed to an "easier" password like 'tweetybird.'
Follow the principle of least privilege
Do not use sa as the login in the connection strings for your application. Use a low-privileged user that can only execute (certain) stored procedures. There is no reason someone should be able to add a query like "SELECT * FROM sys.objects" to your server-side code, or launch extended procedures like xp_cmdshell, or drop objects, because that user should not have sufficient access to do so... the application user should not be sa or db_owner. Lock down your applications, and only give them the rights they need.

Similarly, do not use a domain administrator or otherwise privileged user as the service account. This would mean that anything that runs under the context of SQL Server has free reign over your server or even entire network, using a variety of tools like extended procedures.
Always use stored procedures, or at least parameterized statements
If you build ad hoc SQL in your applications, then you are asking for SQL injection attacks, and I strongly suggest you become familiar with using stored procedures or parameterized queries. Otherwise, all input becomes suspect, since it is very easy to use comments or semi-colons to change the meaning of queries or to append additional queries to be executed. With a query that uses strongly typed parameters, however, this technique becomes fruitless. This does not mean something like:

sql = "EXEC dbo.foo @param1 = '" & Request.QueryString("bar") & "'"
conn.execute(sql)

This is still vulnerable to SQL injection, because I can now call the page using ?bar=';drop table blat;--

Instead you should use a command object and pass the inputs to parameters. (This also prevents you from having to escape apostrophes in names like O'Hagan, delimit date literals correctly, etc.)

Use TRY/CATCH to return more generic error messages
In order to prevent revealing your database structure, do not let errors like foreign key violations or other errors bubble up to the application. This just gives your potential attacker more information about your database structure than they need to have. Instead use error handling to say "That user does not exist" instead of the default error message SQL Server provides -- which gives specific table and column information back to the user. If you are using ASP.Net, then you can make sure that you turn CustomErrorsMode to "On" or "RemoteOnly" and set compilation debug to "false"...
Do not store passwords in your Users table
A lot of web applications store usernames and passwords so that their users can log in to the application. Instead of storing a password in plain text, which can then be read easily by anyone who manages to gain read access to the Users table, store a hash of the password (using MD5 or something similar). When the user attempts to login (hopefully via SSL), you use the same technique to hash their entry and compare the hashed values, instead of a clear text comparison. Even if the user has read access to the stored procedure that implements the hash, all they can do with it is try and try and try... they cannot reverse engineer the data if you use a proper hashing technique.

Don't want to call CSS / PSS to get a cumulative update? You don't have to!

AaronBertrand — Fri, 25 Apr 2008 18:44:00 GMT

Bob Ward posted a blog entry today where he explains the process of getting a cumulative update for SQL Server 2005 without having to call Microsoft's support team (they are called CSS now but you may remember them as PSS). This is a great evolution in the process, which used to be very difficult (you had to open a support case and be deemed eligible to be issued a CU by a support engineer), and a few months ago they made it a little easier, allowing you to submit a form and have an engineer review it (without a phone call or a formal case), and up to a day later, you got an e-mail providing the download link(s). Now, at least for post-SP2 cumulative updates, the e-mail containing the download is almost immediate.

Makes me wonder why they don't just make it downloadable like a service pack, but in any case, this is a great step forward. You can see his post here:

http://blogs.msdn.com/sqlreleaseservices/archive/2008/04/15/cumulative-update-7-for-sql-server-2005-service-pack-2-2.aspx