舉報(bào) 
會(huì)員
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 更新時(shí)間：2021-06-24 14:39:01
推薦閱讀
- 大數(shù)據(jù)項(xiàng)目管理：從規(guī)劃到實(shí)現(xiàn)
- 大數(shù)據(jù)戰(zhàn)爭：人工智能時(shí)代不能不說的事
- 面向STEM的mBlock智能機(jī)器人創(chuàng)新課程
- 3D Printing with RepRap Cookbook
- 人工免疫算法改進(jìn)及其應(yīng)用
- Cloud Analytics with Microsoft Azure
- 網(wǎng)站前臺設(shè)計(jì)綜合實(shí)訓(xùn)
- 多媒體制作與應(yīng)用
- 液壓機(jī)智能故障診斷方法集成技術(shù)
- Spatial Analytics with ArcGIS
- Hands-On Data Warehousing with Azure Data Factory
- PowerMill 2020五軸數(shù)控加工編程應(yīng)用實(shí)例
- Creating ELearning Games with Unity
- Natural Language Processing and Computational Linguistics
- 玩機(jī)器人 學(xué)單片機(jī)
- Linux常用命令簡明手冊
- 網(wǎng)頁設(shè)計(jì)與制作
- NetSuite ERP for Administrators
- 細(xì)節(jié)決定交互設(shè)計(jì)的成敗
- 單片機(jī)C語言編程實(shí)踐
- Practical Autodesk AutoCAD 2021 and AutoCAD LT 2021
- Fedora 31 Essentials
- Learning Docker（Second Edition）
- Gitolite Essentials
- Excel VBA語法與應(yīng)用手冊
- 絕美Maya
- 數(shù)據(jù)庫應(yīng)用基礎(chǔ)：Visual FoxPro 6.0計(jì)算機(jī)網(wǎng)絡(luò)基礎(chǔ)
- 電子商務(wù)網(wǎng)站安全與維護(hù)
- UG NX 5中文版完美自學(xué)手冊
- Ceph Cookbook（Second Edition）
