Neil Hambly

Following on my from posting on Cardinality earlier this month, As I will likely want a way to quickly evaluate the stats history for a database

http://sqlblogcasts.com/blogs/neilhambly/archive/2012/01/14/show-me-some-cardinality.aspx

I can of course automate the processs with something like the following script - which can be run against a database to provide a summary of the statistics for a given database

Have a Happy Stats day

SET NOCOUNT ON

CREATE TABLE #STATS

(

 TABLE_CATALOG             VARCHAR(128)

,TABLE_SCHEMA        VARCHAR(128)

,TABLE_NAME                VARCHAR(128)

,STATISTICS_CATALOG  VARCHAR(128)

,STATISTICS_SCHEMA   VARCHAR(128)

,STATISTICS_NAME     VARCHAR(128)

,STATISTICS_TYPE     INT

,COLUMN_NAME         VARCHAR(128)

,COLUMN_GUID         VARCHAR(128)

,COLUMN_PROPID             VARCHAR(128)

,ORDINAL_POSITION    INT

,SAMPLE_PCT                INT

,LAST_UPDATE_TIME    DATETIME

,NO_OF_RANGES        INT

,COLUMN_CARDINALITY  INT

,TUPLE_CARDINALITY   INT

,TABLE_CARDINALITY   INT

,AVG_COLUMN_LENGTH   INT

)

GO

DECLARE @DBName VARCHAR(64), @SchemaName VARCHAR(64), @TableName VARCHAR(64), @IndexName VARCHAR(64)

DECLARE curStats CURSOR FAST_FORWARD READ_ONLY FOR

       SELECT

              QUOTENAME(DB_NAME()) AS DBName,

              QUOTENAME(Schema_name(SO.schema_id)) AS SchemaName,

              QUOTENAME(OBJECT_NAME(SO.OBJECT_ID)) AS TableName,

              SI.name AS IndexName

       FROM sys.indexes SI

       INNER JOIN sys.objects SO ON SI.object_id = SO.object_id

       WHERE SI.name IS NOT NULL

       AND Schema_name(SO.schema_id) NOT LIKE 'sys'

       AND SO.type = 'U'

OPEN curStats

FETCH NEXT FROM curStats INTO @DBName, @SchemaName, @TableName, @IndexName

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @SQLCmd NVARCHAR(2000)

       SELECT @SQLCmd =

       'INSERT #STATS EXEC (' + CHAR(39) + 'DBCC SHOW_STATISTICS ('

       + CHAR(39) + CHAR(39) + @DBname + '.' + @SchemaName + '.' + @TableName

       + CHAR(39) + CHAR(39) + ',' + CHAR(39) + CHAR(39) + @IndexName + CHAR(39)

       + CHAR(39) + ') WITH STAT_HEADER JOIN DENSITY_VECTOR' + CHAR(39) + ');'

       --SELECT @SQLCmd

       EXEC (@SQLCmd)

FETCH NEXT FROM curStats INTO @DBName, @SchemaName, @TableName, @IndexName

END

CLOSE curStats

DEALLOCATE curStats

GO

SELECT *

FROM #STATS

WHERE SAMPLE_PCT <> 100 OR LAST_UPDATE_TIME < GETDATE()-1

ORDER BY TABLE_SCHEMA ASC, TABLE_NAME ASC, STATISTICS_NAME ASC, ORDINAL_POSITION ASC

SET NOCOUNT OFF

I know I'm a Geek @ heart and I do get that "Ooooh" moment when I find something different in a release (and I haven't just read it in a blog, book or another source)

One such moment occured last week, I'm in a Study group fro the MCM on Yammer  ( https://www.yammer.com/sqlmcmstudygroup )

One of our Members:  Matin Carins {who is a newly minted MCM @ that} Posted a question that got a lot of responses, I thankfully even managed to answer the question correctly  , it was to do with unshrinkable Trans logs .. answer is available in the thread if your intrested

Anyway my post here is that we used fn_dblog to review the contents of the Trans log and I tried the example Script that Martin posted up and ran this on a SQL 2008R2 and also my 2012 RC0 builds

