Steve Hindmarsh's SQL Blog

SSAS - What is running ?

steveh99999 — Thu, 15 Dec 2011 13:04:00 GMT

I saw a tweet today from respected blogger john sansom asking how a SQL DBA should attempt to performance tune SSAS.

I've been through a similar scenario - and one of the tools I developed as part of my troubleshooting was my own 'SSAS version' of sp_who.

I've written a stored procedure which I run on a SQL Server instance, that connects to an SSAS instance via a linked server, I then wrote an SSRS report to use this stored proc to allow developers and support staff access to SSAS cube activity.

It's quite a basic/simple solution but it does help expose what is running - I must say on a fairly low-use SSAS server. I don't know how well this scales on a heavily used SSAS implementation.

To add an SSAS linked server first of all to your SQL Server..

EXEC master.dbo.sp_addlinkedserver @server = N'SSAS_LINKED_SERVER', @provider=N'MSOLAP', @datasrc=N'ISSAS ServerName'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSAS_LINKED_SERVER',@useself=N'False',@locallogin=NULL,@rmtuser=N'Account to run command via' ,@rmtpassword='########'

Then I created a stored procedure :-

CREATE PROC [dbo].[SSAS_whoIsActive]

BEGIN

SELECT ssasSessions.SESSION_SPID AS [SPID],

ssasSessions.SESSION_USER_NAME AS [User ID],

ssasSessions.SESSION_CURRENT_DATABASE AS [Database],

ssasConnections.connection_host_name AS 'Client IP address',

ROUND(CAST(ssasCommands.COMMAND_CPU_TIME_MS AS int)/1000, 2) AS [Command Time(in sec)],

ssasCommands.COMMAND_START_TIME AS [Command Start Time],

ssasCommands.COMMAND_END_TIME AS [Command End Time],

ssasCommands.COMMAND_TEXT AS [MDX Command],

connection_host_application AS 'Application',

CHARINDEX('</Process>',ssasCommands.COMMAND_TEXT) AS ProcessRunning

FROM OPENQUERY(SSAS_LINKED_SERVER,'select * from $system.discover_sessions') AS ssasSessions

LEFT JOIN OPENQUERY(SSAS_LINKED_SERVER,'select * from $system.discover_commands') AS ssasCommands ON ssasSessions.SESSION_SPID = ssasCommands.SESSION_SPID

LEFT JOIN OPENQUERY(SSAS_LINKED_SERVER,'select * from $system.discover_connections') AS ssasConnections ON ssasSessions.SESSION_connection_id = ssasConnections.connection_id

ORDER BY [Command Start Time] DESC

END

I use the 'ProcessRunning' column to idenitfy if a cube process command is running, and then display this MDX command in a different colour (red) in my report.

This can be done by adding an expression to the report 'MDX Command' text box properties. =IIF(Fields!ProcessRunning.Value > 0, "Red", "Black")

The MDX command column can sometimes be very large in this report output - it can be quite a shock for a DBA to see how complicated MDX generated by SSAS sometimes can be ! I usually change the font size on this output column to be 8pt.

Note my sproc is named in honour of the famous sp_whoIsActive written by Adam Machanic.

Auto Update Stats change in SQL 2008 R2 sp 1

steveh99999 — Tue, 13 Sep 2011 11:43:00 GMT

