skip to content
Pro SQL server internals Preview this item
ClosePreview this item
Checking...

Pro SQL server internals

Author: Dmitri Korotkevitch
Publisher: Berkeley, CA : Apress, New York, NY : Distributed to the Book trade worldwide by Springer 2014. ©2014
Series: Expert's voice in SQL server.
Edition/Format:   eBook : Document : EnglishView all editions and formats
Summary:
Explains how different SQL Server components work internally and how they communicate with each other. Presents a large number of examples that will show how various design and implementation decisions affect the behavior and performance of systems. Covers multiple SQL Server versions from SQL Server 2005 through SQL Server 2014. The book covers new SQL Server 2014 features including the new Cardinality Estimator.  Read more...
Rating:

(not yet rated) 0 with reviews - Be the first.

Subjects
More like this

Find a copy in the library

&AllPage.SpinnerRetrieving; Finding libraries that hold this item...

Details

Genre/Form: Electronic books
Additional Physical Format: Print version:
Material Type: Document, Internet resource
Document Type: Internet Resource, Computer File
All Authors / Contributors: Dmitri Korotkevitch
ISBN: 9781430259633 1430259639
OCLC Number: 883297697
Description: 1 online resource (xxv, 750 pages) : illustrations.
Contents: Machine generated contents note: pt. 1 Tables and Indexes --
ch. 1 Data Storage Internals --
Database Files and Filegroups --
Data Pages and Data Rows --
Large Objects Storage --
Row-Overflow Storage --
LOB Storage --
SELECT* and I/O --
Extents and Allocation Map Pages --
Data Modifications --
Much Ado About Data Row Size --
Table Alteration --
Summary --
ch. 2 Tables and Indexes: Internal Structure and Access Methods --
Heap Tables --
Clustered Indexes --
Composite Indexes --
Nonclustered Indexes --
Summary --
ch. 3 Statistics --
Introduction to SQL Server Statistics --
Column-Level Statistics --
Statistics and Execution Plans --
Statistics and Query Memory Grants --
Statistics Maintenance --
SQL Server 2014 Cardinality Estimator --
Comparing Cardinality Estimators: Up-to-Date Statistics --
Comparing Cardinality Estimators: Outdated Statistics --
Comparing Cardinality Estimators: Indexes with Ever-Increasing Key Values --
Comparing Cardinality Estimators: Joins --
Comparing Cardinality Estimators: Multiple Predicates --
Summary --
ch. 4 Special Indexing and Storage Features --
Indexes with Included Columns --
Filtered Indexes --
Filtered Statistics --
Calculated Columns --
Data Compression --
Row Compression --
Page Compression --
Performance Considerations --
Sparse Columns --
Summary --
ch. 5 Index Fragmentation --
Types of Fragmentation --
FILLFACTOR and PAD_INDEX --
Index Maintenance --
Designing an Index Maintenance Strategy --
Patterns that Increase Fragmentation --
Summary --
ch. 6 Designing and tuning the Indexes --
Clustered Index Design Considerations --
Design Guidelines --
Identities, Sequences, and Uniqueidentifiers --
Nonclustered Indexes Design Considerations --
Optimizing and Tuning Indexes --
Detecting Unused and Inefficient Indexes --
Index Consolidation --
Detecting Suboptimal Queries --
Summary --
pt. 2 Other things that matter --
ch. 7 Constraints --
Primary Key Constraints --
Unique Constraints --
Foreign Key Constraints --
Check Constraints --
Wrapping Up --
ch. 8 Triggers --
DML Triggers --
DDL Triggers --
Logon Triggers --
UPDATE() and C0LUMNS_UPDATED() functions --
Nested and Recursive Triggers --
First and Last Triggers --
CONTEXT_INFO --
Summary --
ch. 9 Views --
Regular Views --
Indexed Views --
Partitioned Views --
Updatable Views --
Summary --
ch. 10 User-Defined Functions --
Multi-Statement Functions --
Inline Table-Valued Functions --
Summary --
ch. 11 XML --
To Use or Not to Use XML? That Is the Question! --
XML Data Type --
Working with XML Data --
Value() Method --
Exists() Method --
Query() Method --
Nodes() Method --
Modify() Method --
OPENXML --
FOR XML --
Summary --
ch. 12 Temporary Tables --
Temporary Tables --
Table Variables --
User-Defined Table Types and Table-Valued Parameters --
Regular Tables in tempdb --
Optimizing tempdb Performance --
Summary --
ch. 13 CLR --
CLR Integration Overview --
Security Considerations --
Performance Considerations --
Summary --
ch. 14 CLR Types --
User-Defined CLR Types --
Spatial Data Types --
Hierarchyld --
Summary --
ch. 15 Data Partitioning --
Reasons to Partition Data --
When to Partition? --
Data Partitioning Techniques --
Partitioned Tables --
Partitioned Views --
Comparing Partitioned Tables and Partitioned Views --
Using Partitioned Tables and Views Together --
Tiered Storage --
Moving Non-Partitioned Tables Between Filegroups --
Moving Partitions Between Filegroups --
Moving Data Files Between Disk Arrays --
Tiered Storage in Action --
Tiered Storage and High Availability Technologies --
Implementing Sliding Window Scenario and Data Purge --
Potential Issues --
Summary --
ch. 16 System Design Considerations --
General System Architecture --
Data Access Layer Design --
Connection Pooling --
Working with Database Tables Directly --
Database Views --
Stored Procedures --
Code Generators and ORM Frameworks --
Analyzing Microsoft Entity Framework 6 --
Connections and Transactions --
Executing Stored Procedures and Queries --
Data Loading --
Parameterization --
IN Lists --
Deletions --
Optimistic Concurrency --
Conclusions --
Summary --
pt. 3 Locking, Blocking and Concurrency --
ch. 17 Lock Types --
ch. 18 Troubleshooting Blocking Issues --
General Troubleshooting Approach --
Troubleshooting Blocking Issues in Real Time --
Collecting Blocking Information for Further Analysis --
Summary --
ch. 19 Deadlocks --
Classic Deadlock --
Deadlock Due to Non-Optimized Queries --
Deadlock Due to Simultaneous Read/Update Statements --
Deadlock Due to Multiple Updates of the Same Row --
Deadlock Troubleshooting --
Reducing the Chance of Deadlocks --
Summary --
ch. 20 Lock Escalation --
Lock Escalation Overview --
Lock Escalation Troubleshooting --
Summary --
ch. 21 Optimistic Isolation Levels --
Row Versioning Overview --
Optimistic Transaction Isolation Levels --
READ COMMITTED SNAPSHOT Isolation Level --
SNAPSHOT Isolation Level --
Version Store Behavior --
Summary --
ch. 22 Application Locks --
ch. 23 Schema Locks --
Schema Modification Locks --
Multiple Sessions and Lock Compatibility --
Lock Partitioning --
Low-Priority Locks (SQL Server 2014) --
Summary --
ch. 24 Designing Transaction Strategies --
pt. 4 Query Life Cycle --
ch. 25 Query Optimization and Execution --
Query Life Cycle --
Query Optimization --
Query Execution --
Operators --
Joins --
Aggregates --
Spools --
Parallelism --
Query and Table Hints --
INDEX Query Hint --
FORCE ORDER Hint --
LOOP, MERGE, and HASH JOIN Hints --
FORCESEEK/FORCESCAN Hints --
NOEXPAND/EXPAND VIEWS Hints --
FAST N Hints --
Summary --
ch. 26 Plan Caching --
Plan Caching Overview --
Parameter Sniffing --
Plan Reuse --
Plan Caching for Ad-Hoc Queries --
Auto-Parameterization --
Plan Guides --
Plan Cache Internals --
Examining Plan Cache --
Summary --
pt. 5 Practical Troubleshooting --
ch. 27 System Troubleshooting --
Looking at the Big Picture --
Hardware and Network --
Operating System Configuration --
SQL Server Configuration --
Database Options --
SQL Server Execution Model --
Wait Statistics Analysis and Troubleshooting --
I/O Subsystem and Non-Optimized Queries --
Memory-Related Wait Types --
High CPU Load --
Parallelism --
Locking and Blocking --
Worker Thread Starvation --
ASYNC_NETWORK_IO Waits --
Allocation Map Contention and Tempdb load --
Wrapping Up --
What to Do When the Server Is Not Responding --
Working with Baseline --
Summary --
ch. 28 Extended Events --
Extended Events Overview --
Extended Events Objects --
Packages --
Events --
Predicates --
Actions --
Types and Maps --
Targets --
Creating Events Sessions --
Working with Event Data --
Working with the ring_buffer Target --
Working with event_file and asynchronous_file_target Targets --
Working with event_counter and synchronous_event_counter Targets --
Working with histogram, synchronous_bucketizer, and asynchronous_bucketizer Targets --
Working with the pair_matching Target --
System_health and AlwaysOn_Health Sessions --
Detecting Expensive Queries --
Summary --
pt. 6 Inside the transaction log --
ch. 29 Transaction Log Internals --
Data Modifications, Logging, and Recovery --
Delayed Durability (SQL Server 2014) --
Virtual Log Files --
Database Recovery Models --
TempDB Logging --
Excessive Transaction Log Growth --
Transaction Log Management --
Summary --
ch. 30 Designing a Backup Strategy --
Database Backup Types --
Backing Up the Database --
Restoring the Database --
Restore to a Point in Time --
Restore with StandBy --
Designing a Backup Strategy --
Partial Database Availability and Piecemeal Restore --
Partial Database Backup --
Backup to Windows Azure --
Managed Backup to Windows Azure --
Summary --
ch. 31 Designing a High Availability Strategy --
SQL Server Failover Cluster --
Database Mirroring --
AlwaysOn Availability Groups --
Log Shipping --
Replication --
Designing a High Availability Strategy --
Summary --
pt. 7 In-Memory OLTP Engine --
ch. 32 In-Memory OLTP Internals --
Why Hekaton? --
In-Memory OLTP Engine Architecture and Data Structures --
Memory-Optimized Tables --
High-Availability Technology Support --
Data Row Structure --
Hash Indexes --
Range Indexes --
Statistics on Memory-Optimized Tables --
Garbage Collection --
Transactions and Concurrency --
Transaction Isolation Levels and Data Consistency --
In-Memory OLTP Transaction Isolation Levels --
Cross-Container Transactions --
Data Access, Modifications, and Transaction Lifetime --
Transaction Logging --
Data Durability and Recovery --
Memory Usage Considerations --
Summary --
pt. 8 Columnstore Indexes --
ch. 33 In-Memory OLTP Programmability --
Native Compilation --
Natively-Compiled Stored Procedures --
Optimization of Natively-Compiled Stored Procedures --
Creating Natively-Compiled Stored Procedures --
Supported T-SQL Features --
Execution Statistics --
Interpreted T-SQL and Memory-Optimized Tables --
Memory-Optimized Table Types and Variables --
In-Memory OLTP: Implementation Considerations --
Summary --
ch. 34 Introduction to Columnstore Indexes --
Data Warehouse Systems Overview --
Columnstore Indexes and Batch-Mode Processing Overview --
Column-Based Storage and Batch-Mode Execution --
Columnstore Indexes and Batch-Mode Execution in Action --
Columnstore Index Internals --
Nonclustered Columnstore Indexes --
Data Storage Note continued: Metadata --
Design Considerations and Best Practices for Columnstore Indexes --
Reducing Data Row Size --
Giving SQL Server as Much Information as Possible --
Maintaining Statistics --
Avoiding String Columns in Fact Tables --
Summary --
ch. 35 Clustered Columnstore Indexes --
Internal Structure of Clustered Columnstore Indexes --
Internal Structure --
Data Load --
Delta Store and Delete Bitmap --
Archival Compression --
Clustered Columnstore Index Maintenance --
Excessive Number of Partially Populated Row Groups --
Large Delta Stores --
Large Delete Bitmap --
Index Maintenance Options --
SQL Server 2014 Batch-Mode Execution Enhancements --
Design Considerations --
Summary.
Series Title: Expert's voice in SQL server.
Responsibility: Dmitri Korotkevitch.
More information:

