question

swarner22 avatar image
swarner22 asked

What Should I Learn for Query Tuning

Hey guys, I was interested in the idea of perhaps tuning and increasing database performance for non profits, like charities. What skills would you recommend to obtain in order to do so, also as a freelance sql developer, if thats a thing, I'm just saying that since I love sql and its the language I am concentrating my skills on most, what other skills would be Nice to have under ones Belk if you wanted to freelance and obtain juicy contracts. I was thinking the following. > - advanced indexing - Microsoft SQL Server - 2016 and 2008 - data analytics. - Microsoft SQL SSRS - pl sql - Transact-SQL - Microsoft SQL SSAS - SQL Server Integration Services (SSIS) - R programming
sqldatabaseindexingperformance-tuning
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
1 Like 1 ·
I edited the topic so that others can join in. You don't want to limit responses to just me & Tom (although getting Tom's response is going to be helpful). I also edited the list so it shows up as a list. Feel free to fix it if I screwed it up.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Working for non-profits, most of the work will be setting up SQL Server and creating databases. There won't be much query tuning at all. But, talking about query tuning, your list is a little too much of a shotgun. Let's remove several items: - Data Analytics (when you get really advanced, you might want to apply this to query tuning, but let's work on basics first) - SSRS (no need for SSRS at all for query tuning, or for general SQL Server for that matter) - PL SQL (this is Oracle's language and won't help you with SQL Server) - SSAS (again, not needed when getting started or for query tuning) - SSIS (Same as SSRS and SSAS, however, it does have more applicability if you're getting started in SQL Server since most of your tasks are going to be more generalist, and SSIS is a useful tool, but for tuning, nah) - R (it's not out yet, don't sweat tuning this... yet) That leaves - SQL Server 2008 - 2016 - Indexes - T-SQL These are musts. Probably, your best bet, if you're just getting going, is not to go after 2016. It's not released and won't be until probably between March & May of next year. Instead, get a copy of SQL Server 2014 Developer Edition. I believe this is $60 through Amazon. Easiest way to start learning. Indexes, yes. Learn what a cluster is, a non-cluster, when they apply, when they don't, additional types of indexes & when to use them, INCLUDE, statistics, etc., etc.. Tons there. T-SQL, yes. All the things there. To these I would add: - Database design - Constraints - More T-SQL - Execution plans - Powershell - Basic server configuration - Maybe a programming language (to understand how and why developers do what they do) Tuning is a huge area. People have written books on it (ha!). There is lots to learn, but that's a rough list of where I'd spend my time. And yeah, if I was just getting started, I would learn R (I'm planning to learn it), but, since the Developer Edition of 2016 isn't available yet, don't waste time waiting, get started with 2014.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
@Grant has touched on the subject of books. He's far too modest to say that he's written some himself, so I'll say it for him. Check out Red-Gate's library of freebies - http://www.red-gate.com/community/books/#sqldev - you should find some useful books there.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered
I agree with Grant's points about putting a lot of those things aside until you understand the basics, but I want to take a step back first because you are just starting. Which basics should you start with? If you do want to focus on query tuning, then I completely agree with starting with database engine, T-SQL, and (traditional, B-tree) indexes. But the first question I typically ask is "What do you enjoy?" - Database engine: build tables and views; stored procedures; functions; indexing; performance tuning - Reporting: possibly some of the engine skills but with more focus on SSRS and/or other reporting and UI tools - Deeper analysis: T-SQL pivot; SSAS; PowerBI - Integration: SSIS to pull together potentially disparate data sets to provide a new source for reporting or other needs If you decide that engine work and query tuning are your thing, there are definitely good books, articles, videos, etc. available. Grant is a great author of some of these. Also check out Paul Randal, Kimberly Tripp, Kalen Delaney, Brent Ozar, and many others. You can find many free resources online, including a lot of nice videos at [ https://mva.microsoft.com/][1]. Also, I can't say enough about the benefits of getting involved in your local PASS chapter and attending [SQL Saturday][2] events. Participation is free. The only cost is time, but the value is priceless. [1]: https://mva.microsoft.com/ [2]: http://sqlsaturday.com/
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yeah, good gosh, how did I miss mentioning PASS. It's not like I'm on the Board of Directors and responsible for Local Chapters and will shortly be taking over SQL Saturday...what? I am? Well, fudge. Yes, absolutely take advantage of what PASS offers for free training.
2 Likes 2 ·
LOL, Grant. I want to add to my comment about PASS. Different people learn different ways. Some by doing; some by listening/watching others; some by reading. But I believe the PASS human interaction is important regardless. If nothing else, you get the opportunity to meet a lot of people who you will learn are really not that different from you. Some might be ahead of you on their career path, and others might be behind. But everyone is there because they enjoy SQL Server, community, and learning. I haven't had many training opportunities in my career and had to learn the large majority on the job or on my own time (or both). Those evenings and weekends spent with my peers have been some of the best moments of my career.
1 Like 1 ·
Marketing will contact you shortly. Ha! I do agree though. Getting involved with PASS absolutely changed my life for the better.
0 Likes 0 ·
swarner22 avatar image
swarner22 answered
haha you guys are awesome! I am glad there are fellow SQL enthusiasts out there! So I was referred to this woman who is very well connected throughout the nonprofit industry and she has plenty of contacts. I've yet to email her and if I wanted to go the "database engine route" what should I tell her. Seth
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Just be yourself and be honest. Explain what you know, what you're doing to increase your knowledge, and what your goals are. But, to quote Morpheus from The Matrix, "I can only show you the door. You’re the one that has to walk through it." Good luck. :)
1 Like 1 ·
Thanks Tom! I got the ball rolling ;)
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.