End of the conference season 2008. At least for me, my friends in Belgium are looking forward to their SQL Server Day, coming up soon.

I was able to catch up with a lot of friends at TechEd EMEA ITForum, TechEd EMEA for Developers, and SQLPASS, including some folks I hadn't seen in years. Just wanted to reiterate how good it was to see you, how much fun it was to hang out. I heard about of a lot of exciting new upcoming technologies and was able to discuss them with the industry's experts one-on-one.

My contribution was some hopefully useful information that made it easier to understand the zen of the new SQL Server 2008 features, the motivation for migration, and a lot of cool tools for your planning, deploying, and troubleshooting toolbox. All the sample code that I can give away is posted on the SQLskills website. The folks at the SQLPASS preconference even received DVDs. I hope you had as good a time as I did.

I've been doing talks and demos on SQL Server 2008 Extended Events for a while now, it's one of my favorite parts of the product. Per session waitstats, SQL stack, built-in system health session, what's not to like?

Before my talk at TechEd EMEA for IT Professionals last week (which went really well), I discovered something that was very helpful in getting things to run more smoothly. Since the betas, I've always used multi-part names, not only for events, actions, and providers, but for predicates too. To be clearer. Lately, I'd been running into a weird problem. Consider the following event session:

create event session errorsession on server
add event sqlserver.error_reported
(
action
(   
sqlserver.session_id, 
sqlserver.sql_text    
)
where sqlserver.error_reported.error = 547
and package0.counter <= 3 
)
add target package0.ring_buffer
go

About one third of the time, running this DDL would produce:

Msg 25706, Level 16, State 8, Line 1
The event attribute or predicate source, "sqlserver.error_reported.error", could not be found.

Then, I'd move the DDL code to another window, execute it again, and it would work. Sometimes starting up the session:

alter event session errorsession on server state=start

would produce the error. And, of course that attribute DID exist, when I got past the weird error, it resultiing session worked like a charm. You can see the attribute/field in sys.dm_xe_object_columns. Hmmmm, scratches head...

After looking at the definition of the system_health session, I decided to try a one-part attribute name. Instead of:

...where sqlserver.error_reported.error = 547 and...
-- how about
...where error = 547 and...

Bingo! Now CREATE and ALTER SESSION work first time and every time. Don't know why this should happen, but at least there's a syntax correction that works. For the folks at the talk, all my event session code will be up on the SQLskills website shortly, under the "Past Conferences" section. With ONE-PART attribute names in predicates.

BTW, a few other things to remember to save you some head-scratching.

When using the ETW target, your SQL Server service account (which DOES have least-privileges...right?) needs to be a member of the Performance Monitor Users and Performance Log Users Windows groups.

When using a file-based target, the service account has to have permissions on the directory where you're writing the file. This sounds obvious, but folks forget that the SQL Server service account isn't all-powerful. Or at least *shouldn't be*, if you're running SQL Server as local system or administrator, FIX IT, using SQL Server Configuration Manager (not Control Panel/Services).

Use an asynchronous target with default event latency rather than a synchronous target, for less overhead, better throughput. However the ETW target is synchronous only.

All of the events, targets, etc are sharable, mix-n-match, across all packages WITH ONE EXCEPTION. The items in the SecAudit package are private. Folks always want to use SecAudit.asynchronous_security_audit_event_log_target, ... you can't. It's for the new Auditing feature only.

All that said, Happy extended eventing!

I remember hearing during the SQL Server 2008 beta that there would be diagnostic stored procedures that would produce information about spatial indexes and help in troubleshooting why an index was not being used. In addition, these procedures would help in determining the best spatial index. A few weeks ago I had the good fortune to run across these procedures.

sp_help_spatial_geometry_index and sp_help_spatial_geography_index put out information as columns
sp_help_spatial_geometry_index_xml and sp_help_spatial_geography_index_xml put out XML format

Each procedure takes a table name, index name, and sample query. There's an option for verbose or basic properties. The parameters and output are doc'd in BOL. Rather than repeat the doc, I'd like to walk through a problem and show how you'd interpret the output.

Say that you are trying to decide between an index with high and medium density to run a query similar to the following:

Declare @geometry geometry =
 Geometry::STGeomFromText('POLYGON((500000 300000,500000 340000,540000 340000,540000 300000,500000 300000))', 0)
exec sp_executesql N'select * from spatial_table where LOCATION.STIntersects(@g) = 1', N'@g geometry', @geometry

Create both indexes and run the procedure against each one, using the sample query. There is a lot of very detailed information about the index itself because I was running in verbose mode, including information about the number of cells at each level of tesselation. Before looking at this information, it would be good to review Isaac's excellent blog post on how tesselation works. The most useful info though, was contained in the "Number of rows selected by ... filter" properties and filter efficiency properties. In my case there they were

High density index:
Number_Of_Rows_Selected_By_Primary_Filter:  2931
Number_Of_Rows_Selected_By_Internal_Filter: 2399
Number_Of_Times_Secondary_Filter_Is_Called:  532

Medium density index:

Number_Of_Rows_Selected_By_Primary_Filter:  2931
Number_Of_Rows_Selected_By_Internal_Filter: 2666
Number_Of_Times_Secondary_Filter_Is_Called:  265

Note that "primary filter" in this case is the spatial index in question, the goal is to have the rows selected by the primary or internal filter rather than run the secondary filter (the actual Intersects operation). Note that there can be false positives, the actual number of rows returned was not 2931 but 2779.

The best way to judge the effectiveness of the index, however, is with the property Internal_Filter_Efficiency or Primary_Filter_Efficiency.  Internal_Filter_Efficiency, for example, showed the high density index at 86% and the medium denisty index at 96%. Clearly, the medium density index is a better choice for this query. In addition, looking at the statistics on the index itself shows that the medium density index is a smaller index with less rows and index row per base row. The figures were:

High density:
Total_Primary_Index_Rows: 5273534 rows
Average_Number_Of_Index_Rows_Per_Base_Row : 10

Medium density:
Total_Primary_Index_Rows: 1965865 rows
Average_Number_Of_Index_Rows_Per_Base_Row : 3

There is also a property, Total_Number_Of_ObjectCells_In_Level0_For_QuerySample, that will tell you if the index *can* be used. A non-zero value indicates this and properties that will assist in determining whether to adjust the bounding box on a geometry index.

Hope this will give you a good head start in working with these excellent informational and diagnostic stored procedures.