I came across a very interesting post via Brent Ozar’s weekly news email – ( I strongly recommend you go to Brent's excellent website and subscribe to the weekly update)

The post states that a new trace flag had been made available in SQL 2008 R2 sp1 to change auto-update stats behaviour. Using the new 2371 trace flag, SQL Server will now run auto-update-stats much more aggressively. ie at a much lower threshold than the previous default of 20% change.

Although this post relates to SAP – I think for many SQL DBAs this could be a very relevant trace flag. I have certainly encountered many situations where this would have been a useful option.

I also hope Microsoft consider introducing this for SQL 2008 ?

As always, any change to a production SQL Server needs to be well-tested before deploying – but this does look worthy of further investigation…

Denali / SSAS Free Training in Reading–Expedition Denali workshop

steveh99999 — Tue, 07 Jun 2011 09:13:30 GMT

I came across this Microsoft Training event being held in Reading at the end of June – sounds excellent…. 2 days free, intensive training !

Hope to see you there..

Do you send output from sys.dm_exec_query_stats to your developers ?

steveh99999 — Tue, 22 Mar 2011 00:10:22 GMT

A few years ago, I had the good fortune to work on an engagement with Kevin McPherrin of Microsoft. Kevin is one of those Microsoft employees that you never hear about, but is an absolute expert on DBA good-practice and SQL advice.

One really useful tip he gave me was to regularly run reports on my production servers, to email details of heavily-used and badly performing stored procedures to your developers… the DMV sys.dm_exec_query_stats can be queried for this summary information.

Now, I send out reports every night, using SSRS, such as this :-

See how often ‘proc A’ is run – and also that the oldest entry in procedure cache is only 6 hours old…

Using this information, developers can focus their attention on optimising the most heavily-used stored procedures in a system.

The query used to produce this report can easily be altered to show what procedures use the most CPU…

Interestingly now that although stored procedure A is run over 25 million timesin 5 and a half hours – there are other stored procedures that use a lot more CPU.

For example, Proc H uses more CPU, even though it has only been run 32 times. And note the CPU usage from this proc only relates to the last hour and a half..

I find these reports are excellent for focusing developer attention on production problems.

The SQL used to produce a report of procedures using the highest average logical IO per execution :-

SELECT TOP 20 RANK() OVER(ORDER BY (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count desc) AS 'Rank',
        qs.execution_count AS 'Executions',
        qs.last_execution_time,
        qs.max_elapsed_time/1000 AS 'Max Duration ms',
         qs.total_elapsed_time/1000 AS 'Total Duration ms',
        (qs.total_elapsed_time/(qs.execution_count*1000)) AS [Avg Duration ms],
        qs.total_worker_time/1000 AS 'Total CPU ms', (qs.total_worker_time/(qs.execution_count*1000)) AS 'Avg CPU ms',
        qs.max_worker_time/1000 AS 'Max CPU ms',
        qs.total_logical_reads + qs.total_physical_reads AS 'Total Reads', qs.total_logical_writes AS 'TotalWrites',
        (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
         qs.max_logical_reads AS 'Max logical reads',
         qs.max_logical_writes AS 'Max logical writes',
            SUBSTRING(qt.text,qs.statement_start_offset/2,
            (CASE WHEN qs.statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
            ELSE qs.statement_end_offset END -qs.statement_start_offset)/2)
        AS query_text,
        dbname=DB_NAME(qt.dbid),
        OBJECT_NAME(qt.objectid,qt.dbid) AS 'object',
        qs.creation_time AS 'plan creation time'
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE object_name(qt.objectid,qt.dbid) IS NOT NULL

Change the text in red to produce a report based on a different order.

I usually include 4 subreports in one email :-

- top 20 most executed stored procedures.

- top 20 highest total CPU stored procedures.

- top 20 highest average logical IOs stored procedures.

- top 20 highest maximum duration stored procedures.

Be aware of the limitations of the sys.dm_exec_query_stats view – for example, it only reports on stored procedures with plans still in cache – but I find this a cheap and easy way of exposing production performance data to a wider audience.

The Exceptional EXCEPT clause

steveh99999 — Wed, 02 Feb 2011 23:03:00 GMT

Ok, I exaggerate, but it can be useful…

I came across some ‘poorly-written’ stored procedures on a SQL server recently, that were using sp_xml_preparedocument.

Unfortunately these procs were not properly removing the memory allocated to XML structures – ie they were not subsequently calling sp_xml_removedocument…

I needed a quick way of identifying on the server how many stored procedures this affected..

Here’s what I used..

EXEC sp_msforeachdb 'USE ?
SELECT DB_NAME(),OBJECT_NAME(s1.id)
FROM syscomments s1
WHERE [text] LIKE ''%sp_xml_preparedocument%''
EXCEPT
SELECT DB_NAME(),OBJECT_NAME(s2.id)
FROM syscomments s2
WHERE [text] LIKE ''%sp_xml_removedocument%'' ‘

There’s three nice features about the code above…

1. It uses sp_msforeachdb. There’s a nice blog on this statement here

2. It uses the EXCEPT clause. So in the above query I get all the procedures which include the sp_xml_preparedocument string, but by using the EXCEPT clause I remove all the procedures which contain sp_xml_removedocument. Read more about EXCEPT here

3. It can be used to quickly identify incorrect usage of sp_xml_preparedocument. Read more about this here

The above query isn’t perfect – I’m not properly parsing the SQL text to ignore comments for example - but for the quick analysis I needed to perform, it was just the job…

Free Microsoft one day BI Seminar in Reading

steveh99999 — Thu, 27 Jan 2011 13:18:52 GMT

A colleague passed details of this event on to me this morning. Sounds interesting…

Free, so if you are interested, register quickly – I imagine this will soon fill up….

The overlooked OUTPUT clause

steveh99999 — Mon, 17 Jan 2011 22:01:00 GMT

I often find myself applying ad-hoc data updates to production systems – usually running scripts written by other people. One of my favourite features of SQL syntax is the OUTPUT clause – I find this is rarely used, and I often wonder if this is due to a lack of awareness of this feature..

The OUTPUT clause was added to SQL Server in the SQL 2005 release – so has been around for quite a while now, yet I often see scripts like this…

SELECT somevalue FROM sometable WHERE keyval = XXX

UPDATE sometable

SET somevalue = newvalue

WHERE keyval = XXX

-- now check the update has worked…

SELECT somevalue FROM sometable WHERE keyval = XXX

This can be rewritten to achieve the same end-result using the OUTPUT clause.

UPDATE sometable

SET somevalue = newvalue

OUTPUT deleted.somevalue AS ‘old value’,

inserted.somevalue AS ‘new value’

WHERE keyval = XXX

The Update statement with output clause also requires less IO - ie I've replaced three SQL Statements with one, using only a third of the IO.

If you are not aware of the power of the output clause – I recommend you look at the output clause in books online

And finally here’s an example of the output produced using the Northwind database…

SQL 2005 Transaction Rollback Hung–unresolved deadlock

steveh99999 — Tue, 14 Dec 2010 17:53:00 GMT

Encountered an interesting issue recently with a SQL 2005 sp3 Enterprise Edition system.

Every weekend, a full database reindex was being run on this system – normally this took around one and a half hours.

Then, one weekend, the job ran for over 17 hours - and had yet to complete...

At this point, DBA cancelled the job. Job status is now cancelled – issue over…

However, cancelling the job had not killed the reindex transaction – DBCC OPENTRAN was still showing the transaction being open.

The oldest open transaction in the database was now over 17 hours old. Consequently, transaction log % used growing dramatically and locks still being held in the database...

Further attempts to kill the transaction did nothing. ie we had a transaction which could not be killed.

In sysprocesses, it was apparent the SPID was in rollback status, but the spid was not accumulating CPU or IO. Was the SPID stuck ?

On examination of the SQL errorlog – shortly after the reindex had started, a whole bunch of deadlock output had been produced by trace flag 1222. Then this :-

spid5s      ***Stack Dump being sent to   xxxxxxx\SQLDump0042.txt
spid5s      * *******************************************************************************
spid5s      *
spid5s      * BEGIN STACK DUMP:
spid5s      *   12/05/10 01:04:47 spid 5
spid5s      *
spid5s      * Unresolved deadlock
spid5s      *
spid5s      *
spid5s      * *******************************************************************************
spid5s      * -------------------------------------------------------------------------------
spid5s      * Short Stack Dump
spid5s      Stack Signature for the dump is 0x000001D7
spid5s      External dump process return code 0x20000001.

Unresolved deadlock – don’t think I’ve ever seen one of these before….

A quick call to Microsoft support confirmed the following bug had been hit :- http://support.microsoft.com/kb/961479

So, only option to get rid of the hung spid – to restart SQL Server…

Fortunately SQL Server restarted without any issues. I was pleasantly surprised to see that recovery on this particular database was fast. However, restarting SQL Server to fix an issue is not something I would normally rush to do...

Short term fix – the reindex was changed to use MAXDOP of 1. Longer term fix will be to apply the correct CU, or wait for SQL 2005 sp 4 ?? This should be released any day soon I hope..

Outlying DBAs

steveh99999 — Tue, 07 Dec 2010 22:45:06 GMT

Read an interesting book recently, ‘Outliers – the story of success’ by Malcolm Gladwell.

There’s a good synopsis of the book here on wikipedia.

I don’t want to write in detailed review of the book, but it’s well worth a read. There were a couple of sections which I thought were possibly relevant to IT professionals and DBAs in particular.

Firstly, ‘the 10,000 hour rule’, in this section Gladwell asserts that to be a real ‘elite performer’ takes 10,000 hours of practice.

‘Practice isn’t the thing you do once you’re good, it’s the thing you do that makes you good’. He gives many interesting examples – the Beatles, Bill Gates etc – but I was wondering could this be applied to DBAs ?

If it takes 10,000 hours to be a really elite DBA – how long does that really take ? 8 hours a day makes 1250 days. If we assume that most DBAs work around 230 days a year – then it takes around 5 and a half years to become an elite DBA.

But how much time per day does a DBA spend actually doing DBA work ? Certainly it’s my experience that the more experienced I get as a DBA, the less time I seem to spend actually doing DBA work – ie meetings, change-control meetings, project planning, liasing with other teams, appraisals etc. Is it more accurate to assume that a DBA spends half their time actually doing ‘real’ DBA work – or is that just my bad luck ?

So, in reality, I’d argue it can take at least 5 1/2 and more likely closer to 10 years to become an elite DBA. Why do I keep receiving CVs for senior DBAs with 2-4 years actual DBA experience ?

In the second section I found particularly interesting, Gladwell writes about analysis of plane crashes and the importance of in-cockpit communications. He describes a couple of crashes involving Korean Airlines – where co-pilots were often deferrential to pilots, and unwilling to openly criticise their more senior colleagues or point out errors when things were going badly wrong…

There’s a better summary of Gladwell’s concepts on mitigation here – but to apply this to a DBA role… If you are a DBA and you do not agree with a decision of one of your superiors, then it’s your duty as a DBA to say what you think is wrong, before it’s too late…

Obviously there’s a fine line between constructive criticism and moaning, but a good senior DBA or manager should be able to take well-researched criticism\debate from a more junior DBA. Is this really possible ?

SQL 2008 sp 2 – partitioning changes

steveh99999 — Tue, 05 Oct 2010 11:26:08 GMT

I was interested to see that SQL 2008 service pack 2 includes support for an increased number of partitions. ie In SQL 2008, the maximum number of partitions on a table is 1,000 – but now with sp2, the maximum number is 15,000.

Microsoft have published a whitepaper to document this change – here.

A couple of interesting points…

1. A new stored procedure has been introduced to turn this on, sp_db_increased_partitions. I do not like how options are now set by ALTER DATABASE – for example recovery model – but then Microsoft introduce new functionality such as this using ‘older style’ stored procedures...

2. SQL 2008 R2 does not support this increase in maximum partition numbers – yet… So you cannot take a database with more than 1,000 partitions in SQL 2008 and upgrade it to SQL 2008 R2.

3 There are also implications with log shipping and mirroring. Again, you need to be very aware of the versions\service packs involved in your log-shipping and mirroring configuration.

When I first saw this improvement, I was very pleased – however after reading the whitepaper, clearly it needs some planning/thought/testing before you decide to implement this feature.