SQL Server queries can be returned as a valid XML as well as a rowset (by default), there are multiple ways you could control the format the end result XML is returned in, giving SQL developers flexibility that can meet most of the demand of an application consuming this XML data. XML support has been natively built into SQL Server since the 2005 release.

In this post I will be briefly exploring the different type of XML output modes supported by SQL Server through the FOR XML statement.

Read more →

NLTK (Natural Language Toolkit) is a Python library that allows developers and researchers to extract information and annotations from text, and run classification algorithms such as the Naive Bayes or Maximum Entropy, as well as many other interesting Natural Language tools and processing techniques.

The Maximum Entropy algorithm from NLTK comes in different flavours, this post will introduce the different Max Ent classification algorithm flavours supported by the NLTK library, as well as provide a compiled MEGAM binary on a Linux (Ubuntu) 64-bit machine, which is a requirement for running Max Ent NLTK classification on the megam algorithm.

Read more →

There has been a lot of buzz on Twitter going around today about Microsoft’s new Mobile BI Platform, dubbed Project Helix, which was unveiled at Microsoft’s SharePoint event last week.

The screenshots originated from Just Blindbaek, and shows some aspects of the new Mobile BI Platform that is due to be released in 2013, if Microsoft’s Mobile BI roadmap is still accurate.

Read more →

Being able to holistically view your SQL Server Job Agent job schedules, how long each job took to run, what other jobs were running at the same time and the status of each job, should be a fundamental part of Job Agent management.

In this post I will be briefly introducing the current SSRS reports integrated into SSMS (or SQL Data Tools) that displays SQL Server Job Agent job information, as well as provide a new report (pictures above) that should give you a better holistic view of activities on your SQL Server Agent, as well as highlight any conflict in job schedules or times were no activity is occurring.

Read more →

There are multiple ways to access SQL Job Agent jobs execution statistics, such as:

• Through SQL Server Management Objects (SMO): which provide a programmatic way of accessing and controlling many SQL Server objects. The Smo.Agent namespace will provide the required objects for accessing job statistics such as the last time each job (or step) ran, the duration the job ran for and the returned status as well as many other relevant information regarding job execution.
• Through undocumented Stored Procedures such as master.dbo.xp_sqlagent_enum_jobs: Although this doesn’t provide a lot of flexibility, particularly if you are executing and consuming the data within the context of T-SQL, rather than programmatically through wrapper classes that simplifies handling stored procedures with multiple output tables. It is important to note that since this stored procedure is undocumented, Microsoft could potentially retire it without prior notice, leaving your code non-functional.
• Through Job Agent System Tables: In my opinion this provides the best and most supported method of access. for the rest of this post I will be discussing the different System Tables provided to access SQL Server Job Agent statistics and execution information.

Lovely post title, and a very annoying error.

I ran into this error while trying to communicate with Windows Management instrumentation in order to get the PathName installation parameter of the MSReportServer_ConfigurationSetting class (which hooks up to a Report Server instance). The issue is a very vague COM exception that doesn’t really give much information, what made the issue worse is that it is not a connection issue to the WMI Namespace, which works absolutely fine, the issue happens when my code tries to load any data from the namespace that I already successfully established a connection to.

In this post I will be giving a very brief introduction into WMI for Reporting Services, the reason this issue was happening, and how I managed to resolve this issue for my particular case.

Read more →

Detecting and reporting when a SQL Server database data or log file experienced a growth operation can be very useful for many reasons, such as:

• Analyzing and predicting disk space utilization on the server
• Detecting optimum values to setup for database growth
• Detecting any long running transactions that causes growth in log or data files.

In this post I present a simple method for querying information regarding database growth activity within a SQL Server instance, as well as setting up a simple email alert that gets triggered based on a configured growth threshold.

Read more →

Diagnosing CPU performance issues with SQL Server can be a bit tricky, particularly if you have a system that exhibit a CPU spikes pattern throughout the day. You could run some SQL DMVs in order to identify holistically how much CPU time each query is consuming, but that does not give you a real-time way of tracking CPU resource starvation on SQL Server.

Recently I have been asked to devise a system on a few of our servers that are experiencing CPU resource bottleneck issues, the monitoring system needs to identify when SQL Server is suffering from CPU bottlenecks, and report the queries currently running on the server via email alerts, along with performance statistics that can help you diagnose which queries are consuming most of the resources, and why are they doing so.
Read more →

The SSIS Catalog in SQL Server 2012 comes with the benefit of logging natively integrated within the service’s architecture, which is awesome considering before SSIS 2012 BI developers and admins had to go for 3rd party tools in order to bring that level of logging into their SSIS packages.

That being said, with a lot of log data comes the responsibility of maintaining and clearing out old log entries and execution messages, which are stored on the SSIS Catalog Database and drive the SSRS reports that are used to diagnose the execution of packages, as well as provide performance statistics. The currently provided SSIS Server Maintenance Job in SQL Server Job Agent that performs these routine maintenance operation can be very slow and extremely blocking, especially on an SSIS server that sees alot of operations and activities, and cannot afford the downtime caused by the heavy locking experienced by this operation.

In this post I provide an introduction to the [internal].[cleanup_server_retention_window] stored procedure executed by the SSIS Server Operation Records Maintenance step in the SSIS Server Maintenance Job, as well as provide an extremely fast truncation query to completely clear out the SSIS log tables. In a  follow-up post I will be providing a rewrite of the [internal].[cleanup_server_retention_window] stored procedure that performs much better on SSIS services with a large number of logged operations per day.

Read more →

After a year from announcing partnership and starting the collaboration project, Microsoft (SQL Server) and Hortonworks (Hadoop) have finally announced the result of this integration: Microsoft HDInsight Server and HDInsight Azure Service.

So what is HDInsight? well, it is essentially Microsoft’s Hadoop-based distribution which is built on top of the Hortonworks Data Platform. So if you download Microsoft HDInsight Server for a local installation of the Hadoop distribution, then you will end up with a local cluster with your own Hadoop Hive able to run Hadoop jobs, as well as benefit from the already released Hadoop integration points with SharePoint and EXCEL. This is just so powerful!

Read more →