The results clearly showed some speed improvements when run on 2012 RC0   (but this wasn't the main highlight for me .. that was that output of the fn_dblog)

The screen shot below shows the excact same Query that was run on the 2 builds.. both created a new database and inserted some dummy data to do the backup & Trans log shrink tests

Results on the LEFT are for the 2012 RC0 build (11.0.1750) and the results on the RIGHT are for the 2008 R2 Build (10.50.2500)

Noticed we have a new Column RecoveryUnitID (I haven't explored this one yet.. but will do so)

and also the query that provide the breakdown of operations has many more results {again intresting to investigate @ a later datetime when I have some spare time)

And finally the 2012 RC0 has less used pages (216 vs 256)  and less active VLFs - so again this looks worthy of further investigations

Below are segments of the script that provided these results

DBCC SHRINKFILE(ActiveVLFDemo_Log,1,TRUNCATEONLY)

DBCC LOGINFO

SELECT Operation,COUNT(*) FROM fn_dblog(NULL,NULL)

GROUP BY Operation HAVING COUNT(*) > 1

ORDER BY COUNT(*) DESC 

In the word of data – Cardinality issues is one issue you will already have encountered or will do @ some stage, having statistical information up to date is vital for the Query optimizer choosing a good query plan

So @ times you may have to explore the Tuple Cardinality & Table Cardinality values

In the screen shot below the top set of values is after a Statistics update has been run, set below was the previous

UPDATE STATISTICS schemaname.tablename WITH FULLSCAN, INDEX

·          The TUPLE_CARDINALITY = TABLE_CARDINALITY for 1^st  row  {Note: This is a Clustered Primary Key index}

·          2^nd - 4^th records are for another non-clustered index which has 2 columns

o    2^nd & 3^rd rows represent these 2 columns (Notice the ORDINAL_POSITION)

o    4^th is the non-clustered internal CI key

·          TUPLE_CARDINALITY values had considerable different values before/after the statistics update

o    2^nd row has TUPLE_CARDINALITY = 33

§   Matches the number of distinct values for leading key column for nc index

o    3^rd rows has TUPLE_CARDINALITY = 1058

§   Matches the number of distinct values for both columns in the nc index

o    4^th rows, TUPLE_CARDINALITY  = 30496916

§   Previously was 229923 that’s just 0.75% of the table rows  ** That was my Red flag **

§   After update it now Matches the number of distinct values for CI key

So clearly something was not as accurate as it could be and the clustered & non-clustered indexes tuples seemed to be a long way from the values I expected them to be

If you like me - Knowing which Build you should have deployed when we look @ the myriad of Service Packs & Cumulative Updates possible, it can quickly become confusing

What actions should I do to "PATCH" a server to the appropriate build, So here is a quick script that can easily be updated as new builds are made available, and easily customized for your own environments

-- This Script only reports on your current version and recommends your course of action  - Usualy disclaimer of using script in your environments.. 

-- As per Aarons article - please ensure this is suitable for you environment

*** Updated script [ 1st March 2012 ] to add in SQL 2012 and CU4 | CU5 fro SQL Server 2008R2 Version ***

;With CTE_SQLEditions([Major],[Minor],[Build],[BuildMinor])

I'm starting on my promise of providing 1 (sometimes more) technical blog, each & every week during 2012

So with this being Week #1 of 2012, I thought it would be nice to start with a new system stored procedure in the SQL 2012 (CTP3 | RC0) builds

It is called nicely sp_server_diagnostics So any guesses on what this does ? 

Well here is the link to BOL on this http://msdn.microsoft.com/en-us/library/ff878233(v=sql.110).aspx and the tag line for it from BOL on its purpose

Captures diagnostic data and health information about SQL Server to detect potential failures. The procedure runs in repeat mode and sends results periodically. It can be invoked from either a regular or a DAC connection

Incidentally during my session @ SQLPASS Summit 2011 on Wait Statistics, I used this new SP, that is because one of the 5 data values returned provides an information snapshot

of the TOP 10 Waits by counter & duration for both pre-emptive & non-preemptive, this means we get 4 sets of 10 results on TOP waits- I wanted to track the most prevalent wait_types

Below is code I have put together to capture data @ 10 second intervals, and then tracking the variouis wait_types from snapshot to snapshot

USE tempdb

--DROP TABLE dbo.tmp_sp_server_diagnostics

CREATE TABLE dbo.tmp_sp_server_diagnostics

       (

       [create_time] datetime,

       [component_type] nvarchar(20),

       [component_name] nvarchar(20),

       [state] int,

       [state_desc] nvarchar(20),

       [data] xml

       )

/*

This doesn't work if we also supply the @Repeat_interval

*/

INSERT dbo.tmp_sp_server_diagnostics

EXEC sys.sp_server_diagnostics

  --    @Repeat_interval = 10  

Select * From tempdb.dbo.tmp_sp_server_diagnostics

/*

Note as we can't use the @Repeat_interval - if you wanted to capture & store data with can't simply use the INSERT method

So we have to collect the data from the output of sp_server_diagnostics SP via Extended Events

## Rememeber to Set your own filename Path in the following code ##

*/

CREATE EVENT SESSION [diag]

ON SERVER

       ADD EVENT [sp_server_diagnostics_component_result] (set collect_data=1)

    ADD TARGET package0.event_file

              (SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\diag.xel')

GO

ALTER EVENT SESSION [diag] ON SERVER STATE = start

GO

/*

So we now can run with the @Repeat_interval parameter supplied and the XE session will capture the information

*/

EXEC sys.sp_server_diagnostics

       @Repeat_interval = 10  

/*

We can now analyze the results of the runs completed

*/

--DROP TABLE TopWaits

CREATE TABLE TopWaits ([RowN] INT, [XMLData] xml);

;WITH CTE_SD ([Name],[Package],[Time],[Component],[State],[State_desc],[Data])

AS(        

SELECT

  xml_data.value('(/event/@name)[1]','varchar(max)')                                      AS [Name]

, xml_data.value('(/event/@package)[1]', 'varchar(max)')                                  AS [Package]

, xml_data.value('(/event/@timestamp)[1]', 'datetime')                                    AS [Time]

, xml_data.value('(/event/data[@name=''component'']/value)[1]','varchar(max)')            AS [Component]

, xml_data.value('(/event/data[@name=''state'']/value)[1]','int')                         AS [State]

, xml_data.value('(/event/data[@name=''state_desc'']/value)[1]','varchar(max)')           AS [State_desc]

, xml_data.query('(/event/data[@name="data"]/value/*)')                                   AS [Data]

FROM

       (

    SELECT  OBJECT_NAME as event,CONVERT(xml, event_data) as xml_data

    FROM  sys.fn_xe_file_target_read_file

              ('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\diag*.xel', NULL, NULL, NULL)

    ) AS XEventData

)

,[ParsedData] ([RowN],[Data])

AS

(

SELECT

ROW_NUMBER() OVER(PARTITION BY [Component] ORDER BY [Time] DESC) AS [RowN],

CAST([Data] AS XML) AS [Data]

FROM CTE_SD

WHERE [Component] = 2 -- <queryProcessing ... >

)

INSERT INTO TopWaits([RowN],[XMLData])

Select [RowN], [Data]

from ParsedData

Select

        Prev_interval.RowN

    ,Prev_interval.Wait_Type

    ,Prev_interval.WaitsCnt - Next_interval.WaitsCnt AS WaitsCnt

    ,Prev_interval.AvgWT - Next_interval.AvgWT       AS AvgWT

    ,Prev_interval.MaxWT - Next_interval.MaxWT       AS MaxWT

FROM

(

SELECT

       RowN,

    c.p.value('@waitType', 'varchar(50)')         [Wait_Type],

       c.p.value('@waits', 'int')                [WaitsCnt],

    c.p.value('@averageWaitTime', 'int')      [AvgWT],

    c.p.value('@maxWaitTime', 'int')          [MaxWT]

FROM TopWaits

CROSS APPLY XMLData.nodes('/queryProcessing/topWaits/nonPreemptive/byCount/*') c(p)

) AS Prev_interval ([RowN],[Wait_Type],[WaitsCnt],[AvgWT],[MaxWT])

INNER JOIN

(

SELECT

       RowN,

    c.p.value('@waitType', 'varchar(50)')         [Wait_Type],

    c.p.value('@waits', 'int')                [WaitsCnt],

    c.p.value('@averageWaitTime', 'int')      [AvgWT],

    c.p.value('@maxWaitTime', 'int')          [MaxWT]

FROM TopWaits

CROSS APPLY XMLData.nodes('/queryProcessing/topWaits/nonPreemptive/byCount/*') c(p)

) AS Next_interval ([RowN],[Wait_Type],[WaitsCnt],[AvgWT],[MaxWT])

ON Prev_interval.[Wait_Type] = Next_interval.[Wait_Type]

and Prev_interval.RowN +1 = Next_interval.RowN

So having completed my review of my 2011 goals, I have put together this years 2012 goals below

SQL Conferences & Training Events

• SQL Master Week # 2 (May 2012) Master Immersion Event on Performance
• SQLPass Summit (will attend my 2^nd Summit - Hopefully once again as a speaker)
• SQLRally (Dallas) 2012
• SQLRally Europe (if any event is planned)
• SQLSaturday #105 (Dublin) I will be Presenting @ this event
• SQLSaturday hopefully another 1-2 events (Chicago + ????)
• SQL BITS X / XI Events – hoping I will be able to present & attend @ these events
• SQLDay 2012 My 1^st Event in Poland (Straight after SQLBits X)
• Present a 2-day Training Course (London)

SQL Conferences & Training Events (All done - Nice)

o   Done , it was an amazing time attending the SQL Cruise and learnt a lot about SQL and other stuff, not to mention an amazing Alaska  adventure

o   Done, I attended the week long course and it was undoubtedly the best training course I have ever attended, I’m still learning  from it

o   Done, I not only attended the conference but was also Privileged enough to be a presenter with my “Why are we Waiting...” Session, which incidentally had the 9^th highest attendance of 310 attendees @ the conference with over 160 sessions

o   Done, I attended both of the Events and was also again fortunate enough to be selected as a presenter with my Part I & II of my “Why are we Waiting…” sessions

SQLFAQ Events (All done - Was a lot of fun doing these events)

·         SQL FAQ London (10-12 Events) organize these & also present @ some of them aiming to do a min of 8 events this year

o   Done, only 8 events (just made my  8 event goal) 

o   Done, I presented @ another 10 non-London UG Events, this included 2 mini UG tours

o   Done, We had an event where some of us got together for drinks, but it was not a big event

Exams (not as many as I had planned - although a lot of prep work done for the exams)

Study & Pass the following 4 Pre-Requisite Exams (Hmmm time for some serious study sessions)

Work on compiling my "MCM Study Notes" (OneNote) for MCM Certification" and keep it going throughout  

o   Planned for March

o   Planned (if I pass Exam on 1^st go) for March/April

o   Done, actually read about 5 books (although some chapters skipped in those, have a lot more to read through)

o   Still on target for 1^st half of 2012 - going to be heavy schedule but achievable

Writing (Not much achieved in this category, aside from getting the opportunity to contribute towards a MSFT SQL Server 2012 book)

o   Didn’t any published, but didn’t pursue this one, so will push this one into my 2012 goals

o   This was shelved as it would take a lot of time I was unable to dedicate and finish it to a suitable level – maybe will look @ this later in 2012

o   Took a different direction, as I’m writing a chapter for a SQL 2012 book instead – very excited to have that opportunity

    Will provide more details once the book is nearing completion (expected to be in 2^nd Qtr. 2012)

Community  (Some of these achieved)     

o   Maintained some activity on all these, but not nearly enough for my likening , LinkedIn was my main activity area

o   Nope, I didn’t focus on this, did a few questions, and really would like to get regular @ this

o   With 691 followers on 1^st Jan 2012 achieved the target and 6647 tweets to-date, have been regular in my activity in  this FUN area {Yes I’m twitter junkie}

o   Big fail here, with only 18 posts – some were  technical and some non-technical posts as well – this year will have to make up for my short fall in 2011 big time

So on reflection, not too bad on most of the goals i had set being achieved.