Archive

Posts Tagged ‘Performance’

When is SELECT INTO not minimally logged?

June 20, 2015 3 comments

If you had asked me that question a month ago i would have said “Never, it is always minimally logged and this behaviour is documented in BOL’. But now I know better…

Recently i have been working on an application migration from SQL 2008 R2 –> SQL 2014 and we found a huge drop in performance for some SELECT INTO statements. I traced the issue down to the fact the operation was being fully logged and then we engaged Microsoft to work out why. Read more…

Advertisements

The anatomy of a process update – Part 2

February 21, 2013 9 comments

In part 1 of this 2 part post I shared an overview of the procedure to optimise process update. I will now walk through the optimisation process on one of our multi terabyte systems.

Part 1 shows a visualisation of process update and i have captured timings in profiler for all the stages shown and I now present my starting point for tuning below.

image

We have 3 stages that can be optimised.

  • Dimension build – This comes in at 92 seconds which is acceptable so i won’t be discussing this further but you can refer to part 1 for tuning options.
  • Partition index processing – Almost 25 minutes is spent here so tuning is required.
  • Commits – Almost 7 agonising minutes are spent waiting for the phase 2 commit with the server lock in place.

Read more…

The Anatomy of a process update – Part 1

February 9, 2013 13 comments

I have been working on optimising the process update of one of our multi terabyte systems and this 2 part post will break down the optimisation process and share with you some very interesting results.

In part 1 I will share an overview of the procedure to optimise process update and in part 2 I will apply this to our system and share some startling discoveries.

Overview

Internally process update is a complex operation and can be difficult to describe so to facilitate explanation I have created the visualisation shown below of the process which also helps to show the single threaded and multithreaded execution paths.

Read more…

SSAS Multithreaded sync with Windows 2008 R2

July 7, 2012 10 comments

We have been happily running some of our systems on Wndows 2003 and have had an upgrade to W2K8 R2 on the list for quite some time. The upgrade has now completed and we can start taking advantage of some of the new features which is the reason for this post.

For a long time we have used the sample Robocopy script from the SQLCat team to synchronize some of our larger SSAS databases. If your wondering what i mean by large, around 5 TB with a good few thousand partitions.

The script works like a dream but at this scale you soon find that the copy process starts to chug when it hits the fact data as this fantastic multi threaded script now becomes single threaded again as the bulk of the data being transferred is fact data.

Read more…

SQL Server 2008 uses half the CPU’s

July 6, 2012 Leave a comment

I recently got my hands on a couple of 4 socket servers with Intel E7-4870’s (10 cores per cpu) and with hyper threading enabled that gave me 80 logical CPU’s.

The server has Windows 2008 R2 SP1 along with SQL 2008 (Currently we can not deploy SQL 2008 R2 for the application being hosted).

When SQL Server started I noticed only 2 NUMA nodes were configured and 40 logical cores where there should have been 4 NUMA nodes and 80 logical cores (see below).

clip_image001

Read more…

Categories: Configuration, Numa, SQL Server

SSAS 2008 R2 Operations Guide

June 2, 2011 Leave a comment

What can i say but WOO HOO! The SSAS 2008 R2 Operations Guide has been published so download your copy now!

This document should be considered essential reading and contains over 100 pages of high quality information for configuring, testing and operating an Analysis Services server.

Finally i consider this document so important i have made a rare exception and gone back and added it to my Deployment Essentials post.

Categories: Analysis Services, Tips

SSAS Native v .net Provider

November 30, 2010 Leave a comment

Recently I was investigating why a new server which is in its parallel running phase was taking significantly longer to process the daily data than the server its due to replace.

The server has SQL & SSAS installed so the problem was not likely to be in the network transfer as its using shared memory. As i dug around the SQL dmv’s i noticed in sys.dm_exec_connections that the SSAS connection had a packet size of 8000 bytes instead of the usual 4096 bytes and from there i found that the datasource had been configured with the wrong provider but what was really interesting and the point of the blog is the performance difference which i have shown below.

Rows per second
.Net SqlClient Data Provide 30,000
SQL Server Native Client 10 240,000

That’s right! For a single partition, the native client was able to process 240,000 rows per second where as the .net client maxes out at 30,000. That means the SQL Native Client is 800% faster! I knew that the .net providers were slower but I had never gathered any metrics before. If your looking after a SSAS server I would definitely recommend taking a few minutes to check which provider is configured in the datasource.

Another point to consider is you may have a custom solution that is doing your ETL and utilising the .net providers. This would also be impacted by the .net provider throughput limits and a switch over to SSIS could dramatically improve your ETL.