舉報 
會員
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
推薦閱讀
- Spark編程基礎（Scala版）
- 傳感器技術實驗教程
- 大數據處理平臺
- C語言寶典
- 統計學習理論與方法：R語言版
- 數據通信與計算機網絡
- Nginx高性能Web服務器詳解
- 空間站多臂機器人運動控制研究
- 精通數據科學：從線性回歸到深度學習
- Bayesian Analysis with Python
- 在實戰中成長：C++開發之路
- Dreamweaver+Photoshop+Flash+Fireworks網站建設與網頁設計完全實用
- 步步驚“芯”
- 筆記本電腦使用與維護
- 樂高創意機器人教程（中級 上冊 10~16歲） (青少年iCAN+創新創意實踐指導叢書)
- 大數據時代的調查師
- 分布式Java應用
- 網絡信息安全項目教程
- 單片機硬件接口電路及實例解析
- Learn Power BI
- Mastering Kubernetes
- 大數據挖掘與統計機器學習
- Implementing Cisco UCS Solutions（Second Edition）
- 人，機，生活
- 初入職場之嵌入式Linux開發快速上手
- 三菱PLC編程技術及工程案例精選（第2版）
- 常用算法深入學習實錄
- Practical DevOps
- 看圖學中文版Windows XP
- Troubleshooting vSphere Storage