Abstract:

Explains how different SQL Server components work internally and how they communicate with each other. Presents a large number of examples that will show how various design and implementation decisions affect the behavior and performance of systems. Covers multiple SQL Server versions from SQL Server 2005 through SQL Server 2014. The book covers new SQL Server 2014 features including the new Cardinality Estimator. Topics covered include: designing efficient database schema, indexing, and transaction strategies and solutions utilizing SQL Server as the database backend; how various database objects and technologies are implemented internally and when they should or should not be used; how SQL Server executes queries and works with data and transaction logs; troubleshooting and addressing design, concurrency, and performance issues in systems; improving system availability and manageability and reduce implementation costs with data partitioning; designing efficient disaster recovery and high-availability strategies for systems; improving the performance of OLTP and data warehouse systems by utilizing new SQL Server 2014 features, such as the In-Memory OLTP Engine (codename Hekaton) and Clustered Columnstore Indexes. This book is for developers and database administrators.

Reviews

User-contributed reviews
Retrieving GoodReads reviews...
Retrieving DOGObooks reviews...

Tags

Be the first.
Confirm this request

You may have already requested this item. Please select Ok if you would like to proceed with this request anyway.

Linked Data


Primary Entity

<http://www.worldcat.org/oclc/883297697> # Pro SQL server internals
    a schema:MediaObject, schema:Book, schema:CreativeWork ;
    library:oclcnum "883297697" ;
    library:placeOfPublication <http://id.loc.gov/vocabulary/countries/cau> ;
    rdfs:comment "Warning: This malformed URI has been treated as a string - 'https://ebookcentral.proquest.com/lib/unt/detail.action?docID=1781931";'" ;
    schema:about <http://experiment.worldcat.org/entity/work/data/1927210495#CreativeWork/sql_server> ; # SQL server.
    schema:about <http://experiment.worldcat.org/entity/work/data/1927210495#Topic/database_management> ; # Database management
    schema:about <http://experiment.worldcat.org/entity/work/data/1927210495#Topic/computers_databases_general> ; # COMPUTERS--Databases--General
    schema:about <http://experiment.worldcat.org/entity/work/data/1927210495#Topic/computers_client_server_computing> ; # COMPUTERS--Client-Server Computing
    schema:about <http://experiment.worldcat.org/entity/work/data/1927210495#Topic/computers_programming_languages_sql> ; # COMPUTERS--Programming Languages--SQL
    schema:about <http://dewey.info/class/005.7585/e23/> ;
    schema:about <http://experiment.worldcat.org/entity/work/data/1927210495#Topic/client_server_computing> ; # Client/server computing
    schema:author <http://experiment.worldcat.org/entity/work/data/1927210495#Person/korotkevitch_dmitri> ; # Dmitri Korotkevitch
    schema:bookFormat schema:EBook ;
    schema:datePublished "2014" ;
    schema:description "Explains how different SQL Server components work internally and how they communicate with each other. Presents a large number of examples that will show how various design and implementation decisions affect the behavior and performance of systems. Covers multiple SQL Server versions from SQL Server 2005 through SQL Server 2014. The book covers new SQL Server 2014 features including the new Cardinality Estimator. Topics covered include: designing efficient database schema, indexing, and transaction strategies and solutions utilizing SQL Server as the database backend; how various database objects and technologies are implemented internally and when they should or should not be used; how SQL Server executes queries and works with data and transaction logs; troubleshooting and addressing design, concurrency, and performance issues in systems; improving system availability and manageability and reduce implementation costs with data partitioning; designing efficient disaster recovery and high-availability strategies for systems; improving the performance of OLTP and data warehouse systems by utilizing new SQL Server 2014 features, such as the In-Memory OLTP Engine (codename Hekaton) and Clustered Columnstore Indexes. This book is for developers and database administrators."@en ;
    schema:exampleOfWork <http://worldcat.org/entity/work/id/1927210495> ;
    schema:genre "Electronic books"@en ;
    schema:inLanguage "en" ;
    schema:isPartOf <http://experiment.worldcat.org/entity/work/data/1927210495#Series/the_expert_s_voice_in_sql_server> ; # The expert's voice in SQL server
    schema:isPartOf <http://experiment.worldcat.org/entity/work/data/1927210495#Series/expert_s_voice_in_sql_server> ; # Expert's voice in SQL server.
    schema:isSimilarTo <http://worldcat.org/entity/work/data/1927210495#CreativeWork/> ;
    schema:name "Pro SQL server internals"@en ;
    schema:productID "883297697" ;
    schema:url <http://proquest.safaribooksonline.com/9781430259633> ;
    schema:url <http://site.ebrary.com/id/10890834> ;
    schema:url <http://www.myilibrary.com?id=644439> ;
    schema:url <https://link.springer.com/openurl?genre=book&isbn=978-1-4302-5962-6> ;
    schema:url <http://www.books24x7.com/marc.asp?bookid=66167> ;
    schema:url <http://dx.doi.org/10.1007/978-1-4302-5963-3> ;
    schema:url <http://link.springer.com/10.1007/978-1-4302-5963-3> ;
    schema:url "https://ebookcentral.proquest.com/lib/unt/detail.action?docID=1781931";" ;
    schema:url <http://public.eblib.com/choice/publicfullrecord.aspx?p=1781931> ;
    schema:url <http://www.myilibrary.com?id=992828> ;
    schema:url <https://login.libproxy.uregina.ca:8443/login?url=https://link.springer.com/openurl?genre=book&isbn=978-1-4302-5962-6> ;
    schema:url <http://resolver.library.cornell.edu/cgi-bin/EBookresolver?set=Books24x7&id=66167> ;
    schema:url <http://search.ebscohost.com/login.aspx?direct=true&scope=site&db=nlebk&db=nlabk&AN=1173856> ;
    schema:url <http://public.ebookcentral.proquest.com/choice/publicfullrecord.aspx?p=1781931> ;
    schema:url <http://proquest.tech.safaribooksonline.de/9781430259633> ;
    schema:url <http://swbplus.bsz-bw.de/bsz417229372cov.htm> ;
    schema:url <http://link.springer.com/openurl?genre=book&isbn=978-1-4302-5962-6> ;
    schema:url <http://books.scholarsportal.info/viewdoc.html?id=/ebooks/ebooks3/springer/2014-09-10/1/9781430259633> ;
    schema:url <http://catdir.loc.gov/catdir/enhancements/fy1603/2015451884-t.html> ;
    schema:url <https://link.springer.com/10.1007/978-1-4302-5963-3> ;
    schema:workExample <http://worldcat.org/isbn/9781430259633> ;
    schema:workExample <http://dx.doi.org/10.1007/978-1-4302-5963-3> ;
    wdrs:describedby <http://www.worldcat.org/title/-/oclc/883297697> ;
    .


