Posts Tagged ‘SQL Server’

Quick Tip: How to Take Snapshots of Data at Specified Intervals

A few weeks ago I was debugging something and needed to take snapshots of a table every five seconds, as a quick solution a colleague started running the following SQL and I thought I’d share it for anyone else stuck in a pinch…

WHILE (1=1)

BEGIN

	INSERT INTO tmp_snapshot
	SELECT field1,field2,field3
	FROM table1 WHERE field4 = 'value'

	WAITFOR DELAY '000:00:05'

END

Just change the time delay and the query and you’re off, of course you could be a lot cleverer with your WHILE conditions and rework this principle for loads of things.  I thought it worth posting as many database folk forget the power of the humble while loop!

SQL Server Agent PowerShell Jobs Waiting for Worker Thread

My normal Data Warehouse load process runs daily in the early hours of the morning so I’m not used to sitting watching it – this morning however through lack of sleep I happened to be there and I noticed something odd: there seemed to be a queue.

What seemed to be happening is that two jobs would run simultaneously whilst other jobs sat showing a status of “Waiting for Worker Thread”.  After digging around I found that…

EXEC sp_configure;

… showed that the server was configured to automatically manage the max_worker_threads setting (value of 0), additionally…

SELECT max_workers_count FROM sys.dm_os_sys_info;

… showed that the value being used (based on the 32/64 bit status and number of cores) was indeed 512.  A quick check of…

SELECT COUNT(*) FROM sys.dm_os_threads;
SELECT COUNT(*) FROM sys.dm_os_workers;

… showed that the actual running values (54 & 46 respectively) were within expectations and nowhere near the 512 thread limit so what was happening?

Well, it turned out that whilst my jobs are a mix of OS admin tasks, legacy DTS jobs and SSIS packages most of them involve some form of PowerShell script to initialise them (usually moving files around, setting permissions, etc.).  The problem was down to an obscure setting that limits the max_worker_threads to just 2 for the PowerShell subsystem, the setting can be seen here:

SELECT subsystem,max_worker_threads
FROM msdb.dbo.syssubsystems;

In my case I just upped the running value to 40 (the same value set for CmdExec) and restarted SQL Server Agent:

UPDATE msdb.dbo.syssubsystems
SET max_worker_threads = 40
WHERE subsystem = ‘PowerShell’;

I’m yet to spot whether this has any other long term effect but things seem to be behaving fine, I will say that as part of my research I found other people saying that this setting did not persist through a restart on certain versions – I’m running 2008 R2 and that appears to be fine but it’s worth double-checking if you try this.

Using MySQL BLOB Data via ODBC in SSIS, SQL Server & Business Objects

Whilst trying to build a centralised cross-platform alerting system I spotted a peculiar issue when trying to move the output of a SHOW FULL PROCESSLIST command on MySQL via ODBC.  It seems that the output of the SHOW FULL PROCESSLIST command returns both integers and binary (BLOB) data types even though to they eye (that is, in the MySQL Query Browser) most of the columns appear to be short text fields.

Despite the fact that the data looks like text whenever I tried to return the data into an application, I tried SSIS, SQL Server Linked Servers and Business Objects, each time the data would come back unusable or an error would be returned.  Business Objects gave me the key by declaring “This is a BLOB.” as you can see in the following screenshots…

SQL Server Integration Services

SQL Server Linked Server

Business Objects Desktop Intelligence

As with my recent post about loading data into MySQL with SSIS the saviour turns out to be an ODBC configuration setting, this time in the Metadata tab of the MySQL ODBC driver.  All you have to do is check the “Always handle binary function results as character data” and instantly your problems will be solved…

Out of a crazy fit of completeness I also took screenshots of the final results and it’d be a shame to waste them so here they are…

SQL Server Integration Services

SQL Server Linked Server

Business Objects Desktop Intelligence

Using SQL Server 2008 R2 Linked Servers with PostgreSQL 64-bit

Having setup a Linked Server in Management Studio talking to a PostgreSQL 8 database I encountered the following error when attempting to run any valid query:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "PG_SERVER" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "PG_SERVER".

After some digging I came across a handy article on Microsoft Connect describing the same issue, with thanks to Nenea Nelu here’s the solution…

• Expand Server Objects > Linked Servers > Providers. 
  
• Right-click on MSDASQL and select Properties…
• In the Properties dialogue un-check “Allow inprocess” as follows…
  
• Click OK and re-run your query. 

Hopefully that should solve your problem, please note that this will affect all Linked Servers using that provider however as the Connect article points out – this is best practice for linked servers anyway.

SQL Server Backup Compression vs. Quest Litespeed Engine

