9/11/14

Are you setting up a SharePoint-farm? You better read this!

When we're talking about SQL Server there are two settings that are commonly debated. "Maximum Degree of Parallellism" and "Cost Threshold for Parallellism". Before we get to the reason of the subject and the main message I'd like to use the moment to debunk and explain these two settings, how to locate them, and how to configure them in normal setups. With that said, anything related to SQL together with SharePoint is or isn't normal, it's all up to you... ;)

"Maximum Degree of Parallellism" or MAXDOP is the amount of parallell threads a single statement can be split up into. You usually see recommendations for OLTP-Environments to change the default value of "0", which means dynamic, to recomended value of "1". This means a single thread, and my opinion on it is actually depends. I would recommend to set it to the half amount of available cores and then look at contention, and I look at physical cores, not the logical hyper-threaded ones. Some queries might suffer from parallellism, but not all. And for that you can hint the query instead, just add "OPTION (MAXDOP 1)" at the end of the statement.

"Cost Threshold for Parallellism" is the result of the Query-cost, and if the estimated cost exceeds the cost threshold the Query Optimizer tries to parallellize the execution plan. The defaulr value "5" was originally 5 seconds, today this is an calculation of cpu time, IO and Formulas depending of which optimizer method used. This means the default value is not valid anymore, and we need a new value. I usually recommend to start out with 50 and then adjust if it is to low or high for average queries.

And now over to the main subject. Did you know that when you run SharePoint there is a requirement to set "Maximum Degree of Parallellism" to "1". This is true for other systems as well, for example System Center and Dynamics. The SharePoint actually set this automatically for you. See this capture when creating a SharePoint Web Service Application:

IF (SELECT value FROM sys.configurations WHERE name = 'max degree of parallelism') <> 1
BEGIN
    exec sp_configure 'show advanced options', 1;
    RECONFIGURE WITH OVERRIDE;
    exec sp_configure 'max degree of parallelism', 1;
    RECONFIGURE WITH OVERRIDE;
END

Being a DBA this would freak me out. But this is done by design, and we have to live with it. Remember this if you are using your data warehouse server as a database host for SharePoint, or if you share databases for other uses with SharePoint. This is an obvious choice for another instance, have a specific, dedicated instance for SharePoint. And Let SharePoint do whatever SharePoint want to with that instance. The DBA can maintain the instance as any other SQL Server instance.

Remember that Resource Governor is a perfect choice for controlling resource usage on a scenario type of level, and in SQL Server 2014 it can not only manage the cpu and memory, it also can manage IOPS.

Have Fun,
Mattias

Sources

9/8/14

Ok, I want Power BI but I do not know which option?

That was the question I got, and there are actually two start scenarios. I base this on the current Power BI offering from Microsoft. Please see the official site for details, http://www.microsoft.com/en-us/powerBI/pricing.aspx.
* 40% off promotion for Office 365 E3 or E4 customers. Offered through December 31, 2014.
 
1) I have an Office 365 Enterprise E3 or E4 subscription, and want to add Power BI functionality.
  • You can add Power BI functionality for the discounted* rate of $20USD per user and month, standard price is $33USD per user and month. On the Buy Now site you just choose "Add-on for E3/E4 Subscribers".

2) I do not have an Office365 E3 or E4 Enterprise subscription, or do not want to use the one I have.
You can choose between two options, "Standalone" or "Standalone + Office 365 ProPlus". It all comes down to if you have the "Office 365 ProPlus" or the "Office 2013 Suite" or not, this is what I call the client tools.
  • With "Standalone" you get a SharePoint Online but you need your own Office-suite. Current offer is $40USD per user and month. 
  • And if you need the client tools you chose the other, "Standalone + Office 365 ProPlus". Current offer under the discount is $52USD per user and month.
After this response I got a follow-up question.

?) Do I need a Power BI license for every user?
It all comes down to what the user needs to do. Once again there are two categories.
  • "Publishers" are the users that create and share Power BI content, these users need a Power BI license.
  • "Consumers" are the users that consume the shared reports, and they just need a license for the SharePoint Online portal.