Related Entities

<http://experiment.worldcat.org/entity/work/data/1927210495#Person/korotkevitch_dmitri> # Dmitri Korotkevitch
    a schema:Person ;
    schema:familyName "Korotkevitch" ;
    schema:givenName "Dmitri" ;
    schema:name "Dmitri Korotkevitch" ;
    .

<http://experiment.worldcat.org/entity/work/data/1927210495#Series/expert_s_voice_in_sql_server> # Expert's voice in SQL server.
    a bgn:PublicationSeries ;
    schema:hasPart <http://www.worldcat.org/oclc/883297697> ; # Pro SQL server internals
    schema:name "Expert's voice in SQL server." ;
    .

<http://experiment.worldcat.org/entity/work/data/1927210495#Series/the_expert_s_voice_in_sql_server> # The expert's voice in SQL server
    a bgn:PublicationSeries ;
    schema:hasPart <http://www.worldcat.org/oclc/883297697> ; # Pro SQL server internals
    schema:name "The expert's voice in SQL server" ;
    .

<http://experiment.worldcat.org/entity/work/data/1927210495#Topic/client_server_computing> # Client/server computing
    a schema:Intangible ;
    schema:name "Client/server computing"@en ;
    .

<http://experiment.worldcat.org/entity/work/data/1927210495#Topic/computers_client_server_computing> # COMPUTERS--Client-Server Computing
    a schema:Intangible ;
    schema:name "COMPUTERS--Client-Server Computing"@en ;
    .

<http://experiment.worldcat.org/entity/work/data/1927210495#Topic/computers_databases_general> # COMPUTERS--Databases--General
    a schema:Intangible ;
    schema:name "COMPUTERS--Databases--General"@en ;
    .

<http://experiment.worldcat.org/entity/work/data/1927210495#Topic/computers_programming_languages_sql> # COMPUTERS--Programming Languages--SQL
    a schema:Intangible ;
    schema:name "COMPUTERS--Programming Languages--SQL"@en ;
    .

<http://experiment.worldcat.org/entity/work/data/1927210495#Topic/database_management> # Database management
    a schema:Intangible ;
    schema:name "Database management"@en ;
    .

<http://worldcat.org/isbn/9781430259633>
    a schema:ProductModel ;
    schema:isbn "1430259639" ;
    schema:isbn "9781430259633" ;
    .


Content-negotiable representations

Close Window

Please sign in to WorldCat 

Don't have an account? You can easily create a free account.