舉報 
會員
Learn T-SQL Querying
Transact-SQL(T-SQL)isMicrosoft'sproprietaryextensiontotheSQLlanguagethatisusedwithMicrosoftSQLServerandAzureSQLDatabase.ThisbookwillbeausefulguidetolearningtheartofwritingefficientT-SQLcodeinmodernSQLServerversions,aswellastheAzureSQLDatabase.Thebookwillgetyoustartedwithqueryprocessingfundamentalstohelpyouwritepowerful,performantT-SQLqueries.Youwillthenfocusonqueryexecutionplansandlearnhowtoleveragethemfortroubleshooting.Inthelaterchapters,youwilllearnhowtoidentifyvariousT-SQLpatternsandanti-patterns.Thiswillhelpyouanalyzeexecutionplanstogaininsightsintocurrentperformance,anddeterminewhetherornotaqueryisscalable.Youwillalsolearntobuilddiagnosticqueriesusingdynamicmanagementviews(DMVs)anddynamicmanagementfunctions(DMFs)toaddressvariouschallengesinT-SQLexecution.Next,youwillstudyhowtoleveragethebuilt-intoolsofSQLServertoshortenthetimetakentoaddressqueryperformanceandscalabilityissues.Intheconcludingchapters,thebookwillguideyouthroughimplementingvariousfeatures,suchasExtendedEvents,QueryStore,andQueryTuningAssistantusinghands-onexamples.Bytheendofthisbook,youwillhavetheskillstodeterminequeryperformancebottlenecks,avoidpitfalls,anddiscovertheanti-patternsinuse.ForewordbyConorCunningham,PartnerArchitect–SQLServerandAzureSQL–Microsoft
目錄(236章)
倒序
- coverpage
- Title Page
- Copyright and Credits
- Learn T-SQL Querying
- About Packt
- Why subscribe?
- Packt.com
- Foreword
- Contributors
- About the authors
- About the reviewers
- Packt is searching for authors like you
- Preface
- Who this book is for
- What this book covers
- To get the most out of this book
- Download the example code files
- Download the color images
- Conventions used
- Get in touch
- Reviews
- Section 1: Query Processing Fundamentals
- Anatomy of a Query
- Building blocks of a T-SQL statement
- SELECT
- DISTINCT
- TOP
- FROM
- INNER JOIN
- OUTER JOIN
- CROSS JOIN
- APPLY
- WHERE
- ORDER BY
- GROUP BY
- HAVING
- Logical statement processing flow
- Summary
- Understanding Query Processing
- Query compilation essentials
- Query optimization essentials
- Query execution essentials
- Plan caching and reuse
- Stored procedures
- Ad hoc plan caching
- Parameterization
- Simple parameterization
- Forced parameterization
- The sp_executesql procedure
- Prepared statements
- How query processing impacts plan reuse
- The importance of parameters
- Security
- Performance
- Parameter sniffing
- To cache or not to cache
- Summary
- Mechanics of the Query Optimizer
- Introducing the Cardinality Estimator
- Understanding the query optimization workflow
- The Trivial Plan stage
- The Exploration stage
- Transaction Processing
- Quick plan
- Full optimization
- Knobs for query optimization
- Summary
- Section 2: Dos and Donts of T-SQL
- Exploring Query Execution Plans
- Accessing a query plan
- Navigating a query plan
- Query plan operators of interest
- Blocking versus non-blocking operators
- Data-access operators
- Table Scan
- Clustered Index Scan
- NonClustered Index Scan
- NonClustered Index Seek
- Clustered Index Seek
- Lookups
- RID Lookups
- Key Lookups
- Columnstore Index Scan
- Joins
- Nested Loops joins
- Merge Joins
- Hash Match joins
- Adaptive Joins
- Spools
- Sorts and aggregation
- Sorts
- Stream aggregation
- Hash aggregation
- Query plan properties of interest
- Plan-level properties
- Cardinality estimation model version
- Degree of Parallelism*
- Memory Grant*
- MemoryGrantInfo
- Optimization Level
- OptimizerHardwareDependentProperties
- OptimizerStatsUsage
- QueryPlanHash
- QueryHash
- Set options
- Statement
- TraceFlags
- WaitStats
- QueryTimeStats*
- MissingIndexes
- Parameter List
- Warnings*
- PlanAffectingConvert
- WaitForMemoryGrant*
- MemoryGrantWarning*
- SpatialGuess*
- UnmatchedIndexes*
- FullUpdateForOnlineIndexBuild
- Operator-level properties
- RunTimeCountersPerThread
- Actual I/O Statistics*
- Actual Number of Rows
- Actual Time Statistics
- Estimated rows
- EstimateRowsWithoutRowGoal
- Warnings*
- Columns With No Statistics*
- Spill To TempDb
- No Join Predicate
- Summary
- Writing Elegant T-SQL Queries
- Understanding predicate SARGability
- Basic index guidelines
- Clustered indexes
- Non-clustered indexes
- INCLUDE columns
- Filtered indexes
- Unique versus non-unique
- Columnstore indexes
- Indexing strategy
- Data structure considerations
- Database usage considerations
- Query considerations
- Best practices for T-SQL querying
- Referencing objects
- Joining tables
- Using NOLOCK
- Using cursors
- Summary
- Easily-Identified T-SQL Anti-Patterns
- The perils of SELECT *
- Functions in our predicate
- Deconstructing table-valued functions
- Complex expressions
- Optimizing OR logic
- NULL means unknown
- Fuzzy string matching
- Inequality logic
- EXECUTE versus sp_executesql
- Composable logic
- Summary
- Discovering T-SQL Anti-Patterns in Depth
- Implicit conversions
- Avoiding unnecessary sort operations
- UNION ALL versus UNION
- SELECT DISTINCT
- SELECT TOP 1 with ORDER BY
- Avoiding UDF pitfalls
- Avoiding unnecessary overhead with stored procedures
- Pitfalls of complex views
- Pitfalls of correlated sub-queries
- Properly storing intermediate results
- Using table variables and temporary tables
- Using Common Table Expressions
- Summary
- Section 3: Assemble Your Query Troubleshooting Toolbox
- Building Diagnostic Queries Using DMVs and DMFs
- Introducing Dynamic Management Views
- Exploring query execution DMVs
- sys.dm_exec_sessions
- sys.dm_exec_requests
- sys.dm_exec_sql_text
- sys.dm_os_waiting_tasks
- Exploring query plan cache DMVs
- sys.dm_exec_query_stats
- sys.dm_exec_procedure_stats
- sys.dm_exec_query_plan
- sys.dm_exec_cached_plans
- Troubleshooting common scenarios with DMV queries
- Investigating blocking
- Cached query plan issues
- Single-use plans (query fingerprints)
- Finding resource intensive queries
- Queries with excessive memory grants
- Mining XML query plans
- Plans with missing indexes
- Plans with warnings
- Plans with implicit conversions
- Plans with lookups
- Summary
- Building XEvent Profiler Traces
- Introducing Extended Events
- SQL Server Profiler – deprecated but not forgotten
- Getting up and running with XEvent Profiler
- Remote collection with PSSDiag and SQLDiag
- Analyzing traces with RML Utilities
- Summary
- Comparative Analysis of Query Plans
- Query Plan Comparison
- Query Plan Analyzer
- Summary
- Tracking Performance History with Query Store
- The Query Store
- Inner workings of the Query Store
- Configuring the Query Store
- Tracking expensive queries
- Fixing regressed queries
- Summary
- Troubleshooting Live Queries
- Using Live Query Statistics
- Understanding the need for Lightweight Profiling
- Diagnostics available with Lightweight Profiling
- The query_thread_profile XEvent
- The query_plan_profile XEvent
- The query_post_execution_plan_profile XEvent
- The sys.dm_exec_query_statistics_xml DMF
- The sys.dm_exec_query_plan_stats DMF
- Activity Monitor gets a new life
- Summary
- Managing Optimizer Changes with the Query Tuning Assistant
- Understanding where QTA is needed
- Understanding QTA fundamentals
- Exploring the QTA workflow
- Summary
- Other Books You May Enjoy
- Leave a review - let other readers know what you think 更新時間：2021-06-24 14:39:01
推薦閱讀
- GNU-Linux Rapid Embedded Programming
- Mastering Mesos
- SCRATCH與機器人
- 計算機控制技術
- VB語言程序設計
- 工業機器人現場編程（FANUC）
- 人工智能趣味入門：光環板程序設計
- Deep Reinforcement Learning Hands-On
- Linux嵌入式系統開發
- AI的25種可能
- 納米集成電路制造工藝（第2版）
- Appcelerator Titanium Smartphone App Development Cookbook（Second Edition）
- SolarWinds Server & Application Monitor：Deployment and Administration
- Learning OpenShift
- PVCBOT零基礎機器人制作（第2版）
- 電氣自動化工程師自學寶典（基礎篇）
- Learn Power BI
- 自動化生產線組建與調試（第2版）：以亞龍YL-335B為例（三菱PLC版本）
- 數據庫技術：Access 2003 計算機網絡技術
- 無線傳感器網絡技術及應用
- 無線傳感器網絡節能、優化與可生存性
- Java ME嵌入式程序設計
- 大數據可視化技術
- Hands-On Predictive Analytics with Python
- 三維動畫綜合實訓
- 輕松學HTML+CSS網站開發
- 大道至簡：軟件工程實踐者的思想
- 應用智能運維實踐
- 電腦高手應用終極技巧金典
- 電子商務網頁設計與制作
