Thursday, January 13, 2011

What do I need to do to upgrade to SQL Server 2008 R2 ?

Photo credit: Ewan traveler
An upgrade of your SQL Server from SQL 2005, 2008 to SQL Server 2008 R2 is something you need to prepare and plan. I will give some examples:
  • Which upgrade strategy is the best for my situation. An in-place uprade or an Side-by-side upgrade?
  • How can I minimize downtime during the upgrade?
  • Backward compatibility. Which features are deprecated, discontinued or changed in the new version?
  • Considerations for upgrading without a DBA.
  • Feature changes in the management and development tools.
  • .......
Microsoft has published a 'Why upgrade' site. On this site you can download the SQL Server 2008 R2 upgrade technical reference guide. This technical reference guide contains a lot of useful information which will help you to plan your SQL Server upgrade. The technical reference guide contains 15 chapters with a total of 490 pages.

Table of contents:
  1. Upgrade Planning and Deployment
  2. Management and Development Tools
  3. Relational Databases
  4. High Availability
  5. Database Security
  6. Full-Text Search
  7. Service Broker
  8. Transact-SQL Queries
  9. Notification Services
  10. SQL Server Express
  11. Analysis Services
  12. Data Mining
  13. Integration Services
  14. Reporting Services
  15. Other Microsoft Applications and Platforms
Enjoy the upgrade to SQL Server 2008 R2. After it, you can make use of the new features for example:
and I will end this blogpost with: My favorite Top 10 Features of SQL 2008 R2.

Monday, January 10, 2011

Analyze performance of your reporting services reports by using SSRS statistics.

With Microsoft SQL Server Reporting Services you can build very nice and useful reports. However, reports which do not perform will not be used by the users. In my blog, Tips to improve performance of MS Reporting service reports,  I gave some performance tips when building SSRS reports. After deploying your reports it is always interesting to know how your reports are used. After a while, you want to know some performance statistics of your deployed SSRS reports. This can be useful information to start analyzing the performance of the reports. In this blogpost, I will give some queries which you can use to get SSRS statistics of your reports. These statistics are stored in the table: ExecutionLog of your the ReportServer database. This table contains statistics of the last 60 days. More information about the ExecutionLog table can be found here.
Some examples of statistics which can be retrieved from the ExecutionLog table:
  1. Most active users
  2. Most popular reports
  3. Reports with most DataRetreival in Total
  4. Reports with highest execution time in Total
  5. Reports with highest data retrieval in Total
  6. Slowest reports
Of course, you can use these queries as datasets in your own monitoring SSRS reports.

-- Most Active users.
SELECT Username, COUNT(*) as Executed
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid

-- Most popular reports
SELECT Name,b.path, COUNT(*)AS Executed
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name,b.path

-- Reports with most Dataretrieval
SELECT TOP 25 Name, b.path, AVG(BYTECOUNT/(1024)) AS AVG_KB,
            SUM(BYTECOUNT/(1024)) as SUM_KB, COUNT(*)AS Executed

FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name,b.path

-- Reports with most execution time
SELECT TOP 25 Name, b.path,
      SUM(TimedataRetrieval + Timeprocessing + TimeRendering)/1000 AS ExecutionTotalTimeinSec,
      COUNT(*) AS Executed,
      AVG(TimedataRetrieval + Timeprocessing + TimeRendering) AS AVGExecutionTimeinSec
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name, b.path
ORDER BY ExecutionTotalTimeinSec desc

-- Reports with most Rendering time
SELECT TOP 25 Name,b.path,
    SUM(TimeRendering)/1000 AS RenderingTotalinSec,
    COUNT(*) as Executed,
    AVG(TimeRendering) AS AvgRenderinginMs
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name,b.path
ORDER BY RenderingTotalinSec desc

-- Reports with most Data retrieval time
SELECT TOP 25 Name,b.path,
     SUM(TimeDataRetrieval)/1000 AS DataRetrievalTimeinSec,
     COUNT(*), AVG(TimeDataRetrieval) AS AvgTimeDataRetrievalinMs
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name,b.path
ORDER BY AvgTimeDataRetrievalinMs desc

-- Slowest executed report.
SELECT TOP 10 Name,b.path, Parameters, FORMAT,TimeStart,
     TimeEnd, ByteCount,
     (TimedataRetrieval + Timeprocessing + TimeRendering)/1000 AS TotalTimeinSec
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
ORDER BY (TimedataRetrieval + Timeprocessing + TimeRendering) DESC

Tuesday, January 4, 2011

Statistics of

Starting with the year 2011, I want to share you some statistics of my blog. I started my blog 2,5 years ago. As of today, I'm pretty happy to see that the number of visitors to my blog is still increasing. Here some statistics of my blog:
  • 100% increase in the number of visitors last year.
  • 2200 visitors per month.
  • Visitors from 137 countries in the last year.
  • 78 % of traffic coming via search engines.
  • Total of 123 blogposts.
  • Browser usage: Internet Explorer 55%, Firefox 25% Chrome 16%. Internet explore share is decrease. Chrome is taking market share from Internet Explorer and Firefox.
  • Most popular content is related to application errors.

    What can we learn from this? Errors from a technical perspective are nice for the developers, however for the user it's unclear what they need to do. People are interested to know what they should do to fix it.
  • Most popular search words are the error messages of my most popular content.

Sunday, January 2, 2011

Happy new year to all of you

Hello to all visitors of my blog. A happy new year to all of you. I'm looking forward to what 2011 will bring. I will try to do my best by posting more information on my blog which might be interesting for you. 
The start of this year was a little bit different as what I expected. As a tradition, there was a lot of fireworks in my street. However, one piece of firework resulted in a fire on the roof of our neighboors at the other site of the street. Thanks to the firework department who did their job very well.

Enjoy reading my blog the coming year.