May 26, 2016 at 2:29 am
Hi,
with recent events and a poor first stab at performance tuning I am very interested in looking at performance tuning for what I consider to be small Data warehouses. What's a small data warehouse you say - well for my purposes it is definatelly not a mature warehouse that has full DBA support, it is more of a young warehouse built by junior BI dudes. It probably has very few keys, few indexes if any, limited back up and limited maintenance.
What I am looking to learn is how to assess the performance
Strategys for improving the performance
Potential hazards along the road to faster performance.
All of the warehouses I work on have to cope with ETL routines and reporting requirements. Most of the recent warehouses seem to have no OLAP element si I am now having to balance the morning load requirements with reports running queries against the warehouse.
An example of what I am looking for is this weeks puzzle below.
I have an 80million row table holding 100GB of data, 38 GB of index, and 97 columns. This has three indexes, and no primary key. The clustered index is not unique and can have nulls so not the best. No Partitions. The favourate query is SELECT *
I will be building a similar table on a laptop so I can have a play well away from Dev or Live. The laptop has SQL Sentry on it so this could be a good time to play with that as well.
Is there a dummies guide to performance tuning available somewhere on the web (perferably from a reputable dude). Once I have got through the dummies guide is there a good source for an intermediate guide that I can look at.
Suggestions with reasoning for where to look first for the current issue would be fab but more interested in the long term.
Cheers
E
May 26, 2016 at 3:13 am
Here you go: http://www.amazon.com/SQL-Server-Query-Performance-Tuning/dp/1430267437/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2016 at 3:50 am
Just had a look through the contents list and that looks like its goin to do me just fine.
Thanks
E
May 26, 2016 at 8:08 am
If you hit specific questions, you can always post them here on SQL Server Central and you'll get help.
If you have specific questions about the book, don't hesitate to get in touch with me.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 26, 2016 at 8:20 am
Thank you,
I hope to enjoy the journey through the book. I also noted there was short session SITC LDN 2014 on you tube so will watch that when I fopnt feel like reading
Thanks
E
May 26, 2016 at 2:00 pm
That's the most I have ever spent on a Kindle book!
That's an observation and a compliment not a complaint. If it saves me one hour of grief it will have paid for itself!
Thanks
E
May 26, 2016 at 3:38 pm
If it makes you feel better, I don't set the price.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 26, 2016 at 3:41 pm
Oh, and head over to Redgate Software. We offer a ton of free e-books. There are a couple that are worth reading. Gail was an author (or tester, I forget) for the "Accidental DBA" book which is excellent. I also have another book just on execution plans. Only make sure you're getting the 2nd Edition. The 1st edition was awful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 27, 2016 at 8:27 am
Grant, how do you know which edition you have? The latest one says 'First published ... Sept 2011' but doesn't say if it's first or second edition.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 27, 2016 at 8:35 am
Copyright on it would be 2014 I think. Mine says " - Second Edition" on the spine.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 27, 2016 at 8:39 am
Then I think the PDF on the Redgate site is first edition.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 27, 2016 at 8:43 am
http://www.sqlservercentral.com/articles/books/94937/
this is the second edition
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 27, 2016 at 8:56 am
The most important thing to keep in mind about performance tuning is: Be sure you're tuning what needs it.
I've seen months and fortunes wasted tuning things that nobody cared about except the person doing the work.
Be sure you tune based on RoI and actual business-needs, not just on "this DMV tells me that this query takes a long time".
After that, I've found tools like Ignite very useful for identifying problem queries and priorities.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply