sql server tips & tricks
DESCRIPTION
Tips on SQL Server, Integration Services, Reporting Services, T-SQL, and PowerShellTRANSCRIPT
Tips & Tricks with SQL Server Performance Tuning, SSAS, SSRS, SSIS, and More!
By Ike Ellis, MVP@ike_ellis
www.ikeellis.comBlog.ikeellis.com
http://www.linkedin.com/in/ikeellis
So you want to be great at SQL Server…SQL Server Integration ServicesSQL Server Analysis Services
TabularMultiDimensional
SQL Server Reporting ServicesExcelData Quality ServicesService BrokerPerformance Tuning
IndexingQuery PlansPlan AnalysisMemory ManagementSANsNetwork
ClusteringAvailability GroupsPowerShellMaster Data ServicesArchitectureData Mart DesignData NormalizationCDCNoSQL/BigData (At least the MS Cloud Offerings)Competitive Knowledge (Oracle, Tablaeu, QlickView, Postgres)ORMs(Entity Framework, Nhibernate, Micros)Installation/Configuration/Upgrading/Service Packing
Power BIPowerMapPowerQueryPowerView
PowerPivotT-SQL
QueryingStored ProceduresFunctionsWindowing FunctionsAggregates
CLRMDXDAXXMLABCPSQL AzureTooling
RedgateSSMSSSDT
Past VersionsCentral ManagementDacPacs/BacPacsProfiler/Extended EventsAuditingSecurity/EncryptionReplicationSQLCMD
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• Let’s see if we can get through all 20
Tip #1: SSIS for the Color Blind
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– http://www.morguefile.com/
6
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.
*Thanks, Grant!
Tip #4: Lifehack: Readable Presentations
• Take the average age of the people in your audience and divide by 2: That’s your font size
•USE THIS SIZE IF YOUR AUDIENCE IS 200
Tip #5: Check for heaps/clustered indexes
SELECT t.[Name] FROM sys.Indexes i JOIN sys.Tables t ON t.Object_ID = i.Object_idWHERE i.type_desc = 'HEAP'ORDER BY t.[Name]
Tip #6: The proper way to run an SSIS package
Tip #7: No reason to use ISNULL CONCAT!
• Messy vs clean code• No + symbol needed• No ISNULL needed
Tip #8: How to search schema
• F7• SQLSearch– Free– Download it!– http://www.red-gate.com/products/sql-de
velopment/sql-search/
– Did I mention it’s free?• Dependency Tracker– Not Free, but still cool
Tip #9: Windowing Functions are pretty cool
• They are worth learning, and have a neat evolution
Tip #10: SSDT Search for options
• No more digging around in options• Just search for everything
Tip #11: Scripting: You have two choices
• Two Choices– Get good at boring repetitive tasks– Get good at PowerShell & Scripting
• Who adds more value to their company or their customers?
• Who gets paid more?
Tip #12: TempDB Configuration
• Current thought is 4 logical processors to 1 file
• Just a good beginning, your mileage may very
• Start there, then go to 2:1 or 1:1 if necessary
16
Tip #13: Prettify!
http://extras.sqlservercentral.com/prettifier/prettifier.aspx
RedGate PlugIn for SQL Server Management Studio
Tip #14: Execute scripts over multiple servers?
• Super easy!
Tip #15: Life is so easy with a dates table
• Find the sales numbers for the first Monday of every month of the year
• T-SQL with no dates table• T-SQL with dates table
Tip #16: Try_Cast
• Avoiding terrible casting errors
Tip #17: Never reinvent the wheel
• Take SQL# for example• Good Documentation• Easy Syntax• Cheap (and much of it is free)
Tip #18: Save scripts for easy access
• Lots of repetitive scripts with business logic
• No reason to write the same queries for the same tables day after day
22
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.TimeRenderingfrom executionlog e join catalog c on e.reportid = c.ItemID
Send a Link, or a file on a shared folder that you can audit. Find someway to audit who opened the link or the file in the folder. Try to avoid sending the PDF without a way to audit it.
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• Email address is just my first name
@ikeellis.com