• Excel doesn’t open CSV files correctly from sp_send_dbmail

    April 23rd, 2014 in Blog, SQL Server

    A nice little nugget today for anyone who uses SQL dbmail to send data in csv attachments. Sometimes, depending on your language settings, CSV files don’t open correctly in Excel. Excel ignores the ‘,’ and puts all data in column A. You then have to go through the hassle of closing the file and opening it throug the flat file wizard.   There’s a very nice solution to this that I found in this thread. It’s to…

    » Read more

  • Analysis Services Tabular or Multidimensional? A performance comparison

    March 31st, 2014 in Analysis Services, Blog, SQL Server, Tabular

    Can SSAS Multidimensional be faster than SSAS Tabular for distinct counts on large datasets? We’ve all seen how fast the SSAS Tabular model can work – it’s an in-memory, heavily compressed analytics database. However you may have seen my previous posts on Tabular and NUMA, which show that at the moment Tabular is limited to a single CPU. This obviously limits its scalability and maximum performance. The situation A large…

    » Read more

  • SSAS Tabular performance – NUMA update

    February 12th, 2014 in Analysis Services, Blog

    How does the SSAS 2012 Tabular model performance change when you add more CPU sockets / NUMA nodes? In my last post (SSAS Tabular NUMA and CPU Cores Performance) I presented the results of some testing I’d been doing on the scalability of the SSAS 2012 Tabular model. Specifically with the performance of distinct count measures over large data volumes (50-200m distinct customers). The conclusion was that moving from 1 NUMA node (CPU socket)…

    » Read more

  • Renaming an SSAS Tabular Model

    February 12th, 2014 in Analysis Services, Blog

    I came across a frustrating problem today. I’d just finished processing a large tabular cube (SQL Server 2012), which had taken 11 hours in total. On trying to connect to the cube to test it, I’d made a schoolboy error; The database was named correctly, but the model inside it was named MyCubeName_Test instead of MyCubeName. No problem, I’ll just right click the cube in SSMS and rename it. Well, no, there is no option to rename…

    » Read more

  • SSAS Tabular – NUMA and CPU Cores Performance

    December 13th, 2013 in Analysis Services, Blog

    [UPDATE] After further investigation, I found that the tests in this post were inacurate and the results unreliable. Updated NUMA test results here In my last post (SSAS Tabular Performance – DefaultSegmentRowCount) I presented some analysis of the query performance impact of changing the DefaultSegmentRowCount setting. This post describes the next tests that I ran on the same system, investigating the impact of restricting SSAS to just 1…

    » Read more

  • SSAS Tabular performance – DefaultSegmentRowCount

    December 11th, 2013 in Analysis Services, Blog

    I’m currently investigating a poorly performing Tabular model, and came across some interesting test results which seem to contradict the advice in Microsoft’s Performance Tuning of Tabular Models white paper. Some background: 7.6Gb SSAS tabular cube, running on a 2 x CPU 32 core (Xeon E5-2650 2Ghz, 2 x NUMA nodes) server with 144Gb RAM SQL Server 2012 SP1 CU7 Enterprise 167m rows of data in primary fact 80m distinct CustomerKey…

    » Read more

  • Dimension ProcessAdd in SSAS

    September 4th, 2013 in Analysis Services, Blog

    When you have very large dimensions in SQL Server Analysis Services (SSAS) cubes, the time taken to process them can cause a problem. This post builds upon an excellent walkthrough of the ProcessAdd option written by Daniel Calbimonte on MSSQLTips.com. and shows how to automate the identification of new data to be added. What are the main options for processing a dimension?  (Technet details here) Process Full – the whole dimension is…

    » Read more

  • LogParser Performance Inserting into SQL Server

    August 2nd, 2013 in Blog, Integration Services, SQL Server

    Recently I’ve been using LogParser a lot to import log files, such as W3C logs from an IIS web server. The distasteful format of these files is made palatable by the awesomeness that is LogParser; it just takes care fo it for you. (Check out this SQLBits session on LogParser by Jonathan Allen (Twitter | Blog) for a good intro to LogParser) However I’ve been suffering with very poor performance with large files, so started to…

    » Read more

  • SQL Relay Interview

    June 5th, 2013 in Blog, Community

    A while back Richard Douglas interviewed me regarding my role as Chair of the SQL Relay 2013 conferences. You can read the resulting interview here! There’s only two weeks to go before the Relay starts in Glasgow, before travelling onto Leeds, Birmingham, Norwich, Cardiff, Reading and London. There are some spaces left at a few of the events, but register quick to make sure you don’t miss out! We’ve got Microsoft’s SQL…

    » Read more

  • SQL Relay 2013

    May 13th, 2013 in Blog, Community

    SQL Relay 2013 is only a month away! If you’ve not signed up yet to SQL Relay 2013 then you’d better get in there quick, as 2 of the 8 events are already full… What is SQL Relay? A series of 8 SQL Server conferences around the UK, held between 17th June and 27th June. They are FREE to attend (thanks to the generous sponsors!), and each contains a full day of technical talks covering different aspects of SQL Server. Who’s…

    » Read more