Hello Analytic Functions


SQL Server 2012 CTP 3, formerly known as SQL Server Code Name “Denali”, introduces a new set of T-SQL functions called Analytic functions. Analytic functions now open up a new vista for business intelligence where in you can calculate moving averages, running totals, percentages or top-N results within a group. I find this very useful while analyzing performance issues while traversing information present in a SQL Server trace file.

I was looking into a performance issue where in an application module executing a series of T-SQL functions was taking a long time to complete it’s operation. When I looked into the total duration of the T-SQL queries executed by the application, I couldn’t account for the total duration that the application was reporting. On tracking some of the statement executions done by the SPID which was being used by the application to execute the queries, I found a difference between the start time of a batch and the completed time of the previous batch. Now I needed to see the complete time difference between two subsequent query completion and start accounted for the difference in duration that I was seeing between the duration reported by the application and sum of duration of all the queries executed by the application. And BINGO… I was finally able to make the co-relation. Till SQL Server 2008 R2, I would have to write a query which involved a self-join to get the comparative analysis that I required:

;WITH cte AS
(SELECT b.name, a.starttime, a.endtime, a.transactionid, a.EventSequence, ROW_NUMBER() OVER(ORDER BY eventsequence) AS RowIDs
FROM trace a
INNER JOIN sys.trace_events b
ON a.eventclass = b.trace_event_id
WHERE spid = 83
AND b.name IN ('RPC:Starting','RPC:Completed','SQL:BatchStarting','SQL:BatchCompleted'))
SELECT TOP 1000 b.name, b.starttime, b.endtime, b.transactionid, DATEDIFF(S,a.endtime,b.starttime) as time_diff_seconds
FROM cte a
LEFT OUTER cte b
ON a.RowIDs = b.RowIDs-1

The output of the above query is shown in the screen shot below:

image

As you can see that there is a 4-second delay between the endtime of the statement in Row# 783 and the next execution shown in Row# 784. With the help of Analytic functions, I can simply use the LEAD function to get the above result and avoid a self-join.

SELECT  TOP 1000 a.name,b.StartTime,b.EndTime,b.TransactionID,
DATEDIFF(s,(LEAD(b.EndTime,1,0) OVER (ORDER BY EventSequence DESC)),b.StartTime) as TimeDiff
FROM sys.trace_events a
INNER JOIN dbo.trace b
on a.trace_event_id = b.EventClass
WHERE b.SPID = 83
and a.name in ('RPC:Starting','RPC:Completed','SQL:BatchStarting','SQL:BatchCompleted')

The output as you can see is the same the previous query:

image

I had imported the data from the profiler trace into a SQL Server database table using the function: fn_trace_gettable. Let’s see what the query plans look like. For the first query which uses the common table expression and a self-join, the graphical query plan is as follows:

image

Now let’s see what the query plan looks like with the new LEAD function in action:

image

As you can see above a new Window Spool operator is the one which performs the analytical operation to calculate the time difference between the subsequent rows using the EventSequence number. As you can see that I have eliminated the need for a self-join with a temporary table or a common table expression and therefore simplifying my query in the process.

In the above example I am using the LEAD function to get value that I am interested in the following row. If you are interested in the values from a preceding row then you can use LAG function.

One gotcha that you need to remember here is that if you don’t take care of the start and end values of the dataset which you are grouping, you could run into the following error due to an overflow or underflow condition.

Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

This is a small example of how analytic functions can help reduce T-SQL complexity when calculating averages, percentiles for grouped data. Happy coding!!

Disclaimer: This information is based on the SQL Server 2012 CTP 3 (Build 11.0.1440), formerly known as SQL Server Code Name “Denali” documentation provided on MSDN which is subject to change in later releases.

SSAS: I will add you to the existing SQL cluster


You have an existing SQL Server 2005 Failover Cluster which has Database Engine and Full-text Search as the clustered components. Now you suddenly decide that this SQL Server cluster group requires a clustered Analysis Services instance. This will then lead you down a rabbit hole trying to figure out what to do to achieve this unless and until you know where to look.

There is a note in the Books Online content stating the following:

You cannot install Analysis Services to the same cluster group as the Database Engine. You must install Analysis Services to its own group and then, after installation, you can move Analysis Services to the same group as SQL Server.

However the above information is incorrect!!

You CANNOT add a failover cluster instance of Analysis Services to an existing SQL Server failover cluster instance using the Setup GUI!

Adding an Analysis Services failover cluster instance to an existing SQL Server cluster group has probably been a point of consternation for a person attempting SQL setup if you ran into the above mentioned scenario.

Now to the interesting part… achieving the objective using setup parameters and command line setup!! Sounds like fun, eh? The setup command would need to use the following parameters:

start /wait <CD or DVD Drive>\setup.exe
/qb VS=<VSName> – Virtual Server name should be the same as the existing database engine virtual server name
INSTALLVS=
Analysis_Server
INSTANCENAME=<InstanceName> – Instance name should also be the same as the instance name of the database engine. For default instances, use MSSQLSERVER.
ADDLOCAL=Analysis_Server
ADDNODE=<NodeName1, NodeName2,… NodeNameN>
GROUP=<SQL Diskgroup>
IP=<IP,Networkname> – Network name here is the cluster network name. See Gotcha#2 below.
ADMINPASSWORD=<StrongPassword>
SAPWD=<StrongPassword
INSTALLSQLDIR=<InstallationPath>
INSTALLASDATADIR=<Sharedrivepath>
SQLACCOUNT=<domain\user>
SQLPASSWORD=<DomainUserPassword>
AGTACCOUNT=<domain\user>
AGTPASSWORD=<DomainUserPassword>
ASACCOUNT=<domain\user>
ASPASSWORD=<DomainUserPassword>
SQLBROWSERACCOUNT=<domain\user>
SQLBROWSERPASSWORD=<DomainUserPassword>
SQLREPORTING=1
ASCLUSTERGROUP=<YourDomain \ YourDomainGroupName>

There are few gotchas here.

1. If you specify a Virtual Server name other than your existing SQL Server virtual server name for the VS parameter, then you will get a failure while trying to create a new IP resource:

Error Code: -2147019839
Windows Error Text: The cluster IP address is already in use

2. If you specify incorrect parameters for the IP parameter, you could encounter a"network name not found" error. The network name value in the IP parameter is the name of the cluster network that shows up in the Failover Cluster Manager snap-in or Cluster Administrator snap-in. The network name is NOT the name of the Windows Network Interface.

3. You need an additional shared disk. If your Database Engine is using G: drive for the existing instance, you cannot use the G: drive to install the Analysis Services instance even though you are installing into the same cluster group.

References

SQL Server 2005 Setup Parameters
http://technet.microsoft.com/en-us/library/ms144259(SQL.90).aspx

Another SQL VDI error that can mystify you


While performing a backup of a SQL Server using a backup software which uses SQLVDI, you might encounter a failure which reports the following failure hex code: 0x080070005. This is basically an Access Denied error message. If your backup software logs all the VDI API calls that are made, then you might even know which function call failed.

If you are using SQL Server Backup Simulator to simulate a VDI backup of the any SQL Server database with the same account that raised the error, you will find the following message in the Backup Simulator logs.

Trying to perform VDI test on a default instance
Error: VDS::Create fails: 0x80070005

The above message tells me that the IClientVirtualDeviceSet2::CreateEx function call failed. Now the important question here is why did the CreateEx function fail. If you look into the application event log, you will find an Error reported by SQLVDI (source) with the following text:

SQLVDI: Loc=CVDS::CreateEx. Desc=Create Memory. ErrorCode=(5)Access is denied.. Process=24456. Thread=13984. Client. Instance=. VD=Global\SQLBackupSim_SQLVDIMemoryName_0.

The above message tells me that I am creating a Global Shared Memory Object but I currently do not have the necessary privilege to create the object. Check the output of whoami /priv for the account that is running the Backup Simulator or the backup software and see if the following privilege is listed: SeCreateGlobalPrivilege. This privilege is granted by default to administrators, services, and the local system account. In case you are trying to use Backup Simulator on a Windows version which has UAC, then you need to run Backup Simulator using “Run as Administrator” option to avoid this issue. If the account trying to create this global memory object doesn’t have this privilege, then you can grant the privilege to this account using the following steps:

  1. On the Start menu, click Run. In the Open box, type gpedit.msc.

    The Group Policy dialog box opens.

  2. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.

  3. Expand Security Settings, and then expand Local Policies.

  4. Select the User Rights Assignment folder.

    The policies will be displayed in the details pane.

  5. In the pane, double-click Create global objects.

  6. In the Local Security Policy Setting dialog box, click Add.

  7. In the Select Users or Groups dialog box, add an account with privileges to run backup software or the Backup Simulator application.

Once this is done, you need to re-launch Backup Simulator or re-start the backup using the Backup Software as the security privilege will not modify the token that is currently being used by the backup software or Backup Simulator.

Another issue that you can run into even if you have the above mentioned security privilege is if you have multiple backups trying to create the same global shared memory object. In such scenarios, you would want to create a unique virtual device name. More information about the same is mentioned in the KB Article below:

903646    An application that uses the Virtual Device Interface feature of SQL Server 2000 to back up a SQL Server 2000 database may not back up the database
http://support.microsoft.com/default.aspx?scid=kb;EN-US;903646

del.icio.us Tags: ,,