Is this a full Picture? Nope, this is basically the view right now and it might change over time. If you are going to Power BI I strongly recommend a session with an Expert that can evaluate your current need and help you decide on what to get or not.

8/19/14

Cumulative update package 3 for SQL Server 2014

Just minutes ago the latest CU-package for SQL Server 2014 got released.
It contains fixes for pretty much every single feature of SQL Server 2014.
Read the full article here, KB2984923!

8/18/14

If you don't like your query plan!

In one of the cool channels us SQLMVPs communicate through the question was raised about why do the execution plan do what they do, and we started to elaborate about tweaks and twerks, and apply hinting in one way or the other. Sometimes you need to do these ninja moves, but usually it's because your statistics are skewed and/or your cached query plan is to old and the data has changed to much. This is usually fixed by updating the statistics or rebuild/reorganize your indexes and updating the statistics. It might be that you need to revamp your indexes too. Maybe wrong cluster key och you need a non-clustered index and include some columns. This if the query you're trying to run is a common query or not. Sometimes even a filtered index might be needed, or sometimes partitioning can be of help. Major changes should only be done if this is a common and re-occuring query.

I'd like to end this post with a lovely quote:

I encourage you to consider updating statistics rather than trying syntactic rewrites. Such things worked on rule-based optimizers but it is a goal of the SQL Server QP to avoid these kinds of gymnastics to the extent possible. Focus on the plans that come out and whether the optimizer has the right info to get you the plan shape you think is best more than the syntax. -Conor Cunningham

SQLTuneIn 2014

In May 2014 I went to Zagreb in Croatia for the SQLTuneIn2014. A totally awesome Conference, I strongly recommend you guys to visit them.

It was some sunny days in Croatia, really nice to have some pre-taste of summer for a swede like myself. And there was this incredible nice hotel, and awesome SQL friends. When I arrived to the Zagreb International Airport, a small and effective Airport, I was greeted by Dean Vittner who offered me a ride to the hotel. And at the hotel we met up with Denny Cherry on the patio by the lobby. The weather and Company was so nice I almost forgot to check in to the hotel, six hours and some beers later the check in was a reality.

Anyway, on site I was informed my session slot were one hour and I just realized I had a 75 minutes session so I had to spend the days to trim the content. Like that never happens to us guys doing presentations... ;) While I was adjusting content I also wanted to add a new demo in the session. Once again, like that never happens when you go through the content and prepping for delivery... ;) I wanted to incorporate the new, cool In-Memory OLTP feature in the data model.

(Note to self: You should never, ever add stuff in the last minute, and this is even more important if you cut 20% of the content just to fit the session slot.)

The session is about Entity Framework and how you can utilize SQL Server functionality in an easy and feasible way to gain higher security and build for performance. With EF6.1 you can let the framework create procedures and specify data types as well as other SQL specific configuration. Ok, this is done with an initialization class and the SQL content it creates just delivers the functionality, and SQL wise there are alot you want to change and optimize.


At least you get the objects "for free", the cool stuff comes when you apply SQL logic, or as I would call it common sense, to the data model. We can normalize the table structures to a better data model, we can create views to mimic the former data model, and we alter the CRUD-procedures to manage the new model.



You can download the slidedeck and the demo files from this link. Download session content!

3/31/14

Changes, moving on and a new start...

For two years ago I was thinking about starting to employ people in my Company, in the same time I got an offer from Another Company. They wanted me as a database architect, mentor and specialist. After some thoughts I decided to hook up on that offer, and I did put my own company on hold. After the summer vacatíon I started at the new Company. Close to a year later I got another offer that I decided to move over to. I started working on Claremont in the end of September last year. Now six months later I do another change, after rigorous talks with Sogeti and their office in Borlänge, 45 kilometers from my home, we found a role that would suite me and Sogeti and of April 1st 2014 I'll continue my journey. I'm really excited to work much closer to home, and to work in that team at Sogeti. I will pretty much do same stuff as before, SQL Server and Business Intelligence, as a architect, mentor, speaker and instructor. I will continue to be community oriented, the only difference compared to the last two years is 200 kilometers less travel to get to my office. For the last couple of weeks we've been discussing the closest future, and I can promise you it's looking bright. In my closest team we'll be focusing on my dearest technology and with that said please be tuned. Furthermore my travel and Conference schedule is getting filled up. * April is focused on the Clash of the Titans where I and Mikael Wedham is delivering a full day SQL Server event together with Informator. * May has TechEd, and the upcoming SQL Server Tour... * June starts in Croatia for the SQL TuneIn. * July carries the MCT Summit Europe in Romania. Until next time, have fun and tune those queries!

3/21/14

Clash of the Titans

För ett par veckor sedan började vi diskutera ett event. Vi är Mikael Wedham, Tobias Strandh och undertecknad. Det är ett endagarsevent runt Microsoft SQL Server 2014 som går av stapeln den 28 april i Stockholm, mer info finns på Informators eventsida. Då det skulle passa ihop med att SQL Server 2014 alldeles i dagarna gick till RTM, Release To Manufacture, och kommer som GA, Global Availability, den första april passar det alldeles ypperligt att stå där på frontlinjen och tala om nyheterna. Det som är lite extra kul är att göra en sån här sak tillsammans med någon, och Micke är en skön typ. Förutom att Micke blev den första, enda?, i Sverige som lyckades hela vägen med att greja Microsoft Certified Master SQL Server 2008, innan MCM/MCSM-programmet lades ner i höstas, så delar vi samma twistade humor vilket bådar gott inför eventet. Men vaddå, är inte jag MCM? Nej, jag hann tyvärr inte med hela vägen. På PASS Summit förrförra året, 2012, skrev jag Knowledge Exam och klarade det, och på TechEd förra året provade jag på Lab Exam. Tyvärr räckte det inte att spontant hoppa in och köra labben vilket var ett flertal scenarier med problemställningar där man ska lösa dem i virtuella miljöer. Skulle man göra labben borde man dels vara väl förberedd, se till att man var totalt fokuserad, och vara utvilad och på allmänt topp. Jag jobbade ihop med Microsoft Learning på dagarna, och rockade runt på eventet övrig tid, och tänkte att va sjutton, visst kan man testa att skriva den där labben. Hamnade i ett certifieringsrum med femtiotalet andra personer med allehanda certifieringsbehov, där folk sprang fram och tillbaka, och med en maskin med alldeles för liten skärm och låg upplösning. Jaja, så är det ibland. Certifiering är något jag förespråkar, och något jag tycker är riktigt bra, men det är inget som jag hänger upp mitt liv på. Är fullständigt övertygad att jag skulle klara labben på andra försöket, tyvärr infann sig aldrig chansen tidsmässigt. Eftersom jag vet att Microsoft Learning jobbar på att hitta någon form av ersättning till Master-nivån väntar jag lugnt in den och tar nia tag då. Tillbaka i ordningen, det blir alltså en heldag med mig och Micke på scenen där vi om vartannat talar om nyheter och coola funktioner i SQL Server 2014, det kommer säkert med sånt som var nyheter i 2012, 2008R2 och 2008 också, sådant som vi bägge tycker är jättebra och bra att ha baserat på våra erfarenheter från verkligheten där ute. Själva upplägget blir kanske lite annorlunda mot vad man är van med, och vi lovar att ge järnet hela vägen in i kaklet. Speciellt ser jag fram emot att få ge bort en Surface till den deltagare som vinner tävlingen, det hittar du information om när du går till Informator eventsida som är länkad i början av det här inlägget. Målet med dagen är i alla fall att ha kul och komma hem med en hel del argument till varför man ska satsa på Microsoft SQL Server 2014, och givetvis lite nyvunna kunskaper.