[Wat] DECLAREing inside a loop? Yes, yes you can.

Came across this code today.  Sticking it in my personal 'Wat?' file (https://www.destroyallsoftware.com/talks/wat/)

Lump this in with the "hey, did you know you can DECLARE and set to a value without being inside a SP"?

This code works on 2008 and higher.


DECLARE @i INT = 1

WHILE @i < 5
BEGIN
DECLARE @a INT = 4
PRINT @i
SET @i = @i+1
END 

Extended Events - climbing the learning curve

I recently spent an evening climbing the learning curve on XE (Extended Events), not the least of which because it acted slightly differently between SQL Server 2008 and SQL Server 2012.  I'd initially got it working, but couldn't reproduce that success for another couple of hours because I was running the code on 2008 (which lacks certain events that 2012 has - doh!).


Here's what I've learned, mostly here as a basic HOWTO, but also to remind me later.


1) It's actually pretty easy!  
2) It's the future - Profiler as we know it is going away (but you have a couple of years).  This is the replacement.
3) Jonathan Kehayias is awesome. He's gone through a bunch of pain - use his lessons learned.
4) SSMS 2012 offers it natively.  There's a plugin for SSMS 2008/R2 (Extended Event Session Explorer) that adds an option under the "View" menu.  Yes it works, but use it to assist what you're doing, don't just be the "GUI guy".
5) BOL is pretty good, but some of the obvious pages are hidden.  http://msdn.microsoft.com/en-us/library/bb630284(v=sql.105).aspx is a good example. (Somehow hadn't seen it before today)
6) Once you go through an example, all the giant blocks of code on the web make perfect sense.  That alone is a good reason to go through this exercise.


Here's an example I'm using now  You can run just this first block of code, pop open a new window, run a command, come back and see what happens.  Go on do it, I'll wait.  This works in 2008 and 2012.


CREATE EVENT SESSION [web_users_XE] ON SERVER
ADD EVENT sqlserver.rpc_completed
(
    ACTION(sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[username]<>'web_users')
)
ADD TARGET package0.asynchronous_file_target(SET filename=N'C:\web_users_XE.xel'
, metadatafile='c:\web_users_XE.xem')
WITH (STARTUP_STATE=OFF)
GO
ALTER EVENT SESSION [web_users_XE] ON SERVER STATE = START
go
--at this point open a new window and run a command or two, hit an SP if you can...
sp_help
go
waitfor delay '00:00:30'
go
ALTER EVENT SESSION [web_users_XE] ON SERVER STATE = STOP
go
DROP EVENT SESSION [web_users_XE] ON SERVER;

--2008


So what's that mean and do?  Let's cover one part at a time.


CREATE EVENT SESSION [web_users_XE] ON SERVER 

pretty explanatory.  mandatory to create it. this creates the actual session.


ADD EVENT sqlserver.rpc_completed(
Now let's see: there are sessions, events, actions, and targets.  The SESSION is like the full sql trace.  The EVENTs are just like Trace Events (RPC:Completed, etc). There's even a table in 2012 that gives you a "this in trace is this in XE".  This is RPC:Completed.


    ACTION(sqlserver.sql_text,sqlserver.username)

what are we going to save? ACTIONS are "columns" in the trace (textdata, etc).  So here we savehe SQL_Text and the Username of the user running the query.  Note that this is for this particular event.  Which means you can do stuff like "event A you save these fields, for event B you save these other fields", etc, etc.


    WHERE ([sqlserver].[username]='web_users')
our filter - for this EVENT, only save from user "web_users".  As with the ACTION, it's per event.


ADD TARGET package0.event_file(SET filename=N'C:\SQL_Log\web_users_XE.xel')
OR

ADD TARGET package0.asynchronous_file_target(SET filename=N'C:\web_users_XE.xel'
, metadatafile='c:\web_users_XE.xem')


targets are "where the data goes".
Wait, why are there 2 versions?  The one in the block of code runs in both 2008/2012.  But the new name is "event_file".  Also, in 2008 you needed a metadata file to be able to parse it; they got rid of that in 2012.  It WILL NOT CREATE ONE, even if specified.  Nor do you need it to parse.
Targets: The 3 most-common are ring, file, and histogram.  
* File is a file. It's XML so it needs to be parsed, but easy enough.
* Ring is a first-in-first-out set of memory.  As you save to it, older things get kicked out.
* Histogram is a series of buckets, grouped by whatever you choose.  Why is that useful?  Kehayias does a clever example with object_id and page splits.  Everytime the split occurs, it either adds or increments a bucket with the object_id and count.  So rather than having to parse a list of events and group to see which objects are used, it's already done by the histogram - just see what buckets have the highest count, and that gives you the object ID.


WITH (STARTUP_STATE=OFF)
should this automatically start when the server starts?


ALTER EVENT SESSION [web_user_XE] ON SERVER STATE = START
actually start the session.

sp_help
Run something so we have an event.


waitfor delay '00:00:30'
There can be a 30 second delay before things are written to the file (to be lower-impact, it waits until a buffer fills).  There's a setting for this, but the default is 30 seconds.


ALTER EVENT SESSION [web_user_XE] ON SERVER STATE = STOP

stop the session.  It still exists, it's just not running.

DROP EVENT SESSION [web_users_XE] ON SERVER;

drop the session entirely.

Now, how do we parse it?  Parts cribbed from Kehayias again, notably getting the sql_text.

SELECT 

event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,

DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 

event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],

event_data.value('(event/data[@name="cpu"]/value)[1]', 'int') AS [cpu],

        event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS [duration],

        event_data.value('(event/data[@name="reads"]/value)[1]', 'bigint') AS [reads],

        event_data.value('(event/data[@name="writes"]/value)[1]', 'bigint') AS [writes],

event_data.value('(event/action[@name="username"]/value)[1]', 'varchar(50)') AS username,

event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'varchar(50)') AS application_name,

event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)') AS attach_activity_id,

        REPLACE(event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)'), CHAR(10), CHAR(13)+CHAR(10)) AS [sql_text],

        event_data

FROM 

 (

 SELECT CAST(event_data AS xml)  AS 'event_data'

FROM sys.fn_xe_file_target_read_file('c:\sql_log\web_users*.xel', 'c:\sql_log\web_users*.xem', NULL, NULL)

)a ORDER BY DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 

event_data.value('(event/@timestamp)[1]', 'datetime2')) 

Since it's XML, you need to parse it.  We convert it to XML from binary data, then use "value" to extract info.  One change between 2012 and 2008 is fn_xe_file_target_read_file.  On 2008 you need the metadatafile location.  On 2012 you don't need it, but it won't complain if it's there.

THAT'S IT!

Man, didn't that look more difficult?

[tuning] Statistics - when were they created... OR RECENTLY UPDATED?

(update 2013/08/07 - since it shows recent times...)

Courtesy of Nayan Raval & #SQLHelp, which led me to another article (http://blogs.solidq.com/fabianosqlserver/post.aspx?id=52&title=undocumented+option(querytraceon+%3Ctracenumber%3E)+and+trace+flags+2388%2C+2389%2C+2390) which documents 2 more undocumented trace flags.

How do you find out when statistics were created?  If it's on an indexed field, when the index was created (crdate in sys.indexes).  But statistics on non-indexed fields?  Using Trace Flag 2388 changes the information that SHOW_STATISTICS returns.


DBCC TRACEON (2388)
DBCC SHOW_STATISTICS ('yourtablename','_WA_Sys_x')
DBCC TRACEOFF (2388)

Look for the row with the oldest Updated. The oldest update, depending on how often it's been updated, MAY show you when it was created.

And there's another use for this trace flag... when, aside from the most-recent date, was a statistic updated?   STATS_DATE will show you the LAST time it was updated, but I recently had a problem where I knew the problem existed and quickly updated stats, then realized I wanted to know when BEFORE then it had been updated.  One search to my blog later, code found, and there we go.

[Partitioned Tables] Does the clustered index take up space if referenced?

Setting up a new partitioned table with associated indexes. I was curious whether the adding the partitioning key to any index would cause the index to grow - I expected not, but you never know.

Our clustered index:

create unique clustered index clustind_pk on ourtable (id, partitionedkey)

Our test indexes:

create nonclustered index A on ourtable (partitionedkey, fielda)
on ps_daily (partitionedkey)

create nonclustered index B on ourtable (fielda)
on ps_daily (partitionedkey)

create nonclustered index C on ourtable (fielda) include (partitionedkey)
on ps_daily (partitionedkey)

Then used this query to check a particular partition for all 3 indexes (thanks to Simon Sabin). Each was within 1 page of the others.

select OBJECT_NAME(p.object_id ), i.name,p.*
from sys.dm_db_partition_stats p
join sys.indexes i on i.object_id = p.object_id and i.index_id = p.index_id
WHERE p.object_id = 2071234567
AND partition_number = 28 

[Tuning] SPARSE varchar calculation

Since it doesn't appear that anybody has done this before, here you go. I've been comparing SPARSE to COMPRESSION, and for my particular tables, I got 25% space savings via parse. However, I got 40% savings from ROW compression, and 50% savings from PAGE.

Standard
=(Number_Of_Rows*(Average_Varchar_Length+2)
*((100-Percent_Null)/100))
+(Number_Of_Rows*(Percent_Null/100*2))

Sparse:
=(Number_Of_Rows*(Average_Varchar_Length+4))*((100-Percent_Null)/100)

Next up is comparing the CPU for each option. Nobody's really talked about whether the compression is symmetric or asymmetric, though I'd hope it's asymmetric (aka easier to decompress than compress, in this case)

[Compression] Seeing how much savings you'll get

If you meet all sorts of criteria (SQL Server 2008, Enterprise Edition) then you can use row or page level compression on your tables. But, you ask: will it make a difference in space used?

Fortunately, and a bit surprisingly, Microsoft came up with a way to do so.
sp_estimate_data_compression_savings

Sample usage:

sp_estimate_data_compression_savings 
       @schema_name =  'dbo'  
     , @object_name =  '20090820__abc' 
     , @index_id = NULL             --NULL does all.
     , @partition_number = null     --if you use partitioned tables
     , @data_compression = 'PAGE'   --can also use ROW or NONE
go

It looks like it takes roughly 40mb of data, copies it to TEMPDB, and compresses that. It then returns the results, including comparing the size to what the current compression is.

For us, mixed results. One set of EDI data, which uses certain characters to split out values, gets roughly 25% compression. A different set of EDI data that uses XML (with huge swathes of repeating data) get a whopping 1% savings.

I love the idea. I want to use it everywhere - since most systems are IO bound (not CPU bound) it seems a home run. But the requirement of Enterprise Edition lessens its usefulness by a _lot_.