Louis Davidson : SQL Server

Why We Write #1 - An Interview With Thomas LaRock

drsql — Fri, 22 Mar 2013 00:03:00 GMT

I 've been a writer of trade level technical materials for over 13 years now, writing books, articles, blogs, and even tweets for a variety of outlets, almost exclusively about Microsoft SQL Server. While I won't claim to be the best writer in the world,...(read more)

Chapter 7–Enforced Data Protection

drsql — Tue, 21 Jun 2011 04:36:13 GMT

As the book progresses, I find myself veering from the original stated outline quite a bit, because as I teach about this more (and I am teaching a daylong db design class in August at http://www.sqlsolstice.com/ … shameless plug, but it is on topic :)...(read more)

See you in Columbus Saturday?

drsql — Mon, 06 Jun 2011 04:32:45 GMT

Assuming all goes as planned, I will be in Columbus, OH this Friday night and Saturday for SQL Saturday 75 . I really love SQL Saturday events the best of all of the events because they are very intimate in nature. As a fairly antisocial person, I sometimes...(read more)

Normalization and How to Know When You Are Done… The short version…

drsql — Sun, 29 May 2011 20:54:15 GMT

A while back, I was working on a short article about Normalization for a book that never got published (admittedly I wasn’t getting paid for the article, and it wasn’t for charity, so I wasn’t that broken up over it.) The task at hand was to, in...(read more)

Quest DMV Breakfast Code – Disk IO

drsql — Wed, 04 Nov 2009 17:52:43 GMT

For any of you who attended the Quest breakfast at PASS (and anyone else really) I wanted to go ahead and post the code and give a little wrap up/explanation. (I will post the stuff on perf counters later. They are cool, but this is really what I wanted...(read more)

Fifth pillar - Secure

drsql — Tue, 06 Oct 2009 23:52:42 GMT

As I have mentioned in all of the previous posts, basic functionality is the foundation of any system. So it goes without saying that if you have just implemented a payroll system, everyone is getting paid. To meet the basic bar that EVERYONE agrees...(read more)

Headed..South..To Atlanta for SQL Saturday

drsql — Fri, 24 Apr 2009 02:55:00 GMT

Heading down to SQL Saturday (www.sqlsaturday.com) in Atlanta to give a 9:15 AM session on Database Design, with a greater than normal dose of normal thrown in. I hope there are more than 7 people to show up, because I would hate if everyone got some swag (I usually like tossing out stuff when people make cool and interesting statements, and NOT when they prove me wrong…you know who (all) you are :). Apparently they were at their capacity, so maybe not. If you need to know where it is, check here: http://www.sqlsaturday.com/eventhome.aspx?eventid=17, and come on out. The more smart alecky know-it-alls I get who want to prove me wrong the better…well, as long as you sit there quietly and learn something…

(Hmm… I hasten to send you that link (www.sqlsaturday.com), because frankly you will have to see that someone has a bad where clause on a query somewhere. I would offer up prizes for the best guess, but every time I do contests no one ever gets it or even tries.)

Commenting your code

drsql — Thu, 31 Jul 2008 02:19:43 GMT

As I am easing back into real life from writing the book, I am in search of easy targets for blogging. My boss mentioned this blog over on Jeff Atwood's Coding Horror Blog and it got me thinking about commenting. His advice is to only comment "why" the code works. I can't quite agree, because the code he claims to be acceptable is:

private double SquareRootApproximation(n) {
r = n / 2;
while ( abs( r - (n/r) ) > t ) {
r = 0.5 * ( r + (n/r) );
}
return r;
}
System.out.println( "r = " + SquareRootApproximation(r) );

I mean, it is better than some code I have seen, but still, I would like a bit more information about why this works. Maybe the name of the algorithm used, or at least what to do if this fails to provide the expected results. Admittedly this is probably something that could be easily found, but most algorithms are not. Comments in my mind should at least lead you to understand the mindset of the programmer. What would actually improve this code in my mind is to change the variables to full words (though in this case it might not make sense to do this.)

On an extremely different side of things is this article from "Edgewood Solutions Engineers" on mssqltips.com. Their answer is to explain what the code is doing in simple terms, making sure to comment almost everything. They have a very elaborate header devised, with dependencies, both users of the object and objects it used. Most of what is said seems a bit like overkill, but their point here "Comment all of the major code blocks of the code and the critical minor points that can be easily overlooked such as a obscure WHERE clause." is a good one. I generally pepper my code with comments where I think it will be hard to debug for myself later, with a consideration for others, particularly when those others will call me to explain the code.

Which brings me to my commenting philosophy. I personally think you have to comment to the expected lowest common denominator. Think of the dumbest person who could have the need to read your code who is also qualified to have their job (otherwise you would have to write instructions on every line of code). If the qualified person can figure out what you are doing just by your naming conventions and , then it doesn't need comments. But if that person would look at the code and reasonable figure it out, then there is no need to comment the code. What this requires is a few things:

Naming objects - if your procedures, tables, columns, functions all have meaningful names, you won't have to explain what they mean, saving time
Good design - if the relationship between objects and the cardinality of those relationships is clear, then you don't need to explain that what you are doing is hack due to poor thinking...
Naming variables - probably the most important thing to avoid the need for comments is naming stuff. Name variables with words, not single character values (except sometimes i, x, etc will suffice for obvious typical uses)
Reasonable code formatting - SQL has no real form, so you could write procedures on a single line. You could. You could smash your hand with a hammer too. Neither action would be very good. (Consider using Red-Gate's SQL Refactor tool if nothing else.)

However, the fact is, for SQL code, the real problem comes in when you start coming up with cool relational methods of solving problems that most moderately qualified people wouldn't get. For example the trick of using a sequence table to break apart a comma delimited list. Couple that with a join and you get some amazingly cool code, but how do you comment it?

For example, say an architect that shouldn't be an architect designs a table with a comma delimited list like this (didn't I mention good design earlier? I hate having to say this is a hack, but it is an elegant hack...)

--excerpted from Chapter 7 of Pro SQL Server 2008 Relational Database Design and Implementation
CREATE TABLE poorDesign
(
poorDesignId int,
badValue varchar(20)
)

INSERT INTO poorDesign --using 2008 syntax
VALUES (1,'1,3,56,7,3,6'),
(2,'22,3'),
(3,'1')

You can "normalize" this set using a table of numbers (in my examples named tools.sequence) and a really cool join:

SELECT    poorDesign.poorDesignId as betterDesignId,
          SUBSTRING(',' + poorDesign.badValue + ',',i + 1,
                CHARINDEX(',',',' + poorDesign.badValue + ',',i + 1) - i - 1)
                               as betterScalarValue
FROM     poorDesign
             JOIN tools.sequence
                    on i >= 1
                      AND i < LEN(',' + poorDesign.badValue + ',') - 1
                      AND SUBSTRING(',' + + poorDesign.badValue + ',', i, 1) = ','

But are there enough pixels available on the planet to make that more understandable to most SQL programmers? Even the reasonably qualified? I mean, I am still kind of amazed at the technique and the fact that it returns the following:

betterDesignId betterScalarValue
-------------- -----------------
             1                 1
             1                 3
             1                56
             1                 7
             1                 3
             1                 6
             2                22
             2                 3
             3                 1

still impresses me. Frankly I don't know how to comment that code to make it readable. In a real situation I would settle for a comment before the SELECT that stated:

--Uses a table of numbers to parse the comma delimited list into a SQL acceptable format.
--If you don't understand this code, read this article: http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum

Opinions? What do you use for a comments in your code? Do you have commenting policies?

2008: Error List in 2008

drsql — Thu, 06 Mar 2008 03:40:47 GMT

Ok, so I was looking around for a blurb about plan guides for my anti ad hoc SQL section of my book (ok, maybe not completely anti- but that isn't the point,) when I found this blog: http://geekswithblogs.net/Sreeblog/articles/117576.aspx that basically just lists some new features in 2008. One that I hadn't seen I feel the need to mention.

Transact-SQL Error List Window:
SQL Server Management Studio includes an Error List window that displays the syntax and semantic errors generated from the IntelliSense code in the Transact-SQL Query Editor.

If you haven't seen this, it is really kind of neat. Say you type:

select *
from sys.object
whe re name = 'fred'

You can see the obvious errors, since this is a very small batch, but IntelliSense underlines sys.object and re in the batch. Go to the View menu and show the Error List, and you will see something like this:

Double-click on the error, it takes you to the error in the query window. Obviously I have just discovered this, so please comment if you know more about this topic (particularly if it has been helpful to you or not) please chime in. And thanks to Sreenivas Mogullapalli for the cool post, it had a few other things I didn't remember too (just don't make me have to pronounce your name...I am pretty sure I wouldn't get it right.)

2008: Declaring and instantiating a value

drsql — Sun, 02 Mar 2008 00:40:00 GMT

Ok, I admit it. Sometimes the least important things are the most fun. As I try to get my blog back up and kicking again after a few months of holiday fun coupled with some dreary personal life things (a death in the family and lots of sickness/busyness, mostly,) I felt the need to write about another little time saving feature that you might not have heard of. Ever if you have it is still cool.

This topic is declaring and instantiating a value in a single statement. So what used to be:

DECLARE @i int
SET @i = 1

Can now be:

DECLARE @i int = 1

Ho hum, I had thought earlier when I first saw this. You know, it saves me 3 keystrokes. But today, I was doing some writing in my book and I realized that it isn't just limited to literals (it had just never crossed my mind) so when I was creating my savepoint names for nested savepoints. You can use literals and functions, whatever you need.

DECLARE @savepoint nvarchar(128) = cast(object_name(@@procid) AS nvarchar(125)) + cast(@@nestlevel AS nvarchar(3))

All in one line of code...clean, neat. And when I was writing a trigger that I could do this:

DECLARE @rowsAffected int = @@rowcount, --stores the number of rows affected
@msg varchar(2000) = '' --used to hold the error message

And save a couple of lines of code, and now, how much cleaner and effective is this code? Not only does it save two lines of code to do the SET (or you could use SELECT), but it saves lines of whitespace too.

Will this save a lot of time? Probably not, but it is one of those long desired features that we SQL Server programmers are so glad to be getting. In my case it is going to cost me time because I have to go back and re-edit places where I forgot about this syntax and didn't use it in my new book, which is one of the worst parts of writing. When you mess up and forget something that is new and more or less essential, you can have to edit large amounts of code/text.

The comment from Steve got me thinking if you could use a query to instatiate the value. Survey says:

create table fred
(
value char(1)
)
go
insert into fred
values ('a'),('b'),('c')
go

declare @value char(1) = (select MAX(value) from fred)

select @value

YES! This returns 'c', just as you would expect. Okay, so now this is even cooler. It doesn't work with table types though. And since we are feature complete, I assume it probably won't (I didn't expect it too, but it never hurts to try.)

2008: Rebuilding a Heap

drsql — Wed, 27 Feb 2008 04:10:24 GMT

In 2005, rebuilding a table that was a heap (no clustered index) wasn't easy. You could copy it to a different table, or you could add a clustered index and then drop it.

In 2008, this is a far easier thing to do. They have added to the ALTER TABLE command a method to rebuild the table, which is the same as rebuilding the clustered index for a clustered table, but for a HEAP, it is the only way to go.

In the following code sample, I create a heap, load it will "little data", then expand every row to much larger values. The result is a little bit of fragmentation, but more than that, tons of forwarding pointers (when a row won't fit on the same heap page, it gets moved to a different page, but the pointer to the row does not change in the indexes.) Rebuilding the heap is now really simple:

create table heapDemo
(
value varchar(1000)
)
GO
set nocount on
insert into heapDemo
select 'hi'
go 10000
--Expand the values to 500 times the size they were
update heapDemo
set value = replicate('hi',500)

Now, check the stats of the table (using the index stats dmv, no less)

select index_type_desc, fragment_count, page_count, forwarded_record_count
from sys.dm_db_index_physical_stats(db_id(),default,default,default,'DETAILED')
where object_id = object_id('heapDemo')

This returns:

index_type_desc fragment_count page_count forwarded_record_count
--------------------- -------------------- -------------------- ----------------------
HEAP 6 1443 9961

Now, you can rebuild the heap with the command:

alter table heapDemo rebuild

Check the values now:

select index_type_desc, fragment_count, page_count, forwarded_record_count
from sys.dm_db_index_physical_stats(db_id(),default,default,default,'DETAILED')
where object_id = object_id('heapDemo')

This shows no that the forwarded_record_count is 0, which is the idea situation for your heap.

index_type_desc fragment_count page_count forwarded_record_count
--------------------- -------------------- -------------------- ----------------------
HEAP 3 1440 0

Nice new addition!

Changing the owner of a database

drsql — Wed, 05 Dec 2007 05:06:46 GMT

Tonight, as I was creating my sample database for my chapter on implementing the database, I learned something new, that existed in 2005. I had always used sp_changedbowner to change the owner of a database, but I was reading in another section about ALTER AUTHORIZATION earlier to change the owner of an object in a schema, so I said to my self "Louis" (I call myself that) "Louis, wonder if ALTER AUTHORIZATION works on other stuff?"

Bizarre and surreal conversations with myself aside, the answer was Yes, you sure could. The syntax is:

ALTER AUTHORIZATION ON Database::<databaseName> TO <serverPrincipal>

To demonstrate, first, I will create a login:

create login test with password = 'like, password, dude'
go

Then a database just taking all of the defaults. Setting an owner is not a part of the CREATE DATABASE syntax anyhow:

create database showChangeOwner
go

Then, check the owner:

select SUSER_SNAME(owner_sid)
from sys.databases
where name = 'showChangeOwner'

This will return the login that you resolve to when you created the database.

MYDOMAIN\LBDAVI

Next, run the following statement to change the owner:

ALTER AUTHORIZATION ON Database::showChangeOwner TO test

Then run the previous statement to see that the database is now owned by login: Test .

As an aside, if you use EXECUTE AS to change context to a different login, any database you create will be created as the user you are EXECUTING AS, so this could technically be the way to set the owner at creation time. I will give the test login rights to create a database then execute as it. (My new favorite permission is "create any database". I looked, but despite the connotation, there is not a "create almost any database", "create just this one database", or even "create database named fred" rights.

grant create any database to test
execute as login = 'test'
create database showChangeOwner2

Checking the owner again:

select SUSER_SNAME(owner_sid)
from sys.databases
where name = 'showChangeOwner2'

Will show that this is owned by user "test". ALTER AUTHORIZATION will actually let you change the owner of quite a few different things. For more information, please visit your public library...wait, no this is 2007, not 1970... Check here in books online.

Don't forget to clean up!

drop database showChangeOwner, showChangeOwner2

(did you know you could do that? Saved me having to type drop database two times. Wow, the time I saved by not typing drop database. Wait, I typed drop database even more times after that. And yes, this obvious lack of material is why I haven't been blogging of late...)

PASS Followup, Technical Edition

drsql — Tue, 25 Sep 2007 05:03:00 GMT

I have already given a rundown of most everything PASS here on my personal blog, including a series of posts with pictures too over the days of PASS) but since this blog is all about things technical with SQL Server, I wanted to just rundown of some of the cool stuff I learned about SQL Server 2008 that I hadn't heard before...

You can index a sql_variant (script in this post)- possibly the least important piece of information is the most interesting. A person asked Dejan Sarka in his session if you could index them, and no one really expected that you could. Turns out, you can....
Spatial Datatypes are going to rock! - Really cool stuff. (good high level rundown here on the Virtual Earth team blog). They will support a flat earth model (like you use on a road map) and a round earth (well, ellipsoidal really) model as well, for more precise modeling of large spaces. Initially I thought this was going to be a fringe datatype and while it still might be one day in the future, I saw a lot of ideas I could use this for, even in an OLTP database.

For example, if you have the longitude and latitude of the address of a customer/constituent, it is going to be easy to join that point to the shape that represents a time zone, a country, the zip code, a voting district, etc, etc. Or you could also do it at a zip code level, and join the shape of a zip code to the the other shapes it intersects with. Of course, just like XML, these values will not always be exactly relational in nature (though you can make a case these types of datatypes represent a scalar value (a shape) while internally they might be made up of tons of points.
Intellisense is a coming - Yes, without an add-in, SQL Server 2008 tools will have intellisense. Can't wait to see just how this works out for them. Red Gate's version is ok, but it can be kind of clumsy to work with. Might even still be useful to have both...who knows. The folks at Red-Gate are smart cookies.
Analysis Services 2008 will be evolutionary, not revolutionary - This is a good thing really. The change from 2000 to 2005 was big, huge, major. The problem was, it was so huge that it was hard to figure out all of the bits and pieces. Part of this was the tools were version one, and 2008 will take the tools to the next level. The biggest neat stuff in there surrounded having the engine able to give warnings about how your cubes are designed. Blue squiggly lines (Donald Farmer claimed this to be a new technical term: "squiggly" in his session on AS 2008) will show you warnings right in the UI, and red ones show errors. Warnings can be ignored, errors not so much. Apparently you can use the new tools to modify 2005 cubes too, though you won't be able to use any new functionality that does get added to 2008.
T-SQL is making small amounts of progress forward - As a person who basically makes his living on relational database design, and T-SQL, I am a bit disappointed by how little T-SQL is changing. I am particularly desirous of lots of usability tweaks being done to T-SQL (like why do something about delayed name resolution on procedures or especially CREATE OR ALTER syntax for creating objects). To me, the biggest wins for DBA could be done at what should be the least cost to the dev team. Both of the ideas stated would only matter to the compiler, right? So nothing in the engine that executes queries need to change. Just the compiler, and with delayed name resolution, we already have both situations implemented (functions do not allow delayed name resolution) so it should be mostly cosmetic.

This however is only my perception, and if they came back and said on the connect site that it was too hard because, well, I would never disagree with them, since all I can program in is T-SQL. And it isn't that they are completely ignoring T-SQl, there are a few interesting enhancements, such as the one's Jamie points out here involving variable initialization, and row constructors to allow > 1 row in an insert statement. Check this post by Greg Duncan for a bit more coolness on that subject.
Change data capture - This is going to be a very interesting addition to the platform. It will allow you to get information about what rows have been changed in your database (each and every time the row changes), much like we have done with triggers for years (they claim a great performance improvement over triggers (sounds like the race is on!)). This will allow very finely grained ETL in a nice package. (See post here for better overview) The downside of course, is that CDC is only going to be available in Enterprise Edition. For all other versions, there will be a feature called Change Tracking that will keep up with what keys of rows that have been modified (even deleted). This will give users of other editions a pretty good tool for seeing rows that have changed.

I am probably the most excited about this feature, since it will allow ETL to be created in a natural way without building triggers or relying on date and time stamp values that you need to maintain.

This is certainly not an exhaustive list (I expect that if you are reading this blog, you have seen the new date types, if not try this post. I saw a pretty exhaustive list in a private session. Lots of really cool fun stuff is coming in the upcoming CTP's leading up to release sometime in 2008 (no scoop there, huh?)

Edit: Forgot about change data capture

sys.dm_db_index_operational_stats

drsql — Sun, 26 Aug 2007 20:12:00 GMT

This object provides very useful stats on how many times an index has been used, locked, waited on, etc. An index in this context can mean several things: a clustered index, heap, index, or a partition of either of these. The grain of the function is down to the partition level, so if you are working with a table that is partitioned into five parts, it will return 5 rows (In the previous section, sys.dm_db_index_usage_stats would have seen the object as only a single row)

This object will give you a deep feel for how indexes are being used, and just exactly how much the index is costing you. It does this in part by telling you how often the index is modified at the Leaf level or non-leaf level, as well as how often users waited on blocks associated with the object, which can mean SQL Server locks, or even hardware or I/O latches.

Type: function

Parameters: database_id , object_id, index_id, partition_number (each parameter can be NULL or DEFAULT if you want to return all rows)

Data: accumulating refreshed when server is restarted or (perhaps obviously) when the index is dropped and recreated. Statistics live on when the index is rebuild, reorganized, and even when it is disabled and rebuilt.

Columns:

database_id, object_id, index_id -identifies the index in question
partition_number - 1 for non-partitioned tables. For partitioned tables will be the 1 – based number of the partition.
leaf_insert_count – the number of times a new row was added to the leaf of the index
leaf_delete_count – the number of times a row was deleted from the leaf
leaf_update_count – the number of changes to index keys for this index
leaf_ghost_count – the number of ghost records that have been marked as deleted but not cleaned up by SQL Server (http://www.sql-server-performance.com/tips/glossary_p1.aspx)
nonleaf_insert_count, nonleaf_delete_count, nonleaf_update_count – Same as the leaf page values, except for the b-tree index pages
leaf_allocation_count, nonleaf_allocation_count – The number of page allocated to the leaf and non-leaf pages of the index, respectively.
leaf_page_merge_count, nonleaf_page_merge_count – The number of pages in the index that have been merged into a single page.
range_scan_count - number of times the index has been used in a range scan operator
singleton_lookup_count – number of times the index has been used to fetch a single row
forwarded_fetch_count – for a heap, shows the number of forward pointers have been used to resolve a query (http://sqlblog.com/blogs/hugo_kornelis/archive/2006/11/03/The-table-scan-from-hell.aspx)
lob_fetch_in_pages, lob_fetch_in_bytes – Quantifies the number of large object (varchar(max), varbinary(max), text, etc) have been retrieved using this index
lob_orphan_create_count, lob_orphan_insert_count – total number of large object pages orphaned by bulk operations
row_overflow_fetch_in_pages , row_overflow_fetch_in_bytes – Quantifies the amount of data retrieved from row overflow pages for rows greater than one page in size
column_value_push_off_row_count, column_value_pull_in_row_count – Number of pages of large object or row overflow pages that have been moved by an in-row data operation
row_lock_count – number of row locks that have been requested against this index
row_lock_wait_count – number of times a process has waited on a row lock against this index
row_lock_wait_in_ms – amount of time spent waiting on a row lock against this index
page_lock_count, page_lock_wait_count, page_lock_wait_in_ms – same as row_lock values at the page grain
index_lock_promotion_attempt_count, index_lock_promotion_count – number of times the lock grain for an operation using this index was attempted or granted to be escalated (like from row to page)
page_latch_wait_count , page_latch_wait_in_ms – number of waits and time waited on the physical page of the object to have the latch removed
page_io_latch_wait_count, page_io_latch_wait_in_ms - number of waits and time waiting on and I/O operation on a physical page of the object to be completed
This object is related to the sys.dm_db_index_usage_stats in that they both provide information about how the index is used. This object, however, gives more detailed information about the inner workings of how the index is used at a physical level, whereas the usage stats is more of a feel for how it is used by the optimizer to satisfy the needs of certain queries. Use usage stats if you want counts of each usage, as each usage counts as 1. The operational stats object may have multiple values set for each type of activity recorded. (http://blogs.msdn.com/sqlcat/archive/2005/12/12/502735.aspx)

Examples:

Tables where the most latch contention is occurring

select object_schema_name(ddios.object_id) + '.' + object_name(ddios.object_id) as objectName,
          indexes.name, case when is_unique = 1 then 'UNIQUE ' else '' end + indexes.type_desc as index_type,
          page_latch_wait_count , page_io_latch_wait_count
from sys.dm_db_index_operational_stats(db_id(),null,null,null) as ddios
             join sys.indexes
                        on indexes.object_id = ddios.object_id
                             and indexes.index_id = ddios.index_id
order by page_latch_wait_count + page_io_latch_wait_count desc

Note: This is part of an ongoing project to write a book about all of the dynamic management views for Red-Gate. It will be freely distributable once complete as an ebook. Any examples, suggestions, corrections are desired and will certainly be mentioned in the final book. Also, I will begin maintaining the following web page once the book is closer to completion: http://drsql.org/dmvbook.aspx.

sys.dm_exec_query_optimizer_info

drsql — Tue, 07 Aug 2007 00:40:55 GMT

Information about how queries have been optimized since the server has been restarted. Note that counters are only affected when there is some sort of optimization event, not on every query. (reference: http://sqlserver-qa.net/blogs/perftune/archive/2007/05/11/get-statistics-on-query-plan-optimizer-execution.aspx )

Type: View

Data: Accumulating reset at restart

Columns:

counter - Name of the query
occurrence – Number of times the counter was recorded to
value – May or may not have some value, but is typically an average of the values that were recorded when the counter was written to (for a complete list of counters, check the books online documentation on sys.dm_exec_query_optimizer_info (1))

Example:

As an example, on a low use server, I restarted the server and executed:

select counter,occurrence, value
from sys.dm_exec_query_optimizer_info

You will get values like:

counter           occurrence    value
----------------- ------------- --------------------
optimizations     1             1
elapsed time      1             0.0235776097702821
final cost        1             4.1957E-05

This tells you the number of statements that have been optimized and a plan created (the same value is for all three of the displayed counters), the average elapsed time spent compiling in the value column for the elapsed time row, and finally, the average final cost in the final cost row. Using these counters and some of the others you will discover will help you to determine how often statements ae being compiled.

Execute it again, it will give you the same values (assuming you are the only user, of course.) Now, execute the same query, this time making it uppercase, forcing another complication, since plans must match in every way:

SELECT COUNTER, OCCURRENCE, VALUE
FROM SYS.DM_EXEC_QUERY_OPTIMIZER_INFO

You will see that the occurrences have increased, and there may be some difference in the average time it took:

counter           occurrence    value
----------------- ------------- --------------------
optimizations     2             1
elapsed time      2             0.020978588737036
final cost        1             4.1957E-05

---------------------------------------