As more and more functionality is built into products like SQL Server it’s always worthwhile reviewing third-party tools and utilities when you’re considering an upgrade to see (a) if they’re still required and (b) if the tools themselves need to be upgraded.  With the introduction of Backup Compression in SQL Server 2008 R2 Standard Edition you could begin to think that the future is grim for Quest’s backup compression software LiteSpeed so I thought I’d do some testing to see exactly how it stacks up against the native compression.

LiteSpeed Engine

I’ve been using LiteSpeed on and off for a few years now and it has always been a great tool but I’ve always found it a bit of a drag to have to use the GUI to administer and setup jobs however in January 2010 Quest launched the LiteSpeed Engine for SQL Server which allows you to administer jobs using the native SQL Server tools.  The LiteSpeed Engine acts as a driver and the configuration tool allows you to define a variety of configuration profiles based on file extension and from that point onwards you can use the Management Studio to setup backup jobs, maintenance plans, etc. and all you have to do is specify the file extension of the profile you wish to use.

The configuration tool allows you specify the compression level from 1 to 8, encryption level including various bit-length versions of RC2, RC4, 3DES and AES though as you’ll see later the overhead of adding the highest level (256-bit AES) isn’t that great so I’d always shoot for the maximum.

Benchmark Structure

The test is relatively unscientific since I used only one database but it was carried out systematically, the data comes from a transactional billing system which I chose as it has a mix of strucured tables and raw transactions and comes in at about 6.5GB so it wouldn’t take too long to test.  I used the following configurations…

Benchmark Results

On my test database the baseline SQL Server native compression reduced the 6.2GB database to 765MB (12.2% of the original size) and took less than half the time (43%), to achieve the same level of compression using LiteSpeed I had to use Level 2 which gave me 12.2% of the original size and 40% of the original duration.

At first this doesn’t look great for the third-party tool but the benefit of using a mature backup compression engine is the flexibility and LiteSpeed’s configurations allow you to tweak the performance to solve whatever problem you have in your environment whether that be the absolute size of the backup, the backup window time or a mixture of the two.

If it’s size you’re after then Level 8 really did seem to work wonders on my test DB bringing the size down to 5.6% of the original at only 352MB though it did take 2.6 times the original duration, if it’s the backup window you’re looking to reduce then the basic Level 1 did manage to improve on the native compression by taking 0nly 37% of the original duration whilst still compressing to 13% of the original size.  If like most people you’re looking to have your cake and eat it (i.e. reducing size and backup window) I’d suggest that Level 3 is the best compromise giving 10.9% of the original size at 77% of the original duration so you get some benefit in both areas, though Level 4 takes compression a bit further and still gave a slight time reduction.

Clearly, the real answer is testing and since I’m at the beginning of data warehousing project I’m not in the position to make any firm decisions but I think that even if you don’t run out and purchase it now LiteSpeed is a very valuable tool to have in your mental arsenal so that if you come up against backup size/window issues or you’re faced with older versions of SQL Server you’ve got a solution in mind already.  Quest have an odd policy of keeping pricing quite opaque but I believe that the full Enterprise version (including the LiteSpeed Engine) retails for around £1,800 ($2,800) which isn’t too bad if you need that level of flexibility.

SQL Server Native Compression

LiteSpeed Compression (No Encryption)

LiteSpeed Compression (With Encryption)

Trends in Business Intelligence & 2010 Review

It’s the time of year when magazine editors can’t resist the urge to fill their glossy wares full of ‘thing of the year’ articles, the print equivalent of the mid-season “clip show” that has plagued many a TV series.  Well, if it’s good enough for them it’s good enough for me so here’s my rather unstructured and unscientific take on Business Intelligence and Data Warehousing in the year that was – 2010…

Market Trends

To start, I’ve taken a series of snapshots from the excellent Google Trends showing global search volumes for each of the Big Four offerings to measure the level of interest.  It’s reasonably clear to see from the graph below that interest in OBIEE shows a small but steady growth whilst Reporting Services shows a marked decline and the other two offerings remains roughly static (maybe a small decline?), this surprised me given that with the release of 2008 R2 I think that Reporting Services is really getting to the point where it offers a legitimate choice in the BI marketplace.  Perhaps the issue that Microsoft have fragmented their BI offering to include a mixture of terms with Excel, PowerPivot, SharePoint, Analysis Services and Reporting Services all making up the BI stack and nobody really knows what to call it?

This year has also brought an increased emphasis on Mobile BI with the iPad and iPhone fast becoming common executive playthings, Business Objects making it’s Explorer and Xcelsius products available on Android in addition to the iPhone (Explorer only).  MicroStrategy took the mobile emphasis a step further (perhaps to help stick their head above the crowd) by announcing a strong focus on the mobile BI market and offering a free 25-seat licence for their Mobile Suite.  Despite a strong focus on marketing Mobile BI I’m still not convinced that any of the vendors have really hit the nail on the head with their solutions in that whilst many offer pretty visualisations and slick interfaces most seem to lack the kind of simplicity that helps to present information quickly and succinctly, even the frankly beautiful independent product RoamBI just feels a little overdone when it comes to actually using it.

Major Product Releases

It’s been quite a year in the BI & Database world with the launch of Microsoft SQL Server 2008 R2, Oracle Business Intelligence Enterprise Edition (OBIEE) 11g and IBM’s Cognos 10…

Microsoft’s launch is effectively a moderate evolution of SQL Server 2008 in most areas with little change to the database engine, it’s ETL tool Integration Services and it’s OLAP engine Analysis Services.  That said, R2 did bring some handy incremental features which will be especially welcomed by the budget-conscious with an increase in the DB size of the free Express Edition from 4GB to 10GB and the addition of Backup Compression to Standard Edition.  There were some interesting additions with PowerPivot, Master Data Services and StreamInsight thought I’m not sure that either will find a natural home for a good year or so as busy DBAs and developers struggle to find the time to try these new features out.

Despite the major jump in the version number Oracle’s release too seems to be mainly an evolution and as a great fan of the product I’m quite considerably relieved since Oracle could quite easily have been over-zealous in integrating their ‘own’ tools like Discoverer and Warehouse Builder with bought-in technologies like Siebel Analytics (which became the bedrock of OBIEE), Hyperion’s Essbase and Sunopsis (now Oracle Data Integrator).  One of the less exciting but fundamentally important additions is that the semantic layer employed in OBIEE will be directly and immediately compatible with future releases of other Oracle products in the CRM, ERP and Finance application spaces.

I’m not as familiar with Cognos as the other two tools having only experimented with Cognos 8 for a couple of weeks but from everything I’ve read it seems that Cognos 10 was certainly a major milestone in the product’s lifecycle.  Aside from the shiny sounding features such as Social Networking and iPad support (actually a very serviceable looking mobile BI app) there are some very cutting-edge additions to the product including a statistical engine drawn from SPSS and Active Reports which allows users to explore and analyse offline data including interactive email reports.

The Future?

No good review and roundup article ends without a nod to the future and whilst I’m not keen on making absolute predictions there are a few developments I’ll be keeping my eye on for 2011 and beyond.

The main event I’m anticipating is the release of Business Objects XI Release 4, I’ve not seen too many concrete details about functionality but over the last few years Business Objects have seen themselves distracted by the Crystal acquisition (including the shoe-horning of their core product into Crystal Enterprise) and in turn their acquisition by SAP.  As a regular and long-term user of Business Objects I’m really hoping that they’ll blow away some of the cobwebs and deliver some new functionality as well as rounding off some of the edges that in previous versions feel a little unfinished, it would be great too if they finally included the key functionality from the legacy desktop client (which many long-term customer still rely on) in their core Web Intelligence product (Freehand-SQL & Grouping – I’m looking at you).

Another area to watch in Business Intelligence and Data Warehousing as well as the wider enterprise market is cloud computing, Informatica’s ETL in the Cloud offering has seen improvements and adoption throughout 2010 and it’s almost a given that Microsoft will be adding some degree of ETL capability to their SQL Azure platform. I’d expect an announcement if not a release along these lines in the coming year, though it’s possible that ETL comes behind providing cloud based analytics (something SSIS guru Jamie Thomson suggests).

In a broader sense I’m expecting to see a little more interest and pickup in the open source BI market, I’ve been saying this for a while (“this time next year, Rodders…“) and I might be wrong for some time to come but I always keep an eye on companies using an Open Source model such as the ETL vendor Talend who recently acquired Sopera (a middleware and SOA vendor), BI vendor Jaspersoft and all-rounder Pentaho.  With the global economy still suffering a hangover from the sub-prime mortgage crisis and banking collapse people have been looking for cheaper alternatives and open source companies provide a great way to achieve that, though some of Talend’s high-end offerings are almost comparable in price with other commercial products.

Another possible area to watch out for is the area of Personal Intelligence, essentially Business Intelligence for the individual.  A colleague and I spoke about this back in 2008 and we could both see that as people increasingly become data-aware they’ll start to look inwards and aim to measure things about themselves, one obvious starting point is fitness and we already have sites to log and chart your weight and calorie intake as well as the brilliant Nike+ product that measures your pace, time and distance during a run using either a sensor in your shoe or GPS (iPhone app), see the sidebar of this blog or below (one of my runs on the Nike+ site) for examples of the output.