Tips & Tricks with SQL Server Performance
Tuning, SSAS, SSRS, SSIS, and More!
By Ike Ellis, MVP @ike_ellis www.ikeellis.com
Blog.ikeellis.com
So you want to be great at SQL Server…
SQL Server Integration Services SQL Server Analysis Services
Tabular
MultiDimensional SQL Server Reporting Services Excel
Data Quality Services Service Broker Performance Tuning Indexing Query Plans Plan Analysis Memory Management SANs Network Clustering Availability Groups PowerShell
Master Data Services Architecture
Data Mart Design Data Normalization CDC
NoSQL/BigData (At least the MS Cloud Offerings) Competitive Knowledge (Oracle, Tablaeu, QlickView, Postgres)
ORMs(Entity Framework, Nhibernate, Micros)
Tips From the SQL Consultant
• For the YouTube/Reddit/Chive/Cracked/Meme generation • Lots of disjointed tips
• Popular mistakes I see or easy things I think you can take advantage of
• Between 3 – 5 minutes each
Tip #2: Five minutes on report formatting = 10x more impressive
• Spend 10 minutes on design (as opposed to the zero we typically spend)
• Choose colors wisely
– 99/100 - developers use the default color palette • HTML color picker websites
– http://www.lavishbootstrap.com • MorgueFile
Tip #3: The right way to find hardware problems
• Merging PerfMon and Tracing• Get the Batch and Completed Events Only
• Never trace from the computer you are monitoring • Always trace to a file and then load in a table after.
6
Tip #4: Lifehack: Readable Presentations
• Take the average age of the people in your audience and divide by 2: That’s your font size
Tip #5: Check for heaps/clustered indexes
SELECT t.[Name] FROM sys.Indexes i JOIN sys.Tables t
Tip #7: No reason to use ISNULL CONCAT!
Tip #8: How to search schema
• F7 • SQLSearch – Free – Download it! – http://www.red-gate.com/products/sql-development/sql-search/– Did I mention it’s free? • Dependency Tracker
– Not Free, but still cool
• select object_name(object_id), definition as name • from sys.all_sql_modules
Tip #9: Windowing Functions are pretty cool
Tip #10: SSDT Search for options
Tip #11: Scripting: You have two choices
• Two Choices
– Get good at boring repetitive tasks – Get good at PowerShell & Scripting
Tip #12: TempDB Configuration
Tip #13: Prettify!
16
http://extras.sqlservercentral.com/prettifier/prettifier.aspx
Tip #14: Execute scripts over multiple servers?
Tip #15: Life is so easy with a dates table
• Find the sales numbers for the first Monday of every month of the year
Tip #16: Try_Cast
Tip #17: Never reinvent the wheel
• Take SQL# for example • Good Documentation • Easy Syntax
Tip #18: Save scripts for easy access
• Lots of repetitive scripts with business logic
Tip #19: Enforce Business Rules in the DB
• Foreign Keys
• Unique Constraints • Check Constraints
Tip #20: Log, Log, Log (and beware of subscriptions) select c.Name , e.InstanceName , e.UserName , e.Parameters , e.TimeStart , e.TimeEnd , e.TimeDataRetrieval , e.TimeProcessing , e.TimeRendering from executionlog e join catalog c on e.reportid = c.ItemID
Ike Ellis
• http://blog.ikeellis.com
• http://www.ikeellis.com
• YouTube
– http://www.youtube.com/user/IkeEllisData
• SQL Pass Book Readers
– http://bookreaders.sqlpass.org/
• San Diego Tech Immersion Group • Twitter: @ike_ellis
• 619.922